由缓冲池（Buffer pool）空间浪费引发的“血案”

日期： 2015-04-08 作者：姜传华 来源：TechTarget中国

对于SQL Server而言，内存(Memory)是其使用的最重要的资源之一，内存不足往往会导致很多问题发生，我们先来看看一般都会出现什么问题：

  • 引发更多的read I/O，因为大量的数据无法保留在数据缓冲池(Data Buffer)中
  • 大量的write I/O，因为惰性写入器lazywrite不得不经常将脏页从缓冲池写入到磁盘以减少占用的空间
  • 可能会遇到Resource_Semaphore等待的信息，因为当查询不能得到在执行时应该获得的内存大小而发生的等待现象
  • 如果计划缓存空间受到压迫可能引发过度的计划重编译

等等一系列的性能问题。

是什么原因导致以上问题的发生？

我们先来看看SQL Server的存储架构，在SQL Server中最小的存储单元是页(page)，大小是8K，有Data Page、Index Page主要的两种存储数据的页类型，表和索引的数据以行的形式存储在这些页面中，并且行不能跨页(但是行的部分可以移出行所在的页，因此行实际可能非常大)，数据在从磁盘读入到Data Cache时也是以Page的形式完成操作的，在Data Cache中同样以Page的形式来进行空间分配的，那就可以想象的到，如果一个页面的数据密度过低自然的首先就会导致磁盘空间、内存空间的浪费，如数据/索引页中出现空间浪费，可能导致存储同样数量的数据需要更多的页面。这不仅会占用更多的磁盘空间，还意味着查询需要执行更多的 I/O 才能读取同样数量的数据。所有这些多出的页面在数据缓存中占用了更多空间，因而占用了更多的服务器内存。

什么原因引起的低密度数据页？

  • 很宽的数据行（比如说一个拥有5000 bytes固定大小的行的表，显然一个Page只能存储一行，那每页就会浪费掉了3000 bytes）
  • 页拆分，由于在填满数据的页面进行随机的insert 或者是update所引发的一种现象（将一个Page的各50%分配到两个页面上，这是SQL Server处理这种情况的一种机制），这种页拆分会导致产生逻辑碎片从而影响数据页面扫描性能，降低Data Page和index Page的数据密度，同时增加了事务日志的开销（页拆分不仅仅引发额外的I/O和索引碎片index fragmentation，同样也能产生更多的事务日志，并且这些日志也可能会影响到你的备份、日志传输和数据库镜像）

低密度的数据页不利于SQL Server的性能，因为：

  • 需要更多地磁盘空间存储数据（包括备份）
  • 更多的I/O来完成从磁盘将数据读入内存
  • 更大的数据缓冲池来存储额外的数据

解决方案：

  • 改变表的结构（可以对表进行垂直分区或者使用较小的数据类型）
  • 修改索引字段（一般仅针对cluster index，比如说修改Cluster的键值从非连续的GUID到连续的GUID或者是Identity）
  • 使用带有Fillfactor的索引减少页拆分（Fillfactor没有一个标准值，要以据实际情况而定 ）
  • 周期性的重建问题索引
  • 考虑在有较少数据变化的表和索引上启用数据压缩

如何计算数据页密度

下面的代码用来分析数据库在Buffer pool中所占用的空间大小的以及其中有多少是空的空间。

SELECT
(CASE WHEN ([database_id] = 32767)
THEN N’Resource Database’
ELSE DB_NAME ([database_id]) END) AS [DatabaseName],
COUNT (*) * 8 / 1024 AS [MBUsed],
SUM (CAST ([free_space_in_bytes] AS BIGINT)) / (1024 * 1024) AS [MBEmpty]
FROM sys.dm_os_buffer_descriptors
GROUP BY [database_id];

GO

下面是输出部分结果：

DatabaseName        MBUsed   MBEmpty
——————- ——– ———
Resource Database   51       11
ProdDB              71287    9779
master              2        1
msdb                481      72
ProdDB2             106      17
model               0        0
tempdb              2226     140

在SQL Server 2000中可以使用DBCC  Showcontig可以查看包含数据页密度在内的一些信息。

下面代码是过滤掉了系统对象后，对所有数据库中的表和索引在buffer pool中所使用的空间大小

EXEC sp_MSforeachdb

    N’IF EXISTS (SELECT 1 FROM (SELECT DISTINCT DB_NAME ([database_id]) AS [name]
    FROM sys.dm_os_buffer_descriptors) AS names WHERE [name] = ”?”)
BEGIN
USE [?]
SELECT
    ”?” AS [Database],
    OBJECT_NAME (p.[object_id]) AS [Object],
    p.[index_id],
    i.[name] AS [Index],
    i.[type_desc] AS [Type],
    –au.[type_desc] AS [AUType],
    –DPCount AS [DirtyPageCount],
    –CPCount AS [CleanPageCount],
    –DPCount * 8 / 1024 AS [DirtyPageMB],
    –CPCount * 8 / 1024 AS [CleanPageMB],
    (DPCount + CPCount) * 8 / 1024 AS [TotalMB],
    –DPFreeSpace / 1024 / 1024 AS [DirtyPageFreeSpace],
    –CPFreeSpace / 1024 / 1024 AS [CleanPageFreeSpace],
    ([DPFreeSpace] + [CPFreeSpace]) / 1024 / 1024 AS [FreeSpaceMB],
    CAST (ROUND (100.0 * (([DPFreeSpace] + [CPFreeSpace]) / 1024) / (([DPCount] + [CPCount]) * 8), 1) AS DECIMAL (4, 1)) AS [FreeSpacePC]
FROM
    (SELECT
        allocation_unit_id,
        SUM (CASE WHEN ([is_modified] = 1)
            THEN 1 ELSE 0 END) AS [DPCount],
        SUM (CASE WHEN ([is_modified] = 1)
            THEN 0 ELSE 1 END) AS [CPCount],
        SUM (CASE WHEN ([is_modified] = 1)
            THEN CAST ([free_space_in_bytes] AS BIGINT) ELSE 0 END) AS [DPFreeSpace],
        SUM (CASE WHEN ([is_modified] = 1)
            THEN 0 ELSE CAST ([free_space_in_bytes] AS BIGINT) END) AS [CPFreeSpace]
    FROM sys.dm_os_buffer_descriptors
    WHERE [database_id] = DB_ID (”?”)
    GROUP BY [allocation_unit_id]) AS buffers
INNER JOIN sys.allocation_units AS au
    ON au.[allocation_unit_id] = buffers.[allocation_unit_id]
INNER JOIN sys.partitions AS p
    ON au.[container_id] = p.[partition_id]
INNER JOIN sys.indexes AS i
    ON i.[index_id] = p.[index_id] AND p.[object_id] = i.[object_id]
WHERE p.[object_id] > 100 AND ([DPCount] + [CPCount]) > 12800 — Taking up more than 100MB
ORDER BY [FreeSpacePC] DESC;
END’;

下面是部分的输出：

Database Object index_id Index        Type         TotalMB FreeSpaceMB FreeSpacePC
——– —— ——– ———— ———— ——- ———– ———–
ProdDB   TableG 1        TableG_IX_1  CLUSTERED    531     130         24.5
ProdDB   TableI 1        TableI_IX_1  CLUSTERED    217     48          22.2
ProdDB   TableG 2        TableG_IX_2  NONCLUSTERED 127     27          21.8
ProdDB   TableC 1        TableC_IX_1  CLUSTERED    224     47          21.4
ProdDB   TableD 3        TableD_IX_3  NONCLUSTERED 1932    393         20.4
ProdDB   TableH 1        TableH_IX_1  CLUSTERED    162     33          20.4
ProdDB   TableF 5        TableF_IX_5  NONCLUSTERED 3128    616         19.7
ProdDB   TableG 9        TableG_IX_9  NONCLUSTERED 149     28          19.1
ProdDB   TableO 10       TableO_IX_10 NONCLUSTERED 1003    190         19
ProdDB   TableF 6        TableF_IX_6  NONCLUSTERED 3677    692         18.8

通过这个脚本就能找到那个table和index存在数据密度问题。

本文作者已授权TechTarget中国进行内容发布，未经允许，不得擅自转载。

原文链接：http://www.qdjch.com/?p=487

关于作者

姜传华，长期从事数据库的教学、设计、开发和应用管理工作，有着20年以上的IT工作经历，深刻理解关系数据库原理及SQL Server体系架构。同时也活跃于Microsoft的各大论坛网站。

个人博客：http://www.qdjch.com/ 

联系方式：qdjch#hotmail.com（将#修改为@）

我们一直都在努力坚持原创.......请不要一声不吭，就悄悄拿走。

我原创，你原创，我们的内容世界才会更加精彩！

【所有原创内容版权均属TechTarget，欢迎大家转发分享。但未经授权，严禁任何媒体（平面媒体、网络媒体、自媒体等）以及微信公众号复制、转载、摘编或以其他方式进行使用。】

微信公众号

TechTarget微信公众号二维码

TechTarget

官方微博

TechTarget中国官方微博二维码

TechTarget中国

电子邮件地址不会被公开。 必填项已用*标注

敬请读者发表评论，本站保留删除与本文无关和不雅评论的权力。

作者

姜传华
姜传华

相关推荐