Please start any new threads on our new site at We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Register Now and get your question answered!
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 SQL Server Administration (2008)
 automating index rebuilds...
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Aged Yak Warrior

United Kingdom
529 Posts

Posted - 05/07/2013 :  05:58:04  Show Profile  Reply with Quote
after looking at the standard report os this, sick of drilling down, what's the best way to run a process for this? Worth making manual decisions, or setting thresholds? SQL Agent?

Using EXPRESS edition

Edited by - mikebird on 05/07/2013 06:03:40

Constraint Violating Yak Guru

352 Posts

Posted - 05/07/2013 :  11:30:00  Show Profile  Reply with Quote
You should REORGANIZE if the fragmentation is less than 30 and REBUILD above. I usually do not worry about low fragmented indexes.
I use something like this which creates a table of executable code. The comment after the code is so I can copy the two columns and know the
percent of fragmentation and still run the
SET @strSQL = 'IF ''?'' IN (''master'', ''model'', ''msdb'', ''tempdb'', ''distribution'') RETURN; '
    + 'USE ?; '
    + 'SELECT ''?'' AS DBName, AS TableName, '
    + 'CASE WHEN avg_fragmentation_in_percent < 30 '
    + 'THEN ''ALTER INDEX ['' + + ''] ON ['' + + ''] REORGANIZE; --'' '
    + 'ELSE ''ALTER INDEX ['' + + ''] ON ['' + + ''] REBUILD; --'' '
    + 'END AS ExecuteCode, avg_fragmentation_in_percent '
    + 'FROM sys.dm_db_index_physical_stats (DB_ID(DB_NAME()), NULL, NULL , NULL, N''LIMITED'') d '
    + 'INNER JOIN sysindexes s ON OBJECT_ID = and d.index_id = s.indid '
    + 'INNER JOIN sys.tables t ON = t.object_id '
    + 'ORDER BY DBName, DESC; '
print @strSQL 

EXECUTE sp_MSForEachdB @strSQL

Example (you will need USE AdventurWorks2008R2):
ALTER INDEX [PK_Vendor_BusinessEntityID] ON [Vendor] REBUILD; --	50

Go to Top of Page

Aged Yak Warrior

737 Posts

Posted - 05/08/2013 :  01:28:52  Show Profile  Send ahmeds08 a Yahoo! Message  Reply with Quote
you can use Ola's script to do that.
Go to Top of Page

Aged Yak Warrior

United Kingdom
529 Posts

Posted - 05/08/2013 :  07:25:30  Show Profile  Reply with Quote

with experience, I've reorganised and rebuilt clustered indexes as flagged by the index physical stats, where only a handful are marked for this action. I've seen fragmentation at 99%, 97%, 94%, 88%, 67%, 50%, etc. Showing properties, the high frags go to 0% immediately. Then others stay the same. The boss saw fragmentation as files on any DOS or Windows disk. I think of filegroups keeping files separate. I thought performance improvement was all about indexing and execution plans (cached)?
Go to Top of Page

Posting Yak Master

160 Posts

Posted - 05/10/2013 :  04:46:48  Show Profile  Reply with Quote
Hi Mike,

I haven't got your description but what I understand from subject line is you wanted to automate the process of rebuilding indexes. The best way to do that is create a maintenance plan and schedule to run during off peak hours (Usually Sundays under maintenance window, if any)and rebuild Index.

Hope this helps, thanks.
Go to Top of Page

Flowing Fount of Yak Knowledge

United Kingdom
2179 Posts

Posted - 05/10/2013 :  13:55:53  Show Profile  Visit jackv's Homepage  Reply with Quote
You can manage it through SQL Server Agent . Also try and negotiate with the owners for a dedicated maintenance window

Jack Vamvas
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.07 seconds. Powered By: Snitz Forums 2000