Selective Index Maintenance

Question

Selective Index Maintenance

Viewing 10 posts - 16 through 24 (of 24 total)

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

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 1

1) if you check the code for sp_updatestats I think you will find that it isn't so selective as you imagine. It updates anything with 1 or more rows modified (@ind_rowmodctr <> 0)

2) why are you updating stats in model, tempdb, master and msdb? I could maybe see some msdb tables in some scenarios.

3) reorganize can be a very important tool to use for index maintenance in numerous situations.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

Gail Wanabee SSCrazy Eights Points: 8471 More actions · Answer 2

TheSQLGuru (11/5/2010)
1) if you check the code for sp_updatestats I think you will find that it isn't so selective as you imagine. It updates anything with 1 or more rows modified (@ind_rowmodctr <> 0)
2) why are you updating stats in model, tempdb, master and msdb? I could maybe see some msdb tables in some scenarios.
3) reorganize can be a very important tool to use for index maintenance in numerous situations.

1) Thanks for the heads up. My experience with its execution times is that it is highly selective on our servers against our databases. This statement includes execution against 2 large databases, a 1.7 TB database and a .7 TB database.

2) Just a catch all, since the execution times for sp_updatestats are very fast on our server. It was easier to include them than to wonder if I should include them or research the subject.

3) Please elaborate on "reorganize". When would it be advisable to use reorganize rather than rebuild? Well, I can think of one: when you can't rebuild indexes online. However, from my research, reorganization of indexes is expensive and provides only marginal benefits.

LC

Steve Jones - SSC Editor SSC Guru Points: 736494 More actions · Answer 3

I believe the guidance that I saw from Paul Randal this week (Again, subject to testing), is 10% < fragmentation < 30% - Reorganize. > 30%, rebuild.

You know your system best, and perhaps reorg isn't worth it for you, but again, test and grab metrics, see if you can better tune things and use resources.

Gail Wanabee SSCrazy Eights Points: 8471 More actions · Answer 4

Steve Jones - SSC Editor (11/6/2010)
I believe the guidance that I saw from Paul Randal this week (Again, subject to testing), is 10% < fragmentation < 30% - Reorganize. > 30%, rebuild.
You know your system best, and perhaps reorg isn't worth it for you, but again, test and grab metrics, see if you can better tune things and use resources.

Steve, I've read Paul Rand's writings on this subject extensively. That was my starting point.

What has prevented me from using index reorganization was an in-depth Microsoft white paper on an extensive set of tests conducted by Microsoft on index reorganizing and index rebuilding. The tests on index reorganization were not encouraging. Index reorganization typically takes 8 times longer to execute that a comparable index rebuild operation. Index pages that are at-that-moment currently locked, will not be reorganized. And, data statistics are not updated with reorganization as they are with rebuilding.

After carefully studying the Microsoft white paper, and then reviewing it again, I couldn't justify using index reorganization. It produced too little benefit for the costs it incurred.

I appreciate your input.

Sincerely,

LC

P.S. Maybe it will be informative to put what I'm saying in context. My employer's servers are 7x24, 365 days of the year servers, which, unfortunately combine our OLTP environment with our reporting environment, not a best practice. We have no optimum time for system maintenance since our customer base is worldwide. With the exception of a once per month reboot to facilitate installation of Microsoft Updates, we have no optimum time to perform maintenance. Because of this, the servers we chose, especially our main database server, are very powerful machines. I've seen periods where we have hour-after-hour processed over a million transactions per hour on that server; that statistic does not include the simultaneous reports being executed. All database maintenance has to be executed within the context of this application environment. We can't afford the overhead and delays associated with index reorganization. We have SQL Server Enterprise Edition licenses so we can rebuild our indexes online. I created an Administration database that I use to track certain metrics associated with important database maintenance operations like index maintenance. These statistics include the elapsed time to perform these operations, among other things. Our index rebuild operations are generally very quick, just a few seconds. The exceptions are generally those indexes that exceed 250,000 pages. Still, the server nor the clients suffer during these index rebuilding operations. In fact, the script that analyzes current fragmentation levels of the indexes on all of the databases produces a much bigger drain on server metrics (like Page Life Expectancy) than the actual index rebuilding operations.

Steve Jones - SSC Editor SSC Guru Points: 736494 More actions · Answer 5

Sounds like maybe we should be reading your writings 😉

What Paul's written is just guidance,and more and more, I think you've got things under control, and likely know a bit more than most of us on what works/doesn't in your environment.

If you want more tuning, I'd suggest that you think about engaging Paul/Kim for a day if possible and see what they think, or if you qualify, check out the SQL CAT team and ask them for help.

Simon Facer SSCertifiable Points: 5427 More actions · Answer 6

crainlee2 (11/7/2010)
After carefully studying the Microsoft white paper ...

Do you have the link to that White Paper? I'd like to see that.

Thanks.

Gail Wanabee SSCrazy Eights Points: 8471 More actions · Answer 7

I'm sure I do. I always print hard copies of really important material.

I'll look in my "piling" system at work and get back to you.

LC

homebrew01 SSC Guru Points: 55548 More actions · Answer 8

I splice frequent t-log backups into my defragmentation routines to prevent large .trn file growth.

Gail Wanabee SSCrazy Eights Points: 8471 More actions · Answer 9

Simon Facer (11/8/2010)
crainlee2 (11/7/2010)
After carefully studying the Microsoft white paper ...
Do you have the link to that White Paper? I'd like to see that.
Thanks.

Simon,

Here's the link to the Microsoft Technet article that I referenced: http://technet.microsoft.com/en-us/library/cc966523.aspx

Although it was originally focused on SS2000, through experimentation I've found that the results and recommendations in it are still valid.

LC

Simon Facer SSCertifiable Points: 5427 More actions · Answer 10

crainlee2 (11/9/2010)
Simon,
Here's the link to the Microsoft Technet article that I referenced: http://technet.microsoft.com/en-us/library/cc966523.aspx
Although it was originally focused on SS2000, through experimentation I've found that the results and recommendations in it are still valid.
LC

Thanks.