Download 3 scripts from here :
https://ola.hallengren.com/downloads.html
Create table : CommandLog.sql
Create procedures: CommandExecute.sql, IndexOptimize.sql
Edit procedure IndexOptimize according to Brent Ozar article. (https://www.brentozar.com/archive/2014/12/tweaking-defaults-ola-hallengrens-maintenance-scripts/)
FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE'
FragmentationHigh = 'INDEX_REBUILD_ONLINE'
FragmentationLevel1 = 50
FragmentationLevel2 = 80
But I prefer these defaults according to my DB size. (It is so small relative to Brent's DB) And I let the rebuild index offline at night time. Thus, I just changed these two lines.
FragmentationLevel1 = 15
FragmentationLevel2 = 70
Define FillFactor paramaters as 90 Define the MaxNumberOfPages and MinNumberOfPages of each request to split jobs.
Why we want to split it in 2 separate jobs ?
Because Azure Automation duration must be max 3 working hours. Azure does not support over 3 hours for automation jobs.
-- small tables
EXEC dbo.IndexOptimize @Databases = 'TestDB', @FillFactor=90, @MaxNumberOfPages=195000, @UpdateStatistics = 'ALL', @OnlyModifiedStatistics = 'Y', @LogToTable = 'Y'
-- large tables. It means about first 10 big tables.
EXEC dbo.IndexOptimize @Databases = 'TestDB', @FillFactor=90, @MinNumberOfPages=195000, @UpdateStatistics = 'ALL', @OnlyModifiedStatistics = 'Y', @LogToTable = 'Y'
To view list of index fragmentation :
SELECT S.name as 'Schema', T.name as 'Table', I.name as 'Index', DDIPS.avg_fragmentation_in_percent, DDIPS.page_count, I.fill_factor FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS DDIPS INNER JOIN sys.tables T on T.object_id = DDIPS.object_id INNER JOIN sys.schemas S on T.schema_id = S.schema_id INNER JOIN sys.indexes I ON I.object_id = DDIPS.object_id AND DDIPS.index_id = I.index_id WHERE DDIPS.database_id = DB_ID() and I.name is not null and DDIPS.page_count > 1000 and DDIPS.avg_fragmentation_in_percent > 0 ORDER BY DDIPS.page_count desc
Azure Automation PowerShell Script :
Before this script, you have to define “sqllogin” to connect the SQL database.
In the “Automation” section in Azure Panel. Find “Credential” (under Shared Resources) and click “Add a credential” named sqllogin
You can set a schedule for every weekend night (ex: Sunday, 00:30)
This script for PowerShell 7.2 version (the latest version of PS for today) :
$AzureSQLServerName = "ServerName.database.windows.net"
$AzureSQLDatabaseName = "DBname"
$Cred = Get-AutomationPSCredential -Name "sqllogin"
$Username = $Cred.UserName
$Password = $Cred.GetNetworkCredential().Password
function Invoke-SqlQuery {
param (
[string]$ServerName,
[string]$DatabaseName,
[string]$Username,
[string]$Password,
[string]$Query
)
$ConnectionString = "Data Source=$ServerName;Initial Catalog=$DatabaseName;User ID=$Username;Password=$Password;Connection Timeout=30"
$connection = New-Object System.Data.SqlClient.SqlConnection
$connection.ConnectionString = $ConnectionString
$connection.Open()
$command = $connection.CreateCommand()
$command.CommandText = $Query
$command.CommandTimeout = 65535
$adapter = New-Object System.Data.SqlClient.SqlDataAdapter $command
$dataset = New-Object System.Data.DataSet
$adapter.Fill($dataset) | Out-Null
$connection.Close()
return $dataset.Tables[0]
}
$Query = "exec [dbo].[StoredProcedureName]"
$SQLOutput = Invoke-SqlQuery -ServerName $AzureSQLServerName -DatabaseName $AzureSQLDatabaseName -Username $Username -Password $Password -Query $Query
Write-Output $SQLOutput
If you want to use PowerShell 5.1 (older version)
$AzureSQLServerName = "ServerName"
$AzureSQLDatabaseName = "DBname"
$AzureSQLServerName = $AzureSQLServerName + ".database.windows.net"
$Cred = Get-AutomationPSCredential -Name "sqllogin"
$SQLOutput = $(Invoke-Sqlcmd -ServerInstance $AzureSQLServerName -Username $Cred.UserName -Password $Cred.GetNetworkCredential().Password -Database $AzureSQLDatabaseName -Query "exec [dbo].[StoredProcedureName]" -QueryTimeout 65535 -ConnectionTimeout 60 -Verbose) 4>&1
Write-Output $SQLOutput
