Troubleshooting Performance Problems in SQL Server 2008

  • Article

Sometimes a poorly designed database or a system that is improperly configured for the workload can cause the slowdowns in SQL Server. Administrators need to proactively prevent or minimize problems and, when they occur, diagnose the cause and take corrective action. Microsoft has released a white paper that provides step-by-step guidelines for diagnosing and troubleshooting common performance problems by using publicly available tools such as

  • SQL Server Profiler,
  • Performance Monitor,
  • Dynamic management views, and
  • SQL Server Extended Events (Extended Events) and the data collector, which are new in SQL Server 2008.

 

This document is available for download at https://download.microsoft.com/download/D/B/D/DBDE7972-1EB9-470A-BA18-58849DB3EB3B/TShootPerfProbs2008.docx

 

Use this document to identify:

  • Resource Bottleneck
    • CPU Bottleneck
    • Memory Bottleneck
    • I/O Bottleneck
  • tempdb
    • Because there is only one tempdb for each SQL Server instance, it can be a performance and a disk space bottleneck. An application can overload tempdb through excessive DDL or DML operations and by taking too much space. This can cause unrelated applications running on the server to slow down or fail.
  • Slow-Running queries

There can be many reasons for this like:

  • Changes in statistical information can lead to a poor query plan for an existing query.
  • Missing indexes can force table scans and slow down the query.
  • An application can slow down due to blocking even if resource utilization is normal.
  • Excessive blocking can be due to poor application or schema design or the choice of an improper isolation level for the transaction.

The causes of these symptoms are not necessarily independent of each other. The poor choice of a query plan can tax system resources and cause an overall slowdown of the workload. So, if a large table is missing a useful index, or if the query optimizer decides not to use it, the query can slow down; these conditions also put heavy pressure on the I/O subsystem to read the unnecessary data pages and on the memory (buffer pool) to store these pages in the cache. Similarly, excessive recompilation of a frequently-run query can put pressure on the CPU.