Tracking query execution with SQL Server 2005 Profiler
Jeremy Kadlec, Edgewood Solutions
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
Premium Access
Register now for unlimited access to our premium content across our network of over 70 information Technology web sites.
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States.
Privacy
This was first published in March 2006
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.
SQL Server Profiler results
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.
| ID |
Column |
Data Type |
Description |
| 1 |
EventClass |
int |
Classification used by Profiler to identify a super class for the row and/or overall
transaction |
| 2 |
TextData |
ntext |
T-SQL statements issued |
| 3 |
ApplicationName |
nvarchar(128) |
Registered application in SQL Server |
| 4 |
NTUserName |
nvarchar(128) |
Windows user without the domain |
| 5 |
LoginName |
nvarchar(128) |
The Windows or SQL Server login |
| 6 |
cpu |
int |
CPU usage for the statement or transaction |
| 7 |
Reads |
bigint |
Number of reads performed by the statement or transaction |
| 8 |
Writes |
bigint |
Number of writes performed by the statement or transaction |
| 9 |
Duration |
bigint |
Elapsed time by the statement or transaction |
| 10 |
SPID |
int |
System process identifier in SQL Server |
| 11 |
StartTime |
datetime |
Start time for the statement or transaction |
| 12 |
EndTime |
datetime |
End time for the statement or transaction |
| 13 |
DatabaseID |
int |
Database identifier where the transaction or statement is performed, which is the value from
the sys.databases.database_id view |
| 14 |
ObjectID |
int |
Database object identifier for the transaction or statement, which is the value from the
sys.all_objects view |
| 15 |
DatabaseName |
nvarchar(128) |
Database name where the transaction or statement is being performed, which is derived from the
sys.databases view |
| 16 |
HostName |
nvarchar(128) |
Host name where the transaction or statement is originating |
| 17 |
NTDomainName |
nvarchar(128) |
Domain name where the transaction is originating |
| 18 |
ServerName |
nvarchar(128) |
SQL Server name and instance name |
| 19 |
IndexID |
int |
Index identifier used for the table |
| 20 |
RowCounts |
bigint |
Number of rows returned by the statement or transaction |
Scenario 1: Single user analysis
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 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.
| CPU |
Duration |
-- Top 10 statements by CPU usage
SELECT TOP 10 *
FROM [ProfilerTest].[dbo].[ProfilerResults]
WHERE LoginName = 'Domain\User'
ORDER BY CPU DESC |
-- Top 10 statements by duration
SELECT TOP 10 *
FROM [ProfilerTest].[dbo].[ProfilerResults]
WHERE LoginName = 'Domain\User'
ORDER BY Duration DESC |
| Reads |
Writes |
-- Top 10 statements by reads
SELECT TOP 10 *
FROM [ProfilerTest].[dbo].[ProfilerResults]
WHERE LoginName = 'Domain\User'
ORDER BY Reads DESC |
-- Top 10 statements by writes
SELECT TOP 10 *
FROM [ProfilerTest].[dbo].[ProfilerResults]
WHERE LoginName = 'Domain\User'
ORDER BY Writes DESC |
| RowCounts |
ObjectID |
-- Top 10 statements by rows returned to the user
SELECT TOP 10 *
FROM [ProfilerTest].[dbo].[ProfilerResults]
WHERE LoginName = 'Domain\User'
ORDER BY RowCounts DESC |
-- Most used objects by the user
SELECT COUNT(ObjectID) AS 'ObjectCount',
OBJECT_NAME(ObjectID) AS 'ObjectName'
FROM [ProfilerTest].[dbo].[ProfilerResults]
WHERE LoginName = 'Domain\User'
GROUP BY ObjectID
ORDER BY ObjectCount DESC |
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.
Scenario 2: Overall data analysis
The second analysis is much broader and requires that you determine the queries where potential
issues could occur.
| Averages |
Max Values |
-- Average usage for key metrics
SELECT AVG(CPU) AS 'AvgCPU',
AVG(Duration) AS 'AvgDuration',
AVG(Reads) AS 'AvgReads',
AVG(Writes) AS 'AvgWrites',
AVG(RowCounts) AS 'AvgRows'
FROM [ProfilerTest].[dbo].[ProfilerResults] |
-- Highest usage statements
SELECT MAX(CPU) AS 'MAXCPU',
MAX(Duration) AS 'MAXDuration',
MAX(Reads) AS 'MAXReads',
MAX(Writes) AS 'MAXWrites',
MAX(RowCounts) AS 'MAXRows'
FROM [ProfilerTest].[dbo].[ProfilerResults] |
| Specific Host |
DISTINCT Applications |
-- Transactions count by machine
SELECT COUNT(HostName) AS 'HostCount', HostName
FROM [ProfilerTest].[dbo].[ProfilerResults]
GROUP BY HostName
ORDER BY HostName DESC |
-- Determine all applications on the SQL Server
SELECT DISTINCT(ApplicationName)
FROM [ProfilerTest].[dbo].[ProfilerResults]
ORDER BY ApplicationName |
| Transactions Per Database |
Most Used Objects |
-- Transaction per database in descending order
SELECT COUNT(DatabaseID), DB_NAME(DatabaseID)
FROM [ProfilerTest].[dbo].[ProfilerResults]
GROUP BY DatabaseID
ORDER BY DatabaseID DESC |
-- Most used objects in descending order
SELECT COUNT(ObjectID) AS 'ObjectCount', OBJECT_NAME(ObjectID) AS 'ObjectName'
FROM [ProfilerTest].[dbo].[ProfilerResults]
GROUP BY ObjectID
ORDER BY ObjectCount DESC |
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.
SQL Query optimization
Based on your analysis, apply the following rules to improve the query performance:
- Review the query plan for possible improvements in queries that consistently appear as
offenders in Profiler analyses.
- Leverage the Database Engine Tuning Advisor for index and partitioning recommendations.
- Review the query join order to minimize the number of rows that are being processed.
- Validate that indexes meet individual query needs for the JOIN, WHERE, GROUP BY and ORDER BY
clauses.
- Ensure that data types are the most efficient for the data (i.e., use integers for keys as
opposed to character data types).
- Remove any unneeded logic in the statements such as GROUP BY or ORDER BY when they are not
needed by users.
- Return only the needed amount of data and nothing more.
- Ensure statements do not explicitly hold any transactions longer than necessary.
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.
Top 5 SQL Server query
tips
Home: Introduction
Tip 1: SQL
Server query design: 10 mistakes to avoid
Tip 2: Troubleshoot
SQL Server queries and improve I/O
Tip 3: Tracking query execution with SQL Server 2005
Profiler
Tip 4: Find
and fix resource-intensive SQL Server queries
Tip 5: Running
analytical queries with Analysis Services
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
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.
Join the conversationComment
Share
Comments
Results
Contribute to the conversation