http://www.sqlmag.com/authors/author/author/5091269/rsscomment/5091269en-USFri, 25 May 2012 10:48:26 GMTFri, 25 May 2012 10:48:26 GMThttp://www.sqlmag.com/article/tsql3/three-in-one-database-maintenance-solution#commentsAnchorMon, 02 Feb 2009 11:36:00 GMT
I work on a moderate size DB. • 12 million inserts and 10 million updates in a 12 hour workday. • 10 million deletes and Index maintenance at night. Our largest table ( 660,000,000 rows) • 8.5 million inserts • 8 million updates • 7.5 million deletes daily. • Organized by time. • Fragment about 2% daily. We were running an index maintenance regime like you propose. On the largest table, ran a index defrag each night, taking 6 hours. One day we ‘accidently’ did a offline rebuild. Took 30 minutes. Sweet. Funny thing all our queries got faster. I mean noticeable faster. We realized it was because the defrag only worked on leaf nodes. Over a period of two years the BTree index nodes got horrible unbalanced. Insert, update and select activity was occurring at the most recent entries and delete and the lest recent entries. These pages became 50% fragmented due to page splits, while daily table fragmentation was around 2%. We kept reducing the point at which we did a rebuild, till we found the point at which it made no difference in performance. 4%. We could have done the rebuild every three days, but decided to do it daily. If it failed for any reason, we had few days to fix it before customers noticed. A nice side effect is they stopped worrying when I’d want to take a few days off (I’m the only full time DBA here)]]>BRIANMon, 02 Feb 2009 11:36:00 GMThttp://www.sqlmag.com/article/tsql3/three-in-one-database-maintenance-solution#commentsAnchorhttp://www.sqlmag.com/article/tsql3/three-in-one-database-maintenance-solution#commentsAnchorFri, 19 Dec 2008 12:17:46 GMT
I’m glad that you found the article useful. I’ll pass along your comment to Ola. I’m sure he’ll appreciate it! Karen Bemowski, senior editor SQL Server Magazine, Windows IT Pro]]>KarenFri, 19 Dec 2008 12:17:46 GMThttp://www.sqlmag.com/article/tsql3/three-in-one-database-maintenance-solution#commentsAnchorhttp://www.sqlmag.com/article/tsql3/three-in-one-database-maintenance-solution#commentsAnchorThu, 18 Dec 2008 15:27:41 GMT
probably learned more by reading this article than all the Microsoft help files on the subject, although I was glad I read them first - it gave me some background - but this really put things into my hands! Thanks! Very useful tool.]]>AlanThu, 18 Dec 2008 15:27:41 GMThttp://www.sqlmag.com/article/tsql3/three-in-one-database-maintenance-solution#commentsAnchor