Tools to troubleshoot SQL Server - part 1

  • Article

Good day, everyone!

With this post I will start a series of posts describing tools which everyone of you can use to troubleshoot SQL Server problems. They are freely available and either come standard with SQL Server distribution or freely downlodable from world wide web. SQL Server Premier Field Engineers at Microsoft worldwide are using these to resolve issues. 

 Here they are:

1. Perfom (perfmon.exe) - Windows veteran executable to collect performance counters

2. SQL traces (server side) and SQL Profiler (located at <drive>:\Program Files\Microsoft SQL Server\<sqlversion>\Tools\Binn\PROFILER.EXE) to collect and analyse SQL server traces and correlate them with performance counters collected via perfmon.

3.  Performance analysis of logs (PAL) a wonderful tool to use to analyze SQL Server performance counter logs against predefined thresholds defined for each Microsoft product by support engineers.

4. SQLDiag.exe - SQL traces and performance counters collection utility. Allows both to be collected together. Comes standard with SQL Server distribution and located at <drive>:\Program Files\Microsoft SQL Server\<sqlversion>\Tools\binn.

5. SQLNexus - a visualizer for output produced by SQLDiag.exe. Shows top N most expensive queries consuming CPU, IO and other resources.  

6. Database Engine Tuning Advisor (DTA) - excellent tool to tune your database against predefined workload. Comes standard with SQL Server distribution and located in <drive>:\Program Files\Microsoft SQL Server\<sqlversion>\Tools\Binn\DTASHELL.EXE

7. Microsoft Best Practices Analyzer for SQL Server (BPA) - gathers data from Microsoft Windows and SQL Server configuration settings. BPA uses a predefined list of SQL Server 2005 recommendations and best practices to determine if there are potential issues in the database environment.

8. SQL Server Database Management Views - virtual tables in SQL Server to provide internal information on the state of the database engine

This is a list only for now. In next posts I will go in more details on them. Obviously, there are 3d-party tools out there but you have to pay for them.

I encourage you to explore these tools and see how you can apply them to your database environment.

This is all for now. Until next time.

Cheers,

-Yuriy