SQL 2005 Index Fragmentation

[fuzzyocelot]

Hello everyone,

We’ve been having performance issues with one of our SQL 2005 servers. I’ve posted about it a few times in the past. It’s running on a Windows 2003 server (SP2) with 4 GB of RAM. We have SQL 2005 SP2 applied as well. This particular server is highly transactional as it uses merge replication with tablets. We’ve run performance counters on it and have optimized it the best we can (set awe, max server memory, etc). The vendor has also been monitoring it as well. The main conclusion we’re all coming to is that it’s maxed out on the memory and they need a more powerful server. So in the meantime, we’re trying to find ways of giving the server some “breathing” room. Since it also has other applications and SQL databases on it, we’re thinking of moving those to a separate server.

However, today I’m looking at index fragmentation and could use some help. We set up a 2005 maintenance plan to run weekly. Right now we have it set up to do the following:

* Check database integrity (include indexes)
* Reorganize index (tables and views, compact large objects)
* Update statistics (tables and views, all existing statistics, full scan)
* Clean up history (backup, job, maintenance plan, older than 12 weeks)

We’ve been wondering if it’s doing any good. So this morning I decided to do some research on it.

I queried the main database using sys.dm_db_index_physical_stats. The query I used is below:

SELECT * FROM sys.dm_db_index_physical_stats(db_id('MAINDB'),NULL,NULL,NULL,'LIMITED')
WHERE avg_fragmentation_in_percent>50

I used Limited just to get an idea of what’s going on. If I’m reading the results correctly, it’s fragmented. The query returned 83 rows.

* The alloc_unit_type_desc shows IN_ROW_DATA for all 83 rows.
* The avg_fragmentation_in_percent varies between 50 and 99.
* The fragment_count ranges between 3 and 6600.
* The avg_fragment_size_in_pages is around 1 for all rows. From what I’ve been reading, combined with the high avg_fragmentation_in_percent, this isn’t good.

Am I reading into this right? Meaning, we have a problem with fragmentation on at least this database? If so, does anyone have any ideas what we can do? Do we need to rebuild the indexes instead of reorganizing them?

I’m still reading BOL and researching this as much as I can. I would appreciate any ideas.

Thanks!

 

[ptheriault]

Hey Fuzzy,
First off you are on the right track in that you should remove any other applications from your SQL Server. SQL should always be a on stand alone server. Currently your SQL server has to compete for resources from the other applications and that will be part of your performance problems.

Now if you are sure that your maint plans you described above are running you may want to check that the tables that are being returned in your query have a unique clustered index. If they don't then you can not re-index or defrag them. I would also recommend that you add a weekly or bi-weekly re-index on your most transactional tables.

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[fuzzyocelot]

Thanks for the quick response! Hopefully we’ll be able to find a server to put those other databases and applications on. Right now I’m not sure where we’re going to put them.

Anyway, the maintenance plans are running every Sunday without any problems.

From what I can tell, out of the 83 records from the query, 28 are clustered indexes. Of those, 24 are unique clustered indexes. I looked at sys.indexes and sys.objects to figure it out. Hopefully those are the right tables. By the way, the fill factor is either 0 or 90 on these indexes. I haven’t even started to look at page splits, though.

When you say "re-index", are you referring to rebuilding the indexes or reorganizing them? I'm not sure which one we should be doing.

This next question is probably a "duh" question because I’m sure we’ve covered this in another thread somewhere. How do I determine which tables are the most transactional? The ones with the highest percentage of average fragmentation? Would sys.dm_db_index_usage_stats be the right place to look?

Thanks!

 

[ptheriault]

>>How do I determine which tables are the most transactional?
There is no query you can run that will show you this. You just have to know your database. it's going to be the tables that have a lot of deletes, inserts and updates.


>>When you say "re-index", are you referring to rebuilding the indexes or reorganizing them?

I'm referring to re-index. I usually don't use index defrag and its going away in future releases of SQL Server. You should be using ALTER INDEX ..REBUILD. You may also be able to use the on-line feature depending on your structure.

BOL has a lot of good information on this topic.

Check out this link.

http://msdn2.microsoft.com/en-us/library/ms188388.aspx

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[fuzzyocelot]

I understand what you're saying about having to know the database. For me it's kind of hard to do that because it was built by a vendor and contains over 500 tables alone. Along with another DBA, I'm also responsible for 15+ SQL servers each containing anywhere from 2 to 100+ databases. Some were built by a vendor and some by in-house developers. Some have multiple instances. To make it more fun, it's a mix of MSDE, 2000, and 2005. Unfortunately, I don't really get much of a chance to get to figure them out. I do understand what you're saying, though. I'll probably end up having to run some SQL traces to get an idea of which tables are the most transactional.

Thanks for the link. I'll definitely read up on it. I started reading BOL on indexing and got a bit lost. Hence my post. I think you've put me back on track, though.

Thanks again for the info!

 

[ptheriault]

No problem Fuzzy. The problems you face are the same everywhere. I have over 150 SQL servers and 500 databases. Some of them I've built but most are from a vendor. You could run some traces but I would go back to the vendor and ask them which tables are the most volatile (transactional).

Good Luck!

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[fuzzyocelot]

I figured I wasn't alone. Talking to the vendor is probably the quickest. Thanks!

I think it finally clicked. I was getting confused on the terminology. So if I understand this correctly...

reorganize = defrag
rebuild = re-index

Is that right? I remembered the DBCC commands (INDEXDEFRAG, REINDEX).

 

[SQLBill]

Yes, 2005 'changed' the terminology and Reorganize is the old Defrag. Rebuild is the old Reindex.

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875