Index maintenance

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:

Learn Azure - Azure tutorial - Run Book Module - Azure examples - Azure programs

Learn Azure - Azure tutorial - Edit Module - Azure examples - Azure programs

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:

Learn Azure - Azure tutorial - Alter Index Module - Azure examples - Azure programs

Learn Azure - Azure tutorial - Parameter Specification - Azure examples - Azure programs

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

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)

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;