0

I have a nightly SSIS package that reorganizes indexes that are between 10% and 40% and then a weekly package that rebuilds for anything over 40%. But Ive noticed a lot of table do not change. Example below my Location table below is just about always at 82%. Am I doing something wrong?

object_id index_id avg_fragmentation_in_percent page_count
1801213617 0 82.9268292682927 2718
1801213617 2 0 369
1801213617 3 0 250

2
Contributors
1
Reply
2
Views
5 Years
Discussion Span
Last Post by cutepinkbunnies
0

object_id index_id avg_fragmentation_in_percent page_count
> 1801213617 0 82.9268292682927 2718
1801213617 2 0 369
1801213617 3 0 250

The bold record is a heap. (table without a clustered index, which can't be organized. The way you can tell it is a heap is from the index_id column:
Index_id of 0 = heap
Index_id of 1 = clustered index
Index_id >= 2 = nonclustered index

Add a clustered index to it to help your query performance and your defrag red flags.

Here's an article on indexing.
http://sqlserverplanet.com/indexes/sql-server-indexes-tutorial/

Here's an article discussing heaps vs tables with clustered indexes.
http://www.mssqltips.com/sqlservertip/1254/clustered-tables-vs-heap-tables/

Let us know how it works out!

Edited by cutepinkbunnies

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.