As a database administrator, managing indexes can be a time-consuming task. However, with the advent of database as a service, such as Azure SQL Database, the process of index management can be automated. In this article, we will explore the concept of automatic indexing in SQL Server and how it can be implemented.

Understanding Automatic Indexing

Automatic indexing is a feature in SQL Server that allows the database engine to automatically create and manage indexes based on query performance. It leverages missing index suggestions, query metrics, and machine learning algorithms to determine the need for indexes and their effectiveness.

By automating the index management process, database administrators can focus on other critical tasks, such as performance tuning and query optimization.

Implementing Automatic Indexing

To implement automatic indexing in SQL Server, you need to follow a few steps:

1. Create or alter the necessary stored procedures that contain the queries you want to optimize.
2. Ensure that the queries have missing index suggestions in their execution plans.
3. Run the stored procedures for an extended period of time to gather query metrics.
4. Allow the database engine to analyze the query metrics and determine the need for indexes.
5. Review the recommendations provided by the database engine and apply the suggested indexes.

By following these steps, you can automate the process of index creation and management in SQL Server.

Example: Automating Automatic Indexing in Azure SQL Database

Let’s take a look at an example of automating automatic indexing in Azure SQL Database. In this scenario, we have a sample database with queries that filter on the FirstName column. However, there is no existing index on this column.

By running the queries for an extended period of time, Azure SQL Database will gather query metrics and analyze the need for indexes. After the analysis, it will provide recommendations for missing indexes.

Here is an example PowerShell script that can be used to automate the execution of the queries:

$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = 'Server=your_server;Database=your_database;trusted_connection=false;user=your_username;password=your_password'

# Load customer names
$DatCmd = New-Object System.Data.SqlClient.SqlCommand
$DatCmd.CommandText = "SELECT c.FirstName, c.EmailAddress
FROM SalesLT.Customer AS c;"
$DatCmd.Connection = $SqlConnection
$DatDataSet = New-Object System.Data.DataSet
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $DatCmd
$SqlAdapter.Fill($DatDataSet)

# Execute stored procedures
$Proccmd = New-Object System.Data.SqlClient.SqlCommand
$Proccmd.CommandType = [System.Data.CommandType]'StoredProcedure'
$Proccmd.CommandText = "dbo.CustomerInfo"
$Proccmd.Parameters.Add("@FirstName",[System.Data.SqlDbType]"varchar")
$Proccmd.Connection = $SqlConnection

$EmailCmd = New-Object System.Data.SqlClient.SqlCommand
$EmailCmd.CommandType = [System.Data.CommandType]'StoredProcedure'
$EmailCmd.CommandText = "dbo.EmailInfo"
$EmailCmd.Parameters.Add("@EmailAddress",[System.Data.SqlDbType]"varchar")
$EmailCmd.Connection = $SqlConnection

$SalesCmd = New-Object System.Data.SqlClient.SqlCommand
$SalesCmd.CommandType = [System.Data.CommandType]'StoredProcedure'
$SalesCmd.CommandText = "dbo.SalesInfo"
$SalesCmd.Parameters.Add("@FirstName",[System.Data.SqlDbType]"varchar")
$SalesCmd.Connection = $SqlConnection

$OddCmd = New-Object System.Data.SqlClient.SqlCommand
$OddCmd.CommandType = [System.Data.CommandType]'StoredProcedure'
$OddCmd.CommandText = "dbo.OddName"
$OddCmd.Parameters.Add("@FirstName",[System.Data.SqlDbType]"varchar")
$OddCmd.Connection = $SqlConnection

while(1 -ne 0)
{
    foreach($row in $DatDataSet.Tables[0])
    {
        $name = $row[0]
        $email = $row[1]
        $SqlConnection.Open()
        $Proccmd.Parameters["@FirstName"].Value = $name
        $Proccmd.ExecuteNonQuery() | Out-Null
        $EmailCmd.Parameters["@EmailAddress"].Value = $email
        $EmailCmd.ExecuteNonQuery() | Out-Null
        $SalesCmd.Parameters["@FirstName"].Value = $name
        $SalesCmd.ExecuteNonQuery() | Out-Null
        $OddCmd.Parameters["@FirstName"].Value = $name
        $OddCmd.ExecuteNonQuery() | Out-Null
        $SqlConnection.Close()
    }
}

By running this script for an extended period of time, you will start to see recommendations for missing indexes in the Azure portal.

Conclusion

Automating automatic indexing in SQL Server can greatly simplify the process of index management. By leveraging missing index suggestions, query metrics, and machine learning algorithms, the database engine can automatically create and manage indexes based on query performance.

By following the steps outlined in this article, you can implement automatic indexing in your SQL Server environment and improve query performance without the need for manual intervention.

For more information on query tuning and indexing, consider attending one of our upcoming events where we offer all-day courses on the topic:

• SQLSaturday NYC on May 18, 2018. Click here to register.
• SQLSaturday Indianapolis on August 10, 2018. Please go here to sign up.
• SQLSaturday Oslo on August 31, 2018. Click here right now to register.

Automating automatic indexing can revolutionize the way you manage indexes in SQL Server. Give it a try and see the performance improvements for yourself!