in SQL Server Azure Automation Azure Azure PAAS Azure SDK Automation Index Syncig ~ read.

Syncing SQL Azure indexes across different environment

We recently had a SQL performance workshop to address ongoing performance issues in one of our client systems. The workshop was conducted by Gail Shaw. The experience is one for another article, but lets just say it was interestingly insightful.
One of her suggestions was to make sure we are syncing table indexes across the various environments using production as the master/hu.

Why sync indexes?

SQL Azure has a great feature called Automatic Tuning. It uses machine learning to analyse your queries and works out if there is a missing index. If there is a missing index it then applies the missing index and monitors it over a 24 - 36 hour period. If it has improved the performance it leaves the index else it removes the index.

This is incredibly powerful and we have seen some indexes created that have blown our mind. It is essentially your own database performance specialist for free. The golden rule about QA and Development environments is that the closer they are to production the better. We can catch performance issues in those environments before they reach production.

This means we needed to find a way to sync the indexes to ensure that the auto-created indexes on production filter to other environments.

How we did it

Our database set up looks like this:
database set up

SQL Azure unfortunately does not allow cross database queries unless they are all in the same pool. This presented a challenge for us and calling a Stored procedure from production in another database and then applying the script wouldn't work. We would need an intermediary to pass the script to create the indexes to who could then pass it on to the subscribers. Enter Azure Automation.

We decided on the following process

  1. Azure Automation job runs once a week
  2. It calls a stored procedure on production that returns a script to create all the indexes on production
  3. It then calls stored procedure on the destination database that passes in the script. This stored procedure deletes all the indexes and then runs the script passed in.

The scripts to do this yourself are available on my Github

1. Azure Automation script

We created a script that ran for each database rather than for all the databases. This was due to another smaller production database being located on another server and the credentials for that were different. The script is below:

param([Parameter(Mandatory=$True)] 
      [ValidateNotNullOrEmpty()] 
      [String]$SourceServerName,
      [Parameter(Mandatory=$True)]  
      [ValidateNotNullOrEmpty()] 
      [String]$SourceDatabaseName,
      [Parameter(Mandatory=$True)]  
      [ValidateNotNullOrEmpty()] 
    [String]$DestDatabaseName,
    [Parameter(Mandatory=$True)] 
      [ValidateNotNullOrEmpty()] 
      [String]$DestServerName, 
    [Parameter(Mandatory=$True)] 
    [ValidateNotNullOrEmpty()] 
      [String]$DestServerCredentials, 
    [Parameter(Mandatory=$True)] 
    [ValidateNotNullOrEmpty()] 
      [String]$SourceServerCredentials


) 
        Write-Output "Getting new index script from source database"  

        $myCredential = Get-AutomationPSCredential -Name $SourceServerCredentials
        $Username = $myCredential.UserName
        $Password = $myCredential.GetNetworkCredential().Password

        $connectionString = "Server=tcp:"+$SourceServerName+".database.windows.net,1433;Initial Catalog="+$SourceDatabaseName+
";Persist Security Info=False;User ID="+$Username+";Password="+$Password+";MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=300;"


         $connection = new-object system.data.SqlClient.SQLConnection($connectionString)
         $SqlCmd = New-Object System.Data.SqlClient.SqlCommand
        $SqlCmd.CommandText = "dbo.[upGetCreateIndexScript]"
        $SqlCmd.Connection = $connection

        $connection.Open()
        $script= $SqlCmd.ExecuteScalar()
        $connection.Close()


        Write-Output "Received new index script from source database"  

        Write-Output "Applying new index script to destination database"  
        $myCredential = Get-AutomationPSCredential -Name $DestServerCredentials
        $Username = $myCredential.UserName
        $Password = $myCredential.GetNetworkCredential().Password

        $connectionString = "Server=tcp:"+$DestServerName+".database.windows.net,1433;Initial Catalog="+$DestDatabaseName+
";Persist Security Info=False;User ID="+$Username+";Password="+$Password+";MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=300;"


         $connection = new-object system.data.SqlClient.SQLConnection($connectionString)
         $SqlCmd = New-Object System.Data.SqlClient.SqlCommand
        $SqlCmd.CommandText = "EXEC [dbo].[upBuildNewIndex] @CreateScript"
        $SqlCmd.Parameters.AddWithValue("@CreateScript", $script)
        $SqlCmd.Connection = $connection
      $SqlCmd.CommandTimeout=9999
        $connection.Open()
        $SqlCmd.ExecuteNonQuery()
        $connection.Close()


        Write-Output "Finished applying new index script to destination database"  

2. The create script

This I will admit is a "Copy from StackOverflow and modify" job but it gets the job done.

 Create PROCEDURE [dbo].[upGetCreateIndexScript]
 as 
 Declare @Result nvarchar(max);
 Select @Result=concat(@Result,';',index_create_statement )from ( 
 Select

CASE si.index_id WHEN 0 THEN N'/* No create statement (Heap) */'
ELSE 
    CASE is_primary_key WHEN 1 THEN
        N'ALTER TABLE ' + QUOTENAME(sc.name) + N'.' + QUOTENAME(t.name) + N' ADD CONSTRAINT ' + QUOTENAME(si.name) + N' PRIMARY KEY ' +
            CASE WHEN si.index_id > 1 THEN N'NON' ELSE N'' END + N'CLUSTERED '
        ELSE N'CREATE ' + 
            CASE WHEN si.is_unique = 1 then N'UNIQUE ' ELSE N'' END +
            CASE WHEN si.index_id > 1 THEN N'NON' ELSE N'' END + N'CLUSTERED ' +
            N'INDEX ' + QUOTENAME(si.name) + N' ON ' + QUOTENAME(sc.name) + N'.' + QUOTENAME(t.name) + N' '
    END +
    /* key def */ N'(' + key_definition + N')' +
    /* includes */ CASE WHEN include_definition IS NOT NULL THEN 
        N' INCLUDE (' + include_definition + N')'
        ELSE N''
    END +
    /* filters */ CASE WHEN filter_definition IS NOT NULL THEN 
        N' WHERE ' + filter_definition ELSE N''
    END +
    /* with clause - compression goes here */
    CASE WHEN row_compression_partition_list IS NOT NULL OR page_compression_partition_list IS NOT NULL 
        THEN N' WITH (' +
            CASE WHEN row_compression_partition_list IS NOT NULL THEN
                N'DATA_COMPRESSION = ROW ' + CASE WHEN psc.name IS NULL THEN N'' ELSE + N' ON PARTITIONS (' + row_compression_partition_list + N')' END
            ELSE N'' END +
            CASE WHEN row_compression_partition_list IS NOT NULL AND page_compression_partition_list IS NOT NULL THEN N', ' ELSE N'' END +
            CASE WHEN page_compression_partition_list IS NOT NULL THEN
                N'DATA_COMPRESSION = PAGE ' + CASE WHEN psc.name IS NULL THEN N'' ELSE + N' ON PARTITIONS (' + page_compression_partition_list + N')' END
            ELSE N'' END
        + N')'
        ELSE N''
    END +
    /* ON where? filegroup? partition scheme? */
    ' ON ' + CASE WHEN psc.name is null 
        THEN ISNULL(QUOTENAME(fg.name),N'')
        ELSE psc.name + N' (' + partitioning_column.column_name + N')' 
        END
    + N';'
END AS index_create_statement

FROM sys.indexes AS si
JOIN sys.tables AS t ON si.object_id=t.object_id
JOIN sys.schemas AS sc ON t.schema_id=sc.schema_id
LEFT JOIN sys.dm_db_index_usage_stats AS stat ON 
stat.database_id = DB_ID() 
and si.object_id=stat.object_id 
and si.index_id=stat.index_id
LEFT JOIN sys.partition_schemes AS psc ON si.data_space_id=psc.data_space_id
LEFT JOIN sys.partition_functions AS pf ON psc.function_id=pf.function_id
LEFT JOIN sys.filegroups AS fg ON si.data_space_id=fg.data_space_id
OUTER APPLY ( SELECT STUFF (
(SELECT N', ' + QUOTENAME(c.name) +
    CASE ic.is_descending_key WHEN 1 then N' DESC' ELSE N'' END
FROM sys.index_columns AS ic 
JOIN sys.columns AS c ON 
    ic.column_id=c.column_id  
    and ic.object_id=c.object_id
WHERE ic.object_id = si.object_id
    and ic.index_id=si.index_id
    and ic.key_ordinal > 0
ORDER BY ic.key_ordinal FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'),1,2,'')) AS keys ( key_definition )
OUTER APPLY (
SELECT MAX(QUOTENAME(c.name)) AS column_name
FROM sys.index_columns AS ic 
JOIN sys.columns AS c ON 
    ic.column_id=c.column_id  
    and ic.object_id=c.object_id
WHERE ic.object_id = si.object_id
    and ic.index_id=si.index_id
    and ic.partition_ordinal = 1) AS partitioning_column
OUTER APPLY ( SELECT STUFF (
(SELECT N', ' + QUOTENAME(c.name)
FROM sys.index_columns AS ic 
JOIN sys.columns AS c ON 
    ic.column_id=c.column_id  
    and ic.object_id=c.object_id
WHERE ic.object_id = si.object_id
    and ic.index_id=si.index_id
    and ic.is_included_column = 1
ORDER BY c.name FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'),1,2,'')) AS includes ( include_definition )
OUTER APPLY ( 
SELECT 
    COUNT(*) AS partition_count,
    CAST(SUM(ps.in_row_reserved_page_count)*8./1024./1024. AS NUMERIC(32,1)) AS reserved_in_row_GB,
    CAST(SUM(ps.lob_reserved_page_count)*8./1024./1024. AS NUMERIC(32,1)) AS reserved_LOB_GB,
    SUM(ps.row_count) AS row_count
FROM sys.partitions AS p
JOIN sys.dm_db_partition_stats AS ps ON
    p.partition_id=ps.partition_id
WHERE p.object_id = si.object_id
    and p.index_id=si.index_id
) AS partition_sums
  OUTER APPLY ( SELECT STUFF (
(SELECT N', ' + CAST(p.partition_number AS VARCHAR(32))
FROM sys.partitions AS p
WHERE p.object_id = si.object_id
    and p.index_id=si.index_id
    and p.data_compression = 1
ORDER BY p.partition_number FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'),1,2,'')) AS row_compression_clause ( row_compression_partition_list )
OUTER APPLY ( SELECT STUFF (
(SELECT N', ' + CAST(p.partition_number AS VARCHAR(32))
FROM sys.partitions AS p
WHERE p.object_id = si.object_id
    and p.index_id=si.index_id
    and p.data_compression = 2
ORDER BY p.partition_number FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'),1,2,'')) AS page_compression_clause ( page_compression_partition_list )
WHERE 
is_primary_key=0and 
si.type IN (0,1,2) /* heap, clustered, nonclustered */

) createScript

select  @Result

3. The build script

This I wrote myself, I am not that lazy.

CREATE PROCEDURE [dbo].[UpBuildNewIndex] @CreateScript NVARCHAR(max) 
AS 
DECLARE @oldindexes NVARCHAR(max) 

SELECT @oldindexes = Concat(@oldindexes, ( ';drop index ' + index_name + 
                                           ' on ' + 
                                                  tablename )) 
FROM   (SELECT t.NAME  AS tableName, 
               si.NAME AS index_name 
        FROM   sys.indexes AS si 
               JOIN sys.tables AS t 
                 ON si.object_id = t.object_id 
        WHERE  si.type IN ( 0, 1, 2 )) AS indexes 
WHERE  index_name NOT LIKE '%PK_%' 


EXEC Sp_executesql 
  @oldindexes 

EXEC Sp_executesql 
  @CreateScript 

Share: