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:
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
- Azure Automation job runs once a week
- It calls a stored procedure on production that returns a script to create all the indexes on production
- 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