November 24, 2011 at 8:54 am
Ok I want to update all indexes over 10% with a rebuild I use the code below .... (running sql 2008 with compat mode 80 so had to pass the variable to sys.dm_db_index_physical_stats or it wouldnt compile )
but when I review the results in sys.dm_db_index_physical_stats it seems whilst they are different they are still over 10% etc ... what am I missing ??/ ( a brain maybe at this stage)
DECLARE @TBL varchar(34),
@SQLSTR VARCHAR (1000),
@ctrl CHAR (2),
@idx varchar(100),
@imdb-2 int
SET @ctrl = CHAR (13) + CHAR (10)
SELECT @imdb-2 = DB_ID()
select @imdb-2
DECLARE TBLCUR CURSOR FOR
sELECT OBJECT_NAME(a.OBJECT_ID) AS [TableName],name
FROM sys.dm_db_index_physical_stats (@imdb, null,NULL, NULL, 'SAMPLED') AS a
JOIN sys.indexes AS b
ON a.object_id = b.object_id
AND a.index_id = b.index_id
where avg_fragmentation_in_percent >= 10
AND a.index_type_desc <> 'HEAP'
ORDER BY a.avg_fragmentation_in_percent DESC
OPEN TBLCUR
FETCH NEXT FROM TBLCUR INTO @TBL,@idx
WHILE @@FETCH_STATUS = 0
BEGIN
SET @SQLSTR = 'ALTER INDEX ' + @idx + ' ON [dbo].[' + RTRIM(LTRIM(@TBL)) + '] REBUILD'
print @SQLSTR
exec (@SQLSTR)
FETCH NEXT FROM TBLCUR INTO @TBL,@idx
END
CLOSE TBLCUR
DEALLOCATE TBLCUR
GO
November 24, 2011 at 8:55 am
How big are those indexes? How many pages?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 24, 2011 at 9:06 am
This solves all your problems.
http://sqlfool.com/2011/06/index-defrag-script-v4-1
You can run the dvm, you just need to be calling it from a db in 90 mode (this wors even if the other db is in compat mode 80).
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply