Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Indexes rebuilding problem


Indexes rebuilding problem

Author
Message
littlesquall
littlesquall
Grasshopper
Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)

Group: General Forum Members
Points: 15 Visits: 42
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 :w00tSmile 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.
Adam Bean
Adam Bean
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1215 Visits: 2160
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.

---
SQLSlayer
Making SQL do what we want it to do.
littlesquall
littlesquall
Grasshopper
Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)

Group: General Forum Members
Points: 15 Visits: 42
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
Adam Bean
Adam Bean
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1215 Visits: 2160
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()



---
SQLSlayer
Making SQL do what we want it to do.
littlesquall
littlesquall
Grasshopper
Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)

Group: General Forum Members
Points: 15 Visits: 42
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
Adam Bean
Adam Bean
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1215 Visits: 2160
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.

---
SQLSlayer
Making SQL do what we want it to do.
littlesquall
littlesquall
Grasshopper
Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)

Group: General Forum Members
Points: 15 Visits: 42
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...
Adam Bean
Adam Bean
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1215 Visits: 2160
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



---
SQLSlayer
Making SQL do what we want it to do.
littlesquall
littlesquall
Grasshopper
Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)

Group: General Forum Members
Points: 15 Visits: 42
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
Wink
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search