Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Indexes rebuilding problem Expand / Collapse
Author
Message
Posted Friday, March 21, 2008 9:48 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, August 21, 2008 7:37 AM
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 ) 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.
Post #472922
Posted Friday, March 21, 2008 9:52 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 7:04 AM
Points: 1,163, Visits: 1,969
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.

Post #472928
Posted Friday, March 21, 2008 10:02 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, August 21, 2008 7:37 AM
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
Post #472935
Posted Friday, March 21, 2008 10:13 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 7:04 AM
Points: 1,163, Visits: 1,969
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.

Post #472949
Posted Friday, March 21, 2008 10:24 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, August 21, 2008 7:37 AM
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)'

...
Post #472963
Posted Friday, March 21, 2008 10:31 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 7:04 AM
Points: 1,163, Visits: 1,969
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.

Post #472970
Posted Friday, March 21, 2008 10:35 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, August 21, 2008 7:37 AM
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...
Post #472973
Posted Friday, March 21, 2008 10:43 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 7:04 AM
Points: 1,163, Visits: 1,969
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.

Post #472976
Posted Tuesday, March 25, 2008 4:38 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, August 21, 2008 7:37 AM
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
;)
Post #473927
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse