Using SQL Server Profiler to capture performance baseline

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

    Requires Free Membership to View

    Login

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 first published in August 2005

Join the conversationComment

Share
Comments

    Results

    Contribute to the conversation

    All fields are required. Comments will appear at the bottom of the article.

    Disclaimer: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.

    Back to top