It is so common to experience the occasional slow down of a SQL Server database. A poorly designed database or a system that is improperly configured for the workload are but several of many possible causes of this type of performance problem. Administrators need to proactively prevent or minimize problems and, when they occur, diagnose the cause and take corrective actions to fix the problem. This paper provides step-by-step guidelines for diagnosing and troubleshooting common performance problems by using publicly available tools such as SQL Server Profiler, System Monitor, and the new Dynamic Management Views in SQL Server 2005.
The paper explores several areas, but some of the key points that it touches upon include:
Which DMVs to use and when
Sample queries that go beyond the DMVs
Stored procedures that give you additional insight including:
Performance Monitor counters to use
Setting and using “Blocked process threshold”
There can be many reasons for a slowdown in SQL Server. We use the following three key symptoms to start diagnosing problems.
Resource bottlenecks: CPU, memory, and I/O bottlenecks are covered in this paper. We do not consider network issues. For each resource bottleneck, we describe how to identify the problem and then iterate through the possible causes. For example, a memory bottleneck can lead to excessive paging that ultimately impacts performance.
Tempdb bottlenecks: Since there is only one tempdb for each SQL Server instance, this can be a performance and a disk space bottleneck. A misbehaving application can overload tempdb both in terms of excessive DDL/DML operations and in space. This can cause unrelated applications running on the server to slow down or fail.
A slow running user query: The performance of an existing query may regress or a new query may appear to be taking longer than expected. There can be many reasons for this. For example:
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, for example, can be due to poor application or schema design or choosing 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 the query optimizer decides not to use it, this not only causes the query to slow down but it also puts 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 running query can put pressure on the CPU.
Filed under: ADMINISTRATION