Databases Reference
In-Depth Information
➤
Understand the rhythm of the business. This enables you to determine whether the current
workload is typical, a seasonal spike, or an unusual pattern.
➤
Capture any situations when the problem does not occur. Understanding these scenarios can
be useful in rei ning the scope of the problem too.
Part of understanding the problem is understanding why the issue is occurring now. If this is a new
system, perhaps you haven't seen this level of load on the system before. If it is an existing system,
review your change control documents to see what has changed recently on the system. Any change,
even if seemingly unrelated, should be reviewed. This can mean any alteration, no matter how
small, such as a Windows or SQL Server patch, a new policy or removed permission, a coni guration
option, or an application or database schema change.
Isolating the Problem
Are you certain the problem is related to the database tier? How do you know it's a database
problem? Many problems begin life as an application behavior or performance issue, and there may
be other software components or interactions that could affect the database platform.
Once you have a good understanding of the problem, decompose it into manageable elements;
isolating each component enables you to focus on the problem area fast. The intention of this
approach is to eliminate or incriminate each area of the environment. Approach troubleshooting as a
series of mini-experiments, each looking to prove or disprove that a specii c feature or component is
functioning correctly.
The following list describes what to look for when troubleshooting each major problem category:
➤
Connectivity issues —
Does the problem only occur with one protocol, such as named pipes
or TCP/IP? Are some applications, users, client workstations, or subnets able to connect
while others cannot? Does the problem occur only with double hops, whereas direct connec-
tions work? Will local connections work but remote connections fail? Is the problem related
to name resolution (does
ping
by name work)? Could network routing be the issue (check
ping
or
tracert
)? Can you connect using the dedicated administrator connection (DAC)?
Try to connect with SQL Authentication as well as using a domain account.
➤
Performance issues —
For a performance problem you need to determine if the problem is
on the client, the middle tier, the server on which SQL Server runs, or the network. If it is
an application performance problem, it is essential to establish how much time is consumed
in the database tier; for example, if application response time is 10 seconds, is 1 second or 9
seconds consumed by the database response time? Capture slow-running stored procedures,
execute these directly on the server, and coni rm execution times.
➤
Hardware bottlenecks —
Identify resource contention around disk, CPU, network, or mem-
ory. Using wait stats analysis and the tools discussed in this topic, identify the top
N
worst
queries by contended resource (disk, memory, or CPU) and investigate further.
➤
SQL Server issues —
As well as hardware contention, SQL Server has i nite internal
resources, such as locks, latches, worker threads, and shared resources such as tempdb.
Isolate these problems with wait stats analysis and DMVs, then investigate queries that are
causing the resource consumption.
Search WWH ::
Custom Search