08 November,2012 by Jack Vamvas
“ I have a slow running query , what steps can I take to speed up the query and achieve an optimised execution plan?” .
Troubleshooting database server performance is an in-depth topic. A methodical and repeatable approach solving the root cause is the perfect situation for any DBA. There are different approaches to SQL Server Tuning. In reality , the root cause may not be a SQL query issue , it may be SAN , SQL Server configuration issue or workload has suddenly increased.
But of course, you’re under pressure and the query is causing delays for the end users.The steps below expand on earlier post :SQL Server Rapid Tuning, providing a very simple emergency approach.
The steps below are a straightforward approach to troubleshooting sub optimal SQL Execution Plans. Follow the steps and repeat as necessary.
Step 1) Statistics – Is Auto Create and Update Statistics Enabled? If Auto Create statistics is disabled , this may indicate out of date statistics. If Disabled, than proceed to Update Statistics – use sp_updatestats to update all statistics on the database. Run the query and check if any improvement.
Step 2) If Auto Create and Update Statistics is Enabled, Identify the longest running queries or highest impact queries .( If the queries have high CPU usage go to step 6). Long duration and highest IO should be a priority.
Step 3) Place every query in the SSMS and analyse the execution plan. First – check for tables or index scans. If large table \ index scans are occurring – progress with Query Analysis. The Query Analysis should ask questions such as : Are all JOINS valid ? Are the JOINS returning excessive data ? Search argument validity? Functions in predicate?
Step 4) If no table or index scans exist and the query is complex , for example a large transaction managing a booking process – check for : excessive joins, temp tables, DDL changes, sub – queries , no set based approach to writing the queries. Review the query , break it down into smaller parts or analyse the JOINS to invoke a new execution plan , ensuring a similar transaction integrity is retained
Step 5) If the query is simple and no no index \table scans exists and executing in SSMS responds with acceptable performance - analyse the Application and how it processes the resultset. Ask the right question a) are just relevant results returned? Talk to the application developers.
Step 6) If the query is no faster in SSMS , more complex query tuning is required, research other methods or contact a performance tuning expert
Step 7) If in Step 2 you identified queries with high CPU usage , analyse in SSMS for Hash Joins, Sorts, Filters. If any of these exists , progress with Query Analysis.
Step 8) Repeat until the problem disappears.