To troubleshoot performance issues, you must complete a
series of steps to isolate and determine the cause of the problem. Possible
- System resource contention
- Application design problems
- Queries or stored procedures that have long execution
Identifying these causes is typically very time consuming, and
you may spend several days evaluating the collected information. The complexity
of the performance analysis is not specific to a particular database product or
operating system. All applications are subject to performance constraints
because of resource, design, or usage factors.
For more information, click the following article number to view the article in the Microsoft Knowledge Base:
How to troubleshoot application performance with SQL Server
To help identify and troubleshoot the problem,
collect the following information at the same time and have the output readily
available before you contact Microsoft Product Support Services (PSS):
- Blocker script output
- SQL Profiler trace log
- SQL Server Performance Monitor log
If you do not collect this information when the performance
problem occurs, you may have to gather all the information again. This can
delay the troubleshooting process.
After the problem occurs, collect
the following information and have it available:
- sqldiag report
- Microsoft Windows NT System and Application Event
In most scenarios, PSS requires this information to understand
the environment and the nature of the performance issue. If any part of this
information is not available, the troubleshooting process may be prolonged and
identifying the performance issue may be delayed.
Even if you are
currently not experiencing any performance issues, Microsoft recommends that
you implement this process. If a performance issue occurs, you can capture the
required information as soon as possible. Additionally, if you capture a
baseline SQL Profiler log, SQL Server Performance Monitor log, and blocker
script when the application is performing as expected, you can use that
information for comparison when the application does not perform as
If you gather this information in a high traffic SQL Server
environment, you may experience some performance degradation. However, you must
have this information to identify the cause of the problem and for
troubleshooting purposes. The SQL Profiler trace has the most impact on
performance. If the performance is severely degraded, you can customize the SQL
Profiler trace by reducing the types of events that it captures. Limiting the
SQL Profiler trace should provide some improvement. If you have questions or
problems setting up and collecting the information, contact PSS.
Blocker script output
The blocker script is critical for identifying blocking scenarios.
However, you can use the output from the script to troubleshoot performance
problems even when blocking is not an issue. This output also helps to
determine if queries are waiting on resources, such as file I/O, or if
transactions are not being committed or rolled back as expected.
For more information
about implementing the blocker script, click the following article numbers to view the articles in the Microsoft Knowledge Base:
How to monitor SQL Server 7.0 blocking
How to monitor SQL Server 2000 blocking
SQL Profiler trace log
The SQL Profiler trace captures the activity on the computer
running SQL Server. You can use this information to identify slow running
queries and non-optimal execution plans. Additionally, SQL Profiler documents
the series of events that occur before the performance problem and helps to
identify its cause.
To create and implement a SQL Profiler trace by
using the GUI, see the "What to Monitor" section of the following Microsoft
Knowledge Base article:
How to troubleshoot application performance with SQL Server
For more information about how to create and execute a SQL Profiler
trace by using Transact-SQL commands, click the following article numbers to view the articles in the Microsoft Knowledge Base:
How to create a SQL Server 7.0 trace
How to create a SQL Server 2000 trace
For more information about how to monitor a SQL Profiler trace by
using Transact-SQL commands, click the following article numbers to view the articles in the Microsoft Knowledge Base:
INF: How to Monitor SQL Server 7.0 traces
How to monitor SQL Server 2000 traces
If you have a SQL Profiler trace of a large workload, you can use
the Index Tuning Wizard. The Index Tuning Wizard uses the SQL Server query
optimizer to determine the optimal set of indexes for the specified queries.
The Index Tuning Wizard is a very efficient tool to determine if the correct
indexes exist in your database. By implementing the indexes that the wizard
suggests, you may be able to increase the performance of your application.
For more information about how to use the Index Tuning Wizard, see
the "Index Tuning Wizard" topic in SQL Server Books Online.
SQL Server Performance Monitor log
SQL Server is typically affected by the following bottlenecks:
- File I/O
- Locking, blocking, or deadlocking
You can use SQL Server Performance Monitor to identify how these
potential bottlenecks may affect SQL Server. Additionally, you can use this log
to identify when an external process is heavily using the computer running SQL
Server and negatively impacting SQL Server performance.
start SQL Server Performance Monitor, make sure that the disk counters are on.
To do so, run diskperf
from a command prompt. If the disk counters are not on, run diskperf -y
and then restart the computer.
When you create a SQL
Server Performance Monitor log, collect the following information:
- Paging file
- All SQL Server counters
- Logical disk
- Physical disk
The default interval of 15 seconds should enough time to monitor
the server; however, for some timing issues, you may have to reduce the time
interval for collecting data.
For more information about how to set up a SQL Server Performance
Monitor log, click the following article numbers to view the articles in the Microsoft Knowledge Base:
How to create a Performance Monitor log for NT troubleshooting
How to create a log using System Monitor in Windows 2000
For more information about monitoring performance in SQL Server 2005, see the "Monitoring and Tuning for Performance" topic in SQL Server 2005 Books Online.
The sqldiag utility is provided with SQL Server. It collects
valuable information about the configuration of the computer running SQL
Server, the operating system, and the information that is reported to the SQL
Server error logs. For information about how to use the sqldiag utility, see
the "sqldiag Utility " topic in SQL Server Books Online.
For more information about how to run
sqldiag on a clustered SQL Server, click the following article number to view the article in the Microsoft Knowledge Base:
How to run SQLDIAG on a clustered/virtual SQL Server
In SQL Server 2005, the SQLdiag utility has changed significantly. The command line arguments for this utility are not compatible with SQL Server 2000. This utility may be changed, and applications or scripts that rely on its command line arguments or behavior may not work correctly in future releases. For more information, see the "SQLdiag Utility" topic in SQL Server 2005 Books Online.
Microsoft Windows NT System and Application Event logs
You can use the Windows NT system and application event logs to
identify issues that you cannot see in other data. These logs help provide a
complete view of server activity and provide a more complete understanding of
Where to save these files
The following Microsoft File Exchange FTP server permits you to
send and receive files to and from PSS engineers:
more information, read the instructions that are provided on the Microsoft File
Exchange FTP Web site. Bookmark: 9