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
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
|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
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)'
Select the following event classes:
Cursors, Objects, Security Audit, Stored Procedures, TSQL
|5||Data Columns Tab
Select data values:
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||
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