Table Fragmentation

Question

Table Fragmentation

yuvipoy

SSChampion

Points: 10469
More actions
April 21, 2014 at 10:57 pm

#306619

When i run the DBCC SHOWCONTIG() on my database it is displaying the attached list , is the tables got fragemented? if so how to resolve them?
most of my tables shows
- Scan Density [Best Count:Actual Count].......: 16.67% [1:6]
- Logical Scan Fragmentation ..................: 71.43%
- Extent Scan Fragmentation ...................: 80.00%
--------------------------------------------------------------------------------------------------
- Scan Density [Best Count:Actual Count].......: 13.85% [19211:138717]
- Logical Scan Fragmentation ..................: 88.27%
- Extent Scan Fragmentation ...................: 21.82%
--------------------------------------------------------------------------------------------------
- Scan Density [Best Count:Actual Count].......: 92.36% [1487:1610]
- Logical Scan Fragmentation ..................: 2.50%
- Extent Scan Fragmentation ...................: 98.41%

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Yogeshwar Phull Default port Points: 1434 More actions · Answer 1

I hope u r having SQL version > 2000. So forget about old ways of checking fragmentation and look for sys.dm_db_index_physical_stats for better results. Look for avg_fragmentation_in_percent and u need to worry about large sized tables only i.e. tables having large no. of records. This information is also there in the stated DMV.

Cheers!!

free_mascot One Orange Chip Points: 27169 More actions · Answer 2

You need to rebuild the indexes where the scan density is lower. Also do not bother if the table is small i.e. pages are less. Refer following link for further detail:

http://forums.databasejournal.com/showthread.php?9420-How-to-read-DBCC-SHOWCONTIG

---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."

Grant Fritchey SSC Guru Points: 398679 More actions · Answer 3

I'm with Yogeshwar Phull. Don't use show_contig any more. The dynamic management objects are so much better for this type of information.

"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

yuvipoy SSChampion Points: 10469 More actions · Answer 4

Actually i just moved my SQL server 2000 to SQL server 2008 and did not run any data. just migrated the SQL server 2000 to 2008, so thats why i used DBCC SHOWCONTIG()

what is the suggestion based on the above.

andrew gothard SSChampion Points: 12301 More actions · Answer 5

I'm with all of them, and further than that I'd just install Michelle 'SQLFool' Ufford's maintenance script, configure any elements specific to your environment and largely forget about it.

http://sqlfool.com/2011/06/index-defrag-script-v4-1/

I'm a DBA.
I'm not paid to solve problems. I'm paid to prevent them.

andrew gothard SSChampion Points: 12301 More actions · Answer 6

yuvipoy (4/22/2014)
Actually i just moved my SQL server 2000 to SQL server 2008.

In that case I'd run a full update statistics against all your databases if you've not already done so

I'm a DBA.
I'm not paid to solve problems. I'm paid to prevent them.

Grant Fritchey SSC Guru Points: 398679 More actions · Answer 7

andrew gothard (4/22/2014)

I'm with all of them, and further than that I'd just install Michelle 'SQLFool' Ufford's maintenance script, configure any elements specific to your environment and largely forget about it.

http://sqlfool.com/2011/06/index-defrag-script-v4-1/%5B/quote%5D

I concur. Nice set of scripts, easy to understand and implement.

"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

Grant Fritchey SSC Guru Points: 398679 More actions · Answer 8

yuvipoy (4/21/2014)
When i run the DBCC SHOWCONTIG() on my database it is displaying the attached list , is the tables got fragemented? if so how to resolve them?
most of my tables shows
- Scan Density [Best Count:Actual Count].......: 16.67% [1:6]
- Logical Scan Fragmentation ..................: 71.43%
- Extent Scan Fragmentation ...................: 80.00%
--------------------------------------------------------------------------------------------------
- Scan Density [Best Count:Actual Count].......: 13.85% [19211:138717]
- Logical Scan Fragmentation ..................: 88.27%
- Extent Scan Fragmentation ...................: 21.82%
--------------------------------------------------------------------------------------------------
- Scan Density [Best Count:Actual Count].......: 92.36% [1487:1610]
- Logical Scan Fragmentation ..................: 2.50%
- Extent Scan Fragmentation ...................: 98.41%

Depending on the number of pages, the first two show signs of fragmentation that could be cleaned up. But, I'd only do it if they were at least 100 pages in size. Other people suggest only doing this when greater than 1000 pages. Testing within your own environment will give you a better answer.

"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