Troubleshooting Methodology and Practices - SQL Server 2012 Internals and Troubleshooting - page 254

Conversely, if the problem happens infrequently or without pattern, a different strategy is required.
Often it isn't possible to start data capture when the problem occurs because events occurring just
before the problem starts may be important. Therefore, consider using a black box circular trace to
enable a continuous lightweight trace that can be stopped when the problem occurs.

Tools and Utilities
The following list summarizes some of the most commonly used data collection tools and analysis
utilities. Subsequent chapters contain additional details covering when and how to use each of these
tools:
➤

PerfMon — Performance Monitor (PerfMon) ships with Windows and can be used to gather
information on server resources and services. It can track serverwide information such as
CPU and memory usage, I/O statistics, and network activity. Several SQL Server-specii c
counters can be useful for various troubleshooting and monitoring scenarios.

➤

Proi ler — SQL Server Proi ler can be used to capture statement-level information from
within the database engine.

➤

XEvents — Extended Events are a lightweight event-driven data-capture feature that can
assist troubleshooting while minimizing the monitoring footprint.

➤

PSSDiag — This is a wrapper around SQLDiag, PerfMon, and other add-ins. SQLDiag
can do anything that PSSDiag can do, but it is not pre-packaged with all the add-ins that
PSSDiag may be coni gured with. PSSDiag is usually coni gured by a Microsoft support
engineer and sent to help troubleshoot a specii c problem. It is specii c to a certain version of
SQL Server, and the add-ins are usually architecture specii c (x86 or x64).

➤

SQLDiag — SQLDiag ships with SQL Server. It can be used to gather basic environmental
information such as the SQL Server Error Logs, Event Logs, and SQL Server coni guration
settings. It can also be used to capture time-synchronized Proi ler and PerfMon information
(see Chapter 11).

➤

Event Logs (Application, System, and Security) — These logs are often useful, displaying
which errors, warnings, and informational messages have occurred in the recent past.

➤

Application logs — If the application instrumentation includes writing Error Log output or
diagnostic information, these logs can be useful for identifying the cause of a problem.

➤

User dumps — If you see an exception in the SQL Server Error Logs, you should also see a
mini-dump i le with the extension .mdmp . This can be used by Microsoft CSS to help
determine why the exception occurred.

➤

NetMon — This is a network sniffer that is used to look at data as it is sent over the
network. It is often used to diagnose connectivity or Kerberos problems.

➤

CMS — Central Management Server is a feature with SQL Server Management Studio and
provides a method of storing your SQL Server registrations in a central database. It can
be useful in a troubleshooting scenario because you don't have to remember specii c SQL
Server instance names and passwords — they are already stored in CMS. In addition, you
can execute commands against groups of CMS servers at once.