In the previous part of this tutorial, I demonstrated the procedure to create & customize the SqlDiag.xml for use with the SQLDiag utility. I began by going through the manual editing process, followed by the graphical utility – SQLDiag Configuration Tool – from CodePlex.

Today, in the final part of the tutorial series on the Profiler, I will demonstrate how to use SQLDiag to consume this modified SqlDiag.xml. In order to prepare for this demo, I have a pre-created SqlDiag.xml file using the SQLDiag Configuration Tool.

Running the SQLDiag utility

For this demo, I will be running the following test workload against my SQL Server. You can notice that the workload involves adding large amounts of data into the SQL Server followed by some reads from this table.

/*******************************************************************************
  WARNING: THIS SCRIPT IS PROVIDED FOR DEMONSTRATION PURPOSES ONLY.
         : THE AUTHOR AND BEYONDRELATIONAL.COM ARE NOT RESPONSIBLE DUE TO ANY
           DAMAGE CAUSED BY MISUSE OF THIS SCRIPT
*******************************************************************************/
USE tempdb
GO

--Set options
SET NOCOUNT ON

--Safety check
IF (OBJECT_ID('#TestWorkload') IS NOT NULL)
	DROP TABLE #TestWorkload

--Declaration/Object creation
CREATE TABLE #TestWorkload (TestId INT IDENTITY(1,1),
                            TestColumn VARCHAR(20) DEFAULT 'Test Entry Dflt'
                           )    
DECLARE @it INT = 0

--Insert some test data
WHILE (@it < 100000)
BEGIN
   IF ((@it % 2) = 0)
   BEGIN
       INSERT INTO #TestWorkload (TestColumn)
       DEFAULT VALUES
   END
   ELSE
   BEGIN
	   INSERT INTO #TestWorkload (TestColumn)
	   SELECT 'Test Entry #' + CAST(@it AS VARCHAR(10))
   END
   
   SET @it += 1
END

--Some Selection
SELECT TestId, TestColumn FROM #TestWorkload WHERE TestColumn LIKE '%9%'
SELECT TestId, TestColumn FROM #TestWorkload WHERE TestColumn LIKE '%dflt%'

--Cleanup
IF (OBJECT_ID('#TestWorkload') IS NOT NULL)
	DROP TABLE #TestWorkload

SQLDiag Input parameter configuration

As mentioned in Books On Line, the SQLDiag accepts many input parameters, the most important ones being:

1. /I – path to the configuration file (typically SqlDiag.xml)
2. /O – Redirects the SqlDiag output to this directory
3. /P – support path, typically the path where SqlDiag.exe resides
4. /M – Machines to diagnose; overrides the machines specified in the SqlDiag.xml/configuration file
5. /B and /E – provide a fixed start & stop analysis time

There are lots of other input parameters, however, for the purposes of this demo, we will keep things simple. We will be stopping the trace manually. Also, we will not be overriding the configuration files and therefore, the only input we need to provide is the configuration file and the output path.

As mentioned in my previous post, the SQLDiag requires that the user be a member of the sysadmin fixed server role at the SQL Server instance being monitored. In addition, the user must also be a local administrator on the server being diagnosed.

1. With a user having both local administrator and sysadmin privileges, login to the server to be diagnosed
2. Using the command prompt, navigate out to the following path:C:Program FilesMicrosoft SQL Server100ToolsBinn
• (The path provided is valid for a default installation of SQL Server 2008. Depending upon your SQL Server version and installation configuration, these paths may differ)
3. Run the SQLDiag utility using the following command line command (file and path names may differ in your case):
• SQLDiag.exe /I SQLDiag100.xml /O "C:Program FilesMicrosoft SQL Server100ToolsBinnSqlDiagOutput"
4. Await indication in green text that SQLDiag is now tracing the system. Ensure that all system information counters are loaded:
5. 
6. Now, let’s go to the SSMS and execute our test workload
7. After execution of the workload, stop the collection by pressing Ctrl+C
8. 
9. Notice that the Profiler trace and the Perfmon trace are now available in the output directory specified in the command line path above:
10. 

All that remains now is to open these using the SQL Server Profiler as demonstrated in tutorial part #6 (SQL Server – Profiler – Part 6 – Correlating the Profiler Trace with Windows Performance Log Dat) of this series.

Alternative tools

SQLDiag itself, although very powerful, is a basic tool. Some of the more advanced tools that offer analytical abilities as well are:

1. PAL (Performance Analysis & Log) tool: http://pal.codeplex.com/
2. SQLNexus: http://sqlnexus.codeplex.com/

Conclusion

This concludes my series on the SQL Server Profiler. Profiling is an art, and I would request everyone to take the time out and practice profiling on your test systems. The Profiler is a double-edged sword. If used incorrectly, Profiler can (and will) result in severe performance issues. If used correctly, it is an excellent debugging and troubleshooting tool, and may also prove to be a lifesaver.

I hope you liked the series. Do leave your valuable feedback, and have a nice day ahead!