How to solve the performance problems of SQL Server Applications

The release number of this article was chs224587. Summary on this page

SQL event Probe

Monitored object

Run the application under normal load

Run the application under normal load

Explanation result

Summary

This section describes how to solve SQL server performance problems step by step. To solve performance problems, you must use a series of steps to isolate and determine the cause of slow application running. Possible causes include:

•	Blocking.
•	System resource contention.
•	Application Design Issues.
•	A group of special queries or stored procedures that have been executed for a long time.

This article describes how to determine the source of performance issues. This article also references other articles in the Microsoft Knowledge Base that contain detailed information about specific performance problems and can be used for troubleshooting other problems.

Back to Top

SQL event Probe

SQL event probe is a very effective tool to solve the performance problems of SQL Server 7.0 or later applications. The SQL event probe allows you to easily capture all events on the server under normal loads and provide information about these events. Use the SQL event probe with Microsoft Windows NT Performance Monitor and some simple queries to determine whether blocking occurs. This provides you with the information necessary to solve most performance problems.

Back to Top

Monitored object

1. Set the SQL event probe to capture the trail. To do this, follow these steps:

A.	Open the SQL event probe.
B.	On the Tools menu, click options ".
C.	Make sure that the selectedAll event classesAndAll data Columns.
D.	Click OK ".
E.	Create a new trail.
F.	On the File menu, point to new and click tracking ".
G.	On the General tab, specify the trail name and the file to capture data.
H.	On the events tab, add the following event types to the trail: Title Event to be added Description Cursors Cursorprepare This event indicates that the cursor has been prepared on the SQL statement using ODBC, oledb, or DB-library. Error and warning Missing column statistics This event indicates that column statistics that may be useful to the optimizer are unavailable.TextMultiple columns with missing statistics are displayed. This event togetherMisc: auto-updatestatsEvent indicates triggeredAutomatically create statistics. Misc. Attention This event indicates that the client sends a notice signal. Misc. Auto-updatestats This event indicates that it has been triggeredAutomatically update statistics. Misc. Exec prepared SQL This event indicates that ODBC, ole db, or DB-library has executed one or more previously prepared Transact-SQL statements. Misc. Execution Plan This event indicates the plan tree for executing the transact-SQL statement. Misc. Prepare SQL This event indicates that the ODBC, ole db, or DB-library application has prepared one or more Transact-SQL statements to be used. Misc. Unprepare SQL This event indicates that the ODBC, ole db, or DB-library application is not preparing one or more of the transact-SQL statements to be used. Sessions Connect This event indicates that a new connection has been created. Sessions Disconnect This event indicates that the client is disconnected. Sessions Existing connection This event indicates that a connection exists when the SQL event probe trail is started. Stored Procedures SP: Completed This event indicates that the stored procedure has been completed. Stored Procedures SP: recompile This event indicates that the stored procedure is re-compiled during execution. Stored Procedures SP: Starting This event indicates that the stored procedure has started to be executed. Stored Procedures SP: stmtcompleted This event indicates that the statement in the stored procedure has been executed. Tsql: SQL: batchcompleted This event indicates that the transact-SQL batch processing is completed.TextThe column displays the executed statements. Tsql: SQL: stmtcompleted This event indicates that the transact-SQL statement has been completed.TextThe column displays the executed statements. Tsql: RPC: Completed This event indicates that the Remote Procedure Call (RPC) has been completed.
I.	If the application receives a timeout error, a stop response (suspension), or other events that cause a problematic statement to never end running, the following events should also be included: Tsql: SQL: batchstarting This event indicates the start of the transact-SQL batch processing.TextThe column displays the statements being executed. Tsql: SQL: stmtstarting This event indicates the beginning of a Transact-SQL statement.TextThe column displays the statements being executed. Tsql: RPC: Starting This event indicates the start of a Remote Procedure Call (RPC. Stored Procedures SP: stmtstarting This event indicates that the statement in the stored procedure is being executed. This will help you to see the statement being executed when the timeout occurs.
J.	InData ColumnTab to ensure that the following columns are included: For SQL Server 2000 Start Time End Time Loginsid Spid Event Type Text Data Integer Data Binary data Duration CPU Read Write Application name NT Username DB Username For SQL Server 7.0 Start Time End Time Connection ID Spid Event Type Text Integer Data Binary data Duration CPU Read Write Application name NT Username SQL user name

For information about using SQL event probes, see SQL Server 7.0 and SQL Server 2000 books online.

2. Use Performance Monitor to capture Windows NT and SQL Server counters. To do this, follow these steps:

A.	Start Windows NT Performance Monitor.
B.	InViewClickLogs.
C.	InOptionClickLogs.
D.	Specify the file name and location to record the performance counter. You can adjust the update interval as appropriate.
E.	InEditClickAdd to log.
F.	Add all objects. Windows NT and SQL Server objects.
G.	To start the log, goOptionClickLogsAnd then clickStartup logButton.

For other information, click the following article number to view the article in the Microsoft Knowledge Base:

150934 (http://support.microsoft.com/kb/150934/) how to create performance monitor logs for NT troubleshooting

3. Check for blocking.

RunSp_whoSystem stored procedure:

exec sp_who

This output will containBLKColumn. Check whether the output contains any non-zero items that indicate blocking. This process is regularly executed within the full range of time when performance drops occur.

Note:: RunSp_whoThe system stored procedure checks for blocking. In general, it is not enough information to completely solve the blocking problem. For other information, click the following article number to view the article in the Microsoft Knowledge Base:

251004 (http://support.microsoft.com/kb/251004/) INF: How to monitor SQL Server 7.0 Blocking

Back to Top

Run the application under normal load

Ideally, it is best to capture SQL event probes, performance monitors, and blocked output within the same time range. This time range must include the performance of the application from good to bad. This information helps you understand the performance drop position.

Explanation result

1.	Check for blocking. IfSp_whoOutputBLKIf the column is not zero, the system is blocked. If the process is blocked, the execution time of the blocked process may be longer. For other information, click the following article number to view the article in the Microsoft Knowledge Base: 224453 (http://support.microsoft.com/kb/224453/) INF: understand and resolve SQL Server 7.0 or 2000 blocking issues
2.	Check the output of the SQL event probe. It is very useful to effectively view the data of the SQL event probe when solving performance problems. The most important thing is to realize that you should selectively view the captured content instead of all the captured content. The SQL event probe provides the function to help you efficiently view captured data. InAttributeTab (clickFileOn the menuAttributeSQL event probe allows you to restrict the displayed data by deleting data columns or events, grouping (sorting) by data columns, and applying filters. You can retrieve the entire trail or only a specific column with a specific value (inEditClickSearch). You can also save the data of the SQL event probe to the SQL Server tableFileMenu, pointingSaveAnd then clickTracking table), And then run SQL query on it. Note: You should filter only the previously saved trace files. If you perform these steps on an activity trail, you may be at risk of losing captured data because the trail has been started. First, save the activity trace to a file or tableFileClickSave), And then re-open the trail before continuing the execution (inFileClickOpen). When processing saved trace files, the filter operation does not permanently delete filtered data, but does not display the data. You can add and delete events and data columns as needed for search. When checking performance in the SQL event probe trace file, you must first determine the location where different types of events occur on the server. Group A trail by event type: A. InFileClickAttribute. B.Data ColumnTab, move with the up buttonGroupUnder the titleEvent TypeAnd use the down button to deleteGroupAll other columns under the title. C. ClickOK. Grouping by event columns shows what types of events are happening on SQL Server and how often they occur. Retrieve the following events in this column: SP: recompile This event indicates that the stored procedure is re-compiled during execution. Multiple recompilation events indicate that the SQL Server spends resources on query compilation rather than query execution. For other information about how to solve the stored procedure re-compilation problem, click the following article number to view the article in the Microsoft Knowledge Base: 243586 (http://support.microsoft.com/kb/243586/) Stored Procedure recompilation troubleshooting Attention Note that the client canceled the query. Generally, this is due to one of the following two reasons: The user explicitly cancels the query or closes the application. -Or- The query timeout is exceeded. If the note signal is displayed, it may indicate that some queries are running slowly. For other information, click the following article number to view the article in the Microsoft Knowledge Base: How does 243589 (http://support.microsoft.com/kb/243589/) address low-performance queries on SQL Server 7.0 or later To help determine the query that receives the attention signal, revise this trail so that it is not grouped by any data column, and then filter out the system process IDS (spids) that receive the signal (inFilterTab, set spid =X). At the top of the Note signalSQL: stmtstarting,SQL: batchstartingOrSP: stmtstartingAn event is a query that has timed out or canceled. You canEvent TypeSearch for the Attention event in the column to make it easy to locate this event (inEditClickSearch). Prepare SQL and exec prepared SQL Prepare SQLEvent indicates that the ODBC, ole db, or DB-library application has prepared one or more Transact-SQL statements to be used.Exec prepared SQLThe event indicates that the application uses the existing prepared statements to execute commands. Compare the occurrences of these two events. Ideally, the application must prepare an SQL statement at a time and execute it multiple times. This will reduce the cost for the optimizer to compile the new plan each time the statement is executed. Therefore,Exec prepared SQLThe number of events should be largerPrepare SQLThe number of events. IfPrepare SQLThe number of events is almost equalExec prepared SQLThe number of events, which may indicate that the application does not make good use of the preparation/execution mode. It is best not to prepare statements that will be executed only once. For more information about preparing SQL statements, see the "Prepare SQL statements" topic in SQL Server 7.0 books online. IfExec prepared SQLNo comparison of the number of eventsPrepare SQLIf the number of events exceeds 3 to 5 times, the application may not use the preparation/execution mode effectively. For other information, click the following article number to view the article in the Microsoft Knowledge Base: 243588 (http://support.microsoft.com/kb/243588/) special query performance problems In SQL Server 2000, too many round trips are eliminated for each preparation/execution. Therefore, the ratio of 3 to 5 is not very strict. However, to try and reuse the prepared plan multiple times, it is still a applicable rule. Missing column statisticsThis event indicates that the statistics used by the optimizer to generate a better query plan are unavailable. This indicates that at least one table involved in the query has no available indexes. Apart from the absence of available indexes, SQL server does not even have statistical data on columns, so it is impossible to make a clear query plan decision. The query plan generated by the result may not be optimal. If you see these events, view the generated query and execution plan, and refer to the following Microsoft Knowledge Base Article for the steps required to improve query performance: How does 243589 (http://support.microsoft.com/kb/243589/) address low-performance queries on SQL Server 7.0 or later ViewMissing column statisticsWhen performing an event, first check the events associated with long-running queries. Some events may be automatically generated and resolved by SQL Server through autostats, and user intervention is not required. Therefore, the best strategy is to first focus on checking the query that lasts for a long period (as shown in the following article), and check whether there is any associationMissing column statisticsEvent. If no instance of these event classes is seen, the next step is to determine where the time is spent. Group the Trace Output by Duration: A. InFileClickAttribute. InData ColumnTab, move with the up buttonGroupUnder the titleDurationAnd use the down button to deleteGroupAll other columns under the title. C.EventTabTsqlAndStored ProceduresDelete all groups. D. ClickOK. Grouping Based on the duration, you can easily see which SQL statements, batch processing, or processes run at the lowest efficiency. It is very important not only to check the time when the problem occurs, but also to obtain a time benchmark with good performance for comparison. You can filter the trail by startup time so that the trail is divided into multiple parts when the performance is good, and the trail is treated as a separate part when the performance is low. Query for the longest duration when the performance is good. These are likely to be the root cause of the problem. If the performance of the entire system declines, even good queries may show a long duration because they are waiting for system resources. If only a small number of queries have a long duration, see the following Microsoft Knowledge Base Article: How does 243589 (http://support.microsoft.com/kb/243589/) address low-performance queries on SQL Server 7.0 or later If you see that the query duration is short, but the number is large, andSQL compilations/secThe counter (which will be described below) is very high. Please refer to the following Microsoft Knowledge Base Article: 243588 (http://support.microsoft.com/kb/243588/) How to query performance problems Check other data Columns: By viewing other data columns in the tracking data, you can further understand the nature of performance issues. The following are some considerations: If the CPU usage is high, group the CPU to determine which queries use the CPU for the longest time. InTextQuery "hash" or "merge" in the column to find which query execution plan is using these join types. These connection types consume more CPU and memory than nested loop connections, while the latter is usually Io intensive. If disk I/O is a bottleneck, read and write data to the group. ViewApplication name,NT UsernameAndSQL user nameFields can help isolate long-running query sources. The integer data column of the exception event displays any errors returned to the client. By searching for these numbers in SQL Server 7.0 books online, you can find the content of these error messages. Connection IDField can help ensure that you are viewing the same session of the specified client. Spid cannot guarantee this because the user may be disconnected and the new user has been connected and received the same spid. Depending on the actual situation, the benefits of these fields may vary. However, if the fields explicitly mentioned above do not provide answers, you should check these fields.
3.	Check the performance monitor output. The performance monitor displays the bottleneck of the entire system. It may show that SQL Server and applications are running normally, but the computer may suffer performance degradation, lack of memory or other resources. Alternatively, some counters may display problems in the execution methods of applications and SQL Server. Check at least the following counters:

•	Object: Process Counter: Processor Instance: SQL Server
•	Object: Processor Counter: % processor time Instance: Check each processor instance
•	Object: physical disk Counter: avg. Disk Queue Length Instance: Check each physical disk instance
•	Object: SQL server: SQL statistics Counter: SQL compilations/sec

Find the trend of performance variation in a certain period of time: What is the first increase? Is the computer CPU bound or disk Io bound? This information, together with the event Probe Output above, will help you narrow down the scope of the problem. High CPU usage problems may indicate that a large number of stored procedures are re-compiled, special query compilation, or hash connections and merge connections are intensive. The correct procedure must be determined based on the document referenced above. A longer disk queue length may indicate that more system memory or a better disk subsystem is required.