Tables and Indexes Will Not Defrag

We have some databases under SQLExpress 2005. There are several tables that show defragmentation of over 60%. I doesn't matter if I reorganize or rebuild them, they show defragmentation of over 60%. Some will drop to around 40% if I rebuild them.
I've tried removing the tables and recreating them, removing the index and recreating it. No change.
What am I missing?
Bruce CarsonSystems AdministratorAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott PletcherSenior DBACommented:
It's extremely like those tables have fewer than 8 pages.  SQL stores such small tables in shared extents by default, where different tables can use the same extent.  You can probably safely ignore those tables.  Or, you could add dummy rows to the table to get it to 8 pages, then remove the dummy rows.  Once SQL goes to dedicated pages for a given table, it never goes back to shared extents, even if the table drops down to only the minimum 1 page.
Zac HarrisSystems Administrator Commented:
Give this a try and see if it helps you... It is set to 30% fragmentation but you can adjust it...
dsackerContract ERP Admin/ConsultantCommented:
It may be your physical database file that is fragmented.

Is your database on a single drive, a RAID array or a SAN?

If you are on SAN, you probably don't need to worry. Simply reorganize your clustered indexes. But that's probably not the case.

Perhaps you might find useful looking into Microsoft's contig utility. If Zac's solution doesn't help, I would back up your database, restore it as a play database somewhere, and pilot around with the MS contig utility.

You can refer to a SQL Tips article on how to use this utility.
The 7 Worst Nightmares of a Sysadmin

Fear not! To defend your business’ IT systems we’re going to shine a light on the seven most sinister terrors that haunt sysadmins. That way you can be sure there’s nothing in your stack waiting to go bump in the night.

Scott PletcherSenior DBACommented:
The SQL Server engine will not report on physical fragmentation, because it has no way of knowing about it (and it's not directly relevant to SQL Server).  Thus, any fragmentation SQL Server reports is not physical fragmentation in the data file(s) itself(themselves).
Bruce CarsonSystems AdministratorAuthor Commented:
Thanks Scott,
Yes, a lot of them have fewer than 8 pages but many have much more. I assume that if the pages are not a multiple of 8 that the same thing could happen?
Wouldn't physical fragmentation also effect performance? The databases reside on a RAID but it is a mirror RAID. I have intentionally excepted those files from my physical defrag routines.

Thanks Zac,
I found that article too. I modified the script to fit but it had no effect either. Nice script though.

I'm going to take one of the small tables and increase the pages to 8 and see what happens.
Scott PletcherSenior DBACommented:
Any table with 8 or more pages should have dedicated extents.  When you rebuild such a table, and it was fragmented, you should definitely see fragmentation go down.
Bruce CarsonSystems AdministratorAuthor Commented:
So for instance:
I have a table that has 1 index, 10 pages, 70% fragmented, 3508 rows, 57 columns, 1.977 MB.
The index is non-clustered unique.
page fullness is 96.66%

Reorganizing or rebuilding makes no difference.
Scott PletcherSenior DBACommented:
Reorganizing would likely make no difference at all.  I would expect rebuild to reduce the fragmentation.  You might try it with trace flag 1118 enabled for the rebuild and see if that makes any difference.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Bruce CarsonSystems AdministratorAuthor Commented:
How is that accomplished (referring to the trace flag 1118)?
Scott PletcherSenior DBACommented:
DBCC TRACEON ( 1118, -1 )

That will take care of the instance while it's running.

Then add T1118 to the startup (line) commands for SQL or add the DBCC command above to a startup proc.
Bruce CarsonSystems AdministratorAuthor Commented:
Thanks ScottPletcher.
I added it to the beginning of my script.
I assume that the DBCC INDEXDEFRAG command probably does more of a reorganize than a rebuild right?
I should probably be using DBCC CHECKTABLE ('TableName',REPAIR_REBUILD)
Problem is I can't get the database into single user.
I just get a message that states the ALTER DATABASE command failed.
Scott PletcherSenior DBACommented:
>> I assume that the DBCC INDEXDEFRAG command probably does more of a reorganize than a rebuild right? <<

>> I should probably be using DBCC CHECKTABLE ('TableName',REPAIR_REBUILD) <<
You wouldn't need to do that unless the db was damaged.  For just reorg/rebuild, you can use sys.dm_db_index_physical_stats or DBCC SHOWCONTIG.
Bruce CarsonSystems AdministratorAuthor Commented:
I was successful at defragmenting one table so far. The second table I was successful at defragmenting the index but the 'heap' is still fragmented at 40%.
Scott PletcherSenior DBACommented:
You can't defragment a heap.  You'll have to add a clustered index; then you can drop it if you want.  But first check the "missing indexes stats" for that table and see if there's a good candidate for a clustering key.
Bruce CarsonSystems AdministratorAuthor Commented:
Yep. That did it.
Thanks so much for your patience ScottPletcher.
I learned a lot today.
Bruce CarsonSystems AdministratorAuthor Commented:
I found that I first had to rebuild the index then reorganize to get the best results.
Adding a clustered index to fix the heap worked just fine.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.