September 16, 2009 at 10:38 am
I ran a script to reindex all the tables in my database such as sp_MSforeachtable"DBCC DBREINDEX("?")WITH NO_INFOMSGS' and I noticed that tables with less than 1000 pages were not touched. I understand that most re-indexing scripts don't touch tables with less than 1000 pages or extents with less than 8, however, a very high number of these specific tables are fragmented at 100%. I also understand that Microsoft supposedly uses index scan on these tables this small as opposed to index seeks (not sure if I have this correct), so therefore we won't see any significant improvements in performance by re-indexing? Some say tables under 500 or even 100 should not be touched? The question I have is 'how do I modify the script to hit tables with less than 500 pages?' and How can I determine what or which type of scan that is being used on these particular tables. I don't think I want a table with less than 1000 pages using seek as opposed to scan?
September 17, 2009 at 4:07 am
Indeed the "small" tables won't be "touched" - in most of the cases.
See http://sqlserverpedia.com/blog/sql-server-bloggers/index-fragmentation-findings-part-2-size-matters/ for some relevant numbers.
See also SQL 2005 BOL ---> Reorganizing and Rebuilding Indexes - there is a "note" regarding the small indexes.
September 18, 2009 at 3:52 pm
The solution is:
1) not to use maintenance plans
2) add FULLSCAN
3) write a better script (Hint: there are lots out in SSC)
Some best practices:
1) do not try to defragment heaps
2) do not defragment indexes with less than 8 pages (1 extent)
3) do defragment the index if it is more than 10% fragmented
4) do reindex if the index is more than 30% fragmented
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply