This blog post is inspired from SQL Architecture Basics Joes 2 Pros: Core Architecture concepts – SQL Exam Prep Series 70-433 – Volume 3.
[Amazon] | [Flipkart] | [Kindle] | [IndiaPlaza]
Basics of a Query Hint
Query hints specify that the indicated hints should be used throughout the query. Query hints affect all operators in the statement and are implemented using the OPTION clause.
The basic syntax structure for a Query Hint is shown below:
DECLARE @Type VARCHAR ( 50 ) SET @Type = 'Business' SELECT * FROM Customer WHERE CustomerType = @Type OPTION ( OPTIMIZE FOR ( @Type = 'Business' ));
Cautionary Note:
Because the SQL Server Query Optimizer typically selects the best execution plan for a query, it is highly recommended that hints be used as a last resort for experienced developers and database administrators to achieve the desired results.
Create an Index for improved Optimization
In the following examples the Customer Table has had a non-clustered index placed on the CustomerType field, as shown in the screenshot below:
This index should help query performance if the query is selective enough. That is, if the Query Optimizer gets the right information in time to make this kind of a decision it will perform a Seek which is much faster than a Scan. Variables often confuse the Query Optimizer in certain ways. You will see this demonstrated in the next exercise.
Expecting a Seek and getting a Scan
SQL Server is actually smart enough to take the exact same index and realize, through statistics, which of these criteria is selective and which is not. You have seen in the previous example that it is possible to accidentally trick the Query Optimizer into making the wrong decision.
First declare a variable named @Type, which is a varchar (50) and Set it equal to ‘Consumer’. This variable will be part of the predicate. Run the query with the variable set to ‘Consumer’ and you get 773 records. Change the @Type variable to ‘Business’ and you get a selective query which returns two records. See both figures below.
The query that predicates on ‘Consumer’ is better off scanning since it’s pulling almost all of the records from the entire table.
However, the query that predicates on ‘Business’ is a very selective query covered by the NCI_Customer_CustomerType index. Thus, you might be shocked to see that it is still a Scan instead of the anticipated Seek.
The Query Optimizer doesn’t know what value the @Type variable holds until runtime, and does not check statistics for variables before a query runs. When in doubt, the Query Optimizer will always choose a scan.
Implementing the Query Hint
When predicating on values from variables you may need to give the query a hint, letting the Query Optimizer know the best way to execute the query. If your @Type variable is most often set to the value of ‘Business’ it would be smarter to optimize this query to perform a Seek based on this value.
To do this, you will need to add some additional code to your query, shown in the screenshot below:
Excellent! The Query Execution Plan now indicates that it will use a Seek to perform this query and your work is done.
This blog post is inspired from SQL Architecture Basics Joes 2 Pros: Core Architecture concepts – SQL Exam Prep Series 70-433 – Volume 3.
[Amazon] | [Flipkart] | [Kindle] | [IndiaPlaza]
Reference: Pinal Dave (https://darkslategrey-bat-805937.hostingersite.com)
