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

Stairway to SQL Server Indexes: Step 11, Index Fragmentation Expand / Collapse
Author
Message
Posted Wednesday, June 1, 2011 2:49 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Saturday, June 23, 2012 10:54 AM
Points: 8, Visits: 23
Comments posted to this topic are about the item Stairway to SQL Server Indexes: Step 11, Index Fragmentation
Post #1118408
Posted Friday, July 1, 2011 4:04 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, July 22, 2014 2:36 PM
Points: 3, Visits: 89
In an example #2 to accomodate one month of inserts into 12 months table, how do we figure out that fillfactor might be 80-84%? We insert 100/13= 8% of the data, so we would reserve 8% of the space.


Post #1135412
Posted Wednesday, February 1, 2012 2:13 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, July 24, 2014 8:41 AM
Points: 861, Visits: 2,357
No offense intended, but when I refer to "external fragmentation", I refer to fragmentation outside of SQL Server's control; even if SQL Server reports page 1's next page is 2, whose next page is 3, at the OS level, perhaps they're on three completely different areas of the disk. Perhaps even the OS level is contiguous, but the SAN (Meta)LUN actually has each on noncontiguous areas of the platters.

In either case, the head has to move more than it should to read three pages.
Post #1245333
Posted Thursday, February 9, 2012 10:04 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, March 4, 2014 8:41 AM
Points: 48, Visits: 266
What would a treshold (in the number of index pages), below which we should not worry about fragmentation and don't have to rebuild?
Post #1249825
Posted Thursday, February 9, 2012 2:06 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, March 4, 2014 8:41 AM
Points: 48, Visits: 266
?
Post #1249956
Posted Tuesday, November 26, 2013 11:40 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, July 3, 2014 4:00 PM
Points: 2,038, Visits: 1,663
I wish people would stop perpetuating the phrases 'external fragmentation' and 'internal fragmentation' and use the phrases that the SQL team, the tools, and Books Online uses: logical fragmentation and low page density.

None of the tools report 'external' or 'internal' fragmentation and haven't done since at last SQL Server 7.0/2000 with my old DBCC SHOWCONTIG.

/rant.


Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005
Post #1517911
Posted Wednesday, December 4, 2013 10:00 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, July 24, 2014 8:41 AM
Points: 861, Visits: 2,357
Paul Randal (11/26/2013)
I wish people would stop perpetuating the phrases 'external fragmentation' and 'internal fragmentation' and use the phrases that the SQL team, the tools, and Books Online uses: logical fragmentation and low page density.

None of the tools report 'external' or 'internal' fragmentation and haven't done since at last SQL Server 7.0/2000 with my old DBCC SHOWCONTIG.

/rant.


I completely agree that "internal" and "external" fragmentation are useless phrases, particularly when you also start talking at a filesystem level or beyond. Perhaps we can come up with a descriptive list of various kinds of fragmentation, and then assign short names?

I'll start, and include some perhaps theoretical types. Paul, if you think this is of any value, I would more than welcome your corrections and input - I'm leaving the SQL types at the very bottom fairly empty of detail, for real experts to fill in.

****** The first set are rare, interesting, and/or often hidden types requiring less common techniques or tools to detect and/or correct - skip to the next set of asterisks for the more common types ******

"short name" - Block level fragmentation - a logical storage object comprised of noncontiguous physical blocks that could be more contiguous than they are. SSD's almost always have this. Spindle disks that have done bad sector remapping may have this.

"short name" - SAN/Host MetaLUN/virtual disk level fragmentation - a MetaLUN/virtual disk comprised of noncontiguous LUN/virtual disks that could be more contiguous than they are.

"short name" - SAN/Host LUN/virtual disk level fragmentation - a LUN/virtual disk comprised of sections of spindle/SSD space that could be more contiguous than they are. Also includes JBOD (Just a Bunch of Disks) where a partition could be more contiguous than it is. Thin provisioned virtual disks usually end up with this (for Hyper-V, the guest sees it as SAN/Host level, while the host sees it as Filesystem level fragmentation, unless you have nested hosts, in which case Google "recursion")

"short name" - Partition level fragmentation - a partition comprised of sections of LUN/spindle/SSD space that could be more contiguous than they are. Windows Dynamic disk volumes can have this level, for instance if they end up with noncontiguous or out of order extents (i.e. extents 1, 2, 3, 4, 5 and D: has extents 3,1,4 while E: has extents 2, 5)

****** Below this line are common types a systems DBA deals with ******

"short name" - Filesystem drive letter/mount point level fragmentation - A drive letter/mount point containing of multiple files that get accessed at overlapping times. For instance, a SQL Server filegroup with more than one file per drive letter/mount point, such that when SQL accesses the filegroup, reads and writes must skip back and forth between files - thus, if the drive letter/mount point represents, say, a single spindle, the drive head has to move from File A to File B and back to File A during the filegroup access. This is also caused by putting both the data and the log files of a READ_WRITE database on the same drive letter/mount point. This is reduced by having one file per drive letter/mount point that is accessed at a time.

"short name" - Filesystem File level fragmentation - one or more files comprised of sectors that could be more contiguous than they are. Typically can be reduced by filesystem level defragmentation tools (Piriform Defraggler, Windows Defrag, etc.) or backing up the file(s), deleting as much as possible including the file(s) affected, then recopying the file(s) back, single-threaded, with a tool that pre-allocated space as much as possible for the filesystem involved. SQL Server data and log files which grow are often subject to this, as they are allocated space from effectively arbitrary locations on the filesystem.

"SQL Server low page density" - pages are could be more full than they are. Typically can be reduced by index REBUILD (where it is controlled by FILLFACTOR and PAD_INDEX settings).

"SQL Server logical fragmentation" - pages and/or extents are out of order. Reduced by index REBUILD or REORGANIZE.



Post #1519703
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse