Reindexing

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » SQL Server 2005 General Discussion » Reindexing

Reindexing

Rate Topic

Display Mode

Topic Options

Author

Message

frank.pron

Posted Wednesday, September 16, 2009 10:38 AM

Forum Newbie

Group: General Forum Members
Last Login: Friday, August 03, 2012 11:45 AM
Points: 5, Visits: 49

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?

Post #789128

dmoldovan

Posted Thursday, September 17, 2009 4:07 AM

Old Hand

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 5:51 AM
Points: 360, Visits: 1,260

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.

Post #789512

rudy - Doctor "X"

Posted Friday, September 18, 2009 3:52 PM

Hall of Fame

Group: General Forum Members
Last Login: Friday, May 17, 2013 2:11 PM
Points: 3,108, Visits: 2,114

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

Regards
Rudy Komacsar
Senior Database Administrator

"Ave Caesar! - Morituri te salutamus."

Post #790719

« Prev Topic | Next Topic »

Permissions