Learn more about SQL Server tools



solving sql server problems for millions of dbas and developers since 2006 join MSSQLTips for free SQL Server tips
































Top SQL Server Tools



































   Got a SQL tip?
            We want to know!

Speed up SQL Server queries with PREFETCH

MSSQLTips author Matteo Lorini By:   |   Read Comments (3)   |   Related Tips: More > Performance Tuning
Problem

The SAN data volume has a throughput capacity of 400MB/sec; however my query is still running slow and it is waiting on I/O (PAGEIOLATCH_SH). Windows Performance Monitor shows data volume speed of 4MB/sec. Where is the problem and how can I find the problem?

Solution

This is another summary of a great article published by R. Meyyappan at www.sqlworkshops.com. In my opinion, this is the first article that highlights and explains with working examples how PREFETCH determines the performance of a Nested Loop join. First of all, I just want to recall that Prefetch is a mechanism with which SQL Server can fire up many I/O requests in parallel for a Nested Loop join.

When SQL Server executes a Nested Loop join, it may or may not enable Prefetch accordingly to the number of rows in the outer table. If the number of rows in the outer table is greater than 25 then SQL will enable and use Prefetch to speed up query performance, but it will not if it is less than 25 rows.

In this section we are going to see different scenarios where prefetch is automatically enabled or disabled.

These examples only use two tables RegionalOrder and Orders. If you want to create the sample tables and sample data, please visit this site www.sqlworkshops.com.

The breakdown of the data in the RegionalOrders table is shown below and the Orders table contains about 6 million rows.

when SQL Server executes a Nested Loop join, it may or may not enable Prefetch


In this first example, I am creating a stored procedure against two tables and then execute the stored procedure. Before running the stored proceudre, I am going to include the actual execution plan.

--Example provided by www.sqlworkshops.com
--Create procedure that pulls orders based on City
--Do not forget to include the actual execution plan
CREATE PROC RegionalOrdersProc @City CHAR(20)
AS
BEGIN
DECLARE
@OrderID INT, @OrderDetails CHAR(200)
SELECT @OrderID = o.OrderID, @OrderDetails = o.OrderDetails
FROM RegionalOrders ao INNER JOIN Orders o ON (o.OrderID = ao.OrderID)
WHERE City = @City
END
GO

SET STATISTICS time ON
GO
--Example provided by www.sqlworkshops.com
--Execute the procedure with parameter SmallCity1
EXEC RegionalOrdersProc 'SmallCity1'
GO

After running the stored procedure, if we right click on the Clustered Index Scan and click Properties we can see the Estimated Numbers of Rows is 24.

creating a stored procedure against two tables

If we right click on Nested Loops and click Properties we do not see Prefetch, because it is disabled. This behavior was expected, because the number of rows containing the value ‘SmallCity1' in the outer table is less than 25.

right click on Nested Loops and click Properties we do not see Prefetch


Now, if I run the same procedure with parameter ‘BigCity' will Prefetch be enabled?

--Example provided by www.sqlworkshops.com
--Execute the procedure with parameter BigCity
--We are using cached plan
EXEC RegionalOrdersProc 'BigCity'
GO

As we can see from the below screenshot, prefetch is not enabled and the query takes around 7 seconds to execute. This is because the query used the cached plan from ‘SmallCity1' that had prefetch disabled. Please note that even if we have 999 rows for ‘BigCity' the Estimated Numbers of Rows is still 24.

prefetch is not enabled and the query takes around 7 seconds to execute


Finally, let's clear the procedure cache to trigger a new optimization and execute the procedure again.

DBCC freeproccache
GO
EXEC RegionalOrdersProc 'BigCity'
GO

This time, our procedure runs under a second, Prefetch is enabled and the Estimated Number of Rows is 999.

our procedure runs under a second, Prefetch is enabled


The RegionalOrdersProc can be optimized by using the below example where we are using an optimizer hint. I have also shown some other hints that could be used as well.

--Example provided by www.sqlworkshops.com
--You can fix the issue by using any of the following
--hints
--Create procedure that pulls orders based on City
DROP PROC RegionalOrdersProc
GO
CREATE PROC RegionalOrdersProc @City CHAR(20)
AS
BEGIN
DECLARE
@OrderID INT, @OrderDetails CHAR(200)
SELECT @OrderID = o.OrderID, @OrderDetails = o.OrderDetails
FROM RegionalOrders ao INNER JOIN Orders o ON (o.OrderID = ao.OrderID)
WHERE City = @City
--Hinting optimizer to use SmallCity2 for estimation
OPTION (optimize FOR (@City = 'SmallCity2'))
--Hinting optimizer to estimate for the currnet parameters
--option (recompile)
--Hinting optimize not to use histogram rather
--density for estimation (average of all 3 cities)
--option (optimize for (@City UNKNOWN))
--option (optimize for UNKNOWN)
END
GO

Conclusion, this tip was mainly aimed at illustrating how Prefetch can speed up query execution and how the different number of rows can trigger this.

Next Steps
  • Next time you see a Nested Loop join, see if prefetch is enabled or not.
  • Keep in mind how small variances of data can totally change SQL Server behavior
  • Check out this site for additional optimization techniques


Last Update: 12/8/2010


About the author
MSSQLTips author Matteo Lorini
Matteo Lorini is a DBA and has been working in IT since 1993. He specializes in SQL Server and also has knowledge of MySQL.

View all my tips
Related Resources


print tip Print  
Become a paid author





join MSSQLTips for free SQL Server tips     



Comments and Feedback:
Wednesday, December 08, 2010 - 9:29:51 AM - Steve S Read The Tip

It seems to me you would want to optimize for "BigCity".  Optimizing for "SmallCity" ensures that the plan in cache always expects less than 25 and prefetch will be disabled.

 


Wednesday, December 08, 2010 - 2:38:14 PM - Matteo Read The Tip

Due to the fact that SmallCity2 has 26 rows, if you optimize for it prefetch will be anabled.


Tuesday, December 14, 2010 - 5:38:03 AM - Sujeet Kumar Read The Tip

Its a nice article. Has helped actually. Thanks.



Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name   *Email Notify for updates



Comments
Get free SQL tips:

*Enter Code refresh code


 
Sponsor Information







Copyright (c) 2006-2014 Edgewood Solutions, LLC All rights reserved
privacy | disclaimer | copyright | advertise | about
authors | contribute | feedback | giveaways | free t-shirt | user groups | community | events | first timer?
Some names and products listed are the registered trademarks of their respective owners.