34,576
社区成员
发帖
与我相关
我的任务
分享
USE [monitor]
GO
/****** Object: StoredProcedure [dbo].[dba_man_alter_index_exec_byxl] Script Date: 2015/2/15 17:19:51 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
* 重建索引存储过程(执行)
* 编写人:肖磊
* 更新时间:2015-02-12
* 更新内容:添加对多个库的重建索引
* 新增索引维护前的存在性判断
* 新增对版本、alwayson副本类型的判断
* 在monitor库建立
*/
CREATE PROC [dbo].[dba_man_alter_index_exec_byxl]
@Etime VARCHAR(8) ='06:30:00' --修改阀值应为标准24小时格式
AS
BEGIN
SET NOCOUNT ON ;
DECLARE @id INT; --标识列
DECLARE @indexname VARCHAR(200) --索引名
DECLARE @sql VARCHAR(2000); --sql语句
DECLARE @optime SMALLDATETIME; --操作时间
DECLARE @fragpercent_altered DECIMAL --重建后的碎片
DECLARE @objectid INT --表ID
DECLARE @indexid INT --索引ID
DECLARE @db_id INT --数据库ID
DECLARE @version INT --数据库版本号
DECLARE @role INT --SQL2012判断角色类型,1为primary,2为secondary,默认为0
--判断版本,如为2012,则继续判断主\辅助副本
SET @version=cast(left(CAST(SERVERPROPERTY('productversion') AS VARCHAR),2) AS int)
SET @role=0
IF @version>=11
BEGIN
SELECT @role=role FROM sys.dm_hadr_availability_replica_states WHERE is_local=1
END
IF (@version<=10) OR (@role=1)
BEGIN
--PRINT 'ver:'+CAST(@version AS VARCHAR(10))+' role='+CAST(ISNULL(@role,0) AS VARCHAR(10))+' IF '
--统计7天内需要重建的索引
SELECT [DB_ID],id,[object_id],[index_id],optype,indexname into #tmp from dbo.dba_man_index_alter_log_byxl WHERE yn=1 and intime>=getdate()-7 ORDER BY id
WHILE (SELECT COUNT(1) FROM #tmp)>0
BEGIN
SELECT top 1 @db_id=DB_ID, @id=id,@objectid=[object_id],@indexid=[index_id],@sql=optype,@indexname=indexname FROM #tmp
--开始重建
SET @sql='use '+db_name(@db_id)+' ;
if exists (SELECT 1 FROM sys.indexes WHERE name='''+@indexname+''') '+CHAR(10)
+@sql+CHAR(10)+
'else
begin
update dba_man_index_alter_log_byxl SET yn=0,optime=getdate(),[fragpercent_altered]=null where id='+CAST(@id AS varchar)+
CHAR(10)+
'
return;
end '
exec (@sql)
--获取结束时间
SET @optime=GETDATE()
--更新日志表
select @fragpercent_altered=avg_fragmentation_in_percent
from sys.dm_db_index_physical_stats(@db_id, null, null , null, 'LIMITED')
where object_id=@objectid and index_id=@indexid
UPDATE dba_man_index_alter_log_byxl SET yn=0 ,optime=@optime,[fragpercent_altered]=@fragpercent_altered WHERE id=@id
--更新临时表
DELETE FROM #tmp WHERE id=@id
--判断是否继续操作
if cast(replace(CONVERT(VARCHAR(8),@optime,8),':','') AS INT) < cast(REPLACE(@Etime,':','') AS INT)
CONTINUE
ELSE
BREAK
END
DROP TABLE #tmp
END
ELSE
BEGIN
--PRINT 'ver:'+CAST(@version AS VARCHAR(10))+' role='+CAST(ISNULL(@role,0) AS VARCHAR(10))+' ELSE '
RETURN(1);
END
END
/****** Object: StoredProcedure [dbo].[dba_man_alter_index_byxl] Script Date: 2015/2/15 17:19:02 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
* 重建索引存储过程(统计)
* 编写人:肖磊
* 更新时间:2014-11-18
* 更新内容1:添加对多个库的重建索引,在每个需要重建索引的库上分别创建
* 更新内容2:修复bug,完善对online模式的判断,支持2012对varchar(max)在线重建索引
* 在每个用户数据库中创建
*/
CREATE PROC [dbo].[dba_man_alter_index_byxl]
@rows_LIMIT int = 50000
as
begin
set nocount on;
declare @rows int --表的总行数
declare @intime smalldatetime --操作时间
declare @fragpercent_altered decimal --重建后的碎片
declare @objectid int --表ID
declare @indexid int --索引ID
declare @partitioncount bigint --分区ID
declare @schemaname sysname --架构
declare @objectname sysname --对象名
declare @indexname sysname --索引名
declare @partitionnum bigint --分区编号?
declare @partitions bigint --分区?
declare @frag float --碎片比例
declare @command varchar(1000)
declare @need_fillfactor smallint --是否需要设置填充因子
DECLARE @space_in_M BIGINT --表大小
IF NOT EXISTS (SELECT * FROM monitor.sys.objects WHERE object_id = OBJECT_ID(N'MONITOR.[dbo].[dba_man_index_alter_log_byxl]') AND type in (N'U'))
CREATE TABLE monitor.[dbo].[dba_man_index_alter_log_byxl](
[id] [int] IDENTITY(1,1) NOT NULL,
[tablename] [varchar](50) NULL,
[object_id] [int] NULL,
[row_numbers] [int] NULL,
[space_in_M] [BIGINT] NULL,
[indexname] [varchar](100) NULL,
[index_id] [int] NULL,
[fragpercent] [decimal](18, 0) NULL,
[fragpercent_altered] [decimal](18, 0) NULL,
[optype] [varchar](1000) NULL,
[intime] [smalldatetime] NULL,
[optime] [smalldatetime] NULL,
[db_id] [int] NULL,
[db_name] [varchar](100) NULL,
[yn] [tinyint] NULL,
CONSTRAINT [PK_DBA_MAN_INDEX_ALTER_LOG_BYXL] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
ELSE
delete monitor.dbo.dba_man_index_alter_log_byxl where intime<dateadd(day,-30,getdate())--删除30天前的索引维护记录,确保记录不会无限增长。
CREATE TABLE #bb(
[tablename] [varchar](50) NULL,
[object_id] [int] NULL,
[row_numbers] [int] NULL,
[space_in_M] [BIGINT] NULL,
[indexname] [varchar](100) NULL,
[index_id] [int] NULL,
[fragpercent] [decimal](18, 0) NULL,
[fragpercent_altered] [decimal](18, 0) NULL,
[optype] [varchar](1000) NULL,
[intime] [smalldatetime] NULL,
[optime] [smalldatetime] NULL,
[db_id] [int] NULL,
[db_name] [varchar](100) NULL,
[yn] [tinyint] NULL)
--取出所有碎片比例大于30%的索引
select objectid= object_id,indexid = index_id,partitionnum = partition_number,frag= avg_fragmentation_in_percent
into #work_to_do
from sys.dm_db_index_physical_stats(db_id(), null, null , null, 'LIMITED') --dm_ph_stats join sys.dm_db_partition_stats dm_pa_st on dm_ph_stats.object_id=dm_pa_st.object_id
where avg_fragmentation_in_percent >= 30.0
and index_id > 0
and object_id in (select distinct a.object_id from sys.dm_db_partition_stats a join sys.indexes b
on a.object_id=b.object_id and a.index_id=b.index_id
where row_count>=@rows_LIMIT and a.index_id>0
and a.in_row_data_page_count>1280
and not exists (select 1 from monitor.dbo.[dba_man_index_alter_log_byxl] c
where c.indexname =b.name and (c.optime >getdate()-3)))
AND NOT (OBJECT_NAME(OBJECT_ID)='orders' AND index_id=1) --不对orders表的聚集索引进行重建
-- 没有需要重建/重组的索引,直接返回
if (select count(*) from #work_to_do)=0 return
declare partitions cursor for select * from #work_to_do
;
with cte as (
select distinct c.object_id,a.system_type_id from sys.types a join sys.columns b
on (a.system_type_id=b.system_type_id and a.user_type_id=b.user_type_id)
join sys.tables c on b.object_id=c.object_id
where a.name in ('text','ntext','image', 'xml') or (a.name in ('varchar','nvarchar','varbinary' ) and (b.max_length=a.max_length or b.max_length=-1))
)
select cte.object_id,cte.system_type_id,index_id into #bb_blog_prikey --把包含大对象的表ID和索引存入临时表
from cte join sys.indexes si on cte.object_id=si.object_id
where si.[type]=1 order by cte.object_id
-- Open the cursor.
open partitions
-- Loop through the partitions.
fetch next from partitions into @objectid, @indexid, @partitionnum, @frag
while @@fetch_status = 0
begin
select @objectname = o.name, @schemaname = s.name from sys.objects as o inner join sys.schemas as s on s.schema_id = o.schema_id
where o.object_id = @objectid
select @indexname = 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
if @frag < 40.0
begin
select @command = 'alter index ' + @indexname + ' on ' + @schemaname + '.' + @objectname + ' reorganize '
if @partitioncount > 1
select @command = @command + ' partition=' + convert(char, @partitionnum)
end
if @frag >= 40.0
BEGIN
--保证只对有大对象列的表的聚集索引离线重建
if exists(select 1 from #bb_blog_prikey where object_id= @objectid and index_id=@indexid )
BEGIN
select @command = 'alter index ' + @indexname +' on ' + @schemaname + '.' + @objectname + ' rebuild with(FILLFACTOR = 90) '
IF (select COUNT(1) from #bb_blog_prikey where object_id= @objectid and index_id=@indexid AND system_type_id NOT IN (34,35,99))>0 and cast(left(CAST(SERVERPROPERTY('productversion') AS VARCHAR),2) AS int)>=11 --SQLSERVER 2012 支持的在线重建聚集索引
select @command = 'alter index ' + @indexname +' on ' + @schemaname + '.' + @objectname + ' rebuild with(FILLFACTOR = 90,ONLINE=ON,MAXDOP=1) '
END
ELSE --非聚集索引
BEGIN
--判断版本,2012版system_type_id IN(34,35,99)只能离线重建
IF cast(left(CAST(SERVERPROPERTY('productversion') AS VARCHAR),2) AS int)>=11
BEGIN
IF ( SELECT COUNT(1) FROM sys.index_columns WHERE object_id=@objectid AND index_id=@indexid
AND column_id IN (SELECT column_id FROM sys.columns WHERE object_id=@objectid AND system_type_id IN(34,35,99)) --列类型为text、ntext、image
)>0
select @command = 'alter index ' + @indexname +' on ' + @schemaname + '.' + @objectname + ' rebuild with(FILLFACTOR = 90) '
ELSE
select @command = 'alter index ' + @indexname +' on ' + @schemaname + '.' + @objectname + ' rebuild with(FILLFACTOR = 90,ONLINE=ON,MAXDOP=1) '
END
ELSE
--判断版本,2012以下版本非聚集索引包含了特定的列只能离线线重建
BEGIN
IF EXISTS( SELECT 1 FROM sys.index_columns WHERE object_id=@objectid AND index_id=@indexid
AND column_id IN
(SELECT column_id FROM sys.columns WHERE object_id=@objectid AND system_type_id IN(34,35,99,241) --列类型为text、ntext、image,xml
OR (object_id=@objectid AND system_type_id IN(165,167,231) AND max_length=-1))--列类型为varbinary,varchar,nvarchar且为max
)
select @command = 'alter index ' + @indexname +' on ' + @schemaname + '.' + @objectname + ' rebuild with(FILLFACTOR = 90) '
ELSE
select @command = 'alter index ' + @indexname +' on ' + @schemaname + '.' + @objectname + ' rebuild with(FILLFACTOR = 90,ONLINE=ON,MAXDOP=1) '
END
END
if @partitioncount > 1
select @command = @command + ' partition=' + convert(char, @partitionnum)
end
-- 取表的总行数
set @intime=convert(smalldatetime,getdate(),120)
select @rows= row_count,@space_in_M=reserved_page_count*8/1024 from sys.dm_db_partition_stats where object_id=(object_id(@objectname)) and index_id=1
insert into #bb
values(@objectname,object_id(@objectname),@rows,@space_in_M,@indexname,@indexid,@frag,null,@command,@intime,null,db_id(),db_name(db_id()),1)
fetch next from partitions into @objectid, @indexid, @partitionnum, @frag
end
close partitions
deallocate partitions
INSERT INTO monitor.dbo.dba_man_index_alter_log_byxl
SELECT * FROM #bb ORDER BY space_in_M DESC
drop table #bb
drop table #work_to_do
drop table #bb_blog_prikey
end
--查表字段及其描述,开发时挺实用
SELECT
表名 = CASE WHEN A.COLORDER=1 THEN D.NAME ELSE '' END,
表备注 = CASE WHEN A.COLORDER=1 THEN ISNULL(F.VALUE,'') ELSE '' END,
列序号 = A.COLORDER,
列名称 = A.NAME,
列备注 = ISNULL(G.[VALUE],''),
标识 = CASE WHEN COLUMNPROPERTY(A.ID,A.NAME,'ISIDENTITY')=1 THEN '√ ' ELSE '' END,
主键 = CASE WHEN EXISTS(SELECT 1 FROM SYSOBJECTS WHERE XTYPE='PK' AND PARENT_OBJ=A.ID AND NAME IN (
SELECT NAME FROM SYSINDEXES WHERE INDID IN(
SELECT INDID FROM SYSINDEXKEYS WHERE ID=A.ID AND COLID=A.COLID))) THEN '√' ELSE '' END,
类型 = B.NAME,
字节 = A.LENGTH,
长度 = COLUMNPROPERTY(A.ID,A.NAME,'PRECISION'),
小数位 = ISNULL(COLUMNPROPERTY(A.ID,A.NAME,'SCALE'),0),
允许空 = CASE WHEN A.ISNULLABLE=1 THEN '√ 'ELSE '' END,
默认值 = ISNULL(E.TEXT,'')
FROM
SYSCOLUMNS A
LEFT JOIN SYSTYPES B ON A.XUSERTYPE=B.XUSERTYPE
INNER JOIN SYSOBJECTS D ON A.ID=D.ID AND D.XTYPE='U ' --AND D.NAME<>'DTPROPERTIES'
LEFT JOIN SYSCOMMENTS E ON A.CDEFAULT=E.ID
LEFT JOIN sys.extended_properties G ON A.ID=G.major_id AND A.COLID=G.minor_id
LEFT JOIN sys.extended_properties F ON D.ID=F.major_id AND F.minor_id=0
where D.NAME='att_interval' --查询这个表
ORDER BY A.ID,A.COLORDER