【基本优化实践】【1.2】索引优化——重建索引、查看堆表、查看索引使用情况、查看索引碎片率

相关监控查阅参考另一篇博文：https://www.cnblogs.com/gered/p/11338221.html

理论知识参考：https://www.cnblogs.com/gered/p/9135379.html

【1】查看堆表

--查看堆表且行大于等于10W的
select * from (
SELECT tables.NAME, 
       (SELECT rows 
        FROM   sys.partitions 
        WHERE  object_id = tables.object_id 
               AND index_id = 0 -- 0 is for heap 
               -- 1 is for clustered index 
                And rows >=100000
       )AS numberofrows 
FROM   db_tank.sys.tables tables 
WHERE  Objectproperty(tables.object_id, N'TableHasClustIndex') = 0
)t where numberofrows is not null

--另外一种办法
select rows,object_name(id),indid from sysindexes where indid=0 and rows>=100000

【2】查看索引相关

-------------------查看缺失索引-----------------------------------------


SELECT
  avg_total_user_cost * avg_user_impact * (user_seeks + user_scans)
    AS PossibleImprovement
  ,last_user_seek
  ,last_user_scan
  ,statement AS Object
  ,'CREATE INDEX [IDX_' + CONVERT(VARCHAR,GS.Group_Handle) + '_' +
  CONVERT(VARCHAR,D.Index_Handle) + '_'
    + REPLACE(REPLACE(REPLACE([statement],']',''),'[',''),'.','') +
    ']'
    +' ON '
    + [statement]
    + ' (' + ISNULL (equality_columns,'')
    + CASE WHEN equality_columns IS NOT NULL AND inequality_columns IS
      NOT NULL THEN ',' ELSE '' END
    + ISNULL (inequality_columns, '')
    + ')'
    + ISNULL (' INCLUDE (' + included_columns + ')', '')
  AS Create_Index_Syntax
FROM
  sys.dm_db_missing_index_groups AS G
INNER JOIN
  sys.dm_db_missing_index_group_stats AS GS
ON
  GS.group_handle = G.index_group_handle
INNER JOIN
  sys.dm_db_missing_index_details AS D
ON
  G.index_handle = D.index_handle
Order By PossibleImprovement DESC


------------------缺失索引-----------------------
SELECT migs.group_handle, mid.* 
FROM sys.dm_db_missing_index_group_stats AS migs 
INNER JOIN sys.dm_db_missing_index_groups AS mig 
ON (migs.group_handle = mig.index_group_handle) 
INNER JOIN sys.dm_db_missing_index_details AS mid 
ON (mig.index_handle = mid.index_handle) 
WHERE migs.group_handle = 2


----------------------------------无用索引----------------------
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED 
SELECT 
DB_NAME() AS DatbaseName 
, SCHEMA_NAME(O.Schema_ID) AS SchemaName 
, OBJECT_NAME(I.object_id) AS TableName 
, I.name AS IndexName 
INTO #TempNeverUsedIndexes 
FROM sys.indexes I INNER JOIN sys.objects O ON I.object_id = O.object_id 
WHERE 1=2 
EXEC sp_MSForEachDB 'USE [?]; INSERT INTO #TempNeverUsedIndexes 
SELECT 
DB_NAME() AS DatbaseName 
, SCHEMA_NAME(O.Schema_ID) AS SchemaName 
, OBJECT_NAME(I.object_id) AS TableName 
, I.NAME AS IndexName 
FROM sys.indexes I INNER JOIN sys.objects O ON I.object_id = O.object_id 
LEFT OUTER JOIN sys.dm_db_index_usage_stats S ON S.object_id = I.object_id 
AND I.index_id = S.index_id 
AND DATABASE_ID = DB_ID() 
WHERE OBJECTPROPERTY(O.object_id,''IsMsShipped'') = 0 
AND I.name IS NOT NULL 
AND S.object_id IS NULL' 
SELECT * FROM #TempNeverUsedIndexes 
ORDER BY DatbaseName, SchemaName, TableName, IndexName 
DROP TABLE #TempNeverUsedIndexes


--------------------------经常被大量更新，但是却基本不适用的索引项--------------------
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED 
SELECT 
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] 
INTO #TempUnusedIndexes 
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 1=2 
EXEC sp_MSForEachDB 'USE [?]; INSERT INTO #TempUnusedIndexes 
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 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 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' 
SELECT TOP 20 * FROM #TempUnusedIndexes ORDER BY [user_updates] DESC 
DROP TABLE #TempUnusedIndexes


----------------------查看未用索引----------------------------

SELECT
ind.Index_id,
obj.Name as TableName,
ind.Name as IndexName,
ind.Type_Desc,
indUsage.user_seeks,
indUsage.user_scans,
indUsage.user_lookups,
indUsage.user_updates,
indUsage.last_user_seek,
indUsage.last_user_scan,
'drop index [' + ind.name + '] ON [' + obj.name + ']' as
DropIndexCommand
FROM
Sys.Indexes as ind
JOIN
Sys.Objects as obj
ON
ind.object_id=obj.Object_ID
LEFT JOIN
sys.dm_db_index_usage_stats indUsage
ON
ind.object_id = indUsage.object_id
AND
ind.Index_id=indUsage.Index_id
WHERE
ind.type_desc<>'HEAP' and obj.type<>'S'
AND
objectproperty(obj.object_id,'isusertable') = 1
AND
(isnull(indUsage.user_seeks,0) = 0
AND
isnull(indUsage.user_scans,0) = 0
AND
isnull(indUsage.user_lookups,0) = 0)
ORDER BY
obj.name,ind.Name
GO

【3】查看索引碎片

--查看索引碎片
select 
db_name(database_id) as '数据库名',
object_name(t.object_id) as '表名',
t.index_id as '索引id',
t1.index_name as '索引名称',
t1.type_desc as '索引类型',
t1.column_name as '索引列名',
t.partition_number as '当前索引所在分区',
t.page_count as '页统计',
t.avg_page_space_used_in_percent as '页使用率' ,
t.record_count as '页行记录数',
t.avg_record_size_in_bytes as '平均每条记录大小(B)',
t.avg_fragmentation_in_percent as '索引碎片比率',
t.fragment_count as '索引中的碎片数量',
t.avg_fragment_size_in_pages as '一个索引中碎片的平均页数'
from sys.dm_db_index_physical_stats(db_id('db_tank'),NULL,NULL,NULL,NULL) t
join (select distinct t3.object_id,t3.index_id,object_name(t3.object_id) as 'table_name',t3.name as 'index_name',t3.type_desc, t1.name as 'column_name'
from sys.columns t1 join sys.index_columns t2 on t1.column_id = t2.column_id AND t1.object_id = t2.object_id
join sys.indexes t3 on t2.index_id = t3.index_id AND t2.object_id = t3.object_id) t1
on t1.object_id = t.object_id AND t1.index_id = t.index_id
where object_name(t.object_id) = 'sys_users_goods'

--查看所有表中对应的索引名与索引列
select t3.object_id,object_name(t3.object_id) as 'table_name',t3.name as 'index_name',t3.type_desc, t1.name as 'column_name'
from sys.columns t1 join sys.index_columns t2 on t1.column_id = t2.column_id AND t1.object_id = t2.object_id
join sys.indexes t3 on t2.index_id = t3.index_id AND t2.object_id = t3.object_id
where object_name(t3.object_id) = 'sys_users_goods'

--查看表中所有索引

SELECT name,type_desc FROM sys.indexes WHERE object_id=object_id('O_Orders')

--根据索引名称查看对应的列
DBCC SHOW_STATISTICS(O_Orders,idx_order_status_2)
DBCC SHOW_STATISTICS(O_Orders,IX_O_OrdersUID)


--查找碎片率大于40%的
SELECT object_name(object_id) ,index_type_desc,alloc_unit_type_desc,avg_fragmentation_in_percent,
  fragment_count,avg_fragment_size_in_pages,page_count,record_count,
  avg_page_space_used_in_percent
FROM sys.dm_db_index_physical_stats(DB_ID('DBNAME'), 
OBJECT_ID(''),NULL,NULL,'Sampled')  
WHERE avg_fragmentation_in_percent>40

【4】在线重建DDL实践

use db_tank
go
--0、修改数据库为简单模式，以便加快效率，避免大量写日志造成频繁IO与文件过大
alter database db_tank set recovery simple
--1、declare variables


--select * from db_del..rebuild_db_tank20190513  where  索引名称='PK_GMActiveInfo'
--alter index PK_GMActiveInfo on GMActiveInfo rebuild with(online=on)
--update db_del..rebuild_db_tank20190513 set flag=1 where 索引名称='PK_GMActiveInfo'
--2、create table db_del..rebuild_db_tank20190513
    select 
    db_name(t.database_id) as '数据库名',
    object_name(t.object_id) as '表名',
    t.index_id as '索引id',
    t1.index_name as '索引名称',
    t1.type_desc as '索引类型',
    t1.column_name as '索引列名',
    t.partition_number as '当前索引所在分区',
    t.page_count as '页统计',
    t.avg_fragmentation_in_percent as '索引碎片比率',
    t.fragment_count as '索引中的碎片数量',
    t.avg_fragment_size_in_pages as '一个索引中碎片的平均页数'
    ,identity(int,1,1) rn
    ,0 as 'flag'
    into db_del..rebuild_db_tank20190513
    from sys.dm_db_index_physical_stats(db_id('db_tank'),NULL,NULL,NULL,'limited') t
    join (select distinct t3.object_id,t3.index_id,object_name(t3.object_id) as 'table_name',t3.name as 'index_name',t3.type_desc, t1.name as 'column_name'
    from sys.columns t1 join sys.index_columns t2 on t1.column_id = t2.column_id AND t1.object_id = t2.object_id
    join sys.indexes t3 on t2.index_id = t3.index_id AND t2.object_id = t3.object_id) t1
    on t1.object_id = t.object_id AND t1.index_id = t.index_id
    where t.avg_fragmentation_in_percent > 5
    
--update 'text','ntext','image','xml','varchar(max)','nvarchar(max)','varbinary(max)','nvarbinary(max)' not online mode,the flag=2    
    use db_tank
    go
    
    update t1
    set flag=2
    from db_del..rebuild_db_tank20190513 t1 join
    (
        select object_name(object_id) as table_name from sys.columns t1 
        where t1.max_length=-1 
        union 
        select object_name(object_id) from sys.columns t1  join
        (select system_type_id from sys.types where name in ('text','ntext','image','xml')) t2
        on t1.system_type_id=t2.system_type_id

    ) t2 on t1.[表名]=t2.table_name
    
declare @index_name varchar(500),@table_name varchar(500),@avg_fragment int,@flag int,@temp_index_name varchar(500)
declare @rn int ,@rn_count int
declare @sql varchar(4000),@db_name varchar(100)
--3、init
    select @rn=1,@rn_count=count(1) from db_del..rebuild_db_tank20190513
    set @temp_index_name=''
--4、do_mian
    while @rn<=@rn_count
    begin
        select @index_name=[索引名称],@table_name=[表名],@avg_fragment=[索引碎片比率],@flag=flag from db_del..rebuild_db_tank20190513 where rn=@rn
        IF    @flag=0
        begin
　　　　 --索引碎片率大于等于30%，则进行重建，否则进行重新整理
            IF @avg_fragment>=30
            BEGIN
                SET @sql='alter index '+@index_name+' on '+@table_name+' rebuild with(online=on)'
            END
            else
            begin
                SET @sql='alter index '+@index_name+' on '+@table_name+' reorganize'
            end
            print @sql
            exec(@sql)
            update db_del..rebuild_db_tank20190513 set flag=1 where [索引名称]=@index_name and [表名]=@table_name
        end
        set @rn=@rn+1
        
    
    end
    
--5 
alter database db_tank set recovery bulk_logged    

go

【5】参考的实践

SQL PASS上大神提供的重建索引GuideLine：
When To Rebuild vs. Defrag
•< 10% do nothing
•10% <> 30% defrag/reorganize
•30%+ rebuild
•And don’t do anything if the index has < 1000 pages

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO

-- =============================================
-- Author:        <AjianGG>
-- Create date:    <2014/03/18>
-- Description:    <Removing Fragmentation>
-- =============================================
CREATE PROCEDURE [dbo].[proc_rebuild_index]
    @ret    INT OUTPUT
AS
SET NOCOUNT ON

BEGIN
    DECLARE @fldDefragFragment INT = 10;
    DECLARE @fldRebuildFragment INT = 30;
    DECLARE @fldMinPageCount INT = 1000;
    DECLARE @fldTable VARCHAR(256);
    DECLARE @fldIndex VARCHAR(256);
    DECLARE @fldPercent INT;
    DECLARE @Sql       VARCHAR(256);

    BEGIN TRY

        SET @ret = -1;
        
        -- 获取索引碎片状况
        DECLARE curIndex CURSOR LOCAL STATIC READ_ONLY FORWARD_ONLY FOR
            SELECT 
                 TBL.NAME TABLE_NAME
                ,IDX.NAME INDEX_NAME
                ,AVGP.AVG_FRAGMENTATION_IN_PERCENT
            FROM SYS.DM_DB_INDEX_PHYSICAL_STATS(DB_ID(), NULL,NULL, NULL, 'LIMITED') AS AVGP 
            INNER JOIN SYS.INDEXES AS IDX 
             ON AVGP.OBJECT_ID = IDX.OBJECT_ID 
            AND AVGP.INDEX_ID = IDX.INDEX_ID 
            INNER JOIN SYS.TABLES AS TBL 
             ON AVGP.OBJECT_ID = TBL.OBJECT_ID
            INNER JOIN SYS.DM_DB_PARTITION_STATS PS
             ON AVGP.OBJECT_ID = PS.OBJECT_ID
            AND AVGP.INDEX_ID = PS.INDEX_ID 
            WHERE
                AVGP.INDEX_ID >= 1 
            AND AVGP.AVG_FRAGMENTATION_IN_PERCENT >= @fldDefragFragment
            AND PS.RESERVED_PAGE_COUNT >= @fldMinPageCount;

        -- 打开游标
        OPEN curIndex;

        -- 获取游标
        FETCH NEXT FROM curIndex
        INTO @fldTable,@fldIndex,@fldPercent;

        WHILE @@FETCH_STATUS = 0
            BEGIN
                
                --碎片率大于30，重建索引
                IF @fldPercent >= @fldRebuildFragment
                    BEGIN
                        SET @Sql = 'ALTER INDEX ' + @fldIndex + ' ON ' + @fldTable + ' REBUILD';
                        EXEC(@Sql);
                    END
                ELSE
                --碎片率小于30，重组索引
                    BEGIN
                        SET @Sql = 'ALTER INDEX ' + @fldIndex + ' ON ' + @fldTable + ' REORGANIZE';
                        EXEC(@Sql);
                    END

                -- 获取游标
                FETCH NEXT FROM curIndex
                INTO @fldTable,@fldIndex,@fldPercent;
                
            END
            
        -- 关闭游标
        CLOSE curIndex;
        DEALLOCATE curIndex;
            
        SET @ret = 0;

    END TRY
    BEGIN CATCH

        SET @ret = -1;

        DECLARE @ErrorMessage    nvarchar(4000);
        DECLARE @ErrorSeverity    int;
        DECLARE @ErrorState        int;

        SELECT
              @ErrorMessage = ERROR_MESSAGE()
            , @ErrorSeverity  = ERROR_SEVERITY()
            , @ErrorState = ERROR_STATE();

        RAISERROR( @ErrorMessage
                            , @ErrorSeverity
                            , @ErrorState);
        RETURN;

    END CATCH;
END

参考：https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-index-transact-sql?view=sql-server-ver15 （alter index）

参考：https://docs.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-dbreindex-transact-sql?redirectedfrom=MSDN&view=sql-server-ver15

官网索引整理必看：https://docs.microsoft.com/zh-cn/previous-versions/technet-magazine/cc162476(v=msdn.10)?redirectedfrom=MSDN