Ola Index Maintenance won't work on msdb

Question

Ola Index Maintenance won't work on msdb

Viewing 3 posts - 16 through 17 (of 17 total)

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

Michael L John One Orange Chip Points: 27055 More actions · Answer 1

andreas.kreuzberg wrote:

I read a lot about index maintenance, but I think, I need a second life to understand everything.

Andreas, we had this revelation about 5 years ago. Jeff, Ed, and myself saw a post from Bret Ozar that stated it's probably less intensive for SQL to scan extra data pages than it is to reindex, figure out fill factor, and then deal with the inevitable page splits. We all dug in, and of course Jeff really dug in. Which became his Black Arts Index Maintenance.

We were all following these "best practices". In my case, we had a 27/7 operation. Nightly, the folks in Asia were complaining because the maintenance consumed massive resources. Every morning, the USA users were complaining about performance. The number of support tickets for the database was 10-15 a week.

I dug into my systems. The pattern was reindex every night, which took 8-10 hours. Once complete, the page splits started. The only time when the system was "clean" was when usage was light. This was a very busy set of databases that supported multiple web apps. Peak time, there where 5k-7k users in the system, off peak, there were 1500 to 2000 users. As well as reporting and ETL processes.

I took a leap of faith,. and stopped reindexing. I modified my update stats routine, and only updated individual stats that had enough changes. The nightly maintenance was reduced to 30-40 minutes for the backups, and less than an hour for updating stats. And, most importantly, the impact on the systems was negligible.

What ultimately occurred was that the amount of page splits went down to nearly nothing. Fill factor was no longer a concern, the system automatically created that with the fragmentation.

The biggest thing? The number of support tickets that were attributed to database issues went to ZERO over a three year period.

Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 2

Just posting so Michael's post above will show up. This site won't show a single post on page 2. I really wish they'd fix that. 🙁

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

homebrew01 SSC Guru Points: 55548 More actions · Answer 3

Yes, Annoying. I reported it over a year ago.

"....Just posting so Michael's post above will show up. This site won't show a single post on page 2. I really wish they'd fix that...."

This reply was modified 1 year, 3 months ago by homebrew01.
This reply was modified 1 year, 3 months ago by homebrew01.