Reindexing

  • 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?

  • 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.

  • 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