Databases Reference
In-Depth Information
compare two sets of data collection, which may not even belong to the same environment.
This may sound appalling but is a harsh reality in the production world scenario where
it is not always feasible to add additional workload or bring in new executables to collect
diagnostic data. Bottleneck analysis helps you arrive at the top
N
bottlenecks that your
SQL Server instance is experiencing by identifying the road-blocks which are preventing the
smooth functioning of your SQL Server instance.
This requires some precise data collection capabilities, along with various other requirements like
knowing which system catalogs to query, what tools to run to collect the required data, etc. The
next few pages in this chapter will help you understand how developing a friendship with SQLdiag
can prove benei cial when a SQL Server crisis arises. The following section will explain how
SQLdiag can be used to coni gure and collect diagnostic data within a few minutes.
GETTING FRIENDLY WITH SQLDIAG
There are two times when you collect data for troubleshooting a SQL Server issue:
➤
You are faced with a critical problem in your production environment and the data collec-
tion is being done to identify and i x the root cause.
➤
You are trying to reproduce an issue for which sufi cient data was not collected initially to
ascertain a dei nitive root cause.
The latter scenario is always a cumbersome process because you might end up playing the wait-
ing game for intermittent issues. The frustration of such waiting for the actual issue to occur can
become more excruciating due to the overhead of managing the data that diagnostics tools collect
when left running.
This is where SQLdiag becomes a SQL Server professional's best friend, living up to the adage that
“a friend in need is a friend indeed.” SQLdiag is a command-line utility that is available by default
in
C:\Program Files\Microsoft SQL Server\110\Tools\Binn
. This assumes that the binary
installation path of the SQL Server instance on the machine was done on the C: drive.
NOTE
If you have SQL Server 2005 or SQL Server 2008/2008 R2 instances
installed on the server, you will i nd the SQLdiag utility available under
the
C:\Program Files\Microsoft SQL Server\90\Tools\Binn
or
C:\
Program Files\Microsoft SQL Server\100\Tools\Binn
folders, respectively.
You can run SQLdiag in three modes:
➤
Snapshot mode using the /
X
command line parameter
➤
As a command-line application by specifying one or more command-line parameters
➤
As a service
Search WWH ::
Custom Search