【基本优化实践】【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】查看索引相关

【2.1】查看没有索引的表

单库

SELECT DISTINCT
        @@SERVERNAME              AS [SERVER_NAME]
       ,DB_NAME()                 AS [DB_NAME]
       ,so.object_id              AS [OBJECT_ID] 
       ,SCHEMA_NAME(so.schema_id) +'.' +OBJECT_NAME(so.object_id) 
                                  AS [TABLE_NAME] 
       ,MAX(dmv.rows)             AS [APPROXIMATE_ROWS] 
       ,MAX(d.ColumnCount)        AS [COLUMN_COUNT]
FROM    sys.objects so ( NOLOCK )
        JOIN sys.indexes si ( NOLOCK ) ON so.object_id = si.object_id
                                          AND so.type IN ( N'U', N'V' )
        JOIN sysindexes dmv ( NOLOCK ) ON so.object_id = dmv.id
                                          AND si.index_id = dmv.indid
        FULL OUTER JOIN ( SELECT    object_id ,
                                    COUNT(1) AS ColumnCount
                          FROM      sys.columns (NOLOCK)
                          GROUP BY  object_id
                        ) d ON d.object_id = so.object_id
WHERE   so.is_ms_shipped = 0
        AND so.object_id NOT IN (
        SELECT  major_id
        FROM    sys.extended_properties (NOLOCK)
        WHERE   name = N'microsoft_database_tools_support' )
        AND INDEXPROPERTY(so.object_id, si.name, 'IsStatistics') = 0
GROUP BY so.schema_id ,
        so.object_id
HAVING  ( CASE OBJECTPROPERTY(MAX(so.object_id), 'TableHasClustIndex')
            WHEN 0 THEN COUNT(si.index_id) - 1
            ELSE COUNT(si.index_id)
          END = 0 )
ORDER BY [APPROXIMATE_ROWS] DESC;

全库

/**************************************************************************************************************
    --脚本名称  ：      find_without_index_tables.sql
    --脚本作者  :       潇湘隐者
    --创建日期  :       2016‎-10‎-‎27‎
***************************************************************************************************************
    脚本功能    ：      批量查找实例下面的每个数据库，找出没有任何索引的表
***************************************************************************************************************
    注意事项    :       暂无
***************************************************************************************************************
    参考资料    :       
***************************************************************************************************************
    更新记录    ：      2016‎-10‎-‎27‎：创建此脚本
                      2020-03-14: 修改脚本，新增SERVER_NAME，DB_NAME
***************************************************************************************************************/


IF EXISTS (SELECT * FROM tempdb.dbo.sysobjects WHERE id = OBJECT_ID('tempdb.dbo.#Database'))
BEGIN
    DROP TABLE #Database;
END

CREATE TABLE #Database (database_id INT  ,database_name NVARCHAR(128) );

 INSERT INTO #Database
 SELECT database_id, name FROM sys.databases WHERE state_desc='ONLINE' 
 AND name NOT IN ('master','msdb','tempdb','model', 'distribution')


DECLARE @database_name    NVARCHAR(128);
DECLARE @database_id      INT;
DECLARE @cmdText          NVARCHAR(MAX);

SET @database_name ='';
SET @database_id =1;

IF EXISTS (SELECT * FROM tempdb.dbo.sysobjects WHERE id = OBJECT_ID('tempdb.dbo.#TAB_NO_INDEX_INFO'))
BEGIN
    DROP TABLE #TAB_NO_INDEX_INFO;
END

CREATE TABLE #TAB_NO_INDEX_INFO(
    [SERVER_NAME] [NVARCHAR](32) NULL,
    [INSTANCE_NAME] [NVARCHAR](64) NULL,
    [DATABASE_NAME] [NVARCHAR](32) NULL,
    [TABLE_NAME] [NVARCHAR](128) NULL,
    [OBJECT_ID] [INT] NULL,
    [APPROXIMATE_ROWS] [INT] NULL,
    [COLUMN_COUNT] [INT] NULL
    );

WHILE(1=1)
 
BEGIN
    SELECT TOP 1
            @database_id = database_id ,
            @database_name = database_name
    FROM    #Database
    WHERE   database_id > @database_id -- next database_name greater than @database_id
    ORDER BY database_id -- database_id order 
 
     -- exit loop if no more name greater than the last one used
    If @@rowcount = 0 Break 

    SET @cmdText='USE ' + @database_name  +';
         --GO
         INSERT  INTO #TAB_NO_INDEX_INFO
        ( SERVER_NAME ,
          INSTANCE_NAME ,
          DATABASE_NAME ,
          TABLE_NAME ,
          OBJECT_ID ,
          APPROXIMATE_ROWS ,
          COLUMN_COUNT
        )
        SELECT  DISTINCT
               CAST(SERVERPROPERTY(''MachineName'') AS NVARCHAR(32))
                                                    AS [SERVER_NAME]   ,
                @@SERVICENAME                       AS [INSTANCE_NAME] ,
                DB_NAME()                           AS [DATABASE_NAME] ,
                SCHEMA_NAME(so.schema_id)+ ''.'' +  
                OBJECT_NAME(so.object_id)           AS [TABLE_NAME]    ,
                so.object_id                        AS [OBJECT_ID]     ,
                MAX(dmv.rows)                       AS [APPROXIMATE_ROWS] ,
                MAX(d.ColumnCount)                  AS [COLUMN_COUNT]
        FROM    sys.objects so ( NOLOCK )
                JOIN sys.indexes si ( NOLOCK ) ON so.object_id = si.object_id
                                                  AND so.type IN ( N''U'', N''V'' )
                JOIN sysindexes dmv ( NOLOCK ) ON so.object_id = dmv.id
                                                  AND si.index_id = dmv.indid
                FULL OUTER JOIN ( SELECT    object_id ,
                                            COUNT(1) AS ColumnCount
                                  FROM      sys.columns (NOLOCK)
                                  GROUP BY  object_id
                                ) d ON d.object_id = so.object_id
        WHERE   so.is_ms_shipped = 0
                AND so.object_id NOT IN (
                SELECT  major_id
                FROM    sys.extended_properties (NOLOCK)
                WHERE   name = N''microsoft_database_tools_support'' )
                AND INDEXPROPERTY(so.object_id, si.name, ''IsStatistics'') = 0
        GROUP BY so.schema_id ,
                so.object_id
        HAVING  ( CASE OBJECTPROPERTY(MAX(so.object_id), ''TableHasClustIndex'')
                    WHEN 0 THEN COUNT(si.index_id) - 1
                    ELSE COUNT(si.index_id)
                  END = 0 )
        ORDER BY [APPROXIMATE_ROWS] DESC; ' 
        PRINT @cmdText;


        EXEC ( @cmdText);
        --EXEC SP_EXECUTESQL @cmdText, N'@database_name NVARCHAR(32)',@database_name

 
 Delete Db
  From #Database Db WHERE database_id=@database_id;
 
END

SELECT  *
FROM    #TAB_NO_INDEX_INFO
ORDER BY APPROXIMATE_ROWS DESC;


--找出数据量超过1000行没有索引信息的表
SELECT * FROM #TAB_NO_INDEX_INFO
WHERE APPROXIMATE_ROWS > 1000
ORDER BY APPROXIMATE_ROWS DESC

【2.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

【2.3】无用索引

----------------------------------无用索引----------------------
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

【2.4】大量更新但不适用的索引

--------------------------经常被大量更新，但是却基本不适用的索引项--------------------
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

【2.5】未使用的索引

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

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

【2.6】汇总

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


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】查看索引碎片

【3.0】最有效的索引碎片查看方式

use gameabcAction;

--查看索引碎片
select 
db_name(database_id) as '数据库名',
object_name(t1.object_id) as obj_name,
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 '页统计',
cast(t.page_count * 8.0/1024 as decimal(10,2)) as 'indexMB',
t.avg_fragmentation_in_percent as '索引碎片比率',
t2.rows as '行记录数',t.fragment_count as '索引中的碎片数量',
t.avg_fragment_size_in_pages as '一个索引中碎片的平均页数',
t.avg_page_space_used_in_percent as '页使用率/填充因子' ,
t.record_count as '页行记录数',
t.avg_record_size_in_bytes as '平均每条记录大小(B)'
from sys.dm_db_index_physical_stats(db_id('gameabcAction'),NULL,NULL,NULL,NULL) t
join 
(
    select distinct t3.object_id,t3.index_id,t3.name as 'index_name',t3.type_desc, t1.name as 'column_name' --t4.name,t4.xtype,
    from 
    (
        select t1.object_id,t2.index_id,
        stuff((
            select ','+name 
            from sys.index_columns  q2 join  sys.columns q1 
            on q1.column_id = q2.column_id AND q1.object_id = q2.object_id
            where q1.object_id = t1.object_id and q2.index_id=t2.index_id
            for xml path('')
        )
        ,1,1,'') as name 
        from sys.index_columns  t2 join  sys.columns t1 
        on t1.column_id = t2.column_id AND t1.object_id = t2.object_id
        group by t1.object_id,t2.index_id
        
    ) t1
    join sys.indexes t3 on t1.index_id = t3.index_id AND t1.object_id = t3.object_id
    --join  sys.sysobjects t4 on t3.object_id = t4.id
    --where t3.object_id >100
            
) t1
on t1.object_id = t.object_id AND t1.index_id = t.index_id
left join sys.sysindexes t2 on t1.object_id = t2.id and t.index_id=t2.indid
where t.avg_fragmentation_in_percent>0
order by [索引碎片比率] desc,[indexMB] desc

 

【3.1】常规方式

--查看索引碎片
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