技术控

    今日:150| 主题:49194
收藏本版 (1)
最新软件应用技术尽在掌握

[其他] SQL Server Enterprise Advanced Scan aka Merry-Go-Round Scan

[复制链接]
心刺爱人 发表于 2016-10-5 12:28:27
174 1

立即注册CoLaBug.com会员,免费获得投稿人的专业资料,享用更多功能,玩转个人品牌!

您需要 登录 才可以下载或查看,没有帐号?立即注册

x
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 cause disk 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.
  [code]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[/code]  We will be testing using 3 similarly constructed queries with different predicates, but each query will result in a full table scan.
  [code]-- 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)[/code]  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-1 (Microsoft,different,indicate,multiple,discuss)

  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-2 (Microsoft,different,indicate,multiple,discuss)

   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.
  [code]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[/code]  If you look at the resource_description column you can see that all three queries are going after the same data page.
123下一页
友荐云推荐




上一篇:Smooth Scrolling in Buffer for iOS: How (and Why) We Implemented AsyncDisplayKit
下一篇:Going Serverless: Migrating an Express Application to Amazon API Gateway and AWS
酷辣虫提示酷辣虫禁止发表任何与中华人民共和国法律有抵触的内容!所有内容由用户发布,并不代表酷辣虫的观点,酷辣虫无法对用户发布内容真实性提供任何的保证,请自行验证并承担风险与后果。如您有版权、违规等问题,请通过"联系我们"或"违规举报"告知我们处理。

煊宁善 发表于 2016-10-7 01:59:20
我们都知道恶虎架不住群狼。说明:”团队很重要!”
回复 支持 反对

使用道具 举报

*滑动验证:
您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

我要投稿

推荐阅读

扫码访问 @iTTTTT瑞翔 的微博
回页顶回复上一篇下一篇回列表手机版
手机版/CoLaBug.com ( 粤ICP备05003221号 | 文网文[2010]257号 )|网站地图 酷辣虫

© 2001-2016 Comsenz Inc. Design: Dean. DiscuzFans.

返回顶部 返回列表