SQL Server性能分析

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('数据库名'),OBJECT_ID(N'表或视图'),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),</