Created
January 17, 2024 23:33
-
-
Save fluxdigital/b0d4f893eaf6b7dfec4430fc2aa9b8fe to your computer and use it in GitHub Desktop.
Azure Runbook Workflow that finds fragmented database tables and re-indexes them
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
workflow Sitecore-DB-Maintenance-Plan-Workflow-RB | |
{ | |
<# | |
.SYNOPSIS | |
Azure Runbook Workflow that finds fragmented database tables and re-indexes them | |
.DESCRIPTION | |
Finds any tables with a average fragmentation higher than the defined $FragPercentage and then indexes them | |
.PARAMETER SqlServer | |
Your SQL Server Name (e.g 'my-sql-server.database.windows.net') | |
.PARAMETER Database | |
Your Database (e.g 'sc103_Master') | |
.PARAMETER SQLCredentialName | |
Your Credentials name (e.g 'DatabaseCred') | |
.PARAMETER FragPercentage | |
The minimum average % fragmentation of tables to include (e.g '20') | |
.PARAMETER SqlServerPort | |
Your SQL Server Port (usually '1433') | |
.PARAMETER RebuildOffline | |
If the Rebuild should be carried out while the database is still accessible or not (use online to keep accessible) | |
.PARAMETER Table | |
Name of a single table that should be re-indexed only (e.g 'Items'). Leave empty for all tables. | |
#> | |
param( | |
[parameter(Mandatory=$True)] | |
[string] $SqlServer = "", ## Your SQL Server Name (e.g 'my-sql-server.database.windows.net') | |
[parameter(Mandatory=$True)] ## Your Database (e.g 'sc103_Master') | |
[string] $Database = "", | |
[parameter(Mandatory=$True)] | |
[string] $SQLCredentialName = "", ## Your Credentials name (e.g 'DatabaseCred') | |
[parameter(Mandatory=$False)] | |
[int] $FragPercentage = 20, ## The minimum average % fragmentation of tables to include (e.g '20') | |
[parameter(Mandatory=$False)] | |
[int] $SqlServerPort = 1433, ## Your SQL Server Port (usually '1433') | |
[parameter(Mandatory=$False)] | |
[boolean] $RebuildOffline = $False, ## If the Rebuild should be carried out while the database is still accessible or not (use online to keep accessible) | |
[parameter(Mandatory=$False)] | |
[string] $Table ## Name of a single table that should be re-indexed only (e.g 'Items'). Leave empty for all tables. | |
) | |
Write-Output "Running Sitecore DB Maintenance Plan Workflow...`n`r" | |
# 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." | |
} | |
else{ | |
Write-Output "Credential asset: '$SQLCredentialName' found, getting tables to defrag...`n`r" | |
#output the tables found for indexing | |
Write-Output "Fragmented Tables" | |
Write-Output "---------------------`n`r" | |
} | |
$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) | |
$fragCount = 0 | |
# Return the table names that have high fragmentation | |
ForEach ($FragTable in $FragmentedTable.Tables[0]) | |
{ | |
$fragCount++ | |
Write-Verbose "Table: $($FragTable.Item("Name")) - Max Fragmentation: $($FragTable.Item("MaxFragmentation"))" -Verbose | |
$FragTable.Item("Name") | |
} | |
$Conn.Close() | |
Write-Verbose "------------------------------------`n`r" -Verbose | |
Write-Verbose "Total Fragmented Tables Found: $fragCount" -Verbose | |
} | |
# 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) | |
{ | |
#create a workflow checkpoint incase the workflow is interrupted due to an db error etc. PowerShell Workflow uses the data in newest checkpoint to recover and resume the workflow. | |
Write-Verbose "Creating checkpoint" -Verbose | |
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 "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 "`n`r Finished Indexing all tables." | |
} |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment