Does number of indexes affect log space required for REINDEX ?

Question

Does number of indexes affect log space required for REINDEX ?

Joy Smith San

SSC-Insane

Points: 24877
More actions
October 16, 2014 at 1:55 am

#300234

Hi All,
Does number of indexes affect log space required for REINDEX ?
Thanks in advance.
Smith.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Grant Fritchey SSC Guru Points: 398690 More actions · Answer 1

The size of the indexes is much more of a question than the number of them.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Joy Smith San SSC-Insane Points: 24877 More actions · Answer 2

Grant Fritchey (10/16/2014)
The size of the indexes is much more of a question than the number of them.

Thank you.

So if I have multiple unwanted indexes, obviously it will be using more log space during rebuild is it ?

SQLRNNR SSC Guru Points: 281334 More actions · Answer 3

Joy Smith San (10/16/2014)
Grant Fritchey (10/16/2014)
The size of the indexes is much more of a question than the number of them.
Thank you.
So if I have multiple unwanted indexes, obviously it will be using more log space during rebuild is it ?

That depends. Are you rebuilding those unwanted indexes? Are you rebuilding all of your indexes? Or are you using a defrag process that is more intelligent and only targets the indexes that need to be rebuilt?

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

Joy Smith San SSC-Insane Points: 24877 More actions · Answer 4

That depends. Are you rebuilding those unwanted indexes? Are you rebuilding all of your indexes? Or are you using a defrag process that is more intelligent and only targets the indexes that need to be rebuilt?

Yes, they rebuild all indexes once in a week.

So if we rebuild only those indexes which are required, will it be using less log space ?

Thanks.

Igor Micev SSC-Dedicated Points: 33109 More actions · Answer 5

What is the recovery model of the database for which indexes are rebuilt?

Log space depends a lot on it, when in question is the recovery model.

See http://www.sqlservercentral.com/articles/Administration/64582/ and http://technet.microsoft.com/en-us/library/ms191244(v=sql.105).aspx

Igor Micev,My blog: www.igormicev.com

Grant Fritchey SSC Guru Points: 398690 More actions · Answer 6

Joy Smith San (10/16/2014)
Grant Fritchey (10/16/2014)
The size of the indexes is much more of a question than the number of them.
Thank you.
So if I have multiple unwanted indexes, obviously it will be using more log space during rebuild is it ?

Think of it like this, if you have 5 indexes that are each 1gb in size, they will all use about the same amount of log space. But, if you have one index that is 5gb in size, it will use a lot more log space when being rebuilt. The size of the index matters more than the number of indexes. That said, I'd suggest looking up Olla Hollengren's scripts and only rebuilding indexes that actually need it.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

SQLRNNR SSC Guru Points: 281334 More actions · Answer 7

Joy Smith San (10/17/2014)
That depends. Are you rebuilding those unwanted indexes? Are you rebuilding all of your indexes? Or are you using a defrag process that is more intelligent and only targets the indexes that need to be rebuilt?
Yes, they rebuild all indexes once in a week.
So if we rebuild only those indexes which are required, will it be using less log space ?
Thanks.

Building on what Grant said, if you have a 5GB index that you are rebuilding you will need 5GB. But if you don't need to rebuild that index then you won't need that space in the transaction log.

If you need something that helps with intelligent defrags, the best script out there is by SQLFool (Michelle Ufford). It's a starting point since any routine should be relevant to the environment and you should build the routine to suite the environment.

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