[Translation] SQL Server optimization tips

Q: I occasionally find that the SQL Server built-in query optimizer is inefficient and the query plan is not optimized. How can I adjust the query plan?

A:
You can use the SQL Server optimization prompt to overwrite the query optimizer. Optimization tips can be divided into five categories:
Table prompt: used to force index selection
Query prompt: Used to affect group by and UNION functions
Lock prompt: Used to help avoid unwanted locks
View tip: used to specify indexes in the index View
Other Tips: various tips

In general, try to reduce the number of prompts. This is because the query optimizer is much more sensible in most cases, and the query plan it generates is usually optimal. If the specified prompt is incorrect or not the best, it will generate a query plan according to the error method, which continuously affects the performance.

If you think you may need to use a prompt to optimize your query, please first confirm that you have done the following steps that may be the cause of the problem:
1. update statistics of related tables
2. If the problem query is in the stored procedure, recompile the stored procedure and run it again to check whether the situation has been improved.
3. Check the search parameters to make sure they are searchable and optimized.
4. Check the current index and make changes if necessary.

If you have performed the preceding operations and the query still cannot be executed as expected, you need to consider using appropriate optimization tips.

Another problem with the usage prompt is that the prompt may only work in a specific scenario. If the scenario changes, the prompt may not be appropriate.

For more information, see http://msdn.microsoft.com/en-us/library/ms187713.aspx.