Index rebuild on a large database could take longer time. One of our customer was looking for a solution to rebuild index one by one when index fragmentation is greater than 30%. Related newsgroup thread: http://social.msdn.microsoft.com/Forums/en-US/sqlkjmanageability/thread/4faae7c8-3e58-4117-8538-f397e342743f
In this blog post, I have written a SQL Powershell script that identifies indexes on tables that have fragmentation greater than 30% and performs a Index Rebuild one by one. You can also control the number of indexes to process in single script run. By this approach, you can run this SQL powershell script as a recurring scheduled SQL Agent job in non-peak hours.
PowerShell code:
You can download powershell script from rebuildIndexes.ps1
SQL agent job from Indexrebuild_agentjob.sql
[...] I have written a SQL Powershell script that identifies indexes on tables that have fragmentation greater than 30% and performs a Index Rebuild one by one. You can also control the number of indexes to process in single script run. You can run this SQL powershell script as a recurring scheduled SQL Agent job . More details at: http://sethusrinivasan.wordpress.com/2012/02/14/index-rebuild-on-large-database-sql-agent-powershell… [...]
Pingback by Index rebuild on large database – SQL Agent Powershell job | MSDN Blogs — February 14, 2012 @ 9:59 pm |
Hi Sethu,
You script looks great. I am a .NET developer, know about powershell but haven’t worked with it a lot.
Just a couple of clarifications needed.
You are using an Index Object and calling Rebuild all indexes on it. “$index.RebuildAllIndexes()”
Is it going to rebuild all indexes of the table or that specific index.
Can you please point me to the URL where all these are documented.
Thanks Again.
Arun Kumar Allu
Comment by Arun Kumar Allu — February 14, 2012 @ 10:11 pm |
Hello Arun,
SMO API is documented in MSDN. You can take a look at http://msdn.microsoft.com/en-us/library/microsoft.sqlserver.management.smo.index.aspx
Let me check on Rebuild* API and get back..
Thanks
sethu
Comment by sethusrinivasan — February 14, 2012 @ 10:53 pm |
Your script looks great. Do you know if there is a way to do an online index rebuild using powershell/smo? I want to do the equivalent of setting ‘online=on’ in an alter index rebuild.
Comment by JohnS — July 25, 2012 @ 12:10 pm |
# Online Index operations using SMO, Run this script in SQL Powershell
#script assumes that your machine name is sqldemo and mdw database exists in default instance
CD SQLSERVER:\SQL\sqldemo\DEFAULT\Databases\mdw\Tables\snapshots.disk_usage\indexes\NonClusteredIndex-20120326-132208
$idx = (get-item .)
$idx.OnlineIndexOperation = $true
$idx.Rebuild()
Comment by sethusrinivasan — October 9, 2012 @ 10:50 am |
thanks!
Comment by JohnS — October 9, 2012 @ 11:22 am