clean

Rebuilding SQL Database Indexes

A few months ago I wrote a blog post titled “Don’t Forget About Index Maintenance on Azure SQL Database“. Since then, Microsoft Azure SQL Database has changed a lot. We aren’t as concerned about the size of the database anymore, because databases can reach 500 GB in size. Take a moment to think about that number. 500GB is a lot of data! Before you get excited and move on to more important things, ask yourself this question, does all that data really belong in my SQL Database? Put some thought into it, you may be surprised by the answers you come up with.

So if we’re not as concerned about size, why should we care about index fragmentation. Well, if indexes aren’t maintained, your application will slow down. Queries may timeout and users may look for alternatives. Rebuilding indexes is something we overlook on a regular basis, along with missing indexes, it can really harm the overall performance of an application.

Since I started automating everything, I decided it was time to revamp the console application from my earlier post. I reused bits from a couple of blog posts and produced something that will make my life easier. The following PowerShell script is a mix of C# and PowerShell. It takes in the Server name, the Database name, the Username and the Password to build its execution context.

Start-SqlDatabaseIndexRebuild  -Server '' -Database '' -User '' -Password ''

Optionally you can override the default Fragmentation Percentage Threshold of 10%. Ideally, you don’t want to rebuild costly indexes too often, but you don;t want them to get out of hand either. Since you cannot REORGANIZE indexes on SQL Database, I tent to rebuild earlier than we’re used to on SQL Server.

Use this script to rebuild indexes on your Microsoft SQL Databases, it will skip SQL Data Sync tables and will inspect User Tables for fragmented indexes. Feel free to adapt it to your needs. This code is available on my Azure PowerShell GitHub Repository.

<#
.Synopsis
  This will scan a Microsoft Azure SQL Database for Indexes whose Index fragmentation is greater than the defined threshold percentage and will rebuild them sequentially.
.DESCRIPTION
   Be sure to rebuild fragmented indexes on a regular basis on Azure SQL Database. Fragmentation increases Index size and results in slower queries.
.EXAMPLE
   Start-SqlDatabaseIndexRebuild -Server '' -Database '' -User '' -Password ''
.EXAMPLE
   Start-SqlDatabaseIndexRebuild  -Server '' -Database '' -User '' -Password '' -FragmentationPercentageThreshold 10
#>
Function Start-SqlDatabaseIndexRebuild
{
  [CmdletBinding()]
  Param
  (
    # SQL Database Server
    [Parameter(Mandatory=$true, ValueFromPipelineByPropertyName=$true, Position=0)]
    [string]
    $Server,

    # SQL Database Name
    [Parameter(Mandatory=$true, ValueFromPipelineByPropertyName=$true, Position=1)]
    [string]
    $Database,

    # SQL Database Username
    [Parameter(Mandatory=$true, ValueFromPipelineByPropertyName=$true, Position=2)]
    [string]
    $User,

    # SQL Database Password
    [Parameter(Mandatory=$true, ValueFromPipelineByPropertyName=$true, Position=3)]
    [string]
    $Password,

    # Fragmentation Percentage Threshold used to select indexes to defragment
    [Parameter(Mandatory=$false, ValueFromPipelineByPropertyName=$true, Position=4)]
    [int]
    $FragmentationPercentageThreshold = 10,

    # A flag used to skip tables created by Azure SQL DataSync
    [Parameter(Mandatory=$false, ValueFromPipelineByPropertyName=$true, Position=5)]
    [bool]
    $SkipSqlDataSyncTables = $true
  )

  $assemblies = (
    'System.Data',
    'System.Collections',
    'System.Data.DataSetExtensions',
    'System.Xml',
    'System.Linq'
  ) 

  $source = @'
    using System;
    using System.Linq;
    using System.Collections;
    using System.Collections.Generic;
    using System.Data;
    using System.Data.SqlClient;
    using System.Globalization;

    public class DbContext
    {
        public string Server { get; set; }
        public string Database { get; set; }
        public string User { get; set; }
        public string Password { get; set; }

        private string ConnectionString
        {
            get
            {
                return String.Format(CultureInfo.InvariantCulture,
                                     "Server=tcp:{0}.database.windows.net,1433;Database={1};User ID={2}@{0};Password={3};Trusted_Connection=False;Encrypt=True;Connection Timeout=3600;",
                                     Server,
                                     Database,
                                     User,
                                     Password);
            }
        }

        const string listTablesQuery = @"SELECT o.name AS [table_name]
                                            FROM  sys.objects AS o
                                            WHERE o.is_ms_shipped = 0 AND o.[type] = 'U'";
        public List GetTableNames()
        {
            return Query(listTablesQuery)
                        .AsEnumerable()
                        .Select(r => r[0].ToString())
                        .ToList();
        }

        const string findFragmentedIndexesQuery = @"SELECT name, avg_fragmentation_in_percent
                                                    FROM sys.dm_db_index_physical_stats (
                                                            DB_ID(N'DatabaseName')
                                                            , 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
                                                    WHERE avg_fragmentation_in_percent > {1}";

        public Hashtable GetFragmentedTableIndexes(string table, int fragmentationThreshold)
        {
            return ToIndexTableMap(table, Query(string.Format(CultureInfo.InvariantCulture,
                findFragmentedIndexesQuery,
                table,
                fragmentationThreshold)));
        }

        private static Hashtable ToIndexTableMap(string table, DataTable indexes)
        {
            var t = new Hashtable();
            foreach (var i in indexes.AsEnumerable())
                t.Add(i[0],i[1]);    

            return t;
        }

        private DataTable Query(string query)
        {
            using (var connection = new SqlConnection(ConnectionString))
            {
                connection.Open();

                var command = new SqlCommand(query, connection);
                var reader = new SqlDataAdapter(command);
                var table = new DataTable();
                reader.Fill(table);
                return table;
            }
        }

        const string rebuildIndex = "ALTER INDEX [{0}] ON [{1}] REBUILD WITH (ONLINE=ON);";

        public int RebuildIndex(string indexName, string table)
        {
            return Command(string.Format(CultureInfo.InvariantCulture, rebuildIndex, indexName, table));
        }

        private int Command(string cmd)
        {
            using (var connection = new SqlConnection(ConnectionString))
            {
                connection.Open();

                var command = new SqlCommand(cmd, connection);
                return command.ExecuteNonQuery();
            }
        }
    }
'@

  Add-Type -Language CSharp -TypeDefinition $source -ReferencedAssemblies $assemblies

  $db = New-Object DBContext
  $db.Server = $Server
  $db.Database = $Database
  $db.User = $User
  $db.Password = $Password    

  Write-Verbose 'Connecting'

  $tables = $db.GetTableNames()

  Write-Verbose $('Found ' + $tables.Count + ' Tables')

  $percent = 0
  $tableIndex = 0

  foreach($t in $tables){

    if($SkipSqlDataSyncTables -and $t.Contains('dss'))
    {
      Write-Warning $('Skipping  Table ' + $t)
      continue
    }

    $indexes = $db.GetFragmentedTableIndexes($t, $FragmentationPercentageThreshold)
    foreach($i in $indexes.Keys)
    {
      $message = 'Successful'
      try
      {
        $result = $db.RebuildIndex($i,$t)
      }
      catch
      {
        $message = 'Failed'
        Write-Warning $("Exception Message: $($_.Exception.Message)")
      }
      finally
      {
        Write-Verbose $($message + ' Rebuild > Table '+ $t+ ' Index ' + $i + ' fragmented @ '+ $indexes[$i] + '%')
      }
    }
    $tableIndex ++
    $percent = ($tableIndex/$tables.Count) * 100
    Write-Progress $t -PercentComplete $percent
  }
}

4 responses to Using PowerShell to Rebuild #Azure SQL Database Indexes

  1. 

    This looks exactly like what I want. I’m clearly not understanding how to execute the script, though. I’m in a Powershell window, change to the directory with the ps1 file and when I execute the command line in this blog post, it just stops immediately and I don’t get any error or other information displayed.

    Like

Trackbacks and Pingbacks:

  1. Dew Drop – August 19, 2014 (#1837) | Morning Dew - August 19, 2014

    […] Using PowerShell to Rebuild #Azure SQL Database Indexes and #PowerShell Module for the #Azure API Management REST APIs (Alexandre Brisebois) […]

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s