(SQL) Tip of the Day: Four phases of performance troubleshooting

  • Article

Today’s (SQL) Tip…

There are four distinct phases to SQL performance troubleshooting.  If you stick to these phases, you can resolve almost any performance issue.

Phase 1:  Identify the top resource bottleneck.

  • There are only four hardware bottlenecks and just a handful of software specific bottlenecks that one will encounter when working with SQL in the cloud.  Use the query below to see the top wait types for a database.  You will need to take a "delta" over a period of time to see which changes the most in order to avoid misleading data.
    • select * from sys.dm_os_wait_stats
  • This article identifies each wait type and the possible resource (CPU, Memory, Hard Drive, Networking, etc.) that it may indicate as a potential bottleneck.

Phase 2:  Once the top bottleneck resource area has been identified, investigate the top contributor(s) to said bottleneck.

  • Use a query such as the following one to see current sessions that are waiting on the particular wait type identified in phase 1.
    • select * from sys.dm_exec_requests
  • You can sort the output by CPU, Reads/Writes, etc. depending upon which bottleneck resource you're investigating.

Phase 3:  Identify WHY these sessions are contributing to this bottleneck

  • Use the query below with the SQL handle acquired from phase 2 to see the actual query running on the identified session
    • select * from sys.dm_exec_sql_text(<sql_handle>)
  • You can also investigate the query plan with the below query and the plan handle identified in phase 2 to see where most of the time is spent.  (Hint:  Look for higher cost operators.)
    • select * from sys.dm_exec_query_plan(<plan_handle>)

Phase 4:  Identify how to address, work around, or eliminate the WHY identified in phase 3

  • This is really a case-by-case basis.  Do you tune the query with indexes?  Do you need this query at all?  Does it really need to run as often as it does?  The first three phases will give you an idea of the proper answers to provide in phase 4.