未加星标

SQL Server Enterprise Advanced Scan aka Merry-Go-Round Scan

字体大小 | |
[数据库(mssql) 所属分类 数据库(mssql) | 发布者 店小二05 | 时间 2016 | 作者 红领巾 ] 0人收藏点击收藏

By:Simon Liew || Related Tips:More >Performance Tuning

Problem

A rarely known feature of SQL Server Enterprise Edition is the Advanced Scan (aka Merry-Go-Round Scan). Microsoft does not seem to mention this feature much, so there is a lack of documentation about how the Advanced Scan works. Also, there doesn't seem to be a trace flag or Extended Events which indicate if queries are using the Advanced Scan. In this tip we will discuss the Advance Scan feature and demonstrate its characteristics.

Solution

In SQL Server Enterprise Edition, the Advanced Scan feature allows multiple tasks to share full table scans. This means that SQL Server can read a data page once and provide the same page across different executing sessions. This feature only activates when the table size scanned is larger than the SQL Server buffer pool committed target pages.

Here is an explanation of how Advanced Scan works:

Let's say there is a table named TableA that contains 200,000 data pages. Query 1 performs a table scan on TableA and the table scan reads the first 100,000 pages. Query 2 is started which also requires a table scan on TableA. SQL Server can schedule another set of read requests for the pages after 100,000 and pass the rows to both scans that are occurring. When Query 1 and Query 2 reach page 200,000, Query 1 will complete, but Query 2 will wrap back to the first data page and continue to scan until it reaches page 100,000 and then completes. If other queries are started while Query 1 and Query 2 are running that require a table scan on TableA, SQL Server can join the new query to the executing table scan the same way. Hence, this how the Advanced Scan feature works which is also referred to as a merry-go-round scan.

The significance of this feature is multiple tasks are not requesting different data pages individually when performing a scan on the same table. For example, if data page 100,000 is read by Query 1 into the buffer pool, but this data page is already flushed out due to a limited buffer pool size then Query 2 comes along later to request the same data page which will need be read back into memory again. If you have a busy system, this can easily overwhelm the SQL Server buffer pool and causedisk arm contention on very large table scans.

SQL Server Table Scan Exhibiting Advanced Scan Behavior

In this demonstration, SQL Server 2016 Developer Edition RTM is running on a windows Server 2012 R2 Standard virtual machine on a laptop. The virtual machine is allocated 1.5GB of server memory and the SQL Server maximum memory is set to 1GB. The underlying storage is a 1TB Solid State Disk without any RAID.

A test table will be created with each row taking a full 8K data page. We will insert 2 million rows into the test table which will occupy 15.6GB of space. The test table size is 15 times the size of the SQL Server buffer pool, so SQL Server will be reading data pages mostly from disk and quickly flushing out data pages from the buffer cache during this test.

First we create a database, a table and insert some data.

CREATE DATABASE [AdvancedScan]
CONTAINMENT = NONE
ON PRIMARY
( NAME = N'AdvancedScan', FILENAME = N'D:\SQLDATA\mssql13.MSSQLSERVER\MSSQL\DATA\AdvancedScan.mdf' , SIZE = 20480000KB , FILEGROWTH = 65536KB )
LOG ON
( NAME = N'AdvancedScan_log', FILENAME = N'E:\SQLDATA\MSSQL13.MSSQLSERVER\MSSQL\Log\AdvancedScan_log.ldf' , SIZE = 20480000KB , FILEGROWTH = 65536KB )
GO
ALTER DATABASE [AdvancedScan] SET RECOVERY SIMPLE
GO
CREATE TABLE dbo.AdvScanTest
(ID INT IDENTITY(1,1)
, FixedCol CHAR(6000) DEFAULT 'a')
GO
SET NOCOUNT ON;
DECLARE @c INT = 1
BEGIN TRAN
WHILE @c <= 2000000
BEGIN
INSERT INTO dbo.AdvScanTest DEFAULT VALUES
SET @c += 1
END
COMMIT TRAN

We will be testing using 3 similarly constructed queries with different predicates, but each query will result in a full table scan.

-- Query 1
SELECT COUNT(*) FROM dbo.AdvScanTest
WHERE ID BETWEEN 64565 AND 546412
OPTION (MAXDOP 1)
-- Query 2
SELECT COUNT(*) FROM dbo.AdvScanTest
WHERE ID >= 1200000
OPTION (MAXDOP 1)
-- Query 3
SELECT COUNT(*) FROM dbo.AdvScanTest
OPTION (MAXDOP 1)

When one of the queries is executed for the first time, it took 33 seconds to complete as shown below.


SQL Server Enterprise Advanced Scan aka Merry-Go-Round Scan

The three queries are executed around the same time in three different query windows and the execution duration is recorded. To completely clear out everything from memory, the SQL Server service is restarted before each round of execution. This means no data page will remain in the SQL Server buffer cache when the next set of test queries is executed.

Each query is executed 5 times and the duration is measured in seconds in the table below. We seem to be getting pretty consistent results and durations when just a single table scan is occurring. This is probably because the test is run on a laptop with no other users hitting the database server.

Query 1 Query 2

Query 3

Run 1 42 seconds 42 seconds 41 seconds Run 2 34 seconds 33 seconds 33 seconds Run 3 34 seconds 34 seconds 33 seconds Run 4 34 seconds 35 seconds 36 seconds Run 5 33 seconds 33 seconds 32 seconds
SQL Server Enterprise Advanced Scan aka Merry-Go-Round Scan

Checking the SQL Server DMV sys.dm_os_waiting_tasks which returns information about the wait queues for tasks that are waiting on some resource. All three sessions are requesting and waiting on the same data page at any given point-in-time, albeit the queries are scheduled on different SQLOS schedulers. This seems to conform to the Advanced Scan behavior described on MSDN where a single data page read is passed to multiple tasks.

The following T-SQL script should be run in a different query window while the above queries are running.

SELECT rq.start_time, ot.task_state, wt.session_id, ot.task_state
, wt.wait_type, wt.resource_description, ot.scheduler_id
FROM sys.dm_os_waiting_tasks wt
INNER JOIN sys.dm_os_tasks ot ON ot.task_address = wt.waiting_task_address
INNER JOIN sys.dm_exec_sessions es ON es.session_id = wt.session_id
INNER JOIN sys.dm_exec_requests rq ON rq.session_id = es.session_id
WHERE es.is_user_process = 1 AND es.session_id <> @@SPID
ORDER BY es.session_id

If you look at the resource_description column you can see that all three queries are going after the same data page.


SQL Server Enterprise Advanced Scan aka Merry-Go-Round Scan
Table Scan Reading Different Data Pages

For this test, we will execute the same set of queries again in different query windows, but each query is executed 5 seconds after the previous query is started. So, Query 1 is started, then 5 seconds later Query 2 is started and then 10 seconds later Query 3 is started.

Before each set of executions, the SQL Server service is restarted to make sure the SQL Server buffer pool does not contain any cached data pages.

The queries are executed 5 times and the query duration is recorded below.

Query 1 Query 2 Query 3

本文数据库(mssql)相关术语:熊片数据库 mssql数据库 oracle数据库 pubmed数据库 access数据库 万方数据库

主题: SQLSQL ServerWindowsSDNWindows ServWindows ServerTIAUOPTRY
分页:12
转载请注明
本文标题:SQL Server Enterprise Advanced Scan aka Merry-Go-Round Scan
本站链接:http://www.codesec.net/view/480327.html
分享请点击:


1.凡CodeSecTeam转载的文章,均出自其它媒体或其他官网介绍,目的在于传递更多的信息,并不代表本站赞同其观点和其真实性负责;
2.转载的文章仅代表原创作者观点,与本站无关。其原创性以及文中陈述文字和内容未经本站证实,本站对该文以及其中全部或者部分内容、文字的真实性、完整性、及时性,不作出任何保证或承若;
3.如本站转载稿涉及版权等问题,请作者及时联系本站,我们会及时处理。
登录后可拥有收藏文章、关注作者等权限...
技术大类 技术大类 | 数据库(mssql) | 评论(0) | 阅读(36)