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.
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. http://sqlmag.com/database-performance-tuning/merry-go-round-scans-culprit-performance-variances. 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.
ReplyDeleteYes, 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.
ReplyDeleteThank you for your comment.