How to check what tables are fragmented in SQL

Run the following Query
DBCC SHOWCONTIG WITHFAST

You will get output similar to the below.

DBCC SHOWCONTIG scanning ‘PerformanceData_14’ table…

Table: ‘PerformanceData_14’ (564197060); index ID: 1, database ID: 5

TABLE level scan performed.

– Pages Scanned…………………………..: 9504

– Extent Switches…………………………: 5551

– Scan Density [Best Count:Actual Count]…….: 21.40% [1188:5552]

– Logical Scan Fragmentation ………………: 84.70%

The “Scan density” ideally needs to be high (Above 80%) and the “Logical Scan Fragmentation” to be low (below 30%).

http://www.dotnetjohn.com/articles.aspx?articleid=172

Running the query on a table named provider:
  DBCC SHOWCONTIG (provider) WITH ALL_INDEXES

DBCC SHOWCONTIG scanning ‘provider’ table…
Table: ‘provider’ (850102069); index ID: 2, database ID: 14
LEAF level scan performed.
– Pages Scanned…………………………..: 3
– Extents Scanned…………………………: 1
– Extent Switches…………………………: 0
– Avg. Pages per Extent……………………: 3.0
– Scan Density [Best Count:Actual Count]…….: 100.00% [1:1]
– Logical Scan Fragmentation ………………: 33.33%
– Extent Scan Fragmentation ……………….: 0.00%
– Avg. Bytes Free per Page…………………: 5596.0
– Avg. Page Density (full)…………………: 30.86%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

There are a few specific things to take note of and will help determine if index pages are full or if they are heavily fragmented.

The fullness of the index pages can be determined by reading the “Avg. Bytes free per page” and “Avg. Page density (full)” statistics. The “Avg. Bytes free per page” figure should be low and the “Avg. Page density (full)” figure should be high. You’ll notice that both tables likely have very full pages.

The fragmentation level of an index can be determined by comparing the values of “Extent Switches” and “Extents Scanned” and having a clear understanding “Logical Scan Fragmentation” and “Extent Scan Fragmentation” values. The “Extent Switches” should be almost equal to “Extents Scanned.” Based on the examples above, this is the way it should look. “Logical Scan Fragmentation” and “Extent Scan Fragmentation” values give a good indication of a table’s fragmentation level. These values should be as close to zero as possible (10% may be acceptable). The ‘member’ table is highly fragmented and the provider table is slightly fragmented based on the numbers above.

These issues can be corrected by dropping and recreating a clustered index with the FILLFACTOR option specified. Also, the DBCC INDEXDEFRAG command will compact an index, taking into account its FILLFACTOR, which will improve the statistics.

After running the queries below:
  DBCC DBREINDEX (member, ”, 80)
  DBCC DBREINDEX (provider, ”, 80)

Running the queries:
  DBCC SHOWCONTIG (member) WITH ALL_INDEXES
  DBCC SHOWCONTIG (provider) WITH ALL_INDEXES

DBCC SHOWCONTIG scanning ‘member’ table…
Table: ‘member’ (786101841); index ID: 2, database ID: 14
LEAF level scan performed.
– Pages Scanned…………………………..: 41
– Extents Scanned…………………………: 6
– Extent Switches…………………………: 5
– Avg. Pages per Extent……………………: 6.8
– Scan Density [Best Count:Actual Count]…….: 100.00% [6:6]
– Logical Scan Fragmentation ………………: 0.00%
– Extent Scan Fragmentation ……………….: 0.00%
– Avg. Bytes Free per Page…………………: 1657.0
– Avg. Page Density (full)…………………: 79.53%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

DBCC SHOWCONTIG scanning ‘provider’ table…
Table: ‘provider’ (850102069); index ID: 2, database ID: 14
LEAF level scan performed.
– Pages Scanned…………………………..: 2
– Extents Scanned…………………………: 2
– Extent Switches…………………………: 1
– Avg. Pages per Extent……………………: 1.0
– Scan Density [Best Count:Actual Count]…….: 50.00% [1:2]
– Logical Scan Fragmentation ………………: 0.00%
– Extent Scan Fragmentation ……………….: 0.00%
– Avg. Bytes Free per Page…………………: 4346.0
– Avg. Page Density (full)…………………: 46.31%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

As a result of running the DBCC SHOWCONTIG and DBCC INDEXDEFRAG commands, we were able to diagnose and greatly reduce fragmentation on the ‘member’ and ‘provider’ tables. The member table is almost perfect and the ‘provider’ table shows great improvement. This will result in an extraordinary performance increase on queries that are run against these tables.

For running SQL 2000 table fragmentation command DBCC INDEXDEFRAG: In SQL Server 2000, Microsoft introduced DBCC INDEXDEFRAG to help reduce logical disk fragmentation. When this command runs, it reduces fragmentation and does not lock tables, allowing users to access the table when the defragmentation process is running. Unfortunately, this command doesn’t do a great job of logical defragmentation. The only way to truly reduce logical fragmentation is to rebuild your table’s indexes. While this will remove all fragmentation, unfortunately it will lock the table, preventing users from accessing it during this process. This means that you will need to find a time when this will not present a problem to your users. Of course, if you are unable to find a time to reindex your indexes, then running DBCC INDEXDEFRAG is better than doing nothing. Example: DBCC INDEXDEFRAG (Database_Name, Table_Name, Index_Name)

Advertisements
This entry was posted in SQL. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s