{"id":1341,"date":"2012-05-21T00:00:00","date_gmt":"2012-05-21T00:00:00","guid":{"rendered":"https:\/\/test.simple-talk.com\/uncategorized\/sql-server-prefetch-and-query-performance\/"},"modified":"2021-06-03T16:44:12","modified_gmt":"2021-06-03T16:44:12","slug":"sql-server-prefetch-and-query-performance","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/performance-sql-server\/sql-server-prefetch-and-query-performance\/","title":{"rendered":"SQL Server Prefetch and Query Performance"},"content":{"rendered":"
\n

Introduction<\/h2>\n

Prefetching is often ignored by developers and DBAs when they are analyzing performance problems. After you’ve read this article I hope you will understand how prefetching works and why, and under what circumstances, this is so important for speeding-up your queries. I hope you’ll also be interested in the techniques and tools I’ve used to troubleshoot the problem, as they are useful for tracking a lot of other performance problems.<\/p>\n

In short, prefetching is used to execute I\/O operations in parallel, in a way that is somewhat similar to a read-ahead mechanism. It is used in the nested loops execution plan when there are more than a threshold number of rows in the outer input table.<\/span>   You can see whether prefetch is being used  by viewing the property WithOrderedPrefetch<\/code> or WithUnorderedPrefetch<\/code> in the nested loops operator. Prefetching can be ordered or not depending on your query. In this article I’ll show the behavior of unordered prefetching. If you’re interested, you can use the links in the end of this article to read more about ordered prefetching.<\/p>\n

Setting the environment<\/h2>\n

To demonstrate prefetching, I’ll start by creating a 500MB database on a USB flash drive:<\/p>\n

USE Master\nGO\nCREATE DATABASE TestPrefetching ON PRIMARY \n( NAME = N'TestPrefetching', FILENAME = N'H:\\TestPrefetching.mdf' , SIZE = 512000KB , FILEGROWTH = 1024KB )\n LOG ON \n( NAME = N'TestPrefetching_log', FILENAME = N'H:\\TestPrefetching_log.ldf' , SIZE = 1024KB , FILEGROWTH = 10%)\nGO\nALTER DATABASE TestPrefetching SET RECOVERY SIMPLE\nGO\n<\/pre>\n
With the database created, let’s create two tables in it, one called TestTab1<\/b> and another called TestTab2<\/b>. After I create the tables, I’ll update the column ID_Tab1<\/b> with some random values. It is a very simple database in which the TestTab2 <\/b>has a column related to the TestTab1<\/b>.<\/p>\n
After I create and populate the tables I’ll also update the statistics with fullscan to guarantee that the statistics are updated with the best precision.<\/p>\n
The following script took almost 6 mins to run in my notebook.<\/p>\n
USE TestPrefetching\nGO\nIF OBJECT_ID('TestTab1') IS NOT NULL\n  DROP TABLE TestTab1\nGO\nCREATE TABLE TestTab1 (ID Int IDENTITY(1,1) PRIMARY KEY, \n                       Col1 Char(5000), \n                       Col2 Char(1250),\n                       Col3 Char(1250),\n                       Col4 Numeric(18,2))\nGO\n-- 6 mins to run\nINSERT INTO TestTab1 (Col1, Col2, Col3, Col4)\nSELECT TOP 1000 NEWID(), NEWID(), NEWID(), ABS(CHECKSUM(NEWID())) \/ 10000000.\n  FROM sysobjects a\n CROSS JOIN sysobjects b\n CROSS JOIN sysobjects c\n CROSS JOIN sysobjects d\nGO 30\nCREATE INDEX ix_Col4 ON TestTab1(Col4)\nGO\nIF OBJECT_ID('TestTab2') IS NOT NULL\n  DROP TABLE TestTab2\nGO\nCREATE TABLE TestTab2 (ID Int IDENTITY(1,1) PRIMARY KEY,\n                       ID_Tab1 Int,\n                       Col1 Char(5000), \n                       Col2 Char(1250),\n                       Col3 Char(1250))\nGO\nINSERT INTO TestTab2 (ID_Tab1, Col1, Col2, Col3)\nSELECT TOP 1000 0, NEWID(), NEWID(), NEWID()\n  FROM sysobjects a\n CROSS JOIN sysobjects b\n CROSS JOIN sysobjects c\n CROSS JOIN sysobjects d\nGO 10\nCREATE INDEX ix_ID_Tab1 ON TestTab2(ID_Tab1)\nGO\n \nDECLARE @MenorValor Int = 1, @MaiorValor Int = 5000, @i Int\nSET @i = @MenorValor + ABS(CHECKSUM(NEWID())) % (@MaiorValor - @MenorValor)\n;WITH CTE_1\nAS\n(\n  SELECT ID, @MenorValor + ABS(CHECKSUM(NEWID())) % (@MaiorValor - @MenorValor) AS Col1\n    FROM TestTab1\n)\nUPDATE TestTab2 SET ID_Tab1 = CTE_1.Col1\n  FROM TestTab2\n INNER JOIN CTE_1\n    ON CTE_1.ID = TestTab2.ID\nGO\nUPDATE STATISTICS TestTab1 WITH FULLSCAN\nUPDATE STATISTICS TestTab2 WITH FULLSCAN\n<\/pre>\n
Let’s now check on the size of the tables we’ve created.<\/p>\n
-- Checking table size\nsp_spaceused TestTab1\nGO\nsp_spaceused TestTab2\nGO\n<\/pre>\n
\"1495-wpzncw7h.jpg\"<\/p>\n
As we can see, the table TestTab1 has 241 MB of data, and the table TestTab2 has 80 MB of data.<\/p>\n
Testing<\/h2>\n
Now that we’ve created the test scenario, we can execute the join between the two tables.<\/p>\n
CHECKPOINT\nDBCC DROPCLEANBUFFERS WITH NO_INFOMSGS\nGO\nSELECT TestTab1.Col4, TestTab2.Col1\n  FROM TestTab1 WITH(index=ix_Col4)\n INNER JOIN TestTab2\n    ON TestTab1.ID = TestTab2.ID_Tab1\n WHERE TestTab1.Col4 < 0.8\nOPTION (RECOMPILE)\n<\/pre>\n
The query above takes less than 1 second to finish and return 42 rows. Here are the results and the actual execution plan:<\/p>\n
\"1495-img4A.jpg\"<\/p>\n
Query Results<\/p>\n
\"1495-img4B.jpg\"<\/p>\n
Execution plan with 114 rows being returned from table TestTab1<\/p>\n
\"1495-img4C.jpg\"<\/p>\n
Prefetching is being executed by the Nested Loops operator<\/p>\n
As we can see it is a very straightforward execution plan with two Nested Loops operators. Let’s start investigating it in more detail.<\/p>\n
So  how does the loop join work? For each row from the “outer table” (in this case the table TestTab1), search for the match in the “inner table” (TestTab2). For more details about how joins work, look at the links in the final section of this article.<\/p>\n
There are 114 rows being returned from the TestTab1. For each row returned from the table TestTab1, SQL Server will execute an Index Seek on the TestTab2. This seek is a random read because the rows are being retrieved in the order of TestTab1.Col4.<\/p>\n
To optimize the unordered I\/O operations on the table TestTab2, it triggers many asynchronous I\/O operations in parallel, rather than executing one I\/O operation per row read on table TestTab1. You can see prefetching is being used because the nested loops property WithUnorderedPrefetch<\/code> is set to true. <\/p>\n
To compare the performance of this query without Prefetch, I’ll use the traceflag 8744 to disable the prefetch,  and the command QUERYTRACEON<\/code> to disable it only in the query scope.<\/p>\n
CHECKPOINT\nDBCC DROPCLEANBUFFERS WITH NO_INFOMSGS\nGO\n \nSELECT TestTab1.Col4, TestTab2.Col1\n  FROM TestTab1\n INNER JOIN TestTab2\n    ON TestTab1.ID = TestTab2.ID_Tab1\n WHERE TestTab1.Col4 < 0.8\nOPTION (RECOMPILE, QUERYTRACEON 8744)\n<\/pre>\n
The query above also run under 1 second, the only difference from the first query we ran is that I’m now using the trace flag to disable the prefetch. Even though prefetch was not used (you can confirm it looking at the nested loops operator properties, the property), there was no performance difference. <\/p>\n
Without prefetching, SQL Server will trigger the I\/O requests at something similar to “row-by-row”. In other words, for every row that is read on TestTab1,  it will request the I\/O on the table TestTab2: When the I\/O finishes,  it will request another I\/O operation for the second row and so on…<\/p>\n
It is important to understand here that SQL Server always read pages from memory (buffer cache), it means if a page is not in memory, it will request the page to the disk subsystem, put it in memory and then, read from memory.<\/p>\n
When using prefetching, many threads will request the required I\/O operations concurrently, so that  the I\/O requests are likely to finish earlier, because it os running many requests at the same time. Like read-ahead, it means that when data needed to be joined, the chances of a page to be in cache (because the I\/O operation already had finished and the page is in the buffer) is very closely to happen, and it means faster joins.<\/p>\n
The key point here is to understand that because the pages are put in memory earlier in the query execution, when the join is processed it already has the pages in memory and don’t need to wait for the pages being read from disk. Again, it is the same principle from read-ahead.<\/p>\n
Testing under disk pressure<\/h2>\n
To make things a little more interesting, what if we simulate the same behavior in a pressure disk subsystem? Let’s do it.<\/p>\n
To simulate disk pressure I’ll use the SQLIO tool to read some data on my flash drive. SQLIO is a tool provided by Microsoft which can be used to determine the I\/O capacity of a given configuration. It is very easy to setup and can give you valuable information about your disk system.<\/p>\n
To understand more about how to use SQLIO read the link in the end of the article.<\/p>\n
I’ll run the SQLIO with the following parameters:<\/p>\n
sqlio.exe -kR -t16 -dH -s600 -b64<\/pre>\n
\"1495-img4F.jpg\"<\/p>\n
If you had never used SQLIO, here is the translation for the parameters used above:<\/p>\n