數(shù)據(jù)庫在使用一段時間后，就會出現(xiàn)很多的索引碎片。

declare @ID int set @ID=OBJECT_ID('SMT_QC') dbcc showcontig(@ID)

Scan Density值越低代表越需做DBCC ReIndex ,剛做完DBCC會等于 ReIndex100%

?

下面是Reindex的存儲

CREATE procedure [dbo].[DBReindex] @DB varchar(20),@RowsLimit int=100000 as set nocount on declare @str varchar(500) create table #tmpTable(TableName varchar(50),rows int,reserved varchar(50),data varchar(50),index_size varchar(50),unused varchar(50)) declare @table as varchar(50)if not exists(select 0 from sysobjects where name='Reindex_Log')CREATE table Reindex_Log(DB varchar(20),RowsLimit int,Tablename varchar(50),TransDatetime datetime)---循?環(huán)?將?用?戶?表?insert into #tmptable declare TblCur cursor for select '['+name+']' from sysobjects where xtype='u' open TblCur fetch next from tblcur into @table while @@fetch_status=0 begininsert into #tmptable exec sp_spaceused @tablefetch next from tblcur into @table end close TblCur deallocate TblCur---循?環(huán)?Reindex declare TblCur cursor for select '['+TableName+']' from #tmpTable where rows>@RowsLimit open TblCur fetch next from tblcur into @table while @@fetch_status=0 begininsert into reindex_log(db,rowslimit,tablename,transdatetime) values(@DB,@RowsLimit,@table,getdate())dbcc dbreindex(@table)set @str='backup log '+@db+' with no_log'exec(@str)fetch next from tblcur into @table end close TblCur deallocate TblCur ---插?入?日?志? insert into reindex_log(db,rowslimit,tablename,transdatetime) values(@DB,@RowsLimit,'ReindexFinished',getdate()) drop table #tmpTable

注意：請不要在當(dāng)前的Live DB進(jìn)行Reindex。

轉(zhuǎn)載于:https://www.cnblogs.com/songguangqin/archive/2009/03/25/1421208.html

創(chuàng)作挑戰(zhàn)賽新人創(chuàng)作獎勵來咯，堅持創(chuàng)作打卡瓜分現(xiàn)金大獎

總結(jié)

以上是生活随笔為你收集整理的DB Reindex的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔推薦給好友。