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
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!
We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.