Sql Azure - automation of statistics update

Using Azure Automation Account we can schedule an activity to regularly update statistics of a Sql Azure database. Elements involved:

• an Azure Automation Account
• a scheduled PowerShell workbook inside Automation Account
• a stored procedure inside the database to actually do the statistics update

Let’s start from the bottom.

Update statistics - stored procedure

Over the internet, there are many script. Mine is below. UPDATE STATISTICS command has many option: consider to set the right ones for your specific database.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[MyUpdateStatistics]
 AS
BEGIN
    DECLARE @currentTableName nvarchar(500) = ''
    
    DROP TABLE IF EXISTS #tableListUpdateStats
    
    SELECT '[' + table_schema + '].[' + table_name + ']' as TableNameFull INTO #tableListUpdateStats 
           FROM information_schema.tables WHERE TABLE_TYPE = 'BASE TABLE' ORDER BY table_schema, table_name
    
    WHILE 1=1
    BEGIN
    	SET @currentTableName = (select top(1) TableNameFull from #tableListUpdateStats where TableNameFull > @currentTableName order by TableNameFull)
    	IF @currentTableName IS NULL BREAK
    	DECLARE @sqlCommand nvarchar(max)  = 'UPDATE STATISTICS ' + @currentTableName + ' '
    	PRINT @sqlCommand
    	EXEC sp_executesql  @sqlCommand	
    END
    
    DROP TABLE IF EXISTS #tableListUpdateStats
END

The Automation Account and workbook

An Azure Automation Account allows to run a script (workbook) on a regular basis. The key elements are Workbook, Schedules and Credentials. The latter are used to securely stores the credentials needed to connect to the database.

Steps:

• create a Azure Automation Account
• save relevant sensitive information inside Credentials
• create a powershell Workbook
• schedule the Workbook

My Workbook PowerShell script:

# Get credential from Azure Automation credential store
$myCredential = Get-AutomationPSCredential -Name 'SqlXYZCredentials'

# Execute sql query
Invoke-Sqlcmd -ServerInstance xxxxxxxxxxx.database.windows.net -Database MyDB -Query "EXEC MyUpdateStatistics" -Credential $myCredential -Verbose -OutputSqlErrors $true  4>&1

My Schedule: