A single transaction in SQL Server is the lowest common denominator for performance tuning. When it comes to capturing and analyzing data at this level, the best native tool is SQL Server Profiler. Analyzing the results from SQL Server Profiler enables you to discern a great deal of information about your SQL Server; the collected data reflects the actual processing on the server, not just theory or conjecture.
From a performance-tuning perspective, Profiler gives you the option to filter data and then store it in a table for additional analysis. In a previous tip, SQL Profiler: Features, functions and setup in SQL Server 2005, I outlined the steps in a sequential manner. Here I will focus on how to analyze Profiler results in order to find opportunities for query performance improvements in two common scenarios. The first is based on a single user's issues, and the second establishes key SQL Server query performance issues.
[TABLE]
[TABLE]
As SQL Server Profiler captures user transactions, the results are stored in a table with a maximum of 41 columns. Some of the columns have Profiler system-related data, but most have valuable raw data that can be queried and analyzed to determine areas for improvement. Before analyzing the two scenarios, the table below outlines a subset of the columns that are key to the data analysis.
[TABLE]
[TABLE]
A common scenario is a user or department of users experiencing an unknown performance issue. Two options are available to capture the needed data for analysis. The first is to filter the data initially captured by Profiler to only capture a specific user's transactions for a finite period of time. The advantage is a small set of data that could be manually reviewed. The disadvantage is that this data would not show how another process impacts the user. The second option is not to limit the data captured via filtering in
To continue reading for free, register below or login
To read more you must become a member of SearchSQLServer.com
Profiler. This gives you the ability to capture and subsequently query the data in order to review all data associated with and potentially impacting a user's session.
[TABLE]
Note: In the code snippets above, replace the FROM clause, i.e., [ProfilerTest].[dbo].[ProfilerResults] with the database, schema and table where you captured your results. In addition, replace the 'Domain\User' with the associated user's value.
[TABLE]
The second analysis is much broader and requires that you determine the queries where potential issues could occur.
[TABLE]
Note: In the code snippets above, replace the FROM clause, i.e., [ProfilerTest].[dbo].[ProfilerResults] with the database, schema and table where you captured your results.
[TABLE]
Based on your analysis, apply the following rules to improve the query performance:
Starting off, you may need to chip away at the statements one at a time, but over time you will begin to find recognizable patterns with similar solutions yielding a significant performance improvement.
For more help, check out our FAQ: SQL Server query errors and explanations.
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
Tip: SQL Profiler: Features, functions and setup in SQL Server 2005
Tip: Using SQL Server Profiler to capture a performance baseline
Topic: Research performance and tuning best practices
