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 Jeremy Kadlec - Performance Tuning View query execution plan in SQL Server with this method Adding non-clustered indexes to SQL Server columns Change collation to SQL Server 2005 after upgrading databases Is my SQL Server 2000 optimized? What impact does changing one record have on the database? Is there a maximum number of sockets that SQL can handle? Why is my SQL Server frequently corrupted? How to speed up a composite nonclustered index-based query Can I change the date format when moving a flat file to SQL? What's the best way to page SQL Server data results? SQL Server performance and tuning Tips for tuning SQL Server 2005 to improve reporting performance SQL Server errors, failures and other problems fixed from the trenches SQL Server database design disasters: How it all starts Can you shrink your SQL Server database to death? Parent-child dimensions in SQL Server 2005 with Analysis Services MDX SQL Server database design disasters: What not to do Tuning SQL Server performance via memory and CPU processing Troubleshoot Web service issues in SQL Server 2005 Reporting Services Ordering the results of a SQL query Configuring SQL Server with a changed computer name SQL Server overview Storing and searching for image files in SQL Server Can you shrink your SQL Server database to death? Physical data storage in SQL Server 2005 and 2008 SQL Server 2008 data types: Datetime, string, user-defined and more SQL Server and data manipulation in T-SQL Supertype and subtype tables in SQL Server Enforcing data integrity in a SQL Server database SQL Server database design disasters: What not to do Using SQL Server datetime functions GETDATE, DATENAME and DATEPART Tuning SQL Server performance via memory and CPU processing SQL Server overview Research 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.