数据收集器集

数据收集器集可以理解为个进阶的性能监视器工具，它最常用的场景是长时间自动

收集指定数据。它不像性能监视器那样只能短暂地收集一段时间的数据，使用数据收集器，

可以进行长达数月的性能数据收集，对服务器性能评估、建立性能基线及时间段等的监控

都很有帮助，而且它是在后台运行，不需要一直开着性能监视器。

使用PAL分析

PAL可以分析通过性能数据收集器收集的文件，不需要对结果(特别是大量、长时间

收集的数据）进行人工分析。PAL的下载地址为:http://pal.codeplex.com/。下面通过一个

使用DMO 获取性能数据

DMO，动态管理对象，包括动态管理视图(DMV)）和动态管理函数(DMF)，从SQL

Server 2005开始引入，主要目的在于替代SQL Server 2000及以前版本中的系统表，并提

供更丰富的SQL Server运行信息。前面的章节中大量使用这种工具来进行信息统计及性能

问题侦测，并且在后续版本中持续加入了新的DMO。可见微软把DMO当作了一个可持续

使用的技术，并且它在日常管理、优化中具有不可替代的地位。

DMO介绍

DMO并不是实体表，从名称来说，它是动态的。这里面有两个含义，其-是大部分的

DMO 都是在执行时才汇总数据，其二是它的数据具有不稳定性，在服务器或者某些命令执

行之后，相应的数据就会被清空，同时这类数据绝大部分是累计值，并不是实时数据。所

以建议对这些数据进行周期性收集，并记录在一个专用的数据库或者其他库中，以便需要

时使用。详细的知识可以阅读《Performance Tuning with SQL Server Dynamic Management Views》一书。

索引篇

-- 未被使用的索引SELECT  OBJECT_NAME(i.[object_id]) AS [Table Name] ,        i.nameFROM    sys.indexes AS i        INNER JOIN sys.objects AS o ON i.[object_id] = o.[object_id]WHERE   i.index_id NOT IN ( SELECT  ddius.index_id                            FROM    sys.dm_db_index_usage_stats AS ddius                            WHERE   ddius.[object_id] = i.[object_id]                                    AND i.index_id = ddius.index_id                                    AND database_id = DB_ID() )        AND o.[type] = 'U'ORDER BY OBJECT_NAME(i.[object_id]) ASC;--需要维护但是未被用过的索引SELECT  '[' + DB_NAME() + '].[' + su.[name] + '].[' + o.[name] + ']' AS [statement] ,        i.[name] AS [index_name] ,        ddius.[user_seeks] + ddius.[user_scans] + ddius.[user_lookups] AS [user_reads] ,        ddius.[user_updates] AS [user_writes] ,        SUM(SP.rows) AS [total_rows]FROM    sys.dm_db_index_usage_stats ddius        INNER JOIN sys.indexes i ON ddius.[object_id] = i.[object_id]                                    AND i.[index_id] = ddius.[index_id]        INNER JOIN sys.partitions SP ON ddius.[object_id] = SP.[object_id]                                        AND SP.[index_id] = ddius.[index_id]        INNER JOIN sys.objects o ON ddius.[object_id] = o.[object_id]        INNER JOIN sys.sysusers su ON o.[schema_id] = su.[UID]WHERE   ddius.[database_id] = DB_ID() -- current database only         AND OBJECTPROPERTY(ddius.[object_id], 'IsUserTable') = 1        AND ddius.[index_id] > 0GROUP BY su.[name] ,        o.[name] ,        i.[name] ,        ddius.[user_seeks] + ddius.[user_scans] + ddius.[user_lookups] ,        ddius.[user_updates]HAVING  ddius.[user_seeks] + ddius.[user_scans] + ddius.[user_lookups] = 0ORDER BY ddius.[user_updates] DESC ,        su.[name] ,        o.[name] ,        i.[name]-- 可能不高效的非聚集索引 (writes > reads) SELECT  OBJECT_NAME(ddius.[object_id]) AS [Table Name] ,        i.name AS [Index Name] ,        i.index_id ,        user_updates AS [Total Writes] ,        user_seeks + user_scans + user_lookups AS [Total Reads] ,        user_updates - ( user_seeks + user_scans + user_lookups ) AS [Difference]FROM    sys.dm_db_index_usage_stats AS ddius WITH ( NOLOCK )        INNER JOIN sys.indexes AS i WITH ( NOLOCK ) ON ddius.[object_id] = i.[object_id]                                                       AND i.index_id = ddius.index_idWHERE   OBJECTPROPERTY(ddius.[object_id], 'IsUserTable') = 1        AND ddius.database_id = DB_ID()        AND user_updates > ( user_seeks + user_scans + user_lookups )        AND i.index_id > 1ORDER BY [Difference] DESC ,        [Total Writes] DESC ,        [Total Reads] ASC;--没有用于用户查询的索引SELECT  '[' + DB_NAME() + '].[' + su.[name] + '].[' + o.[name] + ']' AS [statement] ,        i.[name] AS [index_name] ,        ddius.[user_seeks] + ddius.[user_scans] + ddius.[user_lookups] AS [user_reads] ,        ddius.[user_updates] AS [user_writes] ,        ddios.[leaf_insert_count] ,        ddios.[leaf_delete_count] ,        ddios.[leaf_update_count] ,        ddios.[nonleaf_insert_count] ,        ddios.[nonleaf_delete_count] ,        ddios.[nonleaf_update_count]FROM    sys.dm_db_index_usage_stats ddius        INNER JOIN sys.indexes i ON ddius.[object_id] = i.[object_id]                                    AND i.[index_id] = ddius.[index_id]        INNER JOIN sys.partitions SP ON ddius.[object_id] = SP.[object_id]                                        AND SP.[index_id] = ddius.[index_id]        INNER JOIN sys.objects o ON ddius.[object_id] = o.[object_id]        INNER JOIN sys.sysusers su ON o.[schema_id] = su.[UID]        INNER JOIN sys.[dm_db_index_operational_stats](DB_ID(), NULL, NULL,                                                       NULL) AS ddios ON ddius.[index_id] = ddios.[index_id]                                                              AND ddius.[object_id] = ddios.[object_id]                                                              AND SP.[partition_number] = ddios.[partition_number]                                                              AND ddius.[database_id] = ddios.[database_id]WHERE   OBJECTPROPERTY(ddius.[object_id], 'IsUserTable') = 1        AND ddius.[index_id] > 0        AND ddius.[user_seeks] + ddius.[user_scans] + ddius.[user_lookups] = 0ORDER BY ddius.[user_updates] DESC ,        su.[name] ,        o.[name] ,        i.[name]--识别在行级的锁定和阻塞SELECT  '[' + DB_NAME(ddios.[database_id]) + '].[' + su.[name] + '].['        + o.[name] + ']' AS [statement] ,        i.[name] AS 'index_name' ,        ddios.[partition_number] ,        ddios.[row_lock_count] ,        ddios.[row_lock_wait_count] ,        CAST (100.0 * ddios.[row_lock_wait_count] / ( ddios.[row_lock_count] ) AS DECIMAL(5,                                                              2)) AS [%_times_blocked] ,        ddios.[row_lock_wait_in_ms] ,        CAST (1.0 * ddios.[row_lock_wait_in_ms] / ddios.[row_lock_wait_count] AS DECIMAL(15,                                                              2)) AS [avg_row_lock_wait_in_ms]FROM    sys.dm_db_index_operational_stats(DB_ID(), NULL, NULL, NULL) ddios        INNER JOIN sys.indexes i ON ddios.[object_id] = i.[object_id]                                    AND i.[index_id] = ddios.[index_id]        INNER JOIN sys.objects o ON ddios.[object_id] = o.[object_id]        INNER JOIN sys.sysusers su ON o.[schema_id] = su.[UID]WHERE   ddios.row_lock_wait_count > 0        AND OBJECTPROPERTY(ddios.[object_id], 'IsUserTable') = 1        AND i.[index_id] > 0ORDER BY ddios.[row_lock_wait_count] DESC ,        su.[name] ,        o.[name] ,        i.[name]--识别闩锁等待SELECT  '[' + DB_NAME() + '].[' + OBJECT_SCHEMA_NAME(ddios.[object_id])        + '].[' + OBJECT_NAME(ddios.[object_id]) + ']' AS [object_name] ,        i.[name] AS index_name ,        ddios.page_io_latch_wait_count ,        ddios.page_io_latch_wait_in_ms ,        ( ddios.page_io_latch_wait_in_ms / ddios.page_io_latch_wait_count ) AS avg_page_io_latch_wait_in_msFROM    sys.dm_db_index_operational_stats(DB_ID(), NULL, NULL, NULL) ddios        INNER JOIN sys.indexes i ON ddios.[object_id] = i.[object_id]                                    AND i.index_id = ddios.index_idWHERE   ddios.page_io_latch_wait_count > 0        AND OBJECTPROPERTY(i.object_id, 'IsUserTable') = 1ORDER BY ddios.page_io_latch_wait_count DESC ,        avg_page_io_latch_wait_in_ms DESC--识别锁升级SELECT  OBJECT_NAME(ddios.[object_id], ddios.database_id) AS [object_name] ,        i.name AS index_name ,        ddios.index_id ,        ddios.partition_number ,        ddios.index_lock_promotion_attempt_count ,        ddios.index_lock_promotion_count ,        ( ddios.index_lock_promotion_attempt_count          / ddios.index_lock_promotion_count ) AS percent_successFROM    sys.dm_db_index_operational_stats(DB_ID(), NULL, NULL, NULL) ddios        INNER JOIN sys.indexes i ON ddios.object_id = i.object_id                                    AND ddios.index_id = i.index_idWHERE   ddios.index_lock_promotion_count > 0ORDER BY index_lock_promotion_count DESC;--与锁争用有关的索引SELECT  OBJECT_NAME(ddios.object_id, ddios.database_id) AS object_name ,        i.name AS index_name ,        ddios.index_id ,        ddios.partition_number ,        ddios.page_lock_wait_count ,        ddios.page_lock_wait_in_ms ,        CASE WHEN DDMID.database_id IS NULL THEN 'N'             ELSE 'Y'        END AS missing_index_identifiedFROM    sys.dm_db_index_operational_stats(DB_ID(), NULL, NULL, NULL) ddios        INNER JOIN sys.indexes i ON ddios.object_id = i.object_id                                    AND ddios.index_id = i.index_id        LEFT OUTER JOIN ( SELECT DISTINCT                                    database_id ,                                    object_id                          FROM      sys.dm_db_missing_index_details                        ) AS DDMID ON DDMID.database_id = ddios.database_id                                      AND DDMID.object_id = ddios.object_idWHERE   ddios.page_lock_wait_in_ms > 0ORDER BY ddios.page_lock_wait_count DESC;--丢失索引SELECT  user_seeks * avg_total_user_cost * ( avg_user_impact * 0.01 ) AS [index_advantage] ,        dbmigs.last_user_seek ,        dbmid.[statement] AS [Database.Schema.Table] ,        dbmid.equality_columns ,        dbmid.inequality_columns ,        dbmid.included_columns ,        dbmigs.unique_compiles ,        dbmigs.user_seeks ,        dbmigs.avg_total_user_cost ,        dbmigs.avg_user_impactFROM    sys.dm_db_missing_index_group_stats AS dbmigs WITH ( NOLOCK )        INNER JOIN sys.dm_db_missing_index_groups AS dbmig WITH ( NOLOCK ) ON dbmigs.group_handle = dbmig.index_group_handle        INNER JOIN sys.dm_db_missing_index_details AS dbmid WITH ( NOLOCK ) ON dbmig.index_handle = dbmid.index_handleWHERE   dbmid.[database_id] = DB_ID()ORDER BY index_advantage DESC;--索引上的碎片超过15%并且索引体积较大（超过500页）的索引。SELECT  '[' + DB_NAME() + '].[' + OBJECT_SCHEMA_NAME(ddips.[object_id],                                                     DB_ID()) + '].['        + OBJECT_NAME(ddips.[object_id], DB_ID()) + ']' AS [statement] ,        i.[name] AS [index_name] ,        ddips.[index_type_desc] ,        ddips.[partition_number] ,        ddips.[alloc_unit_type_desc] ,        ddips.[index_depth] ,        ddips.[index_level] ,        CAST(ddips.[avg_fragmentation_in_percent] AS SMALLINT) AS [avg_frag_%] ,        CAST(ddips.[avg_fragment_size_in_pages] AS SMALLINT) AS [avg_frag_size_in_pages] ,        ddips.[fragment_count] ,        ddips.[page_count]FROM    sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'limited') ddips        INNER JOIN sys.[indexes] i ON ddips.[object_id] = i.[object_id]                                      AND ddips.[index_id] = i.[index_id]WHERE   ddips.[avg_fragmentation_in_percent] > 15        AND ddips.[page_count] > 500ORDER BY ddips.[avg_fragmentation_in_percent] ,        OBJECT_NAME(ddips.[object_id], DB_ID()) ,        i.[name]

锁定篇

--查看因为单条UPDATE语句锁住的用户表SELECT  [resource_type] ,        DB_NAME([resource_database_id]) AS [Database Name] ,        CASE WHEN DTL.resource_type IN ( 'DATABASE', 'FILE', 'METADATA' )             THEN DTL.resource_type             WHEN DTL.resource_type = 'OBJECT'             THEN OBJECT_NAME(DTL.resource_associated_entity_id,                              DTL.[resource_database_id])             WHEN DTL.resource_type IN ( 'KEY', 'PAGE', 'RID' )             THEN ( SELECT  OBJECT_NAME([object_id])                    FROM    sys.partitions                    WHERE   sys.partitions.hobt_id = DTL.resource_associated_entity_id                  )             ELSE 'Unidentified'        END AS requested_object_name ,        [request_mode] ,        [resource_description]FROM    sys.dm_tran_locks DTLWHERE   DTL.[resource_type] <> 'DATABASE';--哪个会话引起阻塞并且它们在运行什么SELECT  DTL.[request_session_id] AS [session_id] ,        DB_NAME(DTL.[resource_database_id]) AS [Database] ,        DTL.resource_type ,        CASE WHEN DTL.resource_type IN ( 'DATABASE', 'FILE', 'METADATA' )             THEN DTL.resource_type             WHEN DTL.resource_type = 'OBJECT'             THEN OBJECT_NAME(DTL.resource_associated_entity_id,                              DTL.[resource_database_id])             WHEN DTL.resource_type IN ( 'KEY', 'PAGE', 'RID' )             THEN ( SELECT  OBJECT_NAME([object_id])                    FROM    sys.partitions                    WHERE   sys.partitions.hobt_id = DTL.resource_associated_entity_id                  )             ELSE 'Unidentified'        END AS [Parent Object] ,        DTL.request_mode AS [Lock Type] ,        DTL.request_status AS [Request Status] ,        DER.[blocking_session_id] ,        DES.[login_name] ,        CASE DTL.request_lifetime          WHEN 0 THEN DEST_R.TEXT          ELSE DEST_C.TEXT        END AS [Statement]FROM    sys.dm_tran_locks DTL        LEFT JOIN sys.[dm_exec_requests] DER ON DTL.[request_session_id] = DER.[session_id]        INNER JOIN sys.dm_exec_sessions DES ON DTL.request_session_id = DES.[session_id]        INNER JOIN sys.dm_exec_connections DEC ON DTL.[request_session_id] = DEC.[most_recent_session_id]        OUTER APPLY sys.dm_exec_sql_text(DEC.[most_recent_sql_handle]) AS DEST_C        OUTER APPLY sys.dm_exec_sql_text(DER.sql_handle) AS DEST_RWHERE   DTL.[resource_database_id] = DB_ID()        AND DTL.[resource_type] NOT IN ( 'DATABASE', 'METADATA' )ORDER BY DTL.[request_session_id];--单库中的锁定和阻塞SELECT  DTL.[resource_type] AS [resource type] ,        CASE WHEN DTL.[resource_type] IN ( 'DATABASE', 'FILE', 'METADATA' )             THEN DTL.[resource_type]             WHEN DTL.[resource_type] = 'OBJECT'             THEN OBJECT_NAME(DTL.resource_associated_entity_id)             WHEN DTL.[resource_type] IN ( 'KEY', 'PAGE', 'RID' )             THEN ( SELECT  OBJECT_NAME([object_id])                    FROM    sys.partitions                    WHERE   sys.partitions.[hobt_id] = DTL.[resource_associated_entity_id]                  )             ELSE 'Unidentified'        END AS [Parent Object] ,        DTL.[request_mode] AS [Lock Type] ,        DTL.[request_status] AS [Request Status] ,        DOWT.[wait_duration_ms] AS [wait duration ms] ,        DOWT.[wait_type] AS [wait type] ,        DOWT.[session_id] AS [blocked session id] ,        DES_blocked.[login_name] AS [blocked_user] ,        SUBSTRING(dest_blocked.text, der.statement_start_offset / 2,                  ( CASE WHEN der.statement_end_offset = -1                         THEN DATALENGTH(dest_blocked.text)                         ELSE der.statement_end_offset                    END - der.statement_start_offset ) / 2) AS [blocked_command] ,        DOWT.[blocking_session_id] AS [blocking session id] ,        DES_blocking.[login_name] AS [blocking user] ,        DEST_blocking.[text] AS [blocking command] ,        DOWT.resource_description AS [blocking resource detail]FROM    sys.dm_tran_locks DTL        INNER JOIN sys.dm_os_waiting_tasks DOWT ON DTL.lock_owner_address = DOWT.resource_address        INNER JOIN sys.[dm_exec_requests] DER ON DOWT.[session_id] = DER.[session_id]        INNER JOIN sys.dm_exec_sessions DES_blocked ON DOWT.[session_id] = DES_Blocked.[session_id]        INNER JOIN sys.dm_exec_sessions DES_blocking ON DOWT.[blocking_session_id] = DES_Blocking.[session_id]        INNER JOIN sys.dm_exec_connections DEC ON DTL.[request_session_id] = DEC.[most_recent_session_id]        CROSS APPLY sys.dm_exec_sql_text(DEC.[most_recent_sql_handle]) AS DEST_Blocking        CROSS APPLY sys.dm_exec_sql_text(DER.sql_handle) AS DEST_BlockedWHERE   DTL.[resource_database_id] = DB_ID()

使用 Profiler 获取性能数据

从sQL Server 2000开始Profiler就存在，当时称为事件探查器，但是从SQL Server2005开始才称为Profiler。它是一个捕获和分析事件的强大工具，得到的结果集可以用于很多性能问题和运行问题的侦测。

DBCC命令

DBCC命令原本是供微软内部使用的，但是后来逐步放开了。不过有些很重要的功能

依旧没有公开。这里重点演示几个常用的 DBCC命令:

DBCC sQLPERF

DBCC INPUTBUFFER

DBCC TRACEON/TRACEOFF

DBCC SHOWCONTIG

DBCC OPENTRAN

查着数据库的日志使用情况

很多人会遇到SQL Server中某个库的日志量暴增的情况，却不知道是什么原因，于是

便选择使用暴力的方法解决，如分离→删除LDF 文件→附件，让SQL Server重新生成LDF

文件。这种方法相当不可取，因为LDF包含了非常重要的信息，如果就这样删除了，轻则

丢失一些未提交的数据，重则数据库无法使用。对于这种问题，可以先到sys.databases中

查看log_reuse_desc字段的信息。一般来说，出现这种情况都是因为没有管理好数据库导致

的，可以用如下命令来查看数据库的使用情况:

USE mastergo--创建测试库IF DB_ID('Log_Demo') IS NOT NULL    DROP DATABASE Log_DemogoCREATE DATABASE Log_DemogoALTER DATABASE Log_Demo SET RECOVERY FULL go USE Log_Demogo--创建测试表IF OBJECT_ID('Table_demo', 'U') IS NOT NULL    DROP TABLE Table_demogoCREATE TABLE Table_demo    (      id INT IDENTITY(1, 1) ,      name CHAR(8000)    )go--检查日志使用情况DBCC sqlperf(logspace)go--插入10000条数据INSERT  INTO Table_demo        ( name )VALUES  ( 'test' )go 10000--再次检查日志情况DBCC sqlperf(logspace)go

在这里插入图片描述

可以看到，Log Size没有变小，但是Log Space Used已经降低。可见，通过这个命令，可以检查日志的使用情况，并决定是否需要进行日志备份以控制日志文件的大小。

转载地址：http://hsesn.baihongyu.com/

你可能感兴趣的文章