5

In this Sitecore on Azure Migration white paper http://www.slideshare.net/Jerrynott/azuremigrationwp004

It mentions on Page 17

It should be remembered that general database maintenance is still required.

Index rebuilds and defragmentation are a standard maintenance task that must be carried out on any database, even Azure SQL databases, as the platform cannot determine whether these tasks are relevant for a given implementation automatically. This is a common cause of Sitecore performance problems.

Create stored procedures to perform routine index maintenance, and use the Azure Scheduling and Automation platform to execute these procedures regularly. Aside from improving database performance, reduced fragmentation also reduces the overall space of the database and therefore reduces the cost.

Is this still needed in the latest version of Azure SQL (PAAS)?

And are there any examples of this?

I also found this quote

https://mhwelander.net/2014/09/23/sitecore-azure-for-beginners-part-1-what-is-microsoft-azure/

Make sure you understand the limitations of the services that the Sitecore Azure module relies on – for example, SQL Azure has limits on transaction log size, which becomes an problem when attempting to rebuild heavily fragmented indexes.

And am I right thinking that you shouldn't add any custom indexes to a sitecore database, even if Azure SQL recommends for an index to be added? (As these changes won't be supported by Sitecore)

6

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.