Database Reference
In-Depth Information
Chapter 27
System Troubleshooting
Things do not always work as expected. System performance can degrade over time when the amount of data and
load increases, or sometimes a server can become unresponsive and stop accepting any connections at all. In either
case, you need to find and fix such problems quickly while working under pressure and stress.
In this chapter, we will talk about the SQL Server execution model and discuss system troubleshooting based on
Wait Statistics Analysis. I will show you how to detect common issues frequently encountered in systems.
Looking at the Big Picture
Even though this chapter focuses on the troubleshooting of database-related issues, you need to remember that
databases and SQL Server never live in a vacuum. There are always the customers who use client applications.
Those applications work with single or multiple databases from one or more instances of SQL Server. SQL Server, in
turn, runs on physical or virtual hardware, with data stored on disks often shared with other customers and database
systems. Finally, all system components use the network for communication and network-based storage access.
From the customers' standpoint, most problems present themselves as general performance issues. Client
applications feel slow and unresponsive, queries time-out and, in some cases, applications cannot even connect to
the database. Nevertheless, the root-cause of the problem could be anywhere. Hardware could be malfunctioning
or incorrectly configured; the database might have inefficient schema, indexing, or code, SQL Server could be
overloaded, or client applications could have bugs or design issues.
■
You should always look at all of the components of a system during troubleshooting and identify the
root-cause of the problem.
Important
The performance of a system depends on its slowest component. For example, if SQL Server uses SAN storage, you
should look at the performance of both the storage subsystem and the network. If network throughput is not sufficient
to transmit data, improving SAN performance wouldn't help much. You could achieve better results by optimizing
network throughput or by reducing the amount of network traffic with extra indexes or database schema changes.
Another example is client-side data processing when a large amount of data needs to be transmitted to client
applications. While you can improve application performance by upgrading a network, you could obtain much
better results by moving the data processing to SQL and/or Application Servers, thereby reducing the amount of data
travelling over the wire.
In this chapter, we will focus on troubleshooting the database portion of the system. However, I would still
like to mention the various components and configuration settings that you should analyze during the initial stage
of performance troubleshooting. Do not consider this list to be a comprehensive guide on hardware and software
configuration. Be sure to do further research using Microsoft TechNet documentation, White Papers, and other
resources, especially when you need to deploy, configure, or troubleshoot complex infrastructures.
