In a recent article, Slow in the Application, Fast in SSMS?, SQL Server MVP, Erland Sommarskog, explores the various things that affect Query Plan, compilation of Dynamic SQL and other things that ultimately affect your SQL Server Database Performance. He covers various concepts and scenarios like parameter sniffing, query plan caching, Blocking, saved settings, issues with linked servers and more.
Some interesting quotes from the article -
The parameter values of the first execution of the procedure have a huge impact for subsequent executions. If this first set of values for some reason is atypical, the cached plan may not be optimal for future executions. This is why parameter sniffing is such a big deal.
Occasionally, I see people in the forums or the newsgroups that tell me that their stored procedure is slow, but when they run the same query outside the procedure it's fast.To troubleshoot the query on its own, they have replaced the variables with constants. But as we have seen, the resulting stand-alone query is quite different, and SQL Server can make more accurate estimates with constants instead of variables, and therefore arrive at a better plan.
(For linked servers) what matters is the permissions on the remote server, not the local server where the query runs. Also, the problem is specific when the linked server is another SQL Server instance, and the same problem may not appear when the linked server is Oracle, MySQL or Access.
The article also explains the various ways to locate bottlenecks and potential problems as well as the related SQL queries -
-
Getting Query Plans and Parameters from within Management Studio
-
Getting them directly from the Query Cache
-
Getting them from a Trace
-
Getting Table and index definitions
-
Finding information about statistics
Erland also lists out various ways to fix parameter-sniffing issues like forcing recompilation, reviewing indexing, using OPTIMIZE FOR and finally just fixing Bad SQL, which could apply in different scenarios.
Finally, the article covers Dynamic SQL and how similar principles affect the performance for Dynamic SQL queries. This is an article definitely worth reading for everyone who works with SQL Server, developers and DBAs alike.
