Subscribe

SQLServer-DBA.com Links

Subscribe to newsletter

Dba_db2_button

Powered by TypePad
How to troubleshoot a slow running query in SQL Server

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.

 

Read More

SQL Server – AUTO CREATE STATISTICS - SQL Server DBA

SQL Server – Find high impact queries with sys.dm_exec_query_stats

SQL Server execution plan as text - SQL Server DBA

SQL Server Query Optimizer and Statistics - SQL Server DBA

SQL Performance tuning - Asking the right question


Author: Jack Vamvas (http://www.sqlserver-dba.com)

******** *******

Subscribe to SQLServer-DBA.com

Latest posts delivered to you daily

Delivered by FeedBurner

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer