我是用stored procedure每晚跑的,rebuild每个index,sp如下:
USE [master]
GO
/****** Object: StoredProcedure [dbo].[RebuildIndexes] Script Date: 11/26/2010 20:10:26 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[RebuildIndexes]
as
BEGIN
set nocount on
DECLARE @execstr VARCHAR (600)
DECLARE @tablename VARCHAR (100)
DECLARE @indexname VARCHAR (100)
DECLARE @schemaname VARCHAR (100)
-- Future enhancement,do DBCC INDEXDEFRAG on big table and -
-- DBREINDEX on small table
-- Declare cursor
DECLARE indexes CURSOR LOCAL FAST_FORWARD FOR
SELECT idx.name,SCHEMA_NAME(tbl.schema_id),tbl.name
FROM sys.tables tbl , sys.indexes idx
where idx.index_id > 0 and idx.is_hypothetical = 0 and
tbl.object_id=idx.object_id
order by tbl.schema_id, tbl.name
-- Open the cursor
OPEN indexes
-- Loop through all the tables in the database
FETCH NEXT FROM indexes INTO @indexname,@schemaname,@tablename
WHILE @@FETCH_STATUS = 0
BEGIN
-- Do the reindex of all indexes of the table
SELECT @execstr=
'ALTER INDEX '+@indexname+' ON '+@schemaname+'.'+@tablename+
' REBUILD WITH (FILLFACTOR=90,PAD_INDEX=OFF,'+'STATISTICS_NORECOMPUTE=OFF'+
',ALLOW_ROW_LOCKS=ON,ALLOW_PAGE_LOCKS=ON,SORT_IN_TEMPDB=OFF,ONLINE=OFF)'
SELECT @execstr -- For debugging
EXEC (@execstr)
FETCH NEXT FROM indexes INTO @indexname,@schemaname,@tablename
END
-- Close and deallocate the cursor
CLOSE indexes
DEALLOCATE indexes
set nocount off
END以前都没问题,早上发现这个job还在executing,而且其他的query都没有result出来看到这个job的Output内容:ALTER PK_CC_TABLE ON dbo.CC_TABLE
------------------------------------------
ALTER PK_BB_TABLE ON dbo.BB_TABLE
------------------------------------------
ALTER PK_AA_TABLE ON dbo.AA_TABLE
但是这之后就一直没有了,这个问题是,卡在了AA这个index上了,还是卡在了AA后面没跑成功的某个index上了?
我要怎样知道是哪个index出了问题呢。。这两天把这个database的rebuild index停了。。
不知道怎么troubleshooting哎。。
求助~~
USE [master]
GO
/****** Object: StoredProcedure [dbo].[RebuildIndexes] Script Date: 11/26/2010 20:10:26 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[RebuildIndexes]
as
BEGIN
set nocount on
DECLARE @execstr VARCHAR (600)
DECLARE @tablename VARCHAR (100)
DECLARE @indexname VARCHAR (100)
DECLARE @schemaname VARCHAR (100)
-- Future enhancement,do DBCC INDEXDEFRAG on big table and -
-- DBREINDEX on small table
-- Declare cursor
DECLARE indexes CURSOR LOCAL FAST_FORWARD FOR
SELECT idx.name,SCHEMA_NAME(tbl.schema_id),tbl.name
FROM sys.tables tbl , sys.indexes idx
where idx.index_id > 0 and idx.is_hypothetical = 0 and
tbl.object_id=idx.object_id
order by tbl.schema_id, tbl.name
-- Open the cursor
OPEN indexes
-- Loop through all the tables in the database
FETCH NEXT FROM indexes INTO @indexname,@schemaname,@tablename
WHILE @@FETCH_STATUS = 0
BEGIN
-- Do the reindex of all indexes of the table
SELECT @execstr=
'ALTER INDEX '+@indexname+' ON '+@schemaname+'.'+@tablename+
' REBUILD WITH (FILLFACTOR=90,PAD_INDEX=OFF,'+'STATISTICS_NORECOMPUTE=OFF'+
',ALLOW_ROW_LOCKS=ON,ALLOW_PAGE_LOCKS=ON,SORT_IN_TEMPDB=OFF,ONLINE=OFF)'
SELECT @execstr -- For debugging
EXEC (@execstr)
FETCH NEXT FROM indexes INTO @indexname,@schemaname,@tablename
END
-- Close and deallocate the cursor
CLOSE indexes
DEALLOCATE indexes
set nocount off
END以前都没问题,早上发现这个job还在executing,而且其他的query都没有result出来看到这个job的Output内容:ALTER PK_CC_TABLE ON dbo.CC_TABLE
------------------------------------------
ALTER PK_BB_TABLE ON dbo.BB_TABLE
------------------------------------------
ALTER PK_AA_TABLE ON dbo.AA_TABLE
但是这之后就一直没有了,这个问题是,卡在了AA这个index上了,还是卡在了AA后面没跑成功的某个index上了?
我要怎样知道是哪个index出了问题呢。。这两天把这个database的rebuild index停了。。
不知道怎么troubleshooting哎。。
求助~~
数据库怎么每天都要重建索引,有必要么?
2。 rebuild index频率太高
我们是SQLServer每天做..oracle每个礼拜做..DBCC怎么查出index是不是无效了呢?
DBCC的哪个可以查index有效无效需不需要重新建index
http://www.cnblogs.com/nzperfect/archive/2010/11/29/1891295.html
SELECT @execstr -- For debugging
EXEC (@execstr)
卡在ALTER PK_AA_TABLE ON dbo.AA_TABLE上了
聚集索引用得着老rebuild吗?rebuild时会造成表无法访问的,如果有进程正在对该表进行写操作,容易造成rebuild的堵塞。