azure Azure-Automation Index maintenance

Example

If you care about your indices (yes, that's the plural of index), you should maintain them, especially if you do frequent inserts or deletes that affect them. Azure Automation provides an almost ready runbook that you can use and schedule to perform automated index rebuilding. Here's what you have to do: First, import the runbook:

After the runbook is imported, you have to go into edit mode and press publish on the runbook, and it becomes active. In edit mode, you can also check out the source code of the runbook.

Then you have to add a credential to the runbook, which can be used to connect to the database (basically it is just a key-value pair, where the value is a username and a password, and the key can be used from the script to reference this particular credential). This has to be a user-password pair that can authenticate to the database, and the user should have rights to access database state and run the ALTER INDEX statement: And finally schedule the runbook with the specified parameters. You can also test the runbook and start it immediately (but you have to specify the parameters in this case too):

Now unfortunately, I have found that there are two problems with this default runbook.

First, it only handles tables that are in the default schema. That's not always enough, so go ahead and find the line:

SELECT  t.name AS TableName, t.OBJECT_ID FROM sys.tables t

And change it to this:

SELECT  '['+SCHEMA_NAME(t.schema_id)+'].['+t.name+']' AS TableName, t.OBJECT_ID FROM sys.tables t

Also, the script cannot handle special characters anywhere in the connection string (like ",' or =). To handle these, you can use the proper connection string escaping, or better yet, use the connectionstring builder. Whereever you see a connection string created in the script (there should be two places), change it to use the connectionstring builder:

$connStringBuilder = New-Object System.Data.SqlClient.SqlConnectionStringBuilder
$connStringBuilder["Server"] = "tcp:$using:SqlServer,$using:SqlServerPort"
$connStringBuilder["Database" ] = "$using:Database"
$connStringBuilder["User ID"] = "$using:SqlUsername"
$connStringBuilder["Password"] = "$using:SqlPass"
$connStringBuilder["Trusted_Connection"] = $False
$connStringBuilder["Encrypt"] = $True
$connStringBuilder["Connection Timeout"] = "30"
$connString = $connStringBuilder.ConnectionString                
$Conn = New-Object System.Data.SqlClient.SqlConnection($connString)

You can also change the query that actually calculates fragmentation a bit. This is the query:

SELECT a.object_id, avg_fragmentation_in_percent
    FROM sys.dm_db_index_physical_stats (
           DB_ID(N'$Database')
         , OBJECT_ID(0)
         , NULL
         , NULL
         , NULL) AS a
    JOIN sys.indexes AS b 
    ON a.object_id = b.object_id AND a.index_id = b.index_id;

If you change the last parameter of sys.dm_db_index_physical_stats from NULL to 'DETAILED', you get a much better estimation of how fragmented the indices are.

I have uploaded this version to Github as well: https://github.com/conwid/IndexRebuildScript