June 4, 2004 at 5:01 am
Is scan density a reliable measurement of fragmentation? I read in Kalen Delaney's Inside SQL Server (Indexes chapter) that it is not vaild for tables spanning multiple files (which I have also read is recommended if you have more than one processor).
Also is there any reasoning behind the choice of the number of rows a table should have above which index fragmentation is significant?
June 4, 2004 at 6:44 am
I can honestly say that I have never read Inside SQL Server so I can't really comment as to scan density being a reliable measurement of fragmentation for tables spanning multiple files. I have also never split a table across multiple filegroups, database yes, table no. Perhaps some other members have experience with that.
Certainly, however, I have found that for a table on a single filegroup that the scan density is the most efficient way of measuring fragmentation.
The number of rows I used in the example is based upon my experience at my last two companies. I've found that below a certain threshold the fragmentation occurs, however it is far less likely to have an impact upon performance. That's an option that can be removed, I've used it mostly to prevent overkill.
June 7, 2004 at 2:09 pm
Displays fragmentation information for the data and indexes of the specified table.
DBCC SHOWCONTIG
[ ( { table_name | table_id | view_name | view_id }
[ , index_name | index_id ]
)
]
[ WITH { ALL_INDEXES
| FAST [ , ALL_INDEXES ]
| TABLERESULTS [ , { ALL_INDEXES } ]
[ , { FAST | ALL_LEVELS } ]
}
]
June 7, 2004 at 3:49 pm
I am sorry to change the subject, but how do you span a table across multiple file/filegroups? Thanks in advance.
June 7, 2004 at 8:04 pm
in SQL Server, a named collection of one or more files that forms a single unit of allocation or for administration of a database.
A file cannot be a member of more than one filegroup. Tables, indexes, and text, ntext, and image data can be associated with a specific filegroup. This means that all their pages are allocated from the files in that filegroup.
The fragmentation level of an index can be determined in two ways:
Note: This method of determining fragmentation does not work if the index spans multiple files. The value of Extent Switches should be as close as possible to that of Extents Scanned. This ratio is calculated as the Scan Density value, which should be as high as possible. This can be improved by either method of reducing fragmentation discussed earlier.
Logical Scan Fragmentation and, to a lesser extent, Extent Scan Fragmentation values give the best indication of a table's fragmentation level. Both these values should be as close to zero as possible (although a value from 0% through 10% may be acceptable). It should be noted that the Extent Scan Fragmentation value will be high if the index spans multiple files. Both methods of reducing fragmentation can be used to reduce these values.
I think these points from BOL would clarify.
June 8, 2004 at 9:13 am
Here's a quick blurb about Particitioned Views. It's one way to span a table, actually multiple tables each containing part of the whole logical table, across filegroups.
Lookup the following in BOL.
A partitioned view joins horizontally partitioned data from a set of member tables across one or more servers, making the data appear as if from one table.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply