Yes, you definitely still need to perform regular database maintenance. Especially on Sitecore DBs like Analytics that have a lot of activity and develop index fragmentation quickly. At least, if you want to maintain performance of your site.
I should mention, that whitepaper was written a long time ago, and Azure gets updates every month. A number of the suggestions and practices we put into that document have been superseded. For example, we mostly use SQL Elastic Pools for all non-production databases these days as they are very cost effective compared with standalone DB provisioning. They can also work for production DBs, but you might want to dedicate performance to your Analytics DB - depending on your workload.
The comment about reduced cost is no longer applicable - SQL DBs now have a capped size based on their performance tier and there is no meter applicable to the actual storage consumed to my knowledge.
Maintenance via Azure Automation
We have an Azure Automation account that has runbooks that perform these tasks for us. Runbooks can be scheduled on a timer, or triggered via a webhook.
Our runbook looks like this:
workflow Update-SQLIndexRunbook
{
param(
[parameter(Mandatory=$True)]
[string] $SqlServer,
[parameter(Mandatory=$True)]
[string] $Database,
[parameter(Mandatory=$True)]
[string] $SQLCredentialName,
[parameter(Mandatory=$False)]
[int] $FragPercentage = 20,
[parameter(Mandatory=$False)]
[int] $SqlServerPort = 1433,
[parameter(Mandatory=$False)]
[boolean] $RebuildOffline = $False,
[parameter(Mandatory=$False)]
[string] $Table
)
# Get the stored username and password from the Automation credential
$SqlCredential = Get-AutomationPSCredential -Name $SQLCredentialName
if ($SqlCredential -eq $null)
{
throw "Could not retrieve '$SQLCredentialName' credential asset. Check that you created this first in the Automation service."
}
$SqlUsername = $SqlCredential.UserName
$SqlPass = $SqlCredential.GetNetworkCredential().Password
$TableNames = Inlinescript {
$db = $Using:Database
$frag = $Using:FragPercentage
# Define the connection to the SQL Database
$Conn = New-Object System.Data.SqlClient.SqlConnection("Server=tcp:$using:SqlServer,$using:SqlServerPort;Database=$using:Database;User ID=$using:SqlUsername;Password=$using:SqlPass;Trusted_Connection=False;Encrypt=True;Connection Timeout=30;")
# Open the SQL connection
$Conn.Open()
# SQL command to find tables and their average fragmentation
$SQLCommandString = @"
SELECT t.Name, MAX(avg_fragmentation_in_percent) MaxFragmentation
FROM sys.dm_db_index_physical_stats (
DB_ID(N'$db'),
OBJECT_ID(0),
NULL,
NULL,
NULL
) a
JOIN sys.indexes b ON a.object_id = b.object_id AND a.index_id = b.index_id
JOIN sys.tables t on t.object_id = a.object_id
WHERE a.avg_fragmentation_in_percent >= $frag
GROUP BY t.name;
"@
# Return the tables with their corresponding average fragmentation
$Cmd=new-object system.Data.SqlClient.SqlCommand($SQLCommandString, $Conn)
$Cmd.CommandTimeout=120
# Execute the SQL command
$FragmentedTable=New-Object system.Data.DataSet
$Da=New-Object system.Data.SqlClient.SqlDataAdapter($Cmd)
[void]$Da.fill($FragmentedTable)
# Return the table names that have high fragmentation
ForEach ($FragTable in $FragmentedTable.Tables[0])
{
Write-Verbose ("Table:" + $FragTable.Item("Name"))
Write-Verbose ("Max Index Fragmentation:" + $FragTable.Item("MaxFragmentation"))
$FragTable.Item("Name")
}
$Conn.Close()
}
# If a specific table was specified, then find this table if it needs to indexed, otherwise
# set the TableNames to $null since we shouldn't process any other tables.
If ($Table)
{
Write-Output ("Single Table specified: $Table")
If ($TableNames -contains $Table)
{
$TableNames = $Table
}
Else
{
# Remove other tables since only a specific table was specified.
Write-Output ("Table not found: $Table")
$TableNames = $Null
}
}
# Iterate through tables with high fragmentation and rebuild indexes
ForEach ($TableName in $TableNames)
{
Write-Output "Creating checkpoint"
Checkpoint-Workflow
Write-Output "Indexing Table $TableName..."
InlineScript {
$SQLCommandString = @"
EXEC('ALTER INDEX ALL ON $Using:TableName REBUILD with (ONLINE=ON)')
"@
# Define the connection to the SQL Database
$Conn = New-Object System.Data.SqlClient.SqlConnection("Server=tcp:$using:SqlServer,$using:SqlServerPort;Database=$using:Database;User ID=$using:SqlUsername;Password=$using:SqlPass;Trusted_Connection=False;Encrypt=True;Connection Timeout=30;")
# Open the SQL connection
$Conn.Open()
# Define the SQL command to run. In this case we are getting the number of rows in the table
$Cmd=new-object system.Data.SqlClient.SqlCommand($SQLCommandString, $Conn)
# Set the Timeout to be less than 30 minutes since the job will get queued if > 30
# Setting to 25 minutes to be safe.
$Cmd.CommandTimeout=1500
# Execute the SQL command
Try
{
$Ds=New-Object system.Data.DataSet
$Da=New-Object system.Data.SqlClient.SqlDataAdapter($Cmd)
[void]$Da.fill($Ds)
}
Catch
{
if (($_.Exception -match "offline") -and ($Using:RebuildOffline) )
{
Write-Output ("Building table $Using:TableName offline")
$SQLCommandString = @"
EXEC('ALTER INDEX ALL ON $Using:TableName REBUILD')
"@
# Define the SQL command to run.
$Cmd=new-object system.Data.SqlClient.SqlCommand($SQLCommandString, $Conn)
# Set the Timeout to be less than 30 minutes since the job will get queued if > 30
# Setting to 25 minutes to be safe.
$Cmd.CommandTimeout=1500
# Execute the SQL command
$Ds=New-Object system.Data.DataSet
$Da=New-Object system.Data.SqlClient.SqlDataAdapter($Cmd)
[void]$Da.fill($Ds)
}
Else
{
# Will catch the exception here so other tables can be processed.
Write-Error "Table $Using:TableName could not be indexed. Investigate indexing each index instead of the complete table $_"
}
}
# Close the SQL connection
$Conn.Close()
}
}
Write-Output "Finished Indexing"
}
As you can see, this runbook takes a number of parameters.
The only one worth mentioning is $SQLCredentialName which refers to the name of a Credential Asset stored in the Automation Account itself.
Suggested Indexes
In regards to the second part of your question, every team uses the Sitecore DBs a little differently. SQL Azure monitors the use of the system and the query plans it generates and tries to work out whether the plans could benefit from additional indexes. When it thinks it has one, it makes the suggestion. If you elect to implement the suggestion it will run some tests to ensure it has actually made an improvement, and will roll back if it hasn't.
We implement almost all the suggestions it offers. Indexes are generally not material to the operation of a database so they're safe to add, with a few critical exceptions (like clustered and unique indexes).
For the most part, as long as your DB tier offers you sufficient storage, we recommend adding any index suggested with either a Medium or High Impact rating.
