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.

DBCC FREEPROCCACHE
SET STATISTICS TIME, IO ON
USE [StackOverflow]
SELECT b.[Id]
,b.[Name]
,u.[Age]
FROM [StackOverflow].[dbo].[Badges] b
join [dbo].[Users] u on u.Id=b.UserId
WHERE Left(b.Name,4) like 'Beta%'
SET STATISTICS IO OFF

Previously I created two indexes to cover this query

On User table index NCI_ID_Age

CREATE NONCLUSTERED INDEX [NCI_ID_Age] ON [dbo].[Users]
(
[Id] ASC
)
INCLUDE ( [Age]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
GO

On Badges table index NCI_Name

CREATE NONCLUSTERED INDEX [NCI_Name] ON [dbo].[Badges]
(
[Name] ASC,
[Id] ASC
)
INCLUDE ( [UserId]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
GO

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

DBCC FREEPROCCACHE
SET STATISTICS TIME, IO ON
USE [StackOverflow]
SELECT b.[Id]
,b.[Name]
,u.[Age]
FROM [StackOverflow].[dbo].[Badges] b
join [dbo].[Users] u on u.Id=b.UserId
WHERE Name like 'Beta%'
SET STATISTICS IO OFF

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

DBCC FREEPROCCACHE
SET STATISTICS TIME, IO ON
USE [StackOverflow]
SELECT b.[Id]
,b.[Name]
,u.[Age]
FROM [StackOverflow].[dbo].[Badges] b
join [dbo].[Users] u on u.Id=b.UserId
WHERE Name='Beta'
SET STATISTICS IO OFF

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:

• SQL Server / Query Optimization / String functions in the WHERE clause and bad estimates
• SQL Server / Query Optimization / Merge Join Operator / Sort Operator
• SQL Server / Query Optimization / TempDB Page Splits / Table Spool (Lazy Spool)
• SQL Server / Index Usage Report Project
• SQL Server / ALTER INDEX / CREATE INDEX Progress