alter procedure zsp_IndexesReorganizeRebuildSQL
@ReorganizePercent int = 10
, @RebuildPercent int = 15
, @Rows int = 10000
, @Online bit = 1
, @FillFactor int = 85
, @IndexType varchar(max) = '1,2' --聚集,非聚集
as
--exec zsp_IndexesReorganizeRebuildSQL 0,0, 0, 1,0,0
begin
--Microshaoft
declare @Text varchar(max)
declare @Table TABLE(id int,F int)
set @Text = @IndexType
set @Text = replace(@Text, ' ', '')
declare @Separator char(1) = ','
DECLARE @SeparatorLen int
SET @SeparatorLen = LEN(@Separator + '$') - 2
set @Text = replace(@Text, ' ', '')
declare @i int
set @i = 1
WHILE CHARINDEX(@Separator, @Text) > 0
BEGIN
declare @v varchar(100)
set @v = (LEFT(@Text, CHARINDEX(@Separator, @Text) - 1))
INSERT @Table (id, F)
select
@i
, @v
where
rtrim(ltrim(@v)) != ''
and not exists
(
select
1
from
@Table
where
F = @v
)
if @@rowcount > 0
begin
set @i = @i + 1
end
SET @Text = STUFF(@Text ,1, CHARINDEX(@Separator, @Text) + @SeparatorLen, '')
END
INSERT @Table (id, F)
select
@i
, @Text
where
rtrim(ltrim(@Text)) != ''
and not exists
(
select
1
from
@Table
where
F = @Text
)
SELECT
t.name as TableName
, ix.Name as IndexName
,
case
when
ix.type in (0)
then
'Heap'
when
ix.type in (1)
then
'Clustered'
when
ix.type in (2)
then
'Non-Clustered'
when
ix.type in (3)
then
'XML'
when
ix.type in (4)
then
'Spatial'
else
'Unknown'
end as IndexType
, avg_fragmentation_in_percent
, RANK() OVER(ORDER BY avg_fragmentation_in_percent DESC) as Rank_avg_fragmentation_in_percent
, ix.fill_factor
,
'ALTER INDEX ['
+
ix.name
+
'] ON ['
+
s.name
+
'].['
+
t.name
+
'] '
+
CASE
WHEN
ps.avg_fragmentation_in_percent >= @RebuildPercent
THEN
'REBUILD'
ELSE
'REORGANIZE'
END
+
CASE
WHEN
pc.partition_count > 1
THEN
' PARTITION = ' + CAST(ps.partition_number AS varchar)
ELSE
''
END
+
--Microshaoft
' WITH (ONLINE = '
+
CASE
WHEN
@Online = 1
THEN
' on'
ELSE
' off'
END
+
CASE
WHEN
@FillFactor > 0 and @FillFactor < 100
THEN
', PAD_INDEX = on, FILLFACTOR = '
+ cast(@FillFactor as varchar)
ELSE
', PAD_INDEX = on'
END
+
')' as [SQL]
FROM
sys.indexes AS ix with(nolock)
INNER JOIN
sys.tables t with(nolock)
ON
t.object_id = ix.object_id
INNER JOIN
sys.schemas s
ON
t.schema_id = s.schema_id
INNER JOIN
(
SELECT
object_id
, index_id
, avg_fragmentation_in_percent
, partition_number
FROM
sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) --with(nolock)
) ps
ON
t.object_id = ps.object_id
AND ix.index_id = ps.index_id
INNER JOIN
(
SELECT
object_id
, index_id
, COUNT(DISTINCT partition_number) AS partition_count
FROM
sys.partitions with(nolock)
GROUP BY
object_id
, index_id
) pc
ON
t.object_id = pc.object_id
AND ix.index_id = pc.index_id
WHERE
ix.Type in
(
select
F
from
@Table
)
and
ps.avg_fragmentation_in_percent >= @ReorganizePercent
AND ix.name IS NOT NULL
--引用 上面语句是网上的,加了行数判断,也可以用页数量判断
and exists
(
select
*
from
sys.partitions with(nolock)
where
object_id = t.object_id
and [rows] >= @Rows
/*
引用
控制一下记录行数,因为小表的rebuild等会根据很多参数标准来判断是否要去重建。
网上搜索的判断因子:
生成查询计划的阀值
缓存机制,缓存的筛选,LRU算法
预读机制
checkpoint减少回滚距离
智能join判断
重编译
*/
)
--Microshaoft
order by
ix.Type
, TableName
, avg_fragmentation_in_percent desc
end
|