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

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

mikebird
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

djj55
Constraint Violating Yak Guru

USA
310 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
DECLARE @strSQL NVARCHAR(2000) 
SET @strSQL = 'IF ''?'' IN (''master'', ''model'', ''msdb'', ''tempdb'', ''distribution'') RETURN; '
    + 'USE ?; '
    + 'SELECT ''?'' AS DBName, t.name AS TableName, '
    + 'CASE WHEN avg_fragmentation_in_percent < 30 '
    + 'THEN ''ALTER INDEX ['' + s.name + ''] ON ['' + t.name + ''] REORGANIZE; --'' '
    + 'ELSE ''ALTER INDEX ['' + s.name + ''] ON ['' + t.name + ''] 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 = s.id and d.index_id = s.indid '
    + 'INNER JOIN sys.tables t ON s.id = t.object_id '
    + 'ORDER BY DBName, s.name DESC; '
print @strSQL 

EXECUTE sp_MSForEachdB @strSQL
go

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


djj
Go to Top of Page

ahmeds08
Aged Yak Warrior

India
573 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.
http://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html

mohammad.javeed.ahmed@gmail.com
Go to Top of Page

mikebird
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

srimami
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

jackv
Flowing Fount of Yak Knowledge

United Kingdom
2008 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
--------------------
http://www.sqlserver-dba.com
Go to Top of Page
  Previous Topic Topic Next Topic  
 New 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.08 seconds. Powered By: Snitz Forums 2000