I’m going to show you something bizarre. I’m going to show it to you because I care for your well-being and long term mental health.
Read the post: How Parameter Sniffing Impacts Locking In SQL Server
AI Training for Database Teams and SQL Server Performance Tuning
I’m going to show you something bizarre. I’m going to show it to you because I care for your well-being and long term mental health.
Read the post: How Parameter Sniffing Impacts Locking In SQL Server
sp_WhoIsActive can identify parameter sniffing on a live server with three parameters. @get_avg_time shows a query's current duration against its average…
Read the post: Troubleshooting Parameter Sniffing In SQL Server With sp_WhoIsActive
Normally, I link people to this post by Kendra and this post by Paul when I need to point them to information about what goes wrong with local variables.
Read the post: Yet Another Post About Local Variables In SQL Server
It’s been a while since SQL Server has had a real RECOMPILE problem. And if you put it up against the performance problems that you can hit with parameter…
Read the post: Is RECOMPILE Really That Bad For SQL Server Query Performance?
Startup Expression Predicates can be helpful. They may also exacerbate parameter sniffing issues in similar ways to IF branches.
Read the post: Startup Expression Predicates And Parameter Sniffing In SQL Server
Parameter sniffing gets blamed for a lot of things, and, well, sometimes it is parameter sniffing.
Read the post: It’s Not Always A Parameter Sniffing Problem In SQL Server
In SQL Server 2019, a few cool performance features under the intelligent query processing umbrella depend on cardinality estimation.
Read the post: SQL Server 2019: Are Query Plans For Big Values Better For Performance
Rounding out a few posts about SQL Server’s choice of one or more indexes depending on the cardinality estimates of literal values.
Read the post: How Parameter Sensitivity Can Change SQL Server Query Plans And Index Choices
Adding a missing index request can make an unrelated query much slower. In my demo, sp_BlitzIndex showed a missing index on parent ID, creation date and…
Read the post: How SQL Server’s Missing Index Requests Can Hurt Performance
Optimizing every parameter sensitive query for a big value is a bad idea because worker threads and query memory are hard limits, so bigger plans mean…
Read the post: Why Not Just Go For The Big Plan To Improve SQL Server Query Performance?
I’m going to use a funny example to show you something weird that I see often in EF queries.
Read the post: Common Parameter Sniffing In Entity Framework Queries For SQL Server
A while back, I put together a pretty good rundown of this on the DBA Stack Exchange site.
Read the post: What Parameter Sniffing Looks Like In SQL Server’s Plan Cache
Like most tricks, this has a specific use case, but can be quite effective when you spot it.
Read the post: Are Self Joins Ever Better Than Key Lookups In SQL Server?
Look, it’s impressive enough that sp_WhoIsActive works as well as it does. Most people who’ve been using it for a while are familiar with using @get_plans…
Read the post: Underused sp_WhoIsActive Capabilities For Troubleshooting SQL Server: @get_avg_time
This’ll give us the key lookup plan you see above. If I re-run the query and use the 2010-12-30 date, we’ll re-use the key lookup plan.
Read the post: Does sp_executesql WITH RECOMPILE Actually Recompile Query Plans In SQL Server?
When I blogged about passing parameters to TOP, we ran into a problem that has many possible solutions.
Read the post: What Happens When You Don’t Parameterize Dynamic SQL In SQL Server?
A parameter passed to TOP is sniffed and cached like any other stored procedure parameter, so the first execution decides the plan for every later one.
Read the post: In SQL Server Stored Procedures, Parameters In TOP Are Sniffed Too
Copyright © 2019-2026 Darling Data