为什么我的Azure SQL数据库索引还是碎片？ [英] Why my Azure SQL Database indexes are still fragmented?

问题描述

我公司一直致力于使用我们的Azure SQL数据库表的GUID作为主键的罪（它实际上比这更糟糕：我们使用VARCHAR（36），而不是UNIQUEIDENTIFIER）。因此，我们结束了索引碎片。他们是这样的：

My company has committed the sin of using GUIDs as Primary Keys on our Azure SQL Database tables (it is actually worse than that: we used VARCHAR(36) instead of UNIQUEIDENTIFIER). As such, we end up with fragmented indexes. They looked like this:

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

我固定通过创建新表的问题。这一次，我用了一个不可改变的，不断增加的DATETIME2（例如CreateTime）列聚簇索引，并且保留了VARCHAR（36）为PRIMARY KEY但这一次，NONCLUSTERED。像这样的：

I "fixed" the problem by creating new tables. This time, I used an immutable, ever-increasing DATETIME2 (e.g. CreateTime) column for CLUSTERED INDEX, and kept the VARCHAR(36) as PRIMARY 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,
)

然后我抄袭行旧表，新表使用INSERT INTO newtable的SELECT * FROM OldTable。最后，我改名表和丢弃的旧的。生活似乎不错。

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

有关我吃惊的是，几个星期后，我发现newtable中有许多零碎的索引，用碎片平均高达80％！即使是IX_NewTable_CreateTime报告碎裂18％。

For my surprise, couple of weeks later, I found out NewTable has many fragmented indexes, with avg fragmentation as high as 80%! Even the IX_NewTable_CreateTime reports fragmentation of 18%.

做的INSERT INTO支离破碎的指数？将重建索引解决问题，好不好？

Did the INSERT INTO fragmented the index? Will REBUILD index solve the problem, for good?

推荐答案

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

Fragmentation will depend on the insert/update frequency on the indexed fields and the size of the Index page.

出于维护目的，你可以使用Azure的自动化，并创建检查索引碎片并优化它的重复脚本。

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

有一个运行手册的画廊只有为：

There's a Runbook in the Gallery just for that:

这个最好的事情是，自动化是免费的，只要你不超过每月500分钟的运行去，你的时间执行好，你将不必支付：）

The best thing about this is that Automation is free as long as you don't go over the 500 running minutes per month, time your executions well and you won't have to pay :)

我做了一个自定义的改进的相册程序，随意使用它也：

I made a custom improvement to the gallery script, feel free to use it too:

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

这篇关于为什么我的Azure SQL数据库索引还是碎片？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！