主页 / dba / 问题 / 44714
Accepted
ooutwire
Asked: 2013-06-18 13:03:48 +0800 CST

是否分离/附加或脱机/联机清除特定数据库的缓冲区缓存？

  • 772

我的一个朋友今天告诉我，我可以简单地分离然后重新附加数据库，而不是弹回 SQL Server，这个操作将从缓存中清除给定数据库的页面和计划。我不同意并在下面提供我的证据。如果你不同意我的观点或有更好的反驳，那就一定要提供。

我在这个版本的 SQL Server 上使用 AdventureWorks2012：

选择@@版本；
微软 SQL Server 2012 - 11.0.2100.60 (X64)
Windows NT 6.1（内部版本 7601：Service Pack 1）上的开发人员版（64 位）

加载数据库后，我运行以下查询：

首先，运行 Jonathan K 在此处找到的 AW 增肥脚本：

AW 发胖

--------------------------
-- 第 1 步：Bpool 的东西？
--------------------------
使用 [AdventureWorks2012]；
去

选择
     OBJECT_NAME(p.object_id) AS [ObjectName]
   , p.object_id
   , p.index_id
   , COUNT(*) / 128 AS [缓冲区大小(MB)]
   , COUNT(*) AS [buffer_count]
从
     sys.allocation_units 作为一个
     INNER JOIN sys.dm_os_buffer_descriptors AS b
           ON a.allocation_unit_id = b.allocation_unit_id
     INNER JOIN sys.partitions AS p
           ON a.container_id = p.hobt_id
在哪里
     b.database_id = DB_ID()
     和 p.object_id > 100
通过...分组
     p.object_id
   , p.index_id
订购方式
     buffer_count DESC；

结果显示在这里： 在此处输入图像描述

分离并重新附加数据库，然后重新运行查询。

--------------------------
-- 第 2 步：分离/附加
--------------------------
-- 分离
使用 [主]
去
EXEC master.dbo.sp_detach_db @dbname = N'AdventureWorks2012'
去

-  附
使用[主]；
去

创建数据库 [AdventureWorks2012] 开启
(
    文件名 = N'C:\sql server\files\AdventureWorks2012_Data.mdf'
)
    ,
(
    文件名 = N'C:\sql server\files\AdventureWorks2012_Log.ldf'
)
 附上；
去

现在 bpool 里有什么？

--------------------------
-- 第 3 步：Bpool 的东西？
--------------------------
使用 [AdventureWorks2012]；
去

选择
     OBJECT_NAME(p.object_id) AS [ObjectName]
   , p.object_id
   , p.index_id
   , COUNT(*) / 128 AS [缓冲区大小(MB)]
   , COUNT(*) AS [buffer_count]
从
     sys.allocation_units 作为一个
     INNER JOIN sys.dm_os_buffer_descriptors AS b
           ON a.allocation_unit_id = b.allocation_unit_id
     INNER JOIN sys.partitions AS p
           ON a.container_id = p.hobt_id
在哪里
     b.database_id = DB_ID()
     和 p.object_id > 100
通过...分组
     p.object_id
   , p.index_id
订购方式
     buffer_count DESC；

结果： 在此处输入图像描述

此时所有的读取都合乎逻辑吗？

--------------------------------
-- 第四步：逻辑只读？
--------------------------------
使用 [AdventureWorks2012]；
去

设置统计 IO 开启；   
    选择 * 从数据库日志；
    去
设置统计 IO 关闭；  

/*
（1597 行受影响）
表“数据库日志”。扫描计数 1，逻辑读取 782，物理读取 0，预读读取 768，lob 逻辑读取 94，lob 物理读取 4，lob 预读读取 24。
*/

我们可以看到缓冲池并没有被分离/附加完全吹走。看来我朋友说错了。有没有人不同意或有更好的论点？

另一种选择是将数据库脱机然后联机。让我们尝试一下。

--------------------------------
-- 第 5 步：离线/在线？
--------------------------------
ALTER DATABASE [AdventureWorks2012] 设置离线；
去
ALTER DATABASE [AdventureWorks2012] 在线设置；
去

--------------------------
-- 第 6 步：Bpool 的东西？
--------------------------
使用 [AdventureWorks2012]；
去

选择
     OBJECT_NAME(p.object_id) AS [ObjectName]
   , p.object_id
   , p.index_id
   , COUNT(*) / 128 AS [缓冲区大小(MB)]
   , COUNT(*) AS [buffer_count]
从
     sys.allocation_units 作为一个
     INNER JOIN sys.dm_os_buffer_descriptors AS b
           ON a.allocation_unit_id = b.allocation_unit_id
     INNER JOIN sys.partitions AS p
           ON a.container_id = p.hobt_id
在哪里
     b.database_id = DB_ID()
     和 p.object_id > 100
通过...分组
     p.object_id
   , p.index_id
订购方式
     buffer_count DESC；

看来离线/在线操作效果要好得多。

在此处输入图像描述

sql-server sql-server-2008-r2

1 个回答

  1. Best Answer

    我最初以为你在这里做点什么。工作假设是这样的，也许缓冲池没有立即刷新，因为它需要“一些工作”才能这样做，为什么要在需要内存之前打扰。但...

    你的测试有缺陷。

    您在缓冲池中看到的是由于重新连接数据库而读取的页面，而不是数据库先前实例的剩余部分。

    我们可以看到缓冲池并没有被分离/附加完全吹走。看来我朋友说错了。有没有人不同意或有更好的论点？

    是的。您正在解释physical reads 0为没有任何物理读取的意思

    表“数据库日志”。扫描计数 1，逻辑读取 782，物理读取 0，预读读取 768，lob 逻辑读取 94，lob 物理读取 4，lob 预读读取 24。

    正如Craig Freedman 的博客中所述，顺序预读机制试图确保页面在查询处理器请求之前就在内存中，这就是为什么您看到报告的物理读取计数为零或低于预期的原因。

    当 SQL Server 对大表执行顺序扫描时，存储引擎会启动预读机制，以确保页面在内存中并准备好在查询处理器需要它们之前进行扫描。预读机制试图在扫描前保持 500 页。

    满足您的查询所需的页面都没有在内存中，直到预读将它们放在那里。

    至于为什么在线/离线导致不同的缓冲池配置文件需要更多空闲调查。@MarkSRasmussen下次他访问时可能会帮助我们解决这个问题。

    • 9

相关问题