Why are my Azure SQL Database indexes still fragmented?

Question

My company committed the sin of using GUIDs as primary keys on our Azure SQL database tables (this is actually worse than what we used instead of VARCHAR (36) instead of UNIQUEIDENTIFIER). Thus, we get fragmented indexes. They looked like this:

CREATE TABLE OldTable ( Id VARCHAR(36) PRIMARY KEY CLUSTERED NOT NULL DEFAULT NEWID(), CreateTime DATETIME2 NOT NULL, ... ) 

I “fixed” the problem by creating new tables. This time I used an unchanging, ever-increasing DATETIME2 column (e.g. CreateTime) for CLUSTERED INDEX, and saved VARCHAR (36) as the MAIN KEY, but this time NONCLUSTERED. Like this:

 CREATE TABLE NewTable ( Id VARCHAR(36) PRIMARY KEY NONCLUSTERED NOT NULL DEFAULT NEWID(), CreateTime DATETIME2 NOT NULL INDEX IX_NewTable_CreateTime CLUSTERED, ) 

Then I “copied” the rows from the old table to the new table using INSERT INTO NewTable SELECT * FROM OldTable. Finally, I renamed the tables and dropped the old one. Life seemed good.

To my surprise, a couple of weeks later, I found out that NewTable has many fragmented indexes, with avg fragmentation reaching 80%! Even IX_NewTable_CreateTime reports 18% fragmentation.

Crashed INSERT INTO Index? Will the REBUILD index solve the problem forever?

Answer 1

Fragmentation will depend on the frequency of insertion / update in the indexed fields and the page size of the index.

For maintenance purposes, you can use Azure Automation and create a repeating script that checks for fragmented indexes and optimizes it.

In the gallery there is a Runbook in the gallery:

The best thing about this: automation is free if you don’t switch to 500 minutes of work per month, your execution time is good, and you won’t have to pay :)

I made my own improvement in the gallery script, feel free to use it as well:

 <# .SYNOPSIS Indexes tables in a database if they have a high fragmentation .DESCRIPTION This runbook indexes all of the tables in a given database if the fragmentation is above a certain percentage. It highlights how to break up calls into smaller chunks, in this case each table in a database, and use checkpoints. This allows the runbook job to resume for the next chunk of work even if the fairshare feature of Azure Automation puts the job back into the queue every 30 minutes .PARAMETER SqlServer Name of the SqlServer .PARAMETER Database Name of the database .PARAMETER SQLCredentialName Name of the Automation PowerShell credential setting from the Automation asset store. This setting stores the username and password for the SQL Azure server .PARAMETER FragPercentage Optional parameter for specifying over what percentage fragmentation to index database Default is 20 percent .PARAMETER RebuildOffline Optional parameter to rebuild indexes offline if online fails Default is false .PARAMETER Table Optional parameter for specifying a specific table to index Default is all tables .PARAMETER SqlServerPort Optional parameter for specifying the SQL port Default is 1433 .EXAMPLE Update-SQLIndexRunbook -SqlServer "server.database.windows.net" -Database "Finance" -SQLCredentialName "FinanceCredentials" .EXAMPLE Update-SQLIndexRunbook -SqlServer "server.database.windows.net" -Database "Finance" -SQLCredentialName "FinanceCredentials" -FragPercentage 30 .EXAMPLE Update-SQLIndexRunbook -SqlServer "server.database.windows.net" -Database "Finance" -SQLCredentialName "FinanceCredentials" -Table "Customers" -RebuildOffline $True .NOTES AUTHOR: Matias Quaranta LASTEDIT: Jan 10th, 2015 #> workflow MyRunBook { param( [parameter(Mandatory=$True)] [string] $SqlServer, [parameter(Mandatory=$True)] [string] $Database, [parameter(Mandatory=$True)] [string] $SQLCredentialName, [parameter(Mandatory=$False)] [int] $FragPercentage = 20, [parameter(Mandatory=$False)] [int] $SqlServerPort = 1433, [parameter(Mandatory=$False)] [boolean] $RebuildOffline = $False, [parameter(Mandatory=$False)] [string] $Table ) # 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." } $SqlUsername = $SqlCredential.UserName $SqlPass = $SqlCredential.GetNetworkCredential().Password InlineScript{ # 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 a.object_id, b.name, (select name from sys.tables t where t.object_id = b.object_id) as tablename, avg_fragmentation_in_percent FROM sys.dm_db_index_physical_stats ( DB_ID(N'$Database') , OBJECT_ID(0) , NULL , NULL , NULL) AS a JOIN sys.indexes AS b ON a.object_id = b.object_id AND a.index_id = b.index_id; "@ # 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) # Return the table names that have high fragmentation ForEach ($FragTable in $FragmentedTable.Tables[0]) { If ($FragTable.avg_fragmentation_in_percent -ge $Using:FragPercentage) { Write-Verbose ("Index found : " + $FragTable.name + " on table:" + $FragTable.tablename) $SQLCommandString = "EXEC('ALTER INDEX "+$FragTable.name+" ON "+$FragTable.tablename+" REBUILD')" $Cmd2=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. $Cmd2.CommandTimeout=1500 Try { $Ds=New-Object system.Data.DataSet $Da=New-Object system.Data.SqlClient.SqlDataAdapter($Cmd2) [void]$Da.fill($Ds) } Catch { Write-Verbose ($FragTable.name +" on table "+$FragTable.tablename+" could NOT be indexed.") } } } $Conn.Close() } Write-Verbose "Finished Indexing" }