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.

USE [AdventureWorks2012]

SELECT ADDR.City, COUNT(ADDR.City) AS 'NoOfCity'
	FROM Person.Address ADDR
GROUP BY ADDR.City

QueryPlanWithoutGroupHint

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).

HashMatchToolTip

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;

USE [AdventureWorks2012]

SELECT ADDR.City, COUNT(ADDR.City) AS 'NoOfCity'
	FROM Person.Address ADDR
GROUP BY ADDR.City
OPTION (ORDER GROUP)

QueryPlanWithGroupHint

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.

StAggrToolTip

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.

Happy Learning!

Regards,

Kanchan

Like us on FaceBook | Join the fastest growing SQL Server group on FaceBook | Follow me on Twitter | Follow me on FaceBook

Data Platform Virtual Summit 2020

Subscribe to SQLServerGeeks YouTube channel. If you want more learning content in your inbox, subscribe to SQLServerGeeks Bulletin.

SQLServerGeeks YouTube | SQLServerGeeks Bulletin | SQLServerGeeks Twitter

Avatar

About Kanchan Bhattacharyya

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.

View all posts by Kanchan Bhattacharyya →

Leave a Reply

Your email address will not be published. Required fields are marked *