SQL Server性能分析

1索引

索引的碎片率高或索引缺失都有可能造成SQL Server引擎的CPU使用率高。

1.1. 查询索引的情况

select  i.name as IndexName, -- 索引名称

              d.database_id, -- 表或视图的数据库 ID

              d.index_id, -- 索引的索引 ID,0 = 堆

              d.partition_number, -- 所属对象内从 1 开始的分区号；表、视图或索引。1 =未分区的索引或堆

              d.index_type_desc, -- 索引类型

              d.avg_fragmentation_in_percent, -- 索引的逻辑碎片

              d.avg_fragment_size_in_pages, -- 

              d.* 

from 
sys.dm_db_index_physical_stats(DB_ID('M3v80_R17C_2080'),OBJECT_ID(N'SMInvoiceDetail'),null,null,'LIMITED') d

       left join sys.indexes i on 

              i.object_id = d.object_id and 

              i.index_id = d.index_id

---

1.2. 重新生成或重新组织索引

/*以下示例将自动重新组织或重新生成数据库中平均碎片超过 10％ 的所有分区。 
 注意 请先修改DB_ID('这里写数据库名称')*/
SET NOCOUNT ON;  
DECLARE @objectid int;  
DECLARE @indexid int;  
DECLARE @partitioncount bigint;  
DECLARE @schemaname nvarchar(130);   
DECLARE @objectname nvarchar(130);   
DECLARE @indexname nvarchar(130);   
DECLARE @partitionnum bigint;  
DECLARE @partitions bigint;  
DECLARE @frag float;  
DECLARE @command nvarchar(4000);   
-- Conditionally select tables and indexes from the sys.dm_db_index_physical_stats function   
-- and convert object and index IDs to names.  
SELECT  
    object_id AS objectid,  
    index_id AS indexid,  
    partition_number AS partitionnum,  
    avg_fragmentation_in_percent AS frag  
INTO #work_to_do  
FROM sys.dm_db_index_physical_stats (DB_ID('这里写数据库名称'), NULL, NULL , NULL, 'LIMITED')  
WHERE avg_fragmentation_in_percent > 10.0	--碎片百分比
	AND index_id > 0;  
  
-- Select * From #work_to_do
-- Declare the cursor for the list of partitions to be processed.  
DECLARE partitions CURSOR FOR SELECT * FROM #work_to_do;  
  
-- Open the cursor.  
OPEN partitions;  
  
-- Loop through the partitions.  
WHILE (1=1)  
    BEGIN;  
        FETCH NEXT  
           FROM partitions  
           INTO @objectid, @indexid, @partitionnum, @frag;  
        IF @@FETCH_STATUS < 0 BREAK;  
        SELECT @objectname = QUOTENAME(o.name), @schemaname = QUOTENAME(s.name)  
        FROM sys.objects AS o  
        JOIN sys.schemas as s ON s.schema_id = o.schema_id  
        WHERE o.object_id = @objectid;  
        SELECT @indexname = QUOTENAME(name)  
        FROM sys.indexes  
        WHERE  object_id = @objectid AND index_id = @indexid;  
        SELECT @partitioncount = count (*)  
        FROM sys.partitions  
        WHERE object_id = @objectid AND index_id = @indexid;  
  
-- 30 is an arbitrary decision point at which to switch between reorganizing and rebuilding.  
        IF @frag < 30.0  
            SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REORGANIZE';  
        IF @frag >= 30.0  
            SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REBUILD';  
        IF @partitioncount > 1  
            SET @command = @command + N' PARTITION=' + CAST(@partitionnum AS nvarchar(10));  
        EXEC (@command);  
        PRINT N'Executed: ' + @command;  
    END;  
  
-- Close and deallocate the cursor.  
CLOSE partitions;  
DEALLOCATE partitions;  
  
-- Drop the temporary table.  
DROP TABLE #work_to_do;  
GO

---

1.3. 查找缺失索引组的缺失索引及其列详细信息

注意：
返回的信息 sys.dm_db_missing_index_group_stats 由每次查询执行更新，而不是每次查询编译或重新编译更新。 使用情况统计信息不会持久保存，而只会在重新启动数据库引擎之前保存。 如果数据库管理员要在服务器回收后保留使用情况统计信息，则应该定期制作缺失索引信息的备份副本。 使用 sqlserver_start_time sys.dm_os_sys_info 中的列查找上次数据库引擎启动时间。

与索引相关的动态管理视图和函数

/* 查找缺失索引组的缺失索引及其列详细信息
   此 DMV 的结果集限制为600行。 每一行都包含一个缺失索引。 如果缺少超过600个索引
*/
SELECT
  -- DMV 信息
  gStats.avg_total_user_cost * (gStats.avg_user_impact / 100.0) *(gStats.user_seeks + gStats.user_scans) AS 实现索引的收益指数,
  gStats.avg_total_user_cost AS 缺失索引的查询成本, -- 可通过组中的索引减少的用户查询的平均成本
  gStats.avg_user_impact AS [实现索引 用户的收益（%）], -- 实现此缺失索引组后，用户查询可能获得的平均百分比收益
  gStats.avg_system_impact AS [实现索引 系统的收益（%）], -- 实现此缺失索引组后，系统查询可能获得的平均百分比收益。
  gStats.user_seeks AS 查找次数, -- 由可能使用了组中建议索引的用户查询所导致的查找次数
  gStats.user_scans AS 扫描次数, -- 由可能使用了组中建议索引的用户查询所导致的扫描次数

  i.database_id AS 数据库ID, 
  DB_NAME( i.database_id ) AS 数据库,
  i.[object_id] AS 缺失索引的表ID,
  OBJECT_NAME( i.[object_id], i.database_id ) AS 缺失索引的表,

  -- 创建索引的SQL语句
  'CREATE INDEX [IX_' + OBJECT_NAME( i.[object_id], i.database_id ) + '_' + CONVERT (varchar, g.index_group_handle) + '_' + CONVERT (varchar, i.index_handle) 
  +  ']'
  + ' ON ' + i.statement
  + ' (' + ISNULL (i.equality_columns,'')
  + CASE WHEN i.equality_columns IS NOT NULL AND i.inequality_columns IS NOT 
NULL THEN ',' ELSE '' END
  + ISNULL (i.inequality_columns, '')
  + ')'
  + ISNULL (' INCLUDE (' + i.included_columns + ')', '') AS [创建索引的SQL语句]

FROM sys.dm_db_missing_index_groups g -- 缺失索引组（一个索引组仅包含一个索引）
       INNER JOIN sys.dm_db_missing_index_group_stats gStats ON -- 缺失索引组的摘要信息
               gStats.group_handle = g.index_group_handle
       INNER JOIN sys.dm_db_missing_index_details i ON  -- 缺失索引的详细信息
              g.index_handle = i.index_handle
ORDER BY gStats.avg_total_user_cost * gStats.avg_user_impact * (gStats.user_seeks + gStats.user_scans) DESC