March 21, 2008 at 9:48 am
Hello SQL community,
I've a problem with my SQL Server 2005 database.
On certain tables, we have declared indexes which are present in both tables : sys.sysindexes and sys.sysobjects. (Present in sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) table too).
However, some indexes are present in :
- sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL)
- sys.sysindexes
But not in
- sys.sysobjects
Consequently, we have implemented a batch script which make the reorg or the rebuild of the indexes.
For the indexes who are not listed in sys.sysobjects table, the script don't see the index to rebuild. So, some indexes (very important indexes :w00t:) are never rebuiled !!
I would like to understand why some indexes are not listed in sys.sysobjects table ?
Here is the code of the rebuild indexes script :
DECLARE @base VARCHAR(100),@diff VARCHAR(100), @date_actuelle datetime,@command VARCHAR(8000),@pre_name_table VARCHAR(150),@name_index VARCHAR(150),@name_table VARCHAR(150),@id_index int,@avg_fragmentation_in_percent int
DECLARE @i int
SET @i = 0
SET @date_actuelle = GETDATE()
DECLARE name_index CURSOR FOR
SELECT a.index_id, b.name, avg_fragmentation_in_percent, c.name, d.name,e.name
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL,
NULL, NULL, NULL) AS a
JOIN sys.indexes AS b ON a.object_id = b.object_id AND a.index_id = b.index_id
JOIN sys.objects AS c ON c.object_id IN (SELECT DISTINCT parent_object_id FROM sys.objects WHERE c.object_id=a.object_id )
JOIN sys.schemas AS d ON d.schema_id = c.schema_id
JOIN sys.databases AS e ON e.database_id = DB_ID()
WHERE a.avg_fragmentation_in_percent BETWEEN 5 AND 100 AND a.index_id > 0 AND a.page_count > 8 ORDER BY a.avg_fragmentation_in_percent;
OPEN name_index
FETCH name_index INTO @id_index, @name_index, @avg_fragmentation_in_percent, @name_table,@pre_name_table, @base
SELECT @command = N'ALTER DATABASE '+ @base + N' SET RECOVERY BULK_LOGGED';
PRINT @command
EXEC(@command);
PRINT ''
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT 'Nom de l index : '+@name_index
PRINT ''
IF @avg_fragmentation_in_percent <= 30
BEGIN
PRINT 'TYPE DE REINDEXATION : ALTER REORG'
PRINT convert(varchar,GETDATE(),131)
SELECT @command = 'ALTER INDEX ' + @name_index + ' ON ' + @pre_name_table + '.' + @name_table + ' REORGANIZE WITH (LOB_COMPACTION=ON)' ;
PRINT @command
PRINT '-------------------------------------------------------------------------'
PRINT ''
SET @i=@i+1
EXEC (@command);
END
IF @avg_fragmentation_in_percent >= 30
BEGIN
PRINT 'TYPE DE REINDEXATION : ALTER REBUILD'
PRINT convert(varchar,GETDATE(),131)
SELECT @command = 'ALTER INDEX ' + @name_index + ' ON ' + @pre_name_table + '.' + @name_table + ' REBUILD WITH ( PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, SORT_IN_TEMPDB = OFF, ONLINE = OFF, MAXDOP=0, SORT_IN_TEMPDB=ON )';
PRINT @command
PRINT '-------------------------------------------------------------------------'
PRINT ''
SET @i=@i+1
EXEC (@command);
END
FETCH name_index INTO @id_index,@name_index,@avg_fragmentation_in_percent,@name_table,@pre_name_table, @base
END
PRINT 'FIN DE LA REINDEXATION'
PRINT convert(varchar,GETDATE(),131)
SELECT @command = N'ALTER DATABASE '+ @base + N' SET RECOVERY FULL';
PRINT @command
EXEC(@command);
CLOSE name_index
DEALLOCATE name_index
PRINT ''
PRINT N'Nombre d index réindexé :'+cast(@i AS varchar(50))
SELECT @diff = DATEDIFF(ss,@date_actuelle,GETDATE())
PRINT N'Temps de maintenance :'+ @diff +N' s'
Thank you in advance.
Littlesquall.
March 21, 2008 at 9:52 am
What are the index id's from sys.indexes? If they are 0, than it is a heap, which would not be in sys.objects. If they are > 255, they are lob, which also would not be in sys.objects.
March 21, 2008 at 10:02 am
In sysindexes table the Id of my index is :
select indid from sys.sysindexes where name='My_index_name'
>>> 11
What is a "lob" and a "heap" ?
littlesquall
March 21, 2008 at 10:13 am
Books Online is your friend 🙂
As for your query ... this should fix ya up, let me know ...
SELECT
a.index_id
,b.name
,avg_fragmentation_in_percent
,c.name
,d.name
,e.name
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL,NULL, NULL, NULL) AS a
JOIN sys.indexes AS b
ON a.object_id = b.object_id AND a.index_id = b.index_id
AND a.index_id = b.index_id
JOIN sys.objects AS c
ON c.object_id = a.object_id
JOIN sys.schemas AS d
ON d.schema_id = c.schema_id
JOIN sys.databases AS e
ON e.database_id = DB_ID()
March 21, 2008 at 10:24 am
Thanks for your interest in my problem.
Your proposition is near the final solution.
But when I execute this query, the name of the indexes who are not in sys.sysobjects is returned with NULL value. (b.name field = NULL)
So the
the query to rebuild the indexes can't work with @name_index=NULL
ALTER INDEX ' + @name_index + ' ON ' + @pre_name_table + '.' + @name_table + ' REORGANIZE WITH (LOB_COMPACTION=ON)'
...:crying:
March 21, 2008 at 10:31 am
Toss a where clause at the end for:
WHERE a.index_id > 0
AND a.index_id < 255
That should have been the only thing causing you nulls.
March 21, 2008 at 10:35 am
Yes I don't need the table name (c.name)
But I need the indexe name (b.name)
However, with your request, the field b.name is returned with NULL value for the indexes which are not listed in sys.objects.
And if I haven't the indexe name, I can't rebuilt it...
March 21, 2008 at 10:43 am
Well, you're not really using much of your query ... that and why are you changing your recovery models for this operation? The ALTER index does not bloat the log files like DBCC DBREINDEX/DEFRAG used to.
You're also not taking into account partitions, the ability to reorganize or rebuild, as well as online operations. I'd recommend using the example in BOL and customizing it to your environment's needs.
Anyways, here is a condensed version of yours ... :
This should give you all your indexes ...
SELECT
a.index_id
,b.name
,a.avg_fragmentation_in_percent
,db_name(db_id()) AS [DBName]
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL,NULL, NULL, NULL) AS a
JOIN sys.indexes AS b
ON a.object_id = b.object_id
AND a.index_id = b.index_id
WHERE a.index_id > 0
AND a.index_id < 255
March 25, 2008 at 4:38 am
Thanks Adams for your help.
I've solved my problem.
I don't know why, but some indexes of my SQL2005 DB listed in sys.objects have the field parent_object_id = 0 in sys_objects.
That why the script in my first post didn't manage some very important indexes.
I've corrected my script with Microsoft recommandations in
(http://msdn2.microsoft.com/en-us/library/ms188917.aspx)
and now, all my indexes are managed by my script.
So, find enclosed the corrected script :
DECLARE @base VARCHAR(100),@diff VARCHAR(100), @date_actuelle datetime,@command VARCHAR(8000),@pre_name_table VARCHAR(150),@name_index VARCHAR(150),@name_table VARCHAR(150),@id_index int,@avg_fragmentation_in_percent int
DECLARE @i int
SET @i = 0
SET @date_actuelle = GETDATE()
DECLARE name_index CURSOR FOR
SELECT
a.index_id,
b.name,
avg_fragmentation_in_percent,
c.name,
d.name,
e.name
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS a
JOIN sys.indexes AS b
ON a.object_id = b.object_id
AND a.index_id = b.index_id
JOIN sys.objects AS c
ON c.object_id =a.object_id
JOIN sys.schemas AS d
ON d.schema_id = c.schema_id
JOIN sys.databases AS e
ON e.database_id = DB_ID()
WHERE a.avg_fragmentation_in_percent BETWEEN 5 AND 100 AND a.index_id > 0 AND a.page_count > 8 ORDER BY a.avg_fragmentation_in_percent;
OPEN name_index
FETCH name_index INTO @id_index, @name_index, @avg_fragmentation_in_percent, @name_table,@pre_name_table, @base
SELECT @command = N'ALTER DATABASE '+ @base + N' SET RECOVERY BULK_LOGGED';
PRINT @command
EXEC(@command);
PRINT ''
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT 'Nom de l index : '+@name_index
PRINT ''
IF @avg_fragmentation_in_percent <= 30
BEGIN
PRINT 'TYPE DE REINDEXATION : ALTER REORG'
PRINT convert(varchar,GETDATE(),103) + ' ' + convert(varchar,GETDATE(),14)
SELECT @command = 'ALTER INDEX [' + @name_index + '] ON [' + @pre_name_table + '].[' + @name_table + '] REORGANIZE WITH (LOB_COMPACTION=ON)' ;
PRINT @command
PRINT '-------------------------------------------------------------------------'
PRINT ''
SET @i=@i+1
EXEC (@command);
END
IF @avg_fragmentation_in_percent >= 30
BEGIN
PRINT 'TYPE DE REINDEXATION : ALTER REBUILD'
PRINT convert(varchar,GETDATE(),103) + ' ' + convert(varchar,GETDATE(),14)
SELECT @command = 'ALTER INDEX [' + @name_index + '] ON [' + @pre_name_table + '].[' + @name_table + '] REBUILD WITH ( PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, SORT_IN_TEMPDB = OFF, ONLINE = OFF, MAXDOP=0, SORT_IN_TEMPDB=ON )';
PRINT @command
PRINT '-------------------------------------------------------------------------'
PRINT ''
SET @i=@i+1
EXEC (@command);
END
FETCH name_index INTO @id_index,@name_index,@avg_fragmentation_in_percent,@name_table,@pre_name_table, @base
END
PRINT 'FIN DE LA REINDEXATION'
PRINT convert(varchar,GETDATE(),103) + ' ' + convert(varchar,GETDATE(),14)
SELECT @command = N'ALTER DATABASE '+ @base + N' SET RECOVERY FULL';
PRINT @command
EXEC(@command);
CLOSE name_index
DEALLOCATE name_index
PRINT ''
PRINT N'Nombre d index réindexé : '+cast(@i AS varchar(50))
SELECT @diff = DATEDIFF(ss,@date_actuelle,GETDATE())
PRINT N'Temps de maintenance : '+ @diff +N' s'
Littlesquall
😉
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply