SQL Server query hints execution plan – Part2

Hi Friends,

Welcome to the Part2 of SQL Server query hints execution plan and you can read Part1 by clicking here. Let us start with a simple query. In the example below we have a GROUP BY clause that is used to display count of each City in Address table.

Transact-SQL

1

2

3

4

5

USE[AdventureWorks2012]

SELECTADDR.City,COUNT(ADDR.City)AS'NoOfCity'

FROMPerson.AddressADDR

GROUPBYADDR.City

Default choice of the query optimizer to use Hashing. Clustered Index Scan will feed into Hash Match (Aggregate) (read here Part1, Part2, Part3) operator and this operator will build a hash table to select distinct values from index scan then count the values based on matched values. Estimated Subtree Cost of this operator is 0.348228 (this may vary in your environment).

As it is not performing the way we wanted to, let’s say all we want is to eliminate have ordered index scan instead of unordered hash match operation. And to do the same, all we need to do is to modify our query as following;

Transact-SQL

1

2

3

4

5

6

USE[AdventureWorks2012]

SELECTADDR.City,COUNT(ADDR.City)AS'NoOfCity'

FROMPerson.AddressADDR

GROUPBYADDR.City

OPTION(ORDERGROUP)

We instructed query optimizer to use ordering in place of hashing. So instead of hash table it is forced to use SORT (read here Part1, Part2) operator which feed the data into Stream Aggregate (read here Part1, Part2, Part3). We see that Estimated Subtree Cost for Stream Aggregate is 1.4876 (this may vary in your environment) which is higher than hashing.

What it means is, though query hints allow you to control behaviour of query optimizer, but that does not guarantee that they will provide you better results.

Note: This is just an illustration and you should try this in live environments.

You can find index to the execution plan series here and click on One operator a day to visit exclusive page for this series.

Share This Story, Choose Your Platform!

Kanchan is an astute IT professional, a seasoned SQL Database Administrator with 13+ years of industry experience. A calculated risk taker with deep technical knowledge and has gained proficiency in database consulting and solutions across different environments. Kanchan holds MBA degree in IT and an International Executive MBA in Project Management. He is very passionate about SQL Server and holds MCSE (Data Platform), MCSA – SQL 2012, MCITP – SQL 2008 certifications. Currently he is focusing on cloud and latest releases of SQL Server. When not working, Kanchan likes to spend his time reading on new technical developments specifically on SQL Server and other related technologies.