SQL Server动态管理视图，性能调校语句

 随着项目的发展，处理的数据量也是与日剧，已然达到千万级别数据量，在日常操作中，难免会出现性能问题，很多时候不知从何入手，今天特地整理使用sql server的动态管理试图来检测性能的方案。

第一句 查看任务等待数

--wait type查询：(用于查看任务执行时的等待数)

  SELECT TOP 20

  wait_type ,

  max_wait_time_ms wait_time_ms ,

  signal_wait_time_ms ,

  wait_time_ms - signal_wait_time_ms AS resource_wait_time_ms ,

  100.0 * wait_time_ms / SUM(wait_time_ms) OVER ( )

  AS percent_total_waits ,

  100.0 * signal_wait_time_ms / SUM(signal_wait_time_ms) OVER ( )

  AS percent_total_signal_waits ,

  100.0 * ( wait_time_ms - signal_wait_time_ms )

  / SUM(wait_time_ms) OVER ( ) AS percent_total_resource_waits

  FROM sys.dm_os_wait_stats

  WHERE wait_time_ms > 0      -- remove zero wait_time

  AND wait_type NOT IN        -- filter out additional irrelevant waits

  ( 'SLEEP_TASK', 'BROKER_TASK_STOP', 'BROKER_TO_FLUSH',

  'SQLTRACE_BUFFER_FLUSH','CLR_AUTO_EVENT', 'CLR_MANUAL_EVENT',

  'LAZYWRITER_SLEEP', 'SLEEP_SYSTEMTASK', 'SLEEP_BPOOL_FLUSH',

  'BROKER_EVENTHANDLER', 'XE_DISPATCHER_WAIT', 'FT_IFTSHC_MUTEX',

  'CHECKPOINT_QUEUE', 'FT_IFTS_SCHEDULER_IDLE_WAIT',

  'BROKER_TRANSMITTER', 'FT_IFTSHC_MUTEX', 'KSOURCE_WAKEUP',

  'LAZYWRITER_SLEEP', 'LOGMGR_QUEUE', 'ONDEMAND_TASK_QUEUE',

  'REQUEST_FOR_DEADLOCK_SEARCH', 'XE_TIMER_EVENT', 'BAD_PAGE_PROCESS',

  'DBMIRROR_EVENTS_QUEUE', 'BROKER_RECEIVE_WAITFOR',

  'PREEMPTIVE_OS_GETPROCADDRESS', 'PREEMPTIVE_OS_AUTHENTICATIONOPS',

  'WAITFOR', 'DISPATCHER_QUEUE_SEMAPHORE', 'XE_DISPATCHER_JOIN',

  'RESOURCE_QUEUE' )

  ORDER BY wait_time_ms DESC

说明：

  1. sys.dm_os_wait_stats 返回正在执行的线程所遇到的等待数的有关信息。

  需要对服务器具有 VIEW SERVER STATE 权限。

  

  2. 此动态管理视图的内容可通过运行以下命令来重置：

DBCC SQLPERF ('sys.dm_os_wait_stats', CLEAR); GO

第二句 查看索引碎片

   （执行时间根据数据库的数据量来决定的）

   SELECT TOP 20

   DB_NAME() AS DatbaseName

   , SCHEMA_NAME(o.Schema_ID) AS SchemaName

   , OBJECT_NAME(s.[object_id]) AS TableName

   , i.name AS IndexName

   , ROUND(s.avg_fragmentation_in_percent,2) AS [Fragmentation %]

   FROM sys.dm_db_index_physical_stats(db_id(),null, null, null, null) s

   INNER JOIN sys.indexes i ON s.[object_id] = i.[object_id]

   AND s.index_id = i.index_id

   INNER JOIN sys.objects o ON i.object_id = O.object_id

   WHERE s.database_id = DB_ID()

   AND i.name IS NOT NULL

   AND OBJECTPROPERTY(s.[object_id],  'IsMsShipped' ) = 0

   ORDER BY [Fragmentation %] DESC

说明：

    1. 大于30%的索引都可进行重建了

    2. sys.dm_db_index_physical_stats  返回指定表或视图的数据和索引的大小和碎片信息。

    3. sys.indexes  每个表格对象（例如，表、视图或表值函数）的索引或堆都包含一行。

    4. sys.objects 在数据库中创建的每个用户定义的架构范围内的对象在该表中均对应一行。

第三句 不同类型索引操作的计数

--Unused查询：(查看当前数据库中，那个表的那个索引未被使用)

  SELECT TOP 20

  DB_NAME() AS DatabaseName

  , SCHEMA_NAME(o.Schema_ID) AS SchemaName

  , OBJECT_NAME(s.[object_id]) AS TableName

  , i.name AS IndexName

  , s.user_updates

  , s.system_seeks + s.system_scans + s.system_lookups

  AS [System usage]

  FROM sys.dm_db_index_usage_stats s

  INNER JOINsys.indexes i ON s.[object_id] = i.[object_id]

  AND s.index_id = i.index_id

  INNER JOIN sys.objects o ON i.object_id = O.object_id

  WHERE s.database_id = DB_ID()

  AND OBJECTPROPERTY(s.[object_id], 'IsMsShipped') = 0

  AND s.user_seeks = 0

  AND s.user_scans = 0

  AND s.user_lookups = 0

  AND i.name IS NOT NULL

  ORDER BY s.user_updates DESC

说明：

   1. sys.dm_db_index_usage_stats 返回不同类型索引操作的计数以及上次执行每种操作的时间。

第四句 

--Maintenance查询：(尚在学习中)

  SELECT TOP 20

  DB_NAME() AS DatabaseName

  , SCHEMA_NAME(o.Schema_ID) AS SchemaName

  , OBJECT_NAME(s.[object_id]) AS TableName

  , i.name AS IndexName

  , (s.user_updates ) AS [update usage]

  , (s.user_seeks + s.user_scans + s.user_lookups)

  AS [Retrieval usage]

  , (s.user_updates) -

  (s.user_seeks + user_scans +

  s.user_lookups) AS [Maintenance cost]

  , s.system_seeks + s.system_scans + s.system_lookups AS [System usage]

  , s.last_user_seek

  , s.last_user_scan

  , s.last_user_lookup

  FROM sys.dm_db_index_usage_stats s

  INNER JOIN sys.indexes i ON s.[object_id] = i.[object_id]

  AND s.index_id = i.index_id

  INNER JOIN sys.objects o ON i.object_id = O.object_id

  WHERE s.database_id = DB_ID()

  AND i.name IS NOT NULL

  AND OBJECTPROPERTY(s.[object_id], 'IsMsShipped') = 0

  AND (s.user_seeks + s.user_scans + s.user_lookups) > 0

  ORDER BY [Maintenance cost] DESC

第五句 查看缺失索引

(查看那些索引建立的不够完善，适当修改索引加入包含性列中)

--missing查询4：

  SELECT TOP 20  

  ROUND(s.avg_total_user_cost * s.avg_user_impact * (s.user_seeks + s.user_scans),0)

  AS [Total Cost]

  , d.[statement] AS [Table Name]

  , equality_columns

  , inequality_columns

  , included_columns

  FROM sys.dm_db_missing_index_groups g

  INNER JOIN sys.dm_db_missing_index_group_stats s

  ON s.group_handle = g.index_group_handle

  INNER JOIN sys.dm_db_missing_index_details d

  ON d.index_handle = g.index_handle

  ORDER BY [Total Cost] DESC

说明：

     sys.dm_db_missing_index_details –返回关于缺失索引的详细信息。

     sys.dm_db_missing_index_group_stats - 返回缺失索引组的摘要信息

     sys.dm_db_missing_index_groups – 返回一个具体组的缺失索引的信息。

     sys.dm_db_missing_index_columns(index_handle) – 返回在一个索引中缺失的数据库表列的信息。这是一个函数，它要求传递index_handle。

检测碎片

在对表进而对表中定义的索引进行数据修改（INSERT、UPDATE 和 DELETE 语句）的整个过程中都会出现碎片。由于这些修改通常并不在表和索引的行中平均分布，所以每页的填充度会随时间而改变。对于扫描表的部分或全部索引的查询，这种碎片会导致附加的页读取。从而延缓了数据的并行扫描。

SQL Server 2005 中计算碎片的算法比 SQL Server 2000 中的算法更精确。因此，碎片值显得更高。例如，在 SQL Server 2000 中，如果一个表的第 11 页和第 13 页在同一区中，而第 12 页不在该区中，该表不被视为含有碎片。但是访问这些页需要两次物理 I/O 操作，因此，在 SQL Server 2005 中，这将算作碎片。

索引或堆的碎片级别显示在 avg_fragmentation_in_percent 列中。对于堆，此值表示堆的区碎片。对于索引，此值表示索引的逻辑碎片。与 DBCC SHOWCONTIG 不同，这两种情况下的碎片计算算法都会考虑跨越多个文件的存储，因而结果是精确的。

备注：

   自己也在学习中，关于这四条语句的具体含义我会继续学习，并完善的，敬请关注！