第七章——DMVs和DMFs(3)——用DMV和DMF监控TempDB

前言:

        我们都知道TempDBSQLServer的系统数据库,且SQLServer的日常运作严重依赖这个库。因此,监控TempDB的性能问题尤为重要。在过去很长一段时间里面,很多人都忽略了TempDB的重要性并忽略了它的性能问题。这并不是一件好事,因为TempDB的性能会影响其他用户数据库的性能,所以需要时时刻刻注意TempDB的性能。

        在一些查询的聚合、排序操作,游标操作和版本存储操作,联机索引创建,用户对象存储如临时表等,都将用到TempDB,作为DBA,需要经常监控TempDB,以便识别出资源消耗较大的操作。此时可以使用数据库相关的DMVs来完成。

        在使用这些DMVs时,要清楚一些基础概念,SQLServer是如何组织数据的。所以先来了解页和区。

就像你所知道的,SQLServer主要通过两类文件来存储数据库。就是数据文件(mdf/ndf)和日志文件(ldf)。这里只讨论数据文件。因为页和区不适用于日志文件。

        数据文件是SQLServer存储数据库的对象如表和索引的一种格式化文件。这些数据文件由更小的单元组成,这些单元叫做页。一个页存放8K的数据。

        另外,区也有页来存放,一个区有8个顺序页组成。所以,一个区有64K1MB16个区。

包含数据的对象会分配到区中的页上。有两类的区——统一区和混合区,一个统一区被一个单独对象所独有,混合区可以存放能够放进8个页的8个不同对象。因为混合区可以共享整个区,所以也叫做共享区。当表很小时,会放入混合区,直到足够大占据一个区时,混合区就会整合成一个统一区。

        本文将演示如何监控TempDB的性能。同时可以识别出引起TempDB空间增加的会话和任务。

      

准备工作:

本文将产生1000万数据,并存放到TempDB的局部临时表中。然后监控页分配和重新分配的情况。

 

步骤:

1、  连到SQLServer

2、  输入以下代码:

 

USE tempdb
GO
--检查表是否存在
IF OBJECT_ID('[dbo].[tbl_TempDBStats]') IS NOT NULL 
    DROP TABLE [dbo].[tbl_TempDBStats]
--创建表用于存放页分配的明细
CREATE TABLE [dbo].[tbl_TempDBStats]
    (
      session_id SMALLINT ,
      database_id SMALLINT ,
      user_objects_alloc_page_count BIGINT ,
      user_objects_dealloc_page_count BIGINT ,
      internal_objects_alloc_page_count BIGINT ,
      internal_objects_dealloc_page_count BIGINT
    )
GO
--收集当前会话在执行查询之前的分配明细
INSERT  INTO [dbo].[tbl_TempDBStats]
        SELECT  session_id ,
                database_id ,
                user_objects_alloc_page_count ,
                user_objects_dealloc_page_count ,
                internal_objects_alloc_page_count ,
                internal_objects_dealloc_page_count
        FROM    sys.dm_db_session_space_usage
        WHERE   session_id = @@SPID
GO
--检查表是否存在
IF OBJECT_ID('TempDB.dbo.#tbl_SampleData') IS NOT NULL 
    DROP TABLE TempDB.dbo.#tbl_SampleData
GO
--产生万数据并插入临时表
SELECT TOP 10000000
        SC1.object_id ,
        SC1.column_id ,
        SC1.name ,
        SC1.system_type_id
INTO    TempDB.dbo.#tbl_SampleData
FROM    sys.columns AS SC1
        CROSS JOIN sys.columns AS SC2
        CROSS JOIN sys.columns AS SC3
ORDER BY SC1.column_id
GO

--重新收集插入数据后的数据页分配情况
INSERT  INTO [dbo].[tbl_TempDBStats]
        SELECT  session_id ,
                database_id ,
                user_objects_alloc_page_count ,
                user_objects_dealloc_page_count ,
                internal_objects_alloc_page_count ,
                internal_objects_dealloc_page_count
        FROM    sys.dm_db_session_space_usage
        WHERE   session_id = @@SPID


 

 

3、  然后输入以下代码,并注意执行前后的数据差异:

 

USE tempdb
GO
SELECT  *
FROM    [dbo].[tbl_TempDBStats]


 

 

4、  结果如下:

 

5、  运行以下查询查找TempDB空间分配情况:

 

SELECT  DB_NAME(FSU.database_id) AS DatabaseName ,
        MF.name AS LogicalFileName ,
        MF.physical_name AS PhysicalFilePath ,
        SUM(FSU.unallocated_extent_page_count) * 8.0 / 1024 AS Free_Space_In_MB ,
        SUM(FSU.version_store_reserved_page_count
            + FSU.user_object_reserved_page_count
            + FSU.internal_object_reserved_page_count
            + FSU.mixed_extent_page_count) * 8.0 / 1024 AS Used_Space_In_MB
FROM    sys.dm_db_file_space_usage AS FSU
        INNER JOIN sys.master_files AS MF ON FSU.database_id = MF.database_id
                                             AND FSU.file_id = MF.file_id
GROUP BY FSU.database_id ,
        FSU.file_id ,
        MF.name ,
        MF.physical_name


 

 

6、  结果如下:

 

分析:

        在本文的开片中,首先创建了一个表tbl_TempDBStats以便存放页分配和释放的统计数据。然后通过查询sys.dm_db_session_space_usage,获取分析信息。把所有用户定义对象和系统内置对象都插入表中。

        下一个查询将产生1000万数据,并插入临时表#tbl_SampleData。使tempdb的分配情况发生改变。

        插入数据以后,检查tbl_TempDBStats表,可以得出一些对比信息,最后通过一个DMVsys.dm_db_file_space_usage。可以看出以MB为单位的分配情况。

        注意:sys.dm_db_file_space_usage ,sys.dm_db_session_space_usage这两个DVM仅适用于tempdb

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值