SQL Server Diagnostics Best Practices

SQL Server Diagnostics Best Practices

I have already spoken about Free SQL Server tools for analysing SQL Server performance in a previous post. In this post, I am going to talk about a little advanced tools for diagnostics of SQL Server internals.

Pssdiag

PSSDIAG is primarily use by Microsoft SQL Server support team. It is a general purpose diagnostic collection utility that Microsoft Product Support Services uses to collect logs and data files.

With PSSDIAG you can natively collect the following data:

• Performance Monitor logs
• SQL Profiler traces
• SQL Server blocking script output
• Windows Event Logs
• SQLDIAG output

You can customize the data collection by enabling or disabling any of the log types, by changing the sample interval of the blocking script and the Performance Monitor logs, and by modifying the specific events and counters for SQL Profiler and Performance Monitor to capture. PSSDIAG can also run custom utilities or custom Transact-SQL scripts for support cases that require data outside the natively supported diagnostic types.

Download Location:

http://diagmanager.codeplex.com/

Use the Codeplex download for Microsoft SQL Server 2005 and later versions.

Sqldumper

Sqldumper.exe utility is already included with Microsoft SQL Server 2005, Microsoft SQL Server 2008, Microsoft SQL Server 2008 R2, and Microsoft SQL Server 2012. Use the following path to find the application.

SQLServerInstallDrive:Program FilesMicrosoft SQL ServernumberShared

You can find more information on the Microsoft site.

Xperf

Performance Analyser provides many different graphical views of trace data including:

• CPU Sampling
• CPU and Disk utilization by process and thread
• Interrupt service routine and deferred procedure call
• Hard faults
• Disk I/O Detail

Download Location: Windows Performance Toolkit – Xperf

Perfmon

In-built tool in Windows Server. See my Performance Tuning Post for detailed description.

Event Logs

See Microsoft site for detailed information on Event Logs.

Error logs

The SQL Server error log can be found using the Management Studio GUI tool. It contains user-defined events and system events. Error logs can be used to troubleshoot problems related to SQL Server. More information on MSDN.

Cluster Logs

• You need to use the command prompt in Windows to generate the log. Type cluster log /g and hit enter. Passing the parameter g allows you to generate the log.
• You can find the file named cluster.log which will be generated and stored in the %windir%ClusterReports directory on each node of the cluster.

System Health Session

The system_health session is a default Extended Events session with SQL Server 2008 onwards. This session starts automatically when the SQL Server Database Engine starts, and is a lightweight application which runs without any noticeable performance effects. More on MSDN.

Ring buffers

It is a dynamic management object (DMO)

sys.dm_os_ring_buffers

It is one of the best way to help you get quick and accurate determination about SQL Server memory allocation. If you have memory problems this will tell you whether they are coming from the Windows operating system or from SQL Server.

Example:

SELECT *

FROM sys.dm_os_ring_buffers AS Memallocation;

Extended Events

Introduced with SQL Server 2008 without a GUI, and in SQL Server 2012 with a GUI. It is a lightweight application in-built into SQL Server Management studio. Find out more here.