For troubleshooting Performance issue on a SQL Server, profiler and perfmon plays a major role.
By combining both the data, the analysis will be very much easy as we can track the queries which are causing the issue at that part of time.
Here is how we can combine both the data.
To do this, we have to make sure that both the log files are time synched. This is Very Much Important for combining the 2 log files.
First create a perfmon log file by accessing the performance monitor.
1.Click on “Run” and type “perfmon” and click “ok” to open the performance monitor.
2.Create a data collector set with the required counters.
3.Note down the data collector set name and close.
Now to make both the log files time synced, I have created 6 Jobs.
1. “DBA - Start Perfmon” to start the perfmon from a SQL Job using operating system command.
2. “DBA - Stop Perfmon” to stop the perfmon from a SQL Job using operating system command.
3. “DBA - Start Tracing SQL Server” to start the SQL Profiler using the T-SQL Commands.
4. “DBA - Stop Tracing SQL Server” to stop the SQL Profiler using the T-SQL Commands.
5. “DBA - Start Perfmon and SQL Trace” to start both the perfmon and Profiler jobs.
6. “DBA - Stop Perfmon and SQL Trace” to start both the perfmon and Profiler jobs.
The first 4 jobs are called by the 5th and 6th job to make sure that both the log files are in time synced.
You can download the scripts for these jobs from here.
Once you create the jobs, make sure to do the below changes.
1. Change the Data Collector Set Name in “DBA - Start Perfmon” and “DBA - Stop Perfmon”
2. Change the Trace file path in the job “DBA - Start Tracing SQL Server”
3. Add or remove any events as required in the job “DBA - Start Tracing SQL Server”
Once you have done all these changes, now start the job “DBA - Start Perfmon and SQL Trace” which in turn starts the jobs “DBA - Start Perfmon” and “DBA - Start Tracing SQL Server” and captures the data in the respective log files.
After an ample amount of time when you feel that the captured data is enough for your analysis then, start the job “DBA - Stop Perfmon and SQL Trace” which in turn starts the jobs “DBA - Stop Perfmon” and “DBA - Stop Tracing SQL Server” and stops capturing the data.
Once you get the log files,
1. Start SQL Profiler and open the SQL trace file (.trc file) in it.
2. Wait until the SQL Trace file is fully loaded.
3. Then go to File -> Import Performance Data.
4. Choose the Perfmon log file (.BLG, .CSV).
5. A dialog will open asking you to select the counters you would like to display. Select the required Counters and click OK.
6. Now you should get a screen looking similar to the below one.
This article is also available in pdf format for downloading.
Please Click here to get your copy.