December 17, 2009 at 1:53 pm
One of many of our servers is running SQL 2005 Std Ed. and is emulating SQL 2000 for several databases . The situation raised when one of the user notice that the application using one of the databases emulating 2000 to begging to degrade performance wise. After analyzing the situation, we (DBA's) saw that the fragmentation of the indexes was high for almost all the indexes of that database. But we where confuse since the rebuild maintenance job ran every week successfully. We ran the maintenance plan for the rebuild of the indexes, but saw no improvement. After several tries we ended up running the old command "DBCC Rebuild" for each index and found it to work just perfectly.
After revising the other servers with the same scenario we found out that it was happening the same to the others. We are currently working on a script that will consider the DB version for then execute the correct command for each version.
December 17, 2009 at 4:07 pm
Were you getting any error messages for the maintplan?
From Technet:
The Rebuild Index Task uses the ALTER INDEX statement when connected to SQL Server 2005, and the DBCC DBREINDEX statement when connected to SQL Server 2000.
What options did you use to setup the maint plan? Were you using any custom scripting in the maint-plan?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
December 18, 2009 at 4:43 am
tinso-728770 (12/17/2009)
One of many of our servers is running SQL 2005 Std Ed. and is emulating SQL 2000 for several databases . The situation raised when one of the user notice that the application using one of the databases emulating 2000 to begging to degrade performance wise. After analyzing the situation, we (DBA's) saw that the fragmentation of the indexes was high for almost all the indexes of that database. But we where confuse since the rebuild maintenance job ran every week successfully. We ran the maintenance plan for the rebuild of the indexes, but saw no improvement. After several tries we ended up running the old command "DBCC Rebuild" for each index and found it to work just perfectly.After revising the other servers with the same scenario we found out that it was happening the same to the others. We are currently working on a script that will consider the DB version for then execute the correct command for each version.
What's the question?
December 18, 2009 at 5:41 am
The maintenance plan didn't throw any errors. We had the default settings in the maintenance plan for the rebuild.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply