SQL SERVER DMO 简介

一、SQL SERVER DMO 简介

简介：

        从SQLServer2005开始，微软引入了一个名叫DMO（动态管理对象）的新特性，DMO可以分为DMFs（Dynamic Manage Functions，动态管理函数）和DMVs（Dynamic Manage Views，动态管理视图）两部分。这些函数和视图用于查找SQLServer实例内部统计信息以供性能监控所用。它们提供实时的，关于SQLServer内部工作的，能用于性能分析和性能故障排除的各种统计信息。

        所有的DMO都属于sys架构，并且以dm_开头。执行DMO需要有VIEW SERVER STATE和VIEW DATABASE STATE权限。

下面简述一下本系列将要介绍的DMO：

Ø  执行相关的DMO（sys.dm_exec_*）：提供与执行相关的统计信息。可以用于监控与缓存查询、执行计划、活动连接/会话和带有执行计划的当前运行的查询的相关统计信息。

Ø  索引相关的DMO（sys.dm_db_index_*和sys.dm_db_missing_*）：提供关于索引的统计信息。这些DMO可以用于监控和分析因为丢失索引、无效索引而导致的性能问题，也可以用来检查索引的使用情况。

Ø  数据库相关DMO（sys.dm_db_*）：提供数据库相关统计信息。可以用于监控和分析数据库的性能问题，分析数据库相关文件的统计信息、会话统计信息和任务统计信息。

Ø  I/O相关DMO（sys.dm_io_*）：提供I/O操作的统计信息，用于监控和分析SQLServer的I/O性能问题。

Ø  OS相关DMO（sys.dm_os_*）：提供关于sqlos内部统计信息，用于监控和分析服务器配置问题。

Ø  事务相关的DMO（sys.dm_trn_*）：提供事务相关的统计信息，用于监控和分析长时间运行的事务的锁定、死锁问题。

 

这些DMO的数据可以通过DBCC SQLPERF(‘SYS.DM_OS_WAIT_STATS’,CLEAR)或者重启服务器来重置。

监控当前查询：

SELECT DB_NAME(R.database_id) AS DatabaseName ,

        S.original_login_name AS LoginName ,

        S.host_name AS ClientMachine ,

        S.program_name AS ApplicationName ,

        R.start_time AS RequestStartTime ,

        ST.text AS SQLQuery ,

        QP.query_plan AS ExecutionPlan ,

        R.cpu_time AS CPUTime ,

        R.total_elapsed_time AS TotalTimeElapsed ,

        R.open_transaction_count AS TotalTransactionOpened ,

        R.reads ,

        R.logical_reads ,

        R.writes AS TotalWrites

FROM sys.dm_exec_requests AS R

        INNER JOIN sys.dm_exec_sessions AS S ON R.session_id = S.session_id

        CROSS APPLY sys.dm_exec_sql_text(R.sql_handle) AS ST

        CROSS APPLY sys.dm_exec_query_plan(R.plan_handle) AS QP

ORDER BY TotalTimeElapsed DESC 

GO

监控当前打开的游标：

SELECT S.host_name AS ClientMachine ,

        S.program_name AS ApplicationName ,

        S.original_login_name AS LoginName ,

        C.name AS CursorName ,

        C.properties AS CursorOptions ,

        C.creation_time AS CursorCreatinTime ,

        ST.text AS SQLQuery ,

        C.is_open AS IsCursorOpen ,

        C.worker_time / 1000 AS DurationInMiliSeconds ,

        C.reads AS NumberOfReads ,

        C.writes AS NumberOfWrites

FROM sys.dm_exec_cursors (0) AS C

        INNER JOIN sys.dm_exec_sessions AS S ON C.session_id = S.session_id

        CROSS APPLY sys.dm_exec_sql_text(C.sql_handle) AS ST

ORDER BY DurationInMiliSeconds DESC 

GO

分析：

在上面步骤中，使用了以下的DMOs：

Ø  Sys.dm_exec_requests

Ø  Sys.dm_exec_sessions

Ø  Sys.dm_exec_sql_text

Ø  Sys.dm_exec_query_plan

 

对于上面的查询结果，需要思考的问题：

Ø  哪个库正在接受请求？

Ø  那个登录名执行了这个请求？

Ø  请求是从哪个计算机发出的？

Ø  请求是从那个应用程序发出的？

Ø  请求是何时到达SQLServer的？

Ø  请求中需要执行什么SQL语句？

Ø  执行的SQL语句的执行计划是什么？

Ø  请求的持续时间有多少？

Ø  请求是否开启了事务？

Ø  请求造成的读写数是多少？

Ø  请求是否被阻塞了？如果是，是哪个会话造成的？

 

        为了找到这些信息，需要把sys.dm_exec_requests和sys.dm_exec_sessions的session_id列关联。

同时，使用CROSS APPLY来关联sys.dm_exec_sql_text()函数来查找请求的SQL文本。关联sys.dm_exec_query_plan()函数来查找请求的执行计划。这两个函数需要从查询中分别获得sql_handle和plan_handle。在结果集中，按TotalTimeElapsed列排序，可以知道最耗资源的查询。

 

        第二个查询中使用了sys.dm_exec_cursors()函数来返回当前正在使用的游标的详细。这个函数接受session_id作为参数。如果传入了特定session_id，只会返回该会话的游标，如果传入0，则返回所有会话的游标。结果集按照DurationInMiliSecondes排序，一边查找最耗资源的游标，注意worker_time除以了1000，因为这个的单位是微妙，除以1000可以得到毫秒。

二、用DMV和DMF监控索引性能         

SQLServer有专门的DMO来显示索引相关统计信息。能帮助你分析现有索引的性能情况。通过这些DMO，可以做到：

Ø  检查索引使用模式

Ø  查找丢失索引

Ø  查找无用索引

Ø  查找索引碎片

Ø  分析索引页分配明细

下面将使用这些DMO来检查数据库的丢失索引，索引上的查找和扫描操作，并分析索引碎片是否有必要重组或重建。

查找丢失索引：

SELECT MID.statement AS ObjectName ,

        MID.equality_columns ,

        MID.inequality_columns ,

        MID.included_columns ,

        MIGS.avg_user_impact AS ExpectedPerformanceImpro vement ,

        ( MIGS.user_seeks + MIGS.user_scans ) * MIGS.avg_total_user_cost

        * MIGS.avg_user_impact AS PossibleImprovement

FROM sys.dm_db_missing_index_details AS MID

        INNER JOIN sys.dm_db_missing_index_groups AS MIG ON MID.index_handle = MIG.index_handle

        INNER JOIN sys.dm_db_missing_index_group_stats AS MIGS ON MIG.index_group_handle = MIGS.group_handle

GO

索引使用明细：

SELECT O.name AS ObjectName ,

        I.name AS IndexName ,

        IUS.user_seeks ,

        IUS.user_scans ,

        IUS.last_user_seek ,

        IUS.last_user_scan

FROM sys.dm_db_index_usage_stats AS IUS

        INNER JOIN sys.indexes AS I ON IUS.object_id = I.object_id

                                        AND IUS.index_id = I.index_id

        INNER JOIN sys.objects AS O ON IUS.object_id = O.object_id

GO

查找索引碎片：

SELECT O.name AS ObjectName ,

        I.name AS IndexName ,

        IPS.avg_page_space_used_in_percent AS AverageSpaceUsedInPages ,

        IPS.avg_fragmentation_in_percent AS AverageFragmentation ,

        IPS.fragment_count AS FragmentCount ,

        suggestedIndexOperation = CASE WHEN IPS.avg_fragmentation_in_percent <= 30

                                        THEN 'ReOrganize Index'

                                        ELSE 'ReBuild Index'

                                  END

FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'DETAILED')

        AS IPS

        INNER JOIN sys.indexes AS I ON IPS.index_id = I.index_id

                                        AND IPS.object_id = I.object_id

        INNER JOIN sys.objects AS O ON IPS.object_id = O.object_id

WHERE IPS.avg_fragmentation_in_percent > 5

ORDER BY AverageFragmentation DESC 

GO

分析：

         SQLServer提供缺失索引的详细情况。缺失索引是指在数据库中不存在的索引，但是如果创建，查询将会得益并运行得更快。可以关联两个DMV，sys.dm_db_missing_index_groups和sys.dm_db_missing_index_group_stats。其中DMV，sys.dm_db_missing_index_groups_stats返回关于如果创建了缺失索引，将会对查询有多少可能性的提高的详细信息。注意从这个视图中查找的avg_user_impact列，这里间接地通过这列来和Sys.dm_db_missing_index_details与sys.dm_db_missing_index_groups关联。

和DTA（数据库优化引擎顾问）类似，DMV可能会建议广泛使用很多INCLUDE列在索引中。所以你不应该不顾一切把所有索引全部创建。因为这对你的DML操作可能会带来比较大的影响，如增删改等操作。

在第二个查询DMO中，返回了特定索引的查找和扫描数量。为了返回索引名称和对象名称，关联了sys.indexes和sys.objects这两个目录视图。

       最后就是查找碎片，这里使用了DMF而不是DMV，sys.dm_db_index_physical_stats()函数。留意一下，这里使用了DB_ID()函数，因为希望得到当前数据库的所有索引信息，所以用这个函数即可。为了得到索引名和对象名，关联了sys.indexex和sys.objects目录视图。

三、用DMV和DMF监控TempDB 

前言：

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

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

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

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

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

        另外，区也有页来存放，一个区有8个顺序页组成。所以，一个区有64K，1MB有16个区。

包含数据的对象会分配到区中的页上。有两类的区——统一区和混合区，一个统一区被一个单独对象所独有，混合区可以存放能够放进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表，可以得出一些对比信息，最后通过一个DMV，sys.dm_db_file_space_usage。可以看出以MB为单位的分配情况。

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

四、用DMV和DMF监控磁盘IO         

前言：

        本文为本系列最后一篇，作为DBA，你必须经常关注磁盘的I/O问题，一旦出现问题，要尽快分析出是什么问题。SQLServer同样提供了一些列与I/O相关的DMO来做监控。

        本文介绍如何使用DMO来监控I/O子系统的性能并找到I/O瓶颈。通过本文，可以区分不同数据库的I/O使用模式。一旦发现有数据库的I/O很高，可能需要考虑把数据库迁移到单独的磁盘，或者深入研究I/O产生的问题。

1. 监控SQLServer实例上的日志文件和数据文件：

SELECT DB_NAME(VFS.database_id) AS DatabaseName ,

        MF.name AS LogicalFileName ,

        MF.physical_name AS PhysicalFileName ,

        CASE MF.type

          WHEN 0 THEN 'Data File'

          WHEN 1 THEN 'Log File'

        END AS FileType ,

        VFS.num_of_reads AS TotalReadOperations ,

        VFS.num_of_bytes_read AS TotalBytesRead ,

        VFS.num_of_writes AS TotalWriteOperations ,

        VFS.num_of_bytes_written AS TotalWriteOperations ,

        VFS.io_stall_read_ms AS TotalWaitTimeForRead ,

        VFS.io_stall_write_ms AS TotalWaitTimeForWrite ,

        VFS.io_stall AS TotalWaitTimeForIO ,

        VFS.size_on_disk_bytes AS FileSizeInBytes

FROM sys.dm_io_virtual_file_stats(NULL, NULL) AS VFS

        INNER JOIN sys.master_files AS MF ON VFS.database_id = MF.database_id

                                             AND VFS.file_id = MF.file_id

ORDER BY VFS.database_id DESC

2. 清空数据缓存：

DBCC DROPCLEANBUFFERS

GO

3. 再次执行监控SQLServer实例上的日志文件和数据文件：

4. 查看是否有IO挂起操作：

SELECT DB_NAME(VFS.database_id) AS DatabaseName ,

        MF.name AS LogicalFileName ,

        MF.physical_name AS PhysicalFileName ,

        CASE MF.type

          WHEN 0 THEN 'Data File'

          WHEN 1 THEN 'Log File'

        END AS FileType ,

        PIOR.io_type AS InputOutputOperationType ,

        PIOR.io_pending AS Is_Request_Pending ,

        PIOR.io_handle ,

        PIOR.scheduler_address

FROM sys.dm_io_pending_io_requests AS PIOR

        INNER JOIN sys.dm_io_virtual_file_stats(DB_ID('AdventureWorks'), NULL)

        AS VFS ON PIOR.io_handle = VFS.file_handle

        INNER JOIN sys.master_files AS MF ON VFS.database_id = MF.database_id

                                             AND VFS.file_id = MF.file_id

GO