技术控

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

[其他] Investigating the proportional fill algorithm

[复制链接]
回首望不到邊 发表于 2016-10-5 02:05:48
69 0

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

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

x
This is something that came up recently on the Microsoft Certified Master DL, and is something I discuss in our IEPTO1 class because of the performance implications of it, so I thought it would make an interesting post.
  Allocation Algorithms

  The SQL Server Storage Engine (SE) uses two algorithms when allocating extents from files in a filegroup: round robin and proportional fill.
  Round robin means that the SE will try to allocate from each file in a filegroup in succession. For instance, for a database with two files in the primary filegroup (with file IDs 1 and 3, as 2 is always the log file), the SE will try to allocate from file 1 then file 3 then file 1 then file 3, and so on.
  The twist in this mechanism is that the SE also has to consider how much free space is in each of the files in the filegroup, and allocate more extents from the file(s) with more free space. In other words, the SE will allocate proportionally more frequently from files in a filegroup with more free space. This twist is called proportional fill.
  Proportional fill works by assigning a number to each file in the filegroup, called a ‘skip target’. You can think of this as an inverse weighting, where the higher the value is above 1, the more times that file will be skipped when going round the round robin loop. During the round robin, the skip target for a file is examined, and if it’s equal to 1, an allocation takes place. If the skip target is higher than 1, it’s decremented by 1 (to a minimum value of 1), no allocation takes place, and consideration moves to the next file in the filegroup.
  (Note that there’s a further twist to this: when the -E startup parameter is used, each file with a skip target of 1 will be used for 64 consecutive extent allocations before the round robin loop progresses. This is documented in Books Online    hereand is useful for increasing the contiguity of index leaf levels for very large scans – think data warehouses.)  
  The skip target for each file is the integer result of (number of free extents in file with most free space) / (number of free extents in this file). The files in the filegroup with the least amount of free space will therefore have the highest skip targets, and there has to be at least one file in the filegroup with a skip target of 1, guaranteeing that each time round the round robin loop, at least one extent allocation takes place.
  The skip targets are recalculated whenever a file is added to or removed from a filegroup, or at least 8192 extent allocations take place in the filegroup.
  Investigating the Skip Targets

  There’s an undocumented trace flag, 1165, that lets us see the skip targets whenever they’re recalculated and I believe the trace flag was added in SQL Server 2008. It also requires trace flag 3605 to be enabled to allow the debugging info to be output.
  Let’s try it out!
  First I’ll turn on the trace flags, cycle the error log, creating a small database, and look in the error log for pertinent information:
  1. DBCC TRACEON (1165, 3605);
  2. GO

  3. EXEC sp_cycle_errorlog;
  4. GO

  5. USE [master];
  6. GO

  7. IF DATABASEPROPERTYEX (N'Company', N'Version') > 0
  8. BEGIN
  9.         ALTER DATABASE [Company] SET SINGLE_USER
  10.                 WITH ROLLBACK IMMEDIATE;
  11.         DROP DATABASE [Company];
  12. END
  13. GO

  14. CREATE DATABASE [Company] ON PRIMARY (
  15.     NAME = N'Company_data',
  16.     FILENAME = N'D:\SQLskills\Company_data.mdf',
  17.         SIZE = 5MB,
  18.     FILEGROWTH = 1MB)
  19. LOG ON (
  20.     NAME = N'Company_log',
  21.     FILENAME = N'D:\SQLskills\Company_log.ldf'
  22. );

  23. EXEC xp_readerrorlog;
  24. GO
复制代码
  1. 2016-10-04 11:38:33.830 spid56       Proportional Fill Recalculation Starting for DB Company with m_cAllocs -856331000.
  2. 2016-10-04 11:38:33.830 spid56       Proportional Fill Recalculation Completed for DB Company new m_cAllocs 8192, most free file is file 1.
  3. 2016-10-04 11:38:33.830 spid56               File [Company_data] (1) has 44 free extents and skip target of 1.
复制代码
The    m_cAllocsis the threshold at which the skip targets will be recalculated. In the first line of output, it has a random number as the database has just been created and the counter hasn’t been initialized yet. It’s the name of a class member of the C++ class inside the SE that implements filegroup management.  
  Now I’ll add another file with the same size:
  1. ALTER DATABASE [Company] ADD FILE (
  2.         NAME = N'SecondFile',
  3.         FILENAME = N'D:\SQLskills\SecondFile.ndf',
  4.         SIZE = 5MB,
  5.     FILEGROWTH = 1MB);
  6. GO

  7. EXEC xp_readerrorlog;
  8. GO
复制代码
  1. 2016-10-04 11:41:27.880 spid56       Proportional Fill Recalculation Starting for DB Company with m_cAllocs 8192.
  2. 2016-10-04 11:41:27.880 spid56       Proportional Fill Recalculation Completed for DB Company new m_cAllocs 8192, most free file is file 3.
  3. 2016-10-04 11:41:27.880 spid56               File [Company_data] (1) has 44 free extents and skip target of 1.
  4. 2016-10-04 11:41:27.880 spid56               File [SecondFile] (3) has 79 free extents and skip target of 1.
复制代码
Note that even though the two files have different numbers of extents, the integer result of 79 / 44 is 1, so the skip targets are both set to 1.
  Now I’ll add a much larger file:
  1. ALTER DATABASE [Company] ADD FILE (
  2.         NAME = N'ThirdFile',
  3.         FILENAME = N'D:\SQLskills\ThirdFile.ndf',
  4.         SIZE = 250MB,
  5.     FILEGROWTH = 1MB);
  6. GO

  7. EXEC xp_readerrorlog;
  8. GO
复制代码
  1. 2016-10-04 11:44:20.310 spid56       Proportional Fill Recalculation Starting for DB Company with m_cAllocs 8192.
  2. 2016-10-04 11:44:20.310 spid56       Proportional Fill Recalculation Completed for DB Company new m_cAllocs 8192, most free file is file 4.
  3. 2016-10-04 11:44:20.310 spid56               File [Company_data] (1) has 44 free extents and skip target of 90.
  4. 2016-10-04 11:44:20.310 spid56               File [ThirdFile] (4) has 3995 free extents and skip target of 1.
  5. 2016-10-04 11:44:20.310 spid56               File [SecondFile] (3) has 79 free extents and skip target of 50.
复制代码
The file with the most free space is file ID 4, so the skip targets of the other files are set to (file 4’s free extents) / (free extents in the file). For example, the skip target for file 1 becomes the integer result of 3995 / 44 = 90.
  Now I’ll create a table that can have only one row per page, and force more than 8192 extent allocations to take place (by inserting more than 8192 x 8 rows, forcing that many pages to be allocated). This will also mean the files will have autogrown and will have roughly equal numbers of free extents.
  1. USE [Company];
  2. GO

  3. CREATE TABLE [BigRows] (
  4.         [c1] INT IDENTITY,
  5.         [c2] CHAR (8000) DEFAULT 'a');
  6. GO

  7. SET NOCOUNT ON;
  8. GO

  9. INSERT INTO [BigRows] DEFAULT VALUES;
  10. GO 70000

  11. EXEC xp_readerrorlog;
  12. GO
复制代码
  1. 2016-10-04 11:55:28.840 spid56       Proportional Fill Recalculation Starting for DB Company with m_cAllocs 8192.
  2. 2016-10-04 11:55:28.840 spid56       Proportional Fill Recalculation Completed for DB Company new m_cAllocs 8192, most free file is file 3.
  3. 2016-10-04 11:55:28.840 spid56               File [Company_data] (1) has 0 free extents and skip target of 74.
  4. 2016-10-04 11:55:28.840 spid56               File [ThirdFile] (4) has 0 free extents and skip target of 74.
  5. 2016-10-04 11:55:28.840 spid56               File [SecondFile] (3) has 74 free extents and skip target of 1.
复制代码
We can see that all the files have filled up and auto grown, and randomly file ID 3 is now the one with the most free space.
  Spinlock Contention

  The skip targets for the files in a filegroup are protected by the    FGCB_PRP_FILLspinlock, so this spinlock has to be acquired for each extent allocation, to determine which file to allocate from next. There’s an exception to this when all the files in a filegroup have roughly the same amount of free space (so they all have a skip target of 1). In that case, there’s no need to acquire the spinlock to check the skip targets.  
  This means that if you create a filegroup that has file sizes that are different, the odds are that they will auto grow at different times and the skip targets will not all be 1, meaning the spinlock has to be acquired for each extent allocation. Not a huge deal, but it’s still extra CPU cycles and the possibility of spinlock contention occurring (for a database with a lot of insert activity) that you could avoid by making all the files in the filegroup the same size initially.
  If you want, you can watch the    FGCB_PRP_FILLspinlock (and others) using the code fromthis blog post.  
  Performance Implications

  So when do you need to care about proportional fill?
  One example is when trying to alleviate tempdb allocation bitmap contention. If you have a single tempdb data file, and huge          PAGELATCH_UP    contention on the first PFS page in that file (from  a workload with many concurrent connections creating and dropping small temp tables), you might decide to add just one more data file to tempdb (which is not the correct solution). If that existing file is very full, and the new file isn’t, the skip target for the old file will be large and the skip target for the new file will be 1. This means that subsequent allocations in tempdb will be from the new file, moving all the PFS contention to the new file and not providing any contention relief at all! I discuss this case in my post on    Correctly adding data file to tempdb.  
  The more common example is where a filegroup is full and someone adds another file to create space. In a similar way to the example above, subsequent allocations will come from the new file, meaning that when it’s time for a checkpoint operation, all the write activity will be on the new file (and it’s location on the I/O subsystem) rather than spread over multiple files (and multiple locations in the I/O subsystem). Depending on the characteristics of the I/O subsystem, this may or may not cause a degradation in performance.
  Summary

  Proportional fill is an algorithm that it’s worth knowing about, so you don’t inadvertently cause a performance issue, and so that you can recognize a performance issue caused by a misconfiguration of file sizes in a filegroup. I don’t expect you to be using trace flag 1165, but if you’re interested, it’s a way to dig into the internals of the allocation system.
  Enjoy!
友荐云推荐




上一篇:玩玩微信小程序(weapp-github)
下一篇:Online tool takes the guesswork out of CSS media queries
酷辣虫提示酷辣虫禁止发表任何与中华人民共和国法律有抵触的内容!所有内容由用户发布,并不代表酷辣虫的观点,酷辣虫无法对用户发布内容真实性提供任何的保证,请自行验证并承担风险与后果。如您有版权、违规等问题,请通过"联系我们"或"违规举报"告知我们处理。

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

本版积分规则

我要投稿

推荐阅读

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

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

返回顶部 返回列表