How to fix slow SQL queries

Troubleshoot slow running queries in SQL Server

  • 6 minutes to read

introduction

This article describes how to troubleshoot a performance problem that SQL Server applications may encounter: slow performance of a specific query or group of queries. If you are trying to troubleshoot a performance problem, but have not isolated the problem to a specific query or a small group of queries that ran slower than expected, see Monitor and tune for Performance before proceeding.

This article assumes that you have used article 298475 to narrow the problem area and that you have captured a SQL Profiler trace with the specific events and data columns listed in article 224587.

Optimizing database queries can be a multifaceted undertaking. The following sections discuss common elements to examine when examining query performance.

Original version of the product: SQL Server
Original KB number:   243589

Verify that the correct indexes exist

One of the first checks to be performed when query execution times are slow is an index analysis. When examining a single query, you can use the Analyze Query option in the Database Engine Tuning Advisor in SQL Query Analyzer. If you have a SQL Profiler trace of a large workload, you can use the Database Engine Tuning Advisor. Both methods use the SQL Server query optimizer to determine which indexes would be useful for the specified queries. This is an efficient way to determine if the correct indexes exist in the database.

For information about using the Database Engine Tuning Advisor, see the topic "Starting and Using the Database Engine Tuning Advisor" in SQL Server Books Online.

If you upgraded your application from an earlier version of SQL Server, different indexes in new SQL Server builds may be more efficient because of optimization and memory engine changes. You can use the Database Engine Tuning Advisor to determine whether a change in indexing strategy would improve performance.

Remove all query, table, and join hints

Hints override query optimization and can prevent the query optimizer from choosing the fastest execution plan. Because of optimization changes, notes that improved performance in previous versions of SQL Server may have no effect or may have a negative impact on performance in later SQL Server builds. Additionally, linkage hints can degrade performance for the following reasons:

  • Linkage notes prevent an ad-hoc query from being authorized for automatic parameterization and intermediate storage of the query plan.

  • When you use a join hint, it means that you want to enforce the join order on all tables in the query, even if those joins don't explicitly use a hint.

If the query hints you analyzed contain them, remove them, and then re-evaluate performance.

Examine the execution plan

After confirming that the correct indexes are in place and that there are no clues limiting the optimizer's ability to generate an efficient plan, you can review the query execution plan. You can use one of the following methods to view the execution plan for a query:

  • SQL Profiler

    If you captured the Other: Execution Plan event in SQL Profiler, it would occur immediately before the StmtCompleted event for the query for the system process ID (SPID).

  • SQL Query Analyzer: graphical display plan

    With the query selected in the query window, click the Query menu, and then click View Estimated Execution Plan.

    Note

    If the stored procedure or batch creates and references temporary tables, you must use a statement to set a statistics profile or explicitly create the temporary tables before viewing the execution plan.

  • and

    If you want a text version of the estimated execution plan, you can set and set the SHOWPLAN_ALL options. For more information, see the topics Define SHOWPLAN_ALL (t-SQL) and Define SHOWPLAN_TEXT (t-SQL) in SQL Server Online Documentation.

    Note

    If the stored procedure or batch creates and references temporary tables, you must use the " Set statistics profile at "or explicitly create the temporary tables before viewing the execution plan.

  • Statistics profile

    If you view the estimated execution plan either graphically or with SHOWPLAN, the query will not run. So if you create temporary tables in a batch or stored procedure, you will not be able to view the estimated execution plans because the temporary tables do not exist. Statistics Profile runs the query first and then shows the actual execution plan. For more information, see the topic on Defining a statistics profile (T-SQL) in SQL Server Online Documentation. When run in SQL Query Analyzer, it is displayed in graphical format on the Execution Plan tab of the results pane.

For more information about how to view the estimated execution plan, see the topic on View the estimated execution plan in SQL Server Online Documentation.

Check the Showplan output

Showplan output contains a lot of information about the execution plan SQL Server uses for a particular query. Here are some basic aspects of the execution plan that you can view to determine if you are using the best plan:

  • Correct index usage

    The Showplan output shows each table involved in the query and the access path used to retrieve data. With the graphical show plan, move the mouse pointer over a table to view the details of each table. If an index is used, the index search is displayed; If no index is used, either table scan for a heap or clustered index scan for a table with a clustered index is displayed. Clustered Index Scan indicates that the table will be scanned against the clustered index, not that the clustered index will be used to access individual rows directly.

    If you find that a useful index exists and is not being used for the query, you can try to force the index with an index hint. For more information on index notes, see the topic from (T-SQL) in SQL Server Online Documentation.

  • Correct join order

    The Showplan output indicates the order in which tables involved in a query are linked together. For nested loop joins, the table above is the outer table and should be the smaller of the two tables. For hash joins, the top table becomes the creation input and should also be the smaller of the two tables. Note, however, that the order is not that important because the query processor can roll back build and test inputs at run time if it determines that the optimizer has made an incorrect decision. You can determine which table will return fewer rows by checking the number of rows estimates in the Showplan output.

    If you find that the query can benefit from a different join order, you can try to force the join order with a join hint. For more information on linkage hints, see the topic from (T-SQL) in SQL Server Online Documentation.

    Note

    When you use a join hint in a large query, the join order for the other tables in the query is implicitly enforced as if it were specified.

  • Correct join type

    SQL Server uses nested loop, hash, and merge joins. If a query that runs slowly is using one join technique over another, you can try forcing a different type of join. For example, if a query uses a hash join, you can force a nested loops join using the loop join hint. For more information about join tips, see the "from (T-SQL)" topic in SQL Server Books Online.

    When you use a join hint in a large query, the join type is implicitly enforced on the other tables in the query as if it were specified.

  • Parallel execution

    If you are using a multiprocessor computer, you can also check to see if a parallel plan is being used. If concurrency is used, a gather stream event is displayed. If a particular query is slow when using a parallel plan, you can try to force a non-parallel plan with the options hint (MAXDOP 1). For more information, see the "Select (T-SQL)" topic in SQL Server Books Online.

For more information about using the Showplan execution plan output, see the following topics in SQL Server Books Online:

  • Saving an execution plan in XML format

  • Compare and analyze execution plans

  • Reference for Showplan Logical and Physical Operators

Danger

Because the query optimizer typically chooses the best execution plan for a query, Microsoft recommends that you use join hints, query hints, and table hints only as a last resort and only if you are an experienced database administrator.