Troubleshooting Parameter Sniffing In SQL Server With sp_WhoIsActive

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

Yet Another Post About Local Variables In SQL Server

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

Is RECOMPILE Really That Bad For SQL Server Query Performance?

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?

How Parameter Sensitivity Can Change SQL Server Query Plans And Index Choices

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

Underused sp_WhoIsActive Capabilities For Troubleshooting SQL Server: @get_avg_time

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

Does sp_executesql WITH RECOMPILE Actually Recompile Query Plans In SQL Server?

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?

In SQL Server Stored Procedures, Parameters In TOP Are Sniffed Too

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