Using SQL Server Profiler to capture performance baseline

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	Amend the scripts to remove any unneeded statements Duplicate particular portions for additional testing Code scripts to back out any inserted, updated or deleted transactions 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.

Rate this Tip To rate tips, you must be a member of SearchSQLServer.com. Register now to start rating these tips. Log in if you are already a member. Digg This!     StumbleUpon     Del.icio.us    RELATED CONTENT Microsoft SQL Server Performance Monitoring and Tuning Using traces in SQL Server Profiler SQL Server Mailbag: CALs, witnesses and unwanted changes SQL Server Mailbag: Data restoration and DB property management Working with IntelliSense in SQL Server 2008 Management Studio SQL Server Mailbag: Stored procedures, triggers and SSRS reports Troubleshooting Distributed Transaction Coordinator errors in SQL Server Clearing the Windows page file and its effect on server performance Optimizing SQL Server indexes –- even when they're not your indexes Performance implications of transaction log autogrowth in SQL Server The short course on how SQL Server really works RELATED GLOSSARY TERMS Terms from Whatis.com − the technology online dictionary contiguity  (SearchSQLServer.com) contiguous  (SearchSQLServer.com) drilldown  (SearchSQLServer.com) hashing  (SearchSQLServer.com) hybrid online analytical processing  (SearchSQLServer.com) RELATED RESOURCES 2020software.com, trial software downloads for accounting software, ERP software, CRM software and business software systems Search Bitpipe.com for the latest white papers and business webcasts Whatis.com, the online computer dictionary 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.