We had briefly touched upon the usage of SQLDiag in some of our previous posts including the one here. While working on a client issue this weekend, it was surprising to see that none of the client DBAs were aware of this utility. In prior versions, this utility was called PSSDiag and you can download it from here if you are still using SQL Server 7 or 2000. In SQL Server 2005, this gets installed along with the product itself and is called SQLDiag. You can read more on it in the online version of BOL over here.
Let’s connect to my instance and see how easily we can gather the data for troubleshooting performance issues and what kind of data do we get. As we had noted before in one of the posts, you can use this utility from:
C:\Program Files\Microsoft SQL Server\90\Tools\Binn>sqldiag /?
You can see all the command line options by using the above command. Let’s execute it by just giving the machine name:
C:\Program Files\Microsoft SQL Server\90\Tools\binn>sqldiag /Mdeciphertest 2008/07/07 14:03:28.64 SQLDIAG Collector version 2005.090.3042.00 2008/07/07 14:03:28.64 SQLDIAG c Microsoft Corp. All rights reserved. IMPORTANT: Please wait until you see "Collection started" before attempting to reproduce your issue 2008/07/07 14:03:28.64 SQLDIAG Output path: C:\Program Files\Microsoft SQL Server\90\Tools\binn\SQLDIAG\ 2008/07/07 14:03:28.75 SQLDIAG Collecting from 1 logical machine(s) 2008/07/07 14:03:28.82 deciphertest\* SQL Server version: 9 2008/07/07 14:03:28.82 deciphertest\* Machine name: deciphertest (this machine) 2008/07/07 14:03:28.82 deciphertest\* Target machine is not a cluster 2008/07/07 14:03:28.82 deciphertest\* Instance: (Default) 2008/07/07 14:03:30.75 SQLDIAG Initialization starting... 2008/07/07 14:03:32.93 deciphertest\* MsInfo: Get MSINFO32 2008/07/07 14:03:33.07 deciphertest\* MsInfo: Get default traces 2008/07/07 14:03:33.25 deciphertest\* MsInfo: Get SQLDumper log 2008/07/07 14:03:33.75 deciphertest\* Collecting diagnostic data 2008/07/07 14:03:33.75 SQLDIAG Initialization complete 2008/07/07 14:03:33.75 SQLDIAG Collection started. Press Ctrl+C to stop.
As you can see from above, we started collecting the information on the default instance and it creates a sub-directory in that path called SQLDIAG and all the output is logged into that sub-directory. If nothing is specified, then you will notice that when you run this utility the first time, it unpackages three XML configuration files: SQLDiag.xml, SD_General.xml and SD_Detailed.xml files which are used for the data collection. We can also chose to modify the configuration file SQLDiag.xml (or a better option is to clone the existing default SQLDiag.xml file and name it something else and make modifications there) in order to collect specific data (which is what you would end up doing once you become familiar with the utility and the data set that gets collected – you would use the /I option for specifying an input XML file). You can check the contents of these XML files to become familiar with the data collection that is done. The difference between the SD_General and SD_Detailed files are the verbose events that get collected for SQL Profiler in the detail one like the performance events, SQL events etc..
Now, let’s stop the collection:
2008/07/07 14:05:16.23 SQLDIAG Ctrl+C pressed. Shutting down the collector 2008/07/07 14:05:18.78 deciphertest\* Shutting down the collector 2008/07/07 14:05:18.79 deciphertest\* Getting SQLConfig report(s) 2008/07/07 14:05:44.75 SQLDIAG Ending data collection. Please wait while the process shuts down and files are compressed (this may take several minutes) 2008/07/07 14:05:44.76 SQLDIAG Collection complete. Collector exiting
Now, if we look under the sub-directory, we will see these files:
- Output of MSInfo32.exe (this is just the same output as the system information).
- SQLDumper Error log.
- The log and the control files for the sqldiag process including the environment variables file, the console log file etc.
- sqldiag output text file which has the error logs, the configuration information, output from the DMVs and DMFs, performance counters output etc.
- Default Trace files
We would strongly recommend becoming familiar with the different options of this utility and then start analyzing the contents of the output. This can help in troubleshooting performance issues due to CPU, Memory, bad T-SQL, I/O or blocking issues and is a very handy way of capturing good information in very less time. In upcoming posts, we will also take some of these outputs by creating some scenarios and go into the details of how to use these to troubleshoot performance issues.