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

Index Rebuilding Expand / Collapse
Author
Message
Posted Friday, October 29, 2010 8:32 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, March 19, 2014 6:30 AM
Points: 250, Visits: 743
Dear All,

Can someone please advise on my question.

I ran a Index rebuilding query on one table and it's taking more than 9 hrs...please advise what is the issue behind this and if we stop it in middle while it's running, what will happen in next time i.e, when we run a query ,will it starts from scratch or from last ?

Thanks and Regards,
Ravichandra.
Post #1013102
Posted Friday, October 29, 2010 9:06 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 2:35 AM
Points: 5,014, Visits: 10,517
If the rebuild process takes 9 hours, this means it needs 9 hours.
If you stop it, it will start from scratch.

I suggest that you start using a different optimisation strategy, for instance, rebuilding only the indexes that really need rebuilding.
Check the IndexOptimisation.sql at http://ola.hallengren.com/


--
Gianluca Sartori

Get your two-cent-answer quickly
spaghettidba.com
@spaghettidba
Post #1013140
Posted Monday, November 1, 2010 2:58 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, March 19, 2014 6:30 AM
Points: 250, Visits: 743
Dear Gianluca Sartori,

Thanks for the reply.

The link which you refered to me is very useful and also it's nice script.

When Iam trying to get the avg_fragmentation_in_percent by using the below query it's taking very huge time to provide result.

SELECT OBJECT_NAME(i.OBJECT_ID) AS TableName,
i.name AS IndexName,
indexstats.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'DETAILED') indexstats
INNER JOIN sys.indexes i ON i.OBJECT_ID = indexstats.OBJECT_ID
AND i.index_id = indexstats.index_id
WHERE indexstats.avg_fragmentation_in_percent > 30

Please advise whether there is any query to get quick output.

Thanks and Regards,
Ravichandra.


Post #1013709
Posted Monday, November 1, 2010 3:14 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 3:22 PM
Points: 42,484, Visits: 35,553
Change DETAILED to LIMITED

If both that and the rebuild are taking forever either the table is huge or your IO subsystem is not up to the load, or both.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1013711
Posted Monday, November 1, 2010 5:47 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, March 19, 2014 6:30 AM
Points: 250, Visits: 743
Thanks GailShaw.

Yes, you are right, the table size is 30 GB & indexes size on that table 35 GB.

I have one more doubt.

Why data(.mdf) & Log(.ldf) files sizes will be increased after index rebuilt?
Please clarify me.

Regards,
Ravichandra.
Post #1013760
Posted Monday, November 1, 2010 6:02 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 3:22 PM
Points: 42,484, Visits: 35,553
Because SQL needs somewhere to put the new index and because index rebuilds are fully logged in full recovery.


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1013765
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse