It’s really important for a DBA to identify and tune the queries so that applications can perform at their best. If you are interested to learn the query tuning, you should at least have the understanding of the followings:
- Understand the phases of query processor/optimizer
- Purpose of query optimizer
- Types of execution plans (Estimated Vs Actual)
- Various execution plan operators like Index seek/scan, Joins, conversions , Compute scalar etc.
- Cardinality estimation
- Understand the purpose of data distribution in the table
- Various DMVs to understand the above information
Based on my experience, the query tuning world revolves around the above topics. If you manage to get a good understanding on the topics, you have already learnt why the query is performing slow. Rest all is an art which one learns with the experience.
We tried to cover these topics briefly during our SQL Server Delhi NCR community event as well. Though these topic were really big but we tried to share our experience on the field and information about must- knows on the above topics.
There are two approaches which we discussed in the event:
1. Proactive approach : This approach is really helpful to reduce the chances of major performance bottlenecks and help to understand the root cause of the ones , if occur. MDW is a great tool which really undervalued. I have personally implemented at many customers’ environments and it’s been really helpful to baseline the workload and identify the top bottlenecks and their causes. For more info on this, read this post
if we understand the system really well, we can identify the cause of the issues quickly. Generally, when the questions like ‘The application was working fine last month and suddenly performance started degrading’ arise we have no answers on what would have gone wrong. But if we have the performance baselining of good scenario , we can simply have a comparison the current state and identify the difference.
Moreover, optimal indexing plays a great role for the optimal performance. Many DBA’s tend to ignore this piece and work randomly on indexing when missing indexes warnings are seen in the execution plans. But as a proactive measure, if we can chuck out unused/duplicate indexes from the database – it really helps with optimal DDL and DML operations. This post is really helpful to find out such information from your server.
In my first post of this series, I have discussed on how to get the top resource intensive queries using query/plan hash. Once we have this information, we can simply start tuning the query after seeing the IO/Time statistics and execution plan of the query.
Reactive approach: This approach is generally followed when the issue is currently happening and we need to resolve the issue as quickly as possible. In this approach, we first try to find out the cause of contention which may be IO/CPU or memory. Once we identify the cause of contention, we need to pick up the queries accordingly.
If the cause of contention is CPU, steps mentioned in this post can be followed. If it’s memory/IO , we need to focus on high logical read queries and try to tune them. In my first post of this series, I have shared the queries to be used to identify the top logical read queries. Once we have those queries, we need to identify the IO/Time statistics along with their execution plans. As we know, all the DMV share the compile time plan and for run time execution plan , we need to run the query.
Once we have these statistics, my approach is as follows:
1. Look at the IO statistics and identify which table has the highest logical/physical reads
2. Look at the time statistics and see how long did it take to compile the query. Compile time may be high for the really big reporting queries.
3. Once the table with highest logical reads is identified – look at the execution plan with the highest cost in percentage which will mostly be around the operations on the same table.
4. Look and fix for the Index/table scan or key lookup in the execution plan
5. Look for the estimated and actual number of rows in the operators. If the actual rows are higher than the estimated rows – check the statistics of the table/columns
6. Look for the warnings in the plan for hash/sort/missing statistics or missing indexes
All the above mentioned steps will resolve 40-50% of the cases. Generally, indexing plays a vital role and fixing that itself will help to make the query run faster. When nothing of the above helps, then we need to go a bit deeper which is: Cardinality estimation which really depends on the data distribution of the table.
Majority of the query issues are caused by wrong cardinality estimation. Once we identify the issue with cardinality estimation, we need to dig and find out what could be the issue. Always try to calculate , how number of estimated rows were identified by query optimizer as there is a formula/reason behind every such calculation.
There are two really great videos from PASS 2014 which really talk about major execution plan issues and the cardinality estimation. If you go though them, you will understand the logic behind this discussion.
Various Query Execution plan patterns:
This post covers the summary of Delhi NCR community December event session 2 – Learn SQL Query tuning