SQL Server / Query Optimization / Memory Grant Warning / Excessive Grant

Recently we had really nice meetup gathering here in Auckland. Was a beautiful evening, there were 20 people talking about SQL server, was perfect. One of the topics was “Bad estimates and Memory Grant Warning”

For this demo I used SQL Server 2014 Developer Edition.

As a database I used StackOverflow, you can download this DB from Brent Ozar web page, just follow the instructions

How to Download the Stack Overflow Database via BitTorrent

 

In the previous post we talked about using string function in the WHERE clause and bad estimates within query plan. Here is one example how wrong estimates can cause other problems.

This time I will use two tables Badges (25 million rows) and Users (8 million rows) and I will join them on userid column. In the WHERE clause I will use STRING FUNCTION LEFT. LEFT Returns the left part of a character string with the specified number of characters.

 

Previously I created two indexes to cover this query

On User table index NCI_ID_Age

 

On Badges table index NCI_Name

 

Execution Plan look like this

 

We have two Non-Clustered Index scans, so lets check estimates on those Index scan operators

Index Scan on Badges using index NCI_Name

 

Estimates are bad, Estimated Number of Rows is over two million and actual number is only 2511, and since we have a index scan operator we actually read whole table with over 20 million rows.

Lets check now Index Scan operator on Users table (non-clustered index NCI_ID_Age)

 

When query requests smaller number of rows of the whole table (approx 10-15%) you will probably have Index Seek as an retrieving data operator (If you have proper index on that table), but when you selecting majority of the rows from your table, you will probably end up with Index Scan within your Execution Plan.

You probably noticed warning sign on our select operator

The reason for that is wrongly estimated number of rows, there is a big difference between GrantedMemory (Based on Estimated number of rows) and MaxUsedMemory 628144>19392

 

Excessive Memory Grant can cause less efficient usage and less remaining memory which can be utilized for other operations, on following MSDN article you can find more information about memory grant related diagnostics.

So lets fix those estimates.

We should remove/replace that LEFT string function in the WHERE Clause, lets see next example

 

Now we have non-clustered index seek on both tables Badges and Users, and we have Nested Loop instead of Hash Match Join

 

Estimates for the Badges table Index Seek operator are more close to actual number of rows. Now we have Estimated Number of Rows equal to 6738, and Number of Rows Read is equal to the Actual Number of Rows.

 

Estimates for the Users table Index Seek operator, since we have Nested Loop join Estimated Number of Executions will be equal to Estimated Number of rows in joining Index Seek operator on Badges table.

 

If we change the query little bit more and use ‘=’ instead of ‘like’ within WHERE clause

 

Plan look the same but estimates are exactly as should be.

 

If we look Index seek operator on Badges table our estimates, actual number of rows and actual number of rows read are the same, 2511.

 

Same for the Users table

 

 

Similar Posts:

Leave a Reply

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