Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Reindexing Expand / Collapse
Author
Message
Posted Wednesday, September 16, 2009 10:38 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, August 3, 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
Posted Thursday, September 17, 2009 4:07 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, August 12, 2014 2:52 AM
Points: 363, Visits: 1,318
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
Posted Friday, September 18, 2009 3:52 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 8:15 AM
Points: 3,214, Visits: 2,325
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 »

Add to briefcase

Permissions Expand / Collapse