SQL SERVER – Introduction to Basics of a Query Hint – A Primer

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:

CTE Screenshot 2

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 (http://blog.sqlauthority.com)

8 thoughts on “SQL SERVER – Introduction to Basics of a Query Hint – A Primer

  1. If I have another Customer Type Visiting with almost same row of Business type then Can I add another Hint
    option(Optimize for (@type=’visting’))

    Like

  2. Pingback: SQL SERVER – Quiz and Video – Introduction to Basics of a Query Hint « SQL Server Journey with SQL Authority

  3. Pingback: SQL SERVER – SQL in Sixty Seconds – 5 Videos from Joes 2 Pros Series – SQL Exam Prep Series 70-433 « SQL Server Journey with SQL Authority

  4. Pingback: SQL SERVER – 5 Videos from Joes 2 Pros Series Exam Prep Series 70-433 – SQL in Sixty Seconds « SQL Server Journey with SQL Authority

  5. Hi ,
    I am trying to get xml from a location into a table using rowset, the following is the query, but n but its not accepting as valid fiile name. Please help me
    CREATE TABLE #XmlImportTest(
    xmlFileName VARCHAR(300) NOT NULL,
    xml_data XML NOT NULL
    )
    GO
    declare @directory varchar(256) set @directory = ‘”D:\To Folder”‘

    declare @filecount int, @fileindex int, @linecount int, @lineindex int
    declare @filename varchar(255), @arg varchar(512), @contents varchar(8000)
    set @filename = ‘D:\To Folder’
    set @arg = ‘dir ‘ + @directory + ‘\ /A-D /B’
    declare @dir table (id int not null identity(1,1),[filename] varchar(512))
    insert @dir exec master.dbo.xp_cmdshell @arg
    DECLARE @ID int
    DECLARE IDs CURSOR LOCAL FOR select id from @dir
    OPEN IDs
    FETCH NEXT FROM IDs into @ID
    WHILE @@FETCH_STATUS = 0
    BEGIN
    SET @filename = @filename + ‘\’+ (select filename from @dir where id = @ID)

    print @filename
    declare @files table ([xmlFileName] varchar(512), [xml_data] xml null)

    INSERT INTO @files(xmlFileName, xml_data)

    SELECT ”’ + @filename + ”’, xmlData
    FROM(
    SELECT *
    FROM OPENROWSET (BULK ”’ + @filename +”’, SINGLE_BLOB) AS XMLDATA
    ) AS FileImport (XMLDATA)

    FETCH NEXT FROM IDs into @ID
    END
    CLOSE IDs
    DEALLOCATE IDs

    Like

  6. Hi ,
    I am trying to get xml from a location into a table using rowset, the following is the query, but n but its not accepting as valid fiile name. Please help me
    CREATE TABLE #XmlImportTest(
    xmlFileName VARCHAR(300) NOT NULL,
    xml_data XML NOT NULL
    )
    GO
    declare @directory varchar(256) set @directory = ‘”D:\To Folder”‘

    declare @filecount int, @fileindex int, @linecount int, @lineindex int
    declare @filename varchar(255), @arg varchar(512), @contents varchar(8000)
    set @filename = ‘D:\To Folder’
    set @arg = ‘dir ‘ + @directory + ‘\ /A-D /B’
    declare @dir table (id int not null identity(1,1),[filename] varchar(512))
    insert @dir exec master.dbo.xp_cmdshell @arg
    DECLARE @ID int
    DECLARE IDs CURSOR LOCAL FOR select id from @dir
    OPEN IDs
    FETCH NEXT FROM IDs into @ID
    WHILE @@FETCH_STATUS = 0
    BEGIN
    SET @filename = @filename + ‘\’+ (select filename from @dir where id = @ID)

    print @filename
    declare @files table ([xmlFileName] varchar(512), [xml_data] xml null)

    INSERT INTO @files(xmlFileName, xml_data)

    SELECT ”’ + @filename + ”’, xmlData
    FROM(
    SELECT *
    FROM OPENROWSET (BULK ”’ + @filename +”’, SINGLE_BLOB) AS XMLDATA
    ) AS FileImport (XMLDATA)

    FETCH NEXT FROM IDs into @ID
    END
    CLOSE IDs
    DEALLOCATE IDs

    Like

  7. Pingback: SQL SERVER – Weekly Series – Memory Lane – #026 | SQL Server Journey with SQL Authority

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s