August 25, 2016 at 9:01 pm
Used this script below to get index fragmentation:
SELECT OBJECT_NAME(ind.OBJECT_ID) AS TableName,
ind.name AS IndexName, indexstats.index_type_desc AS IndexType,
indexstats.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) indexstats
INNER JOIN sys.indexes ind
ON ind.object_id = indexstats.object_id
AND ind.index_id = indexstats.index_id
WHERE indexstats.avg_fragmentation_in_percent > 30--You can specify the percent as you want
ORDER BY indexstats.avg_fragmentation_in_percent DESC
Rebuild indexes for that database using maintenance plan but still few tables indexstats.avg_fragmentation_in_percent is more than 30%
Performed dbcc showcontig: Should I not be concerned with fragmentation level of index because pages scanned is less than 1000 pages?
TABLE level scan performed.
- Pages Scanned................................: 1
- Extents Scanned..............................: 1
- Extent Switches..............................: 0
- Avg. Pages per Extent........................: 1.0
- Scan Density [Best Count:Actual Count].......: 100.00% [1:1]
- Logical Scan Fragmentation ..................: 0.00%
- Extent Scan Fragmentation ...................: 0.00%
- Avg. Bytes Free per Page.....................: 7159.0
- Avg. Page Density (full).....................: 11.55%
August 26, 2016 at 6:42 am
The 1000 pages is just a guideline. If the number of pages is less than an extent (8 pages), it can be completely ignored, regardless of the level of fragmentation. After that, it completely depends on the table, the data and the queries against it. I've seen queries with 100 pages which were supporting lots of scans need to be defragmented frequently. I've seen tables with well over 1000 pages that were only ever doing point lookups (single row or very limited ranges) not even need to be fragmented at all. There is no hard and fast rule on the number of pages.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
August 26, 2016 at 6:56 am
PJ_SQL (8/25/2016)
TABLE level scan performed.- Pages Scanned................................: 1
- Extents Scanned..............................: 1
- Extent Switches..............................: 0
- Avg. Pages per Extent........................: 1.0
- Scan Density [Best Count:Actual Count].......: 100.00% [1:1]
- Logical Scan Fragmentation ..................: 0.00%
- Extent Scan Fragmentation ...................: 0.00%
- Avg. Bytes Free per Page.....................: 7159.0
- Avg. Page Density (full).....................: 11.55%
That index has a single page, and by the looks of things only a couple of rows. You can't defragment a 1-page table.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply