sql-server - 为什么我的 Azure SQL 数据库索引仍然碎片？

Question

我的公司犯下了使用 GUID 作为 Azure SQL 数据库表主键的错误(实际上比这更糟糕:我们使用 VARCHAR(36) 而不是 UNIQUEIDENTIFIER)。因此，我们最终会得到碎片化的索引。它们看起来像这样:

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

我通过创建新表“解决”了该问题。这次，我使用了不可变的、不断增加的 DATETIME2(例如 CreateTime)列作为聚集索引，并将 VARCHAR(36) 保留为主键，但这一次是非聚集索引。像这样:

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

然后我使用 INSERT INTO NewTable SELECT * FROM OldTable 将行从旧表“复制”到新表。最后，我重命名了表格并删除了旧的表格。生活似乎很美好。

令我惊讶的是，几周后，我发现NewTable有很多碎片索引，平均碎片高达80%!甚至 IX_NewTable_CreateTime 也报告了 18% 的碎片。

INSERT INTO 是否对索引进行了碎片化？ REBUILD 索引会永远解决问题吗？

Answer 1

碎片将取决于索引字段的插入/更新频率以及索引页的大小。

出于维护目的，您可以使用 Azure 自动化并创建一个循环脚本来检查碎片索引并对其进行优化。

图库中有一本专门用于此目的的操作手册:

最好的一点是，只要您每月的运行时间不超过 500 分钟，并且合理安排执行时间，自动化就是免费的，您就无需付费:)

我对图库脚本进行了自定义改进，也请随意使用它:

<#
.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"
}