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
|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.