Wednesday, June 24, 2015

SQL Server Advanced Scan, aka Merry-go-round or Scan Piggybacking

I think SQL Server 2005 was the first SQL Server version with Advanced Scan. I have been excited by this feature, comforted by the thought my databases are performing better, but also a little unsure of its efficacy. According to the documentation, when one query is running a table scan, subsequent queries that require a table scan will "piggyback" on the first scan, avoiding additional scan IO. Apparently, any number of queries can all piggyback on the same scan. Once the first one completes, the others carry on from the beginning, as they may have joined in after the first scan started. So there should never be more than one scan of any one table at a point in time.

What a great feature? However, there are no Profiler traces for Advanced Scan, so how do you know it is actually working? I've just ran a performance test of Advanced Scan with 6 concurrent queries that all scan the same table (with slightly different predicates). I ran another test with one query running solo.

I was impressed with the results.
The table I used is about 10 times the size of the available memory, so SQL had no chance of caching the table. The solo scan ran as fast as the read ahead engines could provide the data. The multiple read engines (from the solo scan) kept the SAN storage busy with an IO queue count constantly greater 50 and reached a steady state of about 100MB/second. I ran this a couple of times to warm up the system. Note, this single query was running with MAXDOP=1. It would not run faster with MAXDOP=0 as the SAN storage was already working at maximum throughput with the workload of the multiple readahead engines.

I then ran 6 queries concurrently that all required a scan of the same table, but with slightly different predicates. These 6 scans all completed in a similar time as the first solo scan! That told me that Advanced Scan was working, but where could I see it? I could not find any evidence in Profiler (apart from the duration) that Advanced Scan was operating. In the queries I had SET STATISTICS IO ON. The statistics had the evidence that Advanced Scan was functioning. All 7 queries had similar logical reads, physical reads, CPU time, and elapsed time. However, the 6 concurrent queries had much smaller read-ahead reads. The total for the 6 concurrent queries was equivalent to the one solo scan.

It's unfortunate, that Profiler does not have an Advanced Scan event (or flag on scan). However, this test does demonstrate that the feature works and that it can be quantified.

Note: Advanced Scan is only available in Enterprise Edition. I initially tried to monitor this feature in a developer version of SQL Server, but it gave mixed results. Also note, the test above had Max Degree of Parallelism set to 1. I received similar results with MAXDOP set to 0.


msense said...

Very interesting post often lost in today’s world of virtualizing and “forgetaboutit”. Curious why Dev edition did not agree with enterprise edition since Dev Edition is feature-identical to the Ent Edition. I would like to see more on setup normalization and scripts to make sense of your table of results (checkpoint and dbcc dropcleanbuffers at first start) memory size, timing of concurrent execution causing joining of tasks at the start and then re-looping through the scan to provide missing rows to the late-comers assuming scatter-gather I/O, etc. Since there is only 23.5% decrease in time of concurrent queries vs solo query, and IO queue count > 50 with steady state 100MB/sec, I would like to explore effects/ contribution from automated Storage tiering (ex. EMC FAST), logical and physical fragmentation since if index contains 64 or more pages, IAM driven scans come back ordered by the clustering key, but the overall order of pages is in the order the pages were written as it reads pages in physical order on the disk resulting in sequential IO promoting effective read-ahead, but fragmentation will result in random IO making read-ahead less effective, etc.

Richard Lees said...

Yes, it would be interesting to take this test further. I will do that when I have the time or pressing need. The key takeaway was that in a IO constrained database system (which probably includes the majority of vldb systems) Advanced scan can do the same work with less IO demands.

Thank you for your comment.