Rebuilding indexes for an entire database 1

[SantaMufasa]

I have a product manager that wants to rule out index fragmentation as a contibuting factor in an application's poor performance.

Can someone offer an efficient SQL statement (or a SQL Server administrative action) that could rebuild/defragment all indexes for a specific database?

Thanks,

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[ptheriault]

Use a maint plan. From within EM or SSMS depending on your version right click on Maintenance plans and select new plan. This will open the maint plan wizard. You can then create a plan to re-index all your user databases.

Here are a few queries to show you fragmentation.

SQL 2000
Run the following query with the results going to text. Then take the results and run that in a query window.
(if you take out the "top 10" it will run for all tables.)

SELECT TOP 10
'DBCC SHOWCONTIG(' + CAST(id AS NVARCHAR(20)) + ')'
+ CHAR(10) +
'PRINT '' ''' + CHAR(10)
FROM
sysindexes
WHERE
indid = 1 or
indid = 0
ORDER BY rows DESC

This is for 2005. It will show you all tables that are 50% or more fragmented.

SELECT avg_fragmentation_in_percent
      ,o.name as 'Table'
      ,Index_id
      ,index_type_desc
FROM sys.dm_db_index_physical_stats(db_id('[b]your db[/b]'), null, null, null, null) i
     JOIN sys.objects o
     ON o.object_id = i.object_id 
WHERE avg_fragmentation_in_percent > 50
ORDER BY o.name
        ,index_id

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

 

[ptheriault]

Dave,
A few more things..

if your table doesn't have a clustered index then you can not correct the fragmentation with a re-index command. you have to physically select the data into a temp table, delete the data in the original table and select the data back in with an order by clause.

For 2005 here is a script that will create the ALTER INDEX statement for you so you wouldn't have to use a maint plan.

SELECT DISTINCT 'ALTER INDEX ALL ON '+o.name+' REBUILD'
FROM sys.dm_db_index_physical_stats(db_id('1stComp'), null, null, null, null) i
     JOIN sys.objects o
     ON o.object_id = i.object_id 
ORDER BY o.name
        ,index_id

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

 

[ptheriault]

Sorry Dave,
I was tired last night when I posted the last script. It should look like this..

SELECT DISTINCT 'ALTER INDEX ALL ON '+o.name+' REBUILD'
FROM sys.dm_db_index_physical_stats(db_id('[b]your db[/b]'), null, null, null, null) i
     JOIN sys.objects o
     ON o.object_id = i.object_id
WHERE avg_fragmentation_in_percent > 50

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

 

[SantaMufasa]

Excellent scripts, Paul. Thanks for taking the time to respond. Hava

!

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[ptheriault]

Thanks Dave

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