SQL Profiler: Features, functions and setup in SQL Server 2005
SQL Profiler is often considered the best native SQL Server resource because, for one thing, it understands micro-level processing on any SQL Server. Edgewood Solutions' Jeremy Kadlec introduces you to SQL Server 2005 Profiler and explains how to set up a session.
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
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:
|
![]() |
||
4 | Configure SQL Server Profiler Trace Properties – Events Selection Tab:
|
|
||
5 | Additional Configuration – Column Filters
|
|
||
6 | Additional Configuration – Organize Columns
|
|
||
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 GROUP BY DatabaseID -- High CPU usage in the last one hour -- Long duration in the last one hour |
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