SQL SERVER – 3 Questions Answered on One Query Many Plans

2 years ago

Yesterday I published a video One Query Many Plans – SQL in Sixty Seconds #171 and I received lots of questions on the topic. I have compiled all the questions and decided to answer the top 5 asked questions in this blog post.

Q1: Why is it difficult to tune a query if it has multiple plans?
A1: It is never difficult to tune any query in SQL Server. The challenge here is not to tune the query but the challenge is to find the query to tune. Whenever we are looking for the query to tune, we often look for the query which has executed the most time. In this case, when a single query has many different plans, they are all spread across buffer cache. This makes it difficult to identify if the query which is expensive and has run the most.

Q2: Where should one start to tune SQL Server?
A2: You should start with SQL Wait Statistics. I believe wait statistics gives you a comprehensive view inside your performance bottleneck of SQL Server. Once you identify your biggest bottleneck, you can gradually move to the other area of query tuning. Here is a SQL in Sixty Seconds video on the topic of Wait Stats and also you can read the Wait Stats blog series which I have written on this topic.

Q3: Is there any way to look into memory plan cache and find queries using indexes?
A3: I was surprised to see this question asked by many. Yes, it is possible to look into the memory plan cache and find queries using indexes. I have previously blogged about it over here: How to Find Queries Using an Index From SQL Server Plan Cache? – Interview Question of the Week #195.

Well, that’s it for today. If you liked this video, please do not forget to subscribe to my YouTube Channel – SQL in Sixty Seconds.

Here are my few recent videos and I would like to know what is your feedback about them.

Reference: Pinal Dave (http://blog.SQLAuthority.com)