SQL Profiler: Features, functions and setup in SQL Server 2005

Trying to find the proverbial needle in the haystack of your SQL Server transactions is no small task. SQL Server...

Profiler not only helps you find that needle, it gives you details on all the other needles in a single interface. Profiler is truly the best native SQL Server resource to understand micro-level processing occurring on any SQL Server. The data captured by this graphical event-monitoring tool is as valuable to a DBA as the business-related data captured by SQL Server Analysis Services is to analysts.

As you begin to upgrade your servers -- and your skills -- from SQL Server 2000 to 2005, you will be happy to know that there is a low learning curve from a functional perspective and many new features to leverage. An added bonus is you can leverage SQL Server 2005 Profiler against SQL Server 2000 servers. Hence, learning the new interface SQL Server 2005 interface becomes important for your current servers and allows you to take advantage of exciting new opportunities available with SQL Server 2005.
 

TABLE OF CONTENTS    New features in SQL Server 2005 Profiler    Tried-and-true features in SQL Server 2005 Profiler    How to set up a SQL Server 2005 Profiler session

 
New features in SQL Server 2005 Profiler
Although the interface for SQL Server 2005 Profiler was not changed as dramatically as the interfaces for Enterprise Manager and Analysis Manager, which were merged into Management Studio, the subtle features are beneficial and ease some historical performance-tuning challenges. These features include:

• A single interface with all options to configure the Profiler session:
  • Trace Events, Columns, Filtering, etc.

• New profiling events:
  • Service Broker, CLR, Full Text, etc.

• The ability to profile SQL Server 2005 Analysis Services, which has been a black box with earlier versions of SQL Server.
  • This topic will be covered in a future tip.

• Integration between SQL Server Profiler data and Performance Monitor data into a single interface to correlate the macro- and micro-level data
  • This topic will also be covered in a future tip.

 
Tried-and-true features in SQL Server 2005 Profiler
Many SQL Server 2000 Profiler features are back by popular demand -- keeping the learning curve to a minimum -- and new features are available to quickly leverage the technology.

• Authentication: Remains the first interface when the application is started, but now has options for connecting to Analysis Server.

• Data capture: Enables you to write to a table, record on the screen or save to a text file.

• Graphical user interface and T-SQL commands: Enable you to run Profiler interactively on your desktop or on a predefined schedule with SQL Server Agent.

• T-SQL interface: Allows you to schedule Profiler to execute on a regular basis and write the data for future analysis.
  • sp_trace_create
  • sp_trace_generateevent
  • sp_trace_setevent
  • sp_trace_setfilter
  • sp_trace_setstatus

 
How to set up a SQL Server 2005 Profiler session
Although on the surface SQL Server 2005 Profiler has not changed dramatically, you may still need help setting up a Profiler session. Here I'll outline several steps to get you up and running. The final step includes sample T-SQL queries to analyze the SQL Server Profiler results.

SQL Server 2005 Profiler session setup
ID	Directions	Screen Shot
1	Start SQL Server 2005 Profiler via the GUI interface by navigating to Start | All Programs | Microsoft SQL Server 2005 | Performance Tools | SQL Server Profiler.
2	Start a new Trace session by selecting the 'File' menu and the 'New Trace' option. Once the 'Connect to Server' interface loads, select the 'Type' as either 'Database Engine' or 'Analysis Services'. For this tip we will use the 'Database Engine' option. Then select the 'Server Name' followed by the 'Authentication' type. Finally, press the 'Connect' button to start the configuration.
3	Configure SQL Server Profiler Trace Properties – General Tab: Trace Name: Specify a meaningful name for the session Use the template: A total of eight templates are available with predefined events selected For this tip we will use the 'Standard (default)' template Save to file or Save to table: To retain a copy of the data save the results to either a database table or Windows file. For this tip we will save the results to the dbo.TraceResults table in my user defined database.
4	Configure SQL Server Profiler Trace Properties – Events Selection Tab: Review the specific events and select the needed check boxes to capture the desired data. Show All Events: Select this check box to see all events that Profiler will be able to capture. Show All Columns: Select this check box to see all columns that Profiler will be able to capture.
5	Additional Configuration – Column Filters Specify filters based on the columns that are selected for the session to limit the data.
6	Additional Configuration – Organize Columns Specify the column order as well grouping settings for the final data.
7	To start the session, press the 'Run' button on the Trace Properties interface.
8	Review the results that are captured in the Profiler interface.
9	Sample T-SQL queries to analyze the SQL Server Profiler results.	-- Total transactions per database in the last one hour SELECT COUNT(*) AS 'Total Records', DB_NAME(DatabaseID) AS 'Database Name' FROM dbo.TraceResults (NOLOCK) WHERE StartTime BETWEEN DATEADD(Hour, -1, GETDATE()) AND GETDATE() GROUP BY DatabaseID ORDER BY DatabaseID GO -- High CPU usage in the last one hour USE AdventureWorks GO SELECT * FROM dbo.TraceResults (NOLOCK) WHERE CPU > 5000 AND StartTime BETWEEN DATEADD(Hour, -1, GETDATE()) AND GETDATE() GO -- Long duration in the last one hour USE AdventureWorks GO SELECT * FROM dbo.TraceResults (NOLOCK) WHERE Duration > 10000000 AND StartTime BETWEEN DATEADD(Hour, -1, GETDATE()) AND GETDATE() GO

Conclusion

SQL Server 2005 Profiler remains the primary tool for capturing performance metrics at a transaction level in SQL Server. Although all systems experience performance problems, hopefully the problem is not a needle in a haystack and you will be able to easily find and correct your performance issues. 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.

More information from SearchSQLServer.com

• Checklist: SQL Server performance-tuning checklist
• Webcast: SQL Server performance tuning myths webcast
• Step-by-Step Guide: Hunt down SQL Server performance problems