How to troubleshoot the performance of Ad-Hoc queries in SQL Server (243588)

SUMMARY

This article describes how to troubleshoot the slow performance of many concurrent ad-hoc queries in Microsoft SQL Server. If you have not determined the precise source of your problem, see the following article in the Microsoft Knowledge Base before you continue:
This article assumes that you have used KB 224587 to narrow the scope of the problem and that you have captured a Windows NT Performance Monitor log and SQL Profiler trace that detail the specific counters, events, and data columns.

Characteristics of the performance issues

The performance issue has the following characteristics:

• Short ad-hoc queries that typically have a very short duration result in slow overall system performance when a high number of concurrent users run the queries.
• Very high or 100 percent CPU usage.
• No associated blocking during the periods of slow performance.
  
  You can quickly look for blocking by checking the BLK column in the output of the sp_who system stored procedure. If the BLK column is not zero for a number of system process IDs (SPIDs), there is blocking.
• In some situations, server memory is stressed, and you may receive errors that are similar to the following errors:
  Error: 701, Severity: 17, State: 1
  There is insufficient system memory to run this query.
  -or-
  Msg 8645, Level 17, State 1, Procedure , Line 1
  A time out occurred while waiting for memory resources to execute the query. Re-run the query.

Improvements in query compilations

Because of improvements in system architecture starting in SQL Server 7.0, specifically the query optimizer, you may notice a difference in system resource usage by applications compared to earlier versions of SQL Server. Specifically, SQL Server 7.0 may show an increase in either CPU or memory usage, but earlier versions of SQL Server are typically disk IO bound. These changes can be traced to two factors:

• Hash and merge joins
• Query compilation times

Earlier versions of SQL Server relied completely on nested loop iterations to perform joins. Nested loop joins inherently use disk IO. Starting with SQL Server 7.0, hash and merge joins were introduced. Hash and merge joins do much more in-memory processing than nested loop joins. The logical outcome of this is that CPU and memory usage is higher when these join techniques are used. For more information about hash and merge joins, see the "Understanding Hash Joins" and "Understanding Merge Joins" topics in SQL Server 7.0 Books Online.

Query compilation times are affected because the query optimizer has more options and information available than in earlier versions of SQL Server, including new hash and merge join techniques, improved search algorithms, and column statistics. This additional information permits the query optimizer to select the most efficient method to retrieve query data. However, the analysis and consideration of these new techniques and information requires processing time. This increased CPU usage may result in query compilation times that are longer than in earlier versions of SQL Server.

For most queries, this increase in compile time is offset by a decrease in execution time. The overall effect is that the query runs faster than in earlier versions of SQL Server. One exception, however, occurs with very small, simple, OLTP-type queries that have very low execution times. For these queries, the process of generating a query plan may have an equal or greater expense than the query execution. As a result, the query may perform slightly slower than in earlier versions of SQL Server. Because the difference is typically in milliseconds, these effects are not noticed for a particular query if it is executed individually. However, you may notice that overall system CPU usage is higher than in earlier versions of SQL Server if large numbers of ad-hoc queries are executed concurrently by a high number of users.

Develop parameterized queries

SQL Server 7.0 uses several new techniques, such as caching ad-hoc queries and automatic parameterization. However, the queries that SQL Server 7.0 automatically parameterizes are limited. Use the following methods to make sure that the query plans are parameterized and can be reused more effectively:

• Parameter markers Both the OLE DB and ODBC APIs permit parameters to be specified with a question mark when users submit queries. This can be very useful in any application, especially for middle-tier applications that have query generation modules where using stored procedures is not available. The query plan that is generated for queries that have parameter markers can be reused by any clients that execute the same query, even if different parameter values are specified. For more information, see the "Parameter Markers" topic in SQL Server 7.0 Books Online.
• sp_executesql The sp_executesql stored procedure is called by the OLE DB provider or ODBC driver when parameter markers are used in an application. However, it may also be called directly by the application or in another stored procedure to explicitly parameterize ad-hoc queries. This can be very useful in applications or batch files where the EXECUTE statement is used to execute dynamic SQL statements. Unlike sp_executesql, the EXECUTE statement does not permit parameterization. This limits the chance of query plan reuse. For more information, see the "sp_executesql (T-SQL)" and "Using sp_executesql" topics in SQL Server 7.0 Books Online.
• Stored procedures Stored procedures have many benefits, including the ability to parameterize queries and reuse execution plans. For more information, see the "Stored Procedures" and "Programming Stored Procedures" topics in SQL Server 7.0 Books Online.

View the Performance Monitor data

Use the Performance Monitor log to determine which system resources are causing the bottleneck. The Performance Monitor log can give you an overall picture of the system and help focus your attention when you view the SQL Profiler data. Review the Performance Monitor data from the time when performance was good through the time that performance decreased. Determine the counter that was affected first, and then determine which of the following issues is most relevant to your situation:

• Object: Process
  Counter: Processor
  Instance: SQL Server
• Object: Processor
  Counter: %Processor Time
  Instance: Check each processor instance
• Object: SQL Server:Buffer Manager
  Counter: Free Buffers
• Object: SQL Server:Buffer Manager
  Counter: Stolen Page Count
• Object: SQL Server:Memory Manager
  Counter: Memory Grants Pending
• Object: SQL Server:SQL Statistics
  Counter: SQL Compilations/sec

If the CPU usage, SQL Compilations/sec, and Free Buffers counters are high, and the Memory Grants Pending and Stolen Page Count counters are low, this indicates that the CPU is the bottleneck. Focus on how to effectively parameterize and reuse query plans to avoid the cost of query plan generation, and see the "Group the SQL Profiler trace by event class" section of this article. If the Free Buffers and SQL Compilations/sec counters are low, and the Stolen Page Count and Memory Grants Pending counters are high, SQL Server is memory-constrained. Focus on finding queries where hash joins are used and can be changed to loop joins, and see the "Group the SQL Profiler trace by duration" section of this article. For more information about these counters, use the counter name to search the SQL Server 7.0 Books Online.

View the SQL Profiler data

When you are resolving performance issues, it is extremely valuable to view SQL Profiler data. You do not have to review all the data that you captured; be selective. SQL Profiler helps you to effectively view the captured data. On the Properties tab (on the File menu, click Properties), SQL Profiler permits you to limit the data that is displayed by removing data columns or events, grouping or sorting by data columns, and applying filters. You can search the whole trace or only a specific column for specific values (on the Edit menu, click Find ). You can also save the SQL Profiler data to a SQL Server table (on the File menu, point to Save As, and then click Trace Table), and then run SQL queries against it.

Note Make sure that you only filter a saved trace file. If you follow these steps on an active trace, you risk losing data that was captured since the trace was started. Save an active trace to a file or table first (on the File menu, click Save As ), and then reopen it (on the File menu, click Open) before you continue. When you work with a saved trace file, the filtering does not permanently remove the data; the data is only hidden, not deleted. You can add and remove events and data columns to help focus your searches.

You should also focus on the areas where you receive the most benefit. The following factors can help increase application performance but not necessarily to the same degree. Before you implement any changes, determine how effective the changes may be depending on the following factors:

• How frequently the query runs
• How much improvement the query can be improved

For example, reducing the execution time of a single query from 1.5 seconds to 1.2 seconds may not be helpful if the query is not executed frequently throughout the day. However, if the query is executed very frequently by a high number of concurrent users, the performance improvement can be very effective. Conversely, improving a single query from 6 minutes to 3 seconds may not yield a noticeable increase in overall performance if it is rarely used. Use the grouping and filtering techniques in SQL Profiler and your knowledge of the application to estimate the effects of a particular query or procedure before you implement any changes. Focus on the most effective changes first, and then continue with iterations through other queries and procedures until you reach a level where performance has sufficiently improved.

After you save a SQL Profiler trace to a file or table, reopen the trace in SQL Profiler and review the contents. To group the SQL Profiler trace, follow these steps:

• Group the SQL Profiler trace by duration:
  1. On the File menu, click Properties.
  2. Click the Data Columns tab, and then under Groups, click UP to move Duration. Click DOWN to remove all other columns.
  3. Click the Events tab, and then remove all events except TSQL SQL:StmtCompleted and TSQL RPC:Completed. This permits you to focus on only the queries that are being executed.
  4. Click OK.
  Grouping by duration permits to easily see the SQL statements, batches, and procedures that are running the slowest. Review the trace when the problem is occurring, and create a baseline of good performance. You can filter by start time to break the trace into sections when performance is good and separate sections when performance is poor. Look for the queries with the longest duration when performance is good. These are most likely the root of the problem. When overall system performance decreases, even good queries can show long durations because they are waiting for system resources.
  
  Review the execution plans for the queries that most frequently have long durations. If you see that a hash join is being used, consider using the LOOP JOIN query hint to force a nested loop join for the query. If the execution time for the query using a loop join is less than, equal to, or even slightly higher than the execution time with the hash join, a loop join may be a better option if the computer is experiencing high memory and CPU usage. By reducing the stress on the resource bottleneck (CPU and memory), you can improve overall system performance. For more information about the LOOP JOIN query hint, see the "SELECT (T-SQL)" topic in SQL Server 7.0 Books Online.
• Group the SQL Profiler trace by event class:
  1. On the File menu, click Properties.
  2. Click the Data Columns tab, and then under the Groups heading, click UP to move Event Class and Text with Event Class on top. Click DOWN to remove all other columns under the Groups heading.
  3. Click the Events tab, and then make sure that all the events are included.
  4. Click OK.

Types of events

To see what types of events are occurring on the computer running SQL Server and how frequently the events occur, group by the Event Class column. Search this column for the following events:

• MISC: Prepare SQL and Exec Prepared SQL; CURSORS: Cursorprepare A Prepare SQL event indicates that an SQL statement was prepared for use with a default result set (client-side cursor) using SQLPrepare/SQLExecute (for ODBC) or ICommandText::Prepare/ICommandText::Execute (for OLE DB) with the default cursor options: forward only, read only, rowset size = 1. A Cursorprepare event indicates that a server-side cursor was prepared on an SQL statement using SQLPrepare/SQLExecute (for ODBC) or ICommandText::Prepare/ICommandText::Execute (for OLE DB) with the one of the previous cursor options set to a non-default value. An Exec Prepared SQL event indicates that either of the previous types of existing prepared statements was executed. If you see frequent occurrences of these events, your application is using the prepare/execute model when it opens result sets. If so, you must determine if you are using the prepare/execute model correctly.
  
  Ideally, an application prepares an SQL statement once and executes it many times so that the optimizer does not have to compile a new plan each time the statement is executed. Each time you run a prepared statement, you save the cost of the query compilation. If you only plan to execute a query one time, Microsoft recommends that you not prepare it. Preparing and then executing an SQL statement requires three network roundtrips: one to prepare the statement, one to execute the statement, and one to unprepare the statement. Preparing server-side cursors requires at least five round trips: one to prepare the cursor, one to execute or open it, one or more to fetch from it, one to close it, and one to unprepare it. Executing the query only requires one roundtrip.
  
  To see how effectively your application uses the prepare/execute model, compare the number of times these two events (prepare and execute) occur. The number of Exec Prepared SQL events should be much larger than the total of Prepare SQL and CursorPrepare events (at least three to five times larger is a good estimate). This indicates that prepared statements are being reused frequently enough to overcome the increased overhead to create them. If the number of Prepare SQL and CursorPrepare events is roughly equivalent to the number of Exec Prepared SQL events, this may indicate that the application is not effectively using the prepare/execute model. Try to prepare a statement one time and reuse it as much as possible. You can also change your application to prepare statements one time and reuse those statements.
  
  The application must be specifically written to use the prepare/execute model efficiently. The lifetime of a handle to a prepared statement is controlled by how long you keep the HSTMT open in ODBC or the ICommandText object in OLE DB. One common practice is to obtain an HSTMT, prepare an SQL statement, execute the prepared statement, and then free the HSTMT, thereby losing the handle to the prepared plan. If you do this, you do not receive any benefit from the prepare/execute model. In fact, you may see a performance degradation because of the extra overhead of the network roundtrips. The application must have a method to cache the HSTMT or object with the prepared statement handle and to access them for reuse. The driver or provider does not do this automatically; the application is responsible for implementing, maintaining, and using this information. If the application cannot do so, consider using parameter markers instead of the prepare/execute method.
• Using parameter markers Applications can use parameter markers to optimize the use of the same Transact-SQL statement several times with different input and output values. The first time that a query is executed, it is prepared as a parameterized query, and SQL Server generates and caches a parameterized plan for the query. For subsequent calls to the same query using either the same or different parameters, SQL Server does not have to generate a new query plan; SQL Server can reuse the existing query plan by substituting the current parameters.
  
  If the application uses parameter markers with calls to SQLExecDirect (for ODBC) or ICommandText::Execute (for OLE DB), the driver or provider automatically packages the SQL statement and executes it as an sp_executesql call. The statement does not have to be prepared and executed separately. When SQL Server receives a call to sp_executesql, it automatically checks the procedure cache for a matching plan and reuses that plan or generates a new plan.
  
  To determine if your application currently uses parameter markers, you can search the Text column in the SQL Profiler trace for "sp_executesql." However, because sp_executesql may be called directly, not all instances indicate the use of parameter markers.
  
  For more information about the prepare/execute model, see the "Execution Plan Caching and Reuse" topic in SQL Server 7.0 Books Online. For more information about parameter markers, see the "Parameter Markers" topic in SQL Server 7.0 Books Online.
• SP:Completed Dynamic SQL statements executed with the EXECUTE command show up as an SP:Completed event with the text "Dynamic SQL." Expand the SP:Completed event, and then search for any occurrences that have "Dynamic SQL" as the text. If there are many of these events, you may be able to improve application performance by using sp_executesql instead of the EXECUTE statement. The sp_executesql stored procedure permits SQL Server to reuse execution plans if the same query is executed again using different parameters. When you use the EXECUTE statement, the plan is not parameterized, and it is not reused unless the query is executed again using the same parameters.
  
  To determine the queries or procedures that use dynamic SQL events with the EXECUTE statement, note the Connection ID and Start Time of for each event. Ungroup the trace (remove Event Class and Text from the Groups heading). After you ungroup the trace, it is sorted in chronological order. You can filter the trace by Connection ID (on the Filters tab), and then remove all the event classes except the SP:Starting and SP:Complete events for increased readability. You can then search for the Start Time of the event (on the Edit menu, click Find). The results show when the dynamic SQL event started. If the event occurred in a stored procedure, the event appears between the SP:Starting and SP:Completed events for that procedure. If the event did not occur in a stored procedure, it was executed as an ad-hoc query, and you can use the other data columns (Application Name, NT User Name, and others) to determine where the command was executed. To determine the text of the command and the context where it was executed, you can also add event classes, such as SQL:BatchCompleted and SQL:RPCCompleted.
  
  After you determine where the EXECUTE statement is being used, consider replacing it with a call to sp_executesql. For example, consider the following scenario where the EXECUTE command is used with dynamic SQL. A procedure takes a table name, ID, and idValue as input parameters, and then executes a SELECT statement from the table based on the ID value. Using an EXECUTE statement, the procedure looks similar to the following code:

  drop proc dynamicUsingEXECUTE
  		  go create proc dynamicUsingEXECUTE @table sysname, @idName varchar(10),
  		  @idValue varchar(10) as declare @query nvarchar(4000) -- Build query string
  		  with parameter. -- Notice the use of escape quotes. select @query = 'select *
  		  from ' + @table + ' where ' + @idName + ' = ''' + @idValue + '''' exec (@query)
  		  go

  Assuming that the query is not automatically parameterized, if you execute this procedure against the titles table in the pubs sample database two times with different values for the @idValue parameter, SQL Server must generate a separate query plan for each execution. For example:

  exec dynamicUsingEXECUTE
  		  'titles', 'title_id', 'MC2222' go exec dynamicUsingEXECUTE 'titles',
  		  'title_id', 'BU7832'

  Note In this example, the query is simple enough that SQL Server can automatically parameterize it and actually reuse the execution plan. However, if this was a complex query that SQL Server may not automatically parameterize, SQL Server may not reuse the plan for the second execution if the @idValue parameter was changed. The following simple query limits the complexity of the example.
  
  You can rewrite this procedure to use sp_executesql instead of the EXECUTE statement. Support for parameter substitution makes sp_executesql more efficient because it generates execution plans that are more likely to be reused by SQL Server. For example:

  drop proc dynamicUsingSP_EXECUTESQL go create proc
  		  dynamicUsingSP_EXECUTESQL @table sysname, @idName varchar(10), @idValue
  		  varchar(10) as declare @query nvarchar(4000) -- Build query string with
  		  parameter select @query = 'select * from ' + @table + ' where ' + @idName + ' =
  		  @idValue' -- Now execute with parameter exec sp_executesql @query, N'@idValue
  		  varchar(10)', @idValue go exec dynamicUsingSP_EXECUTESQL 'titles', 'title_id',
  		  'MC2222' go exec dynamicUsingSP_EXECUTESQL 'titles', 'title_id',
  		  'BU7832'

  In this example, the first time that the sp_executesql statement is executed, SQL Server generates a parameterized plan for the SELECT statement from titles with title_id as the parameter. For the second execution, SQL Server reuses the plan with the new parameter value. For more information about sp_executesql, see the "sp_executesql (T-SQL)" and "Using sp_executesql" topics in SQL Server 7.0 Books Online.
• SP:RECOMPILES This event indicates that a stored procedure was recompiled during execution. Many recompile events indicates that SQL Server is using resources for query compilation instead of query execution.

If you do not see any of these events, the application is executing only ad-hoc queries against SQL Server. Unless SQL Server determines that it can automatically parameterize certain queries or if the same parameters are used repeatedly, each query that is executed requires SQL Server to generate a new execution plan. SQL Server Performance Monitor should show many SQL Compilations/sec. This can be CPU-intensive for many concurrent users. To work around this issue, find the most frequently executed queries, and consider creating stored procedures for these queries, using parameter markers, or using sp_executesql.