What are the best query hints to improve T-SQL stored procedure performance?

由人工智能和领英社区提供技术支持

T-SQL stored procedures are a powerful way to execute complex queries on SQL Server databases, but they can also suffer from performance issues if not optimized properly. One way to improve the performance of your stored procedures is to use query hints, which are instructions that you can add to your queries to influence how SQL Server processes them. However, query hints are not a magic bullet, and they can also have negative effects if used incorrectly. In this article, you will learn what are the best query hints to improve T-SQL stored procedure performance, and how to use them wisely.

此文章中的业界达人

由社区从 5 条内容中精选。了解更多

Vedran Kesegic

SQL Server Principal Consultant, SQL Master (MCM)
Marcin Gminski

SQLWATCH? - SQL Server Monitoring for MSPs
Joe Chang

1 Why use query hints?

Query hints can help you improve the performance of your stored procedures by overriding the default behavior of SQL Server's query optimizer, which is the component that decides how to execute your queries. The query optimizer uses statistics, indexes, and other factors to generate an execution plan, which is a set of steps that SQL Server follows to retrieve or modify data. Sometimes, the query optimizer may choose a suboptimal execution plan, resulting in slow or inaccurate queries. Query hints can help you force the query optimizer to use a different execution plan, or to apply certain optimizations that it may not consider otherwise.

添加您的观点

Joe Chang
举报内容
Query hints are a touchy topic in SQL Server. The use of many hints largely cause the query optimizer to disable many options. If you want use hints, you should have the ability to understand for yourself what the best execution should be, then how to write SQL to a specific join sequence and join type. One reason the query optimizer may fail to find a good plan is because it cannot estimate row count due to cumulative propagation errors. True there are a vast number of possible plan shapes and join orders, but the last several versions are good on producing a plan quickly, possibly by knowing what not evaluate. Another reason the query optimizer fails to produce a good plan is because the cost model is based on leaf level pages being …

已翻译

赞

2 What are the types of query hints?

There are two main types of query hints: table hints and join hints. Table hints apply to a single table or view in a query, and affect how SQL Server accesses or locks the data in that table or view. For example, you can use a table hint to specify the type of lock to use, or to force an index scan or seek. Join hints apply to a join operation between two tables or views in a query, and affect how SQL Server performs the join. For example, you can use a join hint to specify the type of join algorithm to use, or to force an ordered join.

添加您的观点

3 How to use query hints?

To use a query hint, you need to add the keyword OPTION to the end of your query, followed by a list of hints enclosed in parentheses. For example, the following query uses the MAXDOP hint to limit the number of processors used by the query to one:

SELECT * FROM Sales.OrderDetail
WHERE ProductID = 776
OPTION (MAXDOP 1);

You can also use table hints and join hints within the FROM clause of your query, after the name of the table or view and before any alias. For example, the following query uses the NOLOCK table hint to avoid locking the Sales.Customer table, and the HASH join hint to force a hash join between Sales.Customer and Sales.OrderHeader:

SELECT c.CustomerID, c.FirstName, c.LastName, o.OrderDate, o.TotalDue
FROM Sales.Customer c WITH (NOLOCK)
INNER HASH JOIN Sales.OrderHeader o
ON c.CustomerID = o.CustomerID;

添加您的观点

Vedran Kesegic

SQL Server Principal Consultant, SQL Master (MCM)
(已编辑)
举报内容
SQL Server has limited time to compile. If the query is complex, having eg. more than 10 joins, there are many possible combinations and optimizer cannot try them all in a limited time. That is usually the main reason we get suboptimal execution plan for complex queries. The goal is to get smaller search space for query optimizer so he has a better chance to find ideal execution plan. We can do that with hints, or by splitting the query into two simpler ones. Result from the first one we store to temp table and in the second query we join temp table with the rest of the tables. Two simple queries have many orders of magnitude less combinations in optimizer search space than one big, complex query.

已翻译

赞

4 What are the best query hints?

When it comes to the best query hints, there is no definite answer. Different hints may have different effects depending on the context and data. As a general guideline, use query hints sparingly and only when necessary. Unintended consequences such as blocking other queries or reducing concurrency can occur, and they can become obsolete over time. To ensure the best performance, test and compare different query hints before applying them to production. Use tools such as SQL Server Management Studio, SQL Server Profiler, or Query Store to measure and analyze the performance of your queries with and without query hints. Additionally, use the most specific query hints to address your performance issue, rather than broad or general query hints that may affect other parts of the query that are not problematic. Lastly, monitor the performance of your queries over time as query hints may need to be adjusted or removed as the data or workload changes.

添加您的观点

5 What are some common query hints?

When working with queries, you may come across a few common query hints. For example, the RECOMPILE hint tells SQL Server to recompile the query’s execution plan every time it is executed, instead of using a cached plan, which can help with parameter sniffing issues. The OPTIMIZE FOR hint instructs SQL Server to optimize the execution plan for a specific parameter value or set of values, which can help with skewed data distribution. The MAXDOP hint limits the number of processors used by the query, which can help with parallelism issues. The NOLOCK hint uses a read uncommitted isolation level for the table or view, which increases performance but also introduces the risk of reading dirty or inconsistent data. Lastly, the FORCESEEK hint uses an index seek operation for the table or view, improving performance for queries with selective predicates but requiring an index on the column or columns used in the search condition.

添加您的观点

Vedran Kesegic

SQL Server Principal Consultant, SQL Master (MCM)
(已编辑)
举报内容
Please, do NOT use NOLOCK hint. There is a superior replacement called RCSI (Read Committed Snapshot Isolation). RCSI is a setting we turn on at the database level. No code change is required, and effect is the same as NOLOCK everywhere, but without "dirty reads" problems - we get consistent results! NOLOCK is a thing of the past. RCSI exists since SQL 2005. From then on, noone should use NOLOCK. Or on very, very, very rare occasions. Using NOLOCK today is more a display of ignorance than a good techique.

已翻译

赞

6 Here’s what else to consider

This is a space to share examples, stories, or insights that don’t fit into any of the previous sections. What else would you like to add?

添加您的观点

Marcin Gminski

SQLWATCH? - SQL Server Monitoring for MSPs
举报内容
It is true that query hints change the behaviour of the optimiser. Microsoft spends an awfully large amount of money on the engine, to make sure optimizer is as clever as it can be. If we think we can somehow magically fix a bad query, with a hint, and do it better than the optimiser, we are very wrong. Query hints should only be used in edge cases where the optimiser really struggles, or to fix a particular problem not otherwise possible to fix - after all other ways have been exhausted.

已翻译

赞
Joe Chang
举报内容
Be aware that the SQL Server query optimizer employs a model for the cost operations based on the assumption that at the beginning of an execution, all leaf level pages are not in buffer cache, requiring IO.The IO operations cost are based on fixed assumption of sequential of 1350 pages per sec (10.8 MB/s) and random access of 320 IOPS. The actual storage system could have very different relative sequential/ random characteristics, as well as complex queue depth dependencies. If data is in memory, then the cost of operations is again very different from what the optimizer assumes. Yes the query optimizer is very good. But not all decisions are based on statistics. Some are based on reasonable rules and the information available. But ..

已翻译

赞

Data Architecture

+ 关注

给文章评分

我们借助人工智能创建了此文章。您认为这篇文章怎么样？

很棒不太好

举报此文章

查看全部

What are the best query hints to improve T-SQL stored procedure performance?

1

2

3

4

5

6

1 Why use query hints?

2 What are the types of query hints?

3 How to use query hints?

4 What are the best query hints?

5 What are some common query hints?

6 Here’s what else to consider

Data Architecture

给文章评分

感谢您的反馈

更多Data Architecture相关文章

更多相关阅读内容

What are the best query hints to improve T-SQL stored procedure performance?

1

2

3

4

5

6

1 Why use query hints?

2 What are the types of query hints?

3 How to use query hints?

4 What are the best query hints?

5 What are some common query hints?

6 Here’s what else to consider

Data Architecture

给文章评分

感谢您的反馈

查看其他技能