SQL Server troubleshooting tools PSSDiag, SQLDiag, SQLNexus, RML Utilities and ReadTrace: which one to choose?
Not alone with SQL Server you would be able to identify the root cause of a performance issue or to monitor any kind of activity.
As you may be aware SYSMON (PERFMON) and PROFILER will help to some extent of finding offending processes on the SQL Server for further fine tuning for queries or tasks. Still there is a need to dig deeper on the troubleshooting tools that are required within your SQL platform irrespective of versions such as SQL Server 2000, 2005 and 2008 (upcoming).
Until the age of SQL Server 2000 there needs to be huge dependancy on third party tools to identify any internal process of Relational engine, that has been changed phenominally from SQL Server 2005 onwards where SQL Server product & support team uses several internally-written utilities that will make their processes easier to work on a typical customer support incident cases. It is a mis-conception that these utilities may only useful for DBAs and not for anyone within the Data platform, it is not quite correct. Most of these utilities would help the Developers & System Administrators who work with or relatedto SQL Server within your environment.
So when we talk about troubleshooting utilities then you might be thinking about Blocker scripts that were provided in good olden SQL Server 2000 days such as sp_blocker_pss80 scripts and procedures, as most of these are applicable to SQL Server 2005 installations too and same kind of information can be obtained by using Blocked Process Report event class in SQL Server Profiler that will capture the information about a task that has been blocked for more than a specified amount of time. Further ReadTrace80 & OSTRESS is released, where ReadTrace80 will be used to process generated trace files by PROFILER & OSTRESS utility is a multithreaded ODBC-based query utility.
Then it comes to PSSDiag utility that has been used within PSS (Customer Support) team in Microsoft as a diagnostic data collector for SQL Server. It can simultaneously collect Perfmon/Sysmon logs, Profiler traces, event logs, SQLDIAG reports, and detailed blocking information. It is commonly used by Microsoft Product Support Services engineers to collect diagnostic data from end-user installations and can also be used by end-users to troubleshoot and monitor their own SQL Server installations. PSSDiag has been released to public to offer further flexibility to the diagnostic tools arena.
Next one in the ranch was SQLNexus tool which was a brian child of SQL Gurus Ken Henderson & Bart Duncan that was offered as a a visualization tool for detecting, analyzing, and troubleshooting problems on SQL Server 2005 database management system (DBMS). This tool uses the SQL Server 2005 diagnostics utility SQLdiag on the back end as its main data collector. Further offers from SQLNexus are not only does it let you run several useful built-in reports, it also lets you customize existing reports and write your own. The tool uses SQL Server Reporting Services (SSRS) to generate reports and charts from the diagnostic data it collects. SQL Nexus then uses SQL Server to collect and aggregate diagnostic information into a DataWarehouse kind of database; you can use this information for long-term diagnostics and performance analysis.
Now recenlty SQL Development team has come up with another utility called RML utilities for SQL Server that is classed as a Precision Performance tool for SQL Server. This RML utilities come up with 2 releases for X86 and X64 based installations, so what kind of answers it can provide means a list of answers are below:
- Which application, database or login is consuming the most resources, and which queries are responsible for that.
- Whether there were any plan changes for a batch during the time when the trace was captured and how each of those plans performed.
- What queries are running slower in today's data as compared to a previous set of data.
Also you can use this utilities to test the system about how it
will behave with some change (different service pack or hot fix build, changing a stored procedure or function, modifying or adding indexes, and so forth)and this will be an added additional advatage to SQL users by using the provided tools to replay the trace files against another instance of SQL Server. If you capture trace during this replay you can use the tools to directly compare to the original baseline capture. Further it has been confirmed that this RML Utilities have undergone with an integration with SQLNexus, SQLDiag, PSSDiag & ReadTrace that were referred above. Also this will supersede the previous release of ReadTrace80 & OSTRESS utilities.
So to download and test this RML utilties within your environment you can download: