Problem solve Get help with specific problems with your technologies, process and projects.

Using SQL Server Profiler to capture performance baseline

SQL Server Profiler allows you to capture and replay processes following a system change so you can stay on top of performance issues and develop a performance baseline. Edgewood Solution's Jeremy Kadlec explains how in this tip.

Don't just release new software and hope for the best. Contributor Jeremy Kadlec explains the importance of establishing...

a SQL Server performance baseline in this three-part series. In part one, Develop a performance baseline he offers a base set of information for troubleshooting a SQL Server performance issue as compared to normal operations. In part two, Top 10 Performance Monitor counters get 10 recommended counters to help you capture a baseline to use as a comparison when researching a system issue. In this final tip, Kadlec discusses how to capture a performance baseline with SQL Server Profiler.


Information from Performance Monitor can assist in troubleshooting from a macro level, SQL Server Profiler can be used to capture a typical set of processes from one user on your system to replay the transactions if an issue is occurring or following a change to the system.
 

 

 

SQL Server Profiler -- Capture Performance Baseline

ID Description Screenshot
1 Start SQL Server Profiler Navigate to Start | All Programs | Microsoft SQL Server | Profiler
2 Start a New Trace Navigate to File | New | Trace
Authenticate to the necessary SQL Server via the Windows or SQL Server Login
3 General Tab

Type in the 'Trace Name'

For the template name, use the SQLProfilerStandard

Save the results to a file via the 'Save to file' parameter

Expand the value for 'Set maximum file size (MB)'

4 Events Tab
Select the following event classes:
Cursors, Objects, Security Audit, Stored Procedures, TSQL
5 Data Columns Tab

Select data values:
EventClass,
DatabaseName,
HostName, ObjectName,
TextData,
ApplicationName, NTUserName,
LoginName, CPU,
Reads, Writes, Duration, SPID

6 Filters Tab

On the Filters tab apply entries for the application name, user name, host name, etc. to only capture transactions from a single user conducting the testing

Exclude the system ID's

Press the 'Run' button to capture the results and ask the user to begin to use the application

7 Post transaction collection steps
  1. Amend the scripts to remove any unneeded statements
  2. Duplicate particular portions for additional testing
  3. Code scripts to back out any inserted, updated or deleted transactions
  4. Save the file to a centralized location


 

Take some time now to build a performance baseline to validate SQL Server's performance following a change to the system. The time to capture the metrics can be minimal and you'll reap benefits by validating that the system is performing properly. So the next time you're asked how the system is running, say "Let me check the baseline," and see how your users respond. Good luck!

About the author: Jeremy Kadlec is the principal database engineer at Edgewood Solutions, a technology services company delivering professional services and product solutions for Microsoft SQL Server. He has authored numerous articles and delivers frequent presentations at regional SQL Server users groups and nationally at SQL PASS. Kadlec is the SearchSQLServer.com Performance Tuning expert. Ask him a question here.
 

This was last published in August 2005

Dig Deeper on Microsoft SQL Server Performance Monitoring and Tuning

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

-ADS BY GOOGLE

SearchBusinessAnalytics

SearchDataCenter

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close