Feature-Specific Custom Diagnostics
The Diag Manager also provides you with
some feature-specific custom collectors that can be very helpful when
you are troubleshooting a particular SQL Server scenario or feature.
The Database Mirroring
custom collector collects information from various system DMVs and
catalogs for the database mirroring configuration and status of the
mirrored databases. In addition, the database mirroring collector
connects the database mirroring partner and witness to collect the
configuration data that it collected from the target instance on which
SQLdiag is running. To ensure that all the data is collected, you would
need to ensure that the account running the SQLdiag has permissions to
collect the database mirroring configuration information from the
mirror and witness. Note that there are no new custom collectors
available for collecting data specific to Availability Groups for SQL
Server 2012 as yet.
WARNING The
collector assumes that the account running SQLdiag will be able to log
into all the SQL Server instances as a sysadmin using Windows
authentication.
The Full Text Search
custom collector collects the full-text search configuration and the
full-text catalog details for each database that is full-text enabled.
The Linked Server Configuration
custom collector collects information about the available linked server
providers on the target machine, and configuration information about
all the linked servers configured on the target SQL Server instance.
WARNING The T-SQL scripts used by the aforementioned collectors make use of the xp_cmdshell extended stored procedure, which will result in errors reported in the SQLdiag log file if xp_cmdshell is not enabled on the target SQL Server instance. If xp_cmdshell cannot
be enabled on the target SQL Server instance due to compliance or
security reasons, then the information that couldn’t be collected can
be manually collected.
The SQL 2005 tempdb Space and Latching
custom collector can be used to troubleshoot SQL Server issues
pertaining to tempdb contention. The T-SQL script used by this
collector collects tempdb usage and statistics in a loop during the
entire duration of the SQLdiag data collection. The collector uses the
following DMVs and system catalogs to collect the diagnostic
information:
- sys.dm_db_file_space_used
- sys.dm_db_session_file_usage
- sys.dm_db_task_space_usage
- sys.sysprocesses
- sys.dm_os_waiting_tasks
The SQL Blocking
custom collector enables the trace flag 1222 during the initialization
phase of SQLdiag, which ensures that deadlock information is written to
the SQL Server Error Log. Note that this collector does not collect
blocking information from the target SQL Server instance. Information
about the blocking chains observed need to be captured by using the SQL 2008 Perf Stats custom collector.
The SQL Memory Error
custom collector collects diagnostic information about the target SQL
Server instance’s memory usage, which can be useful while
troubleshooting SQL out-of-memory issues.
WARNING Some
of the data captured by these custom diagnostics will not be captured
when running SQLdiag from a remote machine. This is one of many reasons
why it is recommended to run SQLdiag from the machine on which the SQL
Server instance is installed.
The SQL Agent
custom collector collects all the SQL Server Agent logs and the backup
of the MSDB database from the target SQL Server instance. This can
result in the output folder becoming very large, and can dramatically
increase the time it takes for the SQLdiag shutdown phase, which would
be directly proportional to the size of the MSDB database. It is
probably a good idea to collect the relevant data from the MSDB
database tables if the MSDB database is quite large.
Capturing Extended Events
The XEvents Waits
custom collector enables you to configure Extended Events (XEvents)
data collection on the target SQL Server instance. By default, the
collector has three XEvent sessions for capturing information for page
latch waits experienced on the target instance. You can view
configuration details of the collector by right-clicking on the XEvent
Waits custom collector and clicking Details as shown in Figure 6.
For example, assume there is a need to capture wait information for all PAGEIOLATCH
waits. PAGEIOLATCH_* waits are encountered when the SQL Server database
engine experiences an I/O sub-system related wait condition. Most
commonly high wait-times experienced by these wait types signify
storage sub-system related latencies. Figure 7 shows what the final configuration would look like. This would require the addition of two custom events:
- A startup event that will execute a T-SQL script to create and
start the XEvent session that will capture the wait information for all
PAGEIOLATCH waits
- A utility-type collector that will execute a T-SQL script when the
SQLdiag utility is shut down to collect the data captured by the XEvent
session into an XML file
One of the new additions to SQL Server 2012
Management Studio is the UI for Extended Events. You can use the UI to
configure your custom Extended Events session. Once the session is
configured, it will be available under the Sessions folder, as shown in
Figure 8.
This new addition to Management Studio enables
you to extend your customization options by adding an Extended Event
collection to your SQL Server data collection arsenal. Once you have
configured the required set of events, you can script out the Extended
Event session and use the acquired T-SQL script to set up your own
XEvent collection while configuring a SQLdiag collection using the _MyCollectors custom collector described in the following section.
