New Job -> Week 5 -> Create Index Maintenance Scripts

This is part of a series of new job blog posts.  You can find them all here.

Week 5 goal: set up Ola index maintenance for all Azure SQL Databases. This week is about learning Flyway. I’m using Flyway because my company requires this for DDL deployments. I’m setting up a home lab because I don’t want to practice my entry-level Flyway skills on work resources.

Table of Contents

Is it possible to set up an Azure SQL database and index maintenance without all this automation? Yes, you can. This post isn’t about that. This also isn’t a detailed tutorial on how to use all these tools.

TL;DR: If you want the code without explanation, visit my GitHub code repository.

Visit Week 3 to Review the Prerequisites

Tools You Will Want/Need

Get Started

Terraform Components

Creating Resources

Creating Azure SQL Database

Install and Configure Flyway

Now that you have your Azure SQL Server in place (if you followed the week 3 blog post), you can add some SQL objects to it. You could do this manually or with PowerShell. In my case, I chose Flyway because we use that to do all our database deployments.

I chose the community edition CLI because I want to use Flyway in the VSCode terminal. Make sure to add Flyway to your PATH so you can run it from your repository in VSCode. Here’s the official Flyway documentation. Here’s a short video on using Flyway at the command line and here’s another one. It’s quite straightforward with just seven commands: Migrate, Clean, Info, Validate, Undo, Baseline, and Repair.

You will need to make sure you have a flyway.conf file. This will configure Flyway to connect to your database along with some other settings. I put this in my repository which contains what I want to deploy to my database. 

flyway.url=jdbc:sqlserver://servername.database.windows.net;database=db-rg-hopeful-monkey;encrypt=true;trustServerCertificate=false;hostNameInCertificate=*.database.windows.net;loginTimeout=30;
    flyway.user=sqladmin
    flyway.password=password@123!
    flyway.locations=filesystem:./Migrations,filesystem:./Functions,filesystem:./Procedures,filesystem:./Views
    flyway.createSchemas=true
    flyway.encoding=ISO-8859-1
    flyway.sqlMigrationPrefix=V
    flyway.repeatableSqlMigrationPrefix=R
    flyway.sqlMigrationSeparator=__
    flyway.sqlMigrationSuffixes=.sql
    flyway.validateMigrationNaming=true

Flyway will use that conf file to know where to deploy and what settings to use like:

  • flyway.locations You will want to build a folder structure so that Flyway knows what to execute.
    • Migrations are in one folder (this is for tables)
    • Views
    • Procedures
    • Functions
  • sqlMigrationSeparator In this case it’s __ (two underscores). This will be used after the migration prefix and repeatable prefix
  • sqlMigrationPrefix You will prefix the files in your Migration folder with V__ (that’s V with two underscores after it). This will be for tables you want to migrate to your database.
  • repeatableSqlMigrationPrefix You will put R __ (that’s R with two underscores after it). This can be used on views, functions, and procedures. For more info about repeatables, visit this link.

Flyway SQL File Setup

Here’s an example of my folder structure and files:

I have no functions or views right now. I have four repeatable stored procedures. Then I have my migrations folder, which contains a script to create a schema and a table. I want to store all my dba related tables and stored procs in their own schema to keep them separate from the app tables in Azure SQL. 

/* V0001_create_schema.sql */
IF
    NOT EXISTS (
        SELECT *
        FROM sys.schemas
        WHERE name = N'dba'
    )
    EXEC ('CREATE SCHEMA [dba]');
GO

/* V0002_CommandLog.sql */
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dba].[CommandLog]') AND type in (N'U'))
BEGIN
CREATE TABLE [dba].[CommandLog](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[DatabaseName] [sysname] NULL,
	[SchemaName] [sysname] NULL,
	[ObjectName] [sysname] NULL,
	[ObjectType] [char](2) NULL,
	[IndexName] [sysname] NULL,
	[IndexType] [tinyint] NULL,
	[StatisticsName] [sysname] NULL,
	[PartitionNumber] [int] NULL,
	[ExtendedInfo] [xml] NULL,
	[Command] [nvarchar](max) NOT NULL,
	[CommandType] [nvarchar](60) NOT NULL,
	[StartTime] [datetime2](7) NOT NULL,
	[EndTime] [datetime2](7) NULL,
	[ErrorNumber] [int] NULL,
	[ErrorMessage] [nvarchar](max) NULL,
 CONSTRAINT [PK_CommandLog] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
END
GO

NOTE: file encoding can be an issue. This means that if you download or create a SQL file, Flyway may error on it. This may happen even if it runs successfully in SSMS or Azure Data Studio. You can change the encoding in VSCode. To do so, view the file encoding which is shown near the bottom right. Click on it then it will open a prompt at the top to save with encoding. Choose UTF-8 and that should fix your encoding problem.

I also downloaded the other bits I needed from Ola’s website.

  • You always need CommandExecute
  • You will also need IndexOptimize
  • CommandLog is optional, but I like having Ola script execution logged to a table

I’m also adding some other stored procs that I will find useful in my environments:

  • sp_whoisactive – A comprehensive activity monitoring stored procedure. It works for all versions of SQL Server from 2005 through 2019 and Azure SQL DB.
  • usp_FindBrokenReferences – This helps you find broken or invalid objects. An invalid object is a database object referencing another object renamed or deleted.

Once you have the scripts in place, you deploy them at VS Code terminal by executing:

flyway migrate

That’s going to push your SQL into the database you specified in the flyway.conf. We use Azure DevOps pipelines to deploy to Azure SQL databases, but I’m not sure how that works, yet. I’ll cover that in future blog posts.

Run Index Maintenance on a Schedule

Azure SQL doesn’t have an agent, and I find elastic jobs complicated. I’m planning to use an Azure Function with PowerShell to call the Ola stored proc in my database. I’m not sure how to make the Azure Function work yet, so that will be covered in a future blog post. This is a work in progress, so for now, I’ll run the index maintenance manually. We don’t have a ton of data changes, so even if I did it once a week, that would cover it.

To update statistics only:

EXECUTE [dba].[IndexOptimize]
@Databases = 'USER_DATABASES' ,
@FragmentationLow = NULL ,
@FragmentationMedium = NULL ,
@FragmentationHigh = NULL ,
@UpdateStatistics = 'ALL' ,
@LogToTable = 'Y';

To rebuild/reorganize indexes, as needed: 

EXECUTE dba.IndexOptimize
@Databases = 'USER_DATABASES',
@FragmentationLow = NULL,
@FragmentationMedium = 'INDEX_REORGANIZE',
@FragmentationHigh = 'INDEX_REBUILD_ONLINE',
@FragmentationLevel1 = 10,
@FragmentationLevel2 = 35,
@UpdateStatistics = 'ALL',
@Indexes = 'ALL_INDEXES',
@LogToTable = 'Y';

Once I automate it, my plan is to have statistics updated nightly with index maintenance only on weekends. For years, the idea was to not have your indexes fragmented. However, the real gains achieved by a rebuild are the statistics updates that happen along with that. See this Microsoft guidance for more information along with their index maintenance guidance. There is also specific guidance for performing index maintenance in Azure.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.