SQL Server Performance Forum – Threads Archive

Analyzing index fragmentation

I’ve inherited a production SQL Server 2000 database that was migrated to SQL Server 2005 by restoring backup tapes on the new server. Stored procedures that took under a minute are now taking over three minutes and causing web applications to time out. With the SQL below, I’m trying to find out which tables have index fragmentation issues. If I run this for all tables for a database, I get lots of object_ids for indexes that need help, but can’t easily tie those to tables and field names. Is there an easier way to do this? I’d go ahead and rebuild all databases and tables, but this is in production and each index rebuild takes time when I use a remote desktop to the db server and rebuild tables individually. Is it better to do this via a query window? Could you recommend any other approaches to this? I’m new to SQL Server, so there’s likely easier ways to do this…
select database_id, object_id, index_type_desc, avg_fragmentation_in_percent, avg_fragment_size_in_pages, avg_page_space_used_in_percent, record_count, page_count, fragment_count, avg_record_size_in_bytes
from sys.dm_db_index_physical_stats(db_id(‘resume’), OBJECT_ID(‘Skills_tbl’), NULL, NULL, ‘LIMITED’);

---

First of all run the update the statics with FULLSCAN option ON against all the tables
or
rebuild the all the indexes without checking the fragmentation which remove fragmentation if any then updates the stats with full scan… In SQL SERVER 2005 you can rebuild the indexes on line if your server is Enterprise Edition…check BOL topic "ALTER TABLE…"
Read BOL topic "Reorganizing and Rebuilding Indexes "
http://msdn2.microsoft.com/en-us/library/ms189858.aspx Check also the following thread…
http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=19594
MohammedU.
Moderator
SQL-Server-Performance.com

---

]]>