SQLdiag

In the early days of SQL Server, the Microsoft Product Support Services (PSS) team created a tool to give them the information they needed for troubleshooting during support calls. The PSSDiag tool, which is supported in SQL Server 2000 versions before Service Pack 3 (SP3), was so popular that it was released to customers for personal use. Microsoft now ships it as SQLdiag, a native tool in the SQL Server 2005 toolkit.

SQLdiag is a general-purpose diagnostics-collection utility that you can run as a console application or as a service. SQLdiag collects logs and other data, and it's a good idea to know how to use the tool for no other reason than you're likely to be asked to use it to expedite and simplify diagnostic information gathering when you put in a support call to Microsoft Customer Support Services (CSS). But you can use the tool to collect data not only from SQL Server but also from just about any Windows server, and it's useful for monitoring your servers over time or for troubleshooting specific problems with your servers.

Functionality
SQLdiag is fully configurable through a SQLdiag.xml configuration file and can collect a variety of diagnostic information, including Windows performance logs, Windows event logs, SQL Server Profiler traces, SQL Server blocking information, and SQL Server configuration information. You can read about the full functionality and usage of SQLdiag in the SQL Server 2005 Books Online (BOL) entry at http://msdn2.microsoft.com/en-us/library/ms162833.aspx. You can also get help directly from the command prompt by typing PSSDiag.exe /?, which shows you all of the configuration parameters for the program. SQL Server guru Ken Henderson also has a detailed write-up of the tool at http://msdn2.microsoft.com/enus/library/aa175399(SQL.80).aspx. But the tool has some neat, under-the-cover aspects that you can't learn by reading those other articles.

First, SQLdiag can collect non–SQL Server information. Although SQLdiag doesn't let you control custom diagnostics, the Microsoft CSS engineers have 20 or so of their own custom diagnostics (e.g., clustering, performance, merge replication). When you place a support call, the engineer you talk to simply selects a checkbox, and the tool collects all of the needed information for the specific custom diagnostic being run. Although Microsoft doesn't advertise this offering, you can get the custom diagnostic templates for your own use if you have an EA or SA support agreement or a relationship with a Microsoft CSS technical account manager (TAM).

In addition, SQLdiag supports a Generic mode in addition to its SQL Server mode. as you see in Figure 1. SQLdiag also ships with Microsoft BizTalk Server, among other Microsoft products that don't use SQL Server as a back end. Consequently, SQLdiag works with those products by running in Generic mode and collecting a variety of non–SQL Server performance metrics.

Another cool aspect of SQLdiag that's not highlighted in the documentation is that it supports multi-server collections. For example, you could add multiple servers to the INI file (or XML file) by using the machine name. Additionally, SQLdiag lets you pass in a list of servers through the /M switch, which designates a text file containing all of the machine names for machines you want to monitor.

Another powerful SQLdiag feature is the /V switch, which controls command-line variables passed into SQLdiag at run time. The /V %foo%=bar switch gives you the ability to specify one or more variables (e.g., a database name) from the command line in a collection task. You can then use the variable in a script called via OSQL. Using a .tem file lets you parameterize the script as a template (generally located in the PSSDiag/SQLDiag startup folder, although it could be anywhere). The template should contain the USE %foo% command to act on the given database.

Another interesting function of the /V switch is that /V variables can be assigned multiple values and executions. For example, assume that we're running SQLdiag with a variable that has lines A, B, and C in it and the collector will run the task once for each variable value. You could pipe the three values into the variable from the console, or you could redirect to the standard DOS output to create the variable with multiple values. Thus, the tool provides an interesting way to create extended batch management.

Finally, you can extend SQLdiag's functionality by using the upcoming SQLNexus utility, a cool new tool that Ken Henderson previewed at last fall's PASS Summit in Seattle. SQLNexus displays SQLdiag data visually in reports and charts. Watch Henderson's blog at http://blogs.msdn.com/khen1234/default.aspx for the announcement of SQLNexus availability. And be sure to visit the Tool Time online forum at http://www.sqlmag.com/go/tooltime to post your own tool tips, questions, and comments.