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.

Indexes are Crucial on Azure SQL Database!

One of the issues that seem to come up time and time again, is the lack of a basic index strategy. Many Windows Azure SQL Databases fail under load because they lack indexes.

Continue Reading...

Steps Towards Optimized Azure SQL Databases

Azure SQL Database is a high density multi-tenant database service, whose performance is not guaranteed due to its nature. Thus, it’s imperative that we pay attention to details. This post is all about reviewing the structure and performance of existing Azure SQL Databases by giving you insights into what you should be looking for.

Prior to making any changes to your application, you should look into taking control of your database’s life cycle management by employing tools like the Microsoft SQL Server Data Tools. These tools allow you to target specific platforms like Azure SQL Database, which is very handy because it allows you to identify unsupported features before you deploy. Furthermore, they provide IntelliSense and make it possible to manage database versions by storing scripts in source control solutions like Microsoft Team Foundation Server.

Identify Missing Indexes in Your Windows Azure SQL Database

Windows Azure SQL Database is a Cloud flavored version of SQL Server. As of today (June 29^th 2013), it isn’t 100% equivalent with the SQL Server SKUs that we are used to working with. Microsoft is continually adding new features to Windows Azure SQL Database so don’t get discouraged just yet. In my opinion, many of the "limitations" that are present in Windows Azure SQL Database are actually a good thing, because they force us as developers to use best practices in order to use it properly.

One of these limitations, is that we cannot profile the database by using the SQL Server Profile.

The teams at Microsoft have built an SQL Database Manager that can be access from your Windows Azure Management Portal ( http://manage.windowsazure.com ). Using this tool you can find missing indexes by browsing through the query statistics.

As you can imagine, this can take some time. Consequently, I set out to find a better way to find missing indexes without wasting a lot of time.

I found the following query on the sys.dm_db_missing_index_details (Transact-SQL) MSDN page.
This is an example script to see missing indexes as used by Microsoft Customer Support.

SELECT CONVERT (varchar, getdate(), 126) AS runtime,
       mig.index_group_handle,
       mid.index_handle,
       CONVERT (decimal (28,1),
        migs.avg_total_user_cost *
        migs.avg_user_impact *
        (migs.user_seeks + migs.user_scans))
        AS improvement_measure,
       'CREATE INDEX missing_index_' +
       CONVERT (varchar, mig.index_group_handle) +
       '_' +
       CONVERT (varchar, mid.index_handle) +
       ' ON ' +
       mid.statement +
       ' (' + ISNULL (mid.equality_columns,'') +
       CASE WHEN mid.equality_columns IS NOT NULL
            AND mid.inequality_columns IS NOT NULL
        THEN ','
        ELSE ''
        END + ISNULL (mid.inequality_columns, '') +
        ')' +
        ISNULL (' INCLUDE (' + mid.included_columns + ')',
                '') AS create_index_statement,
        migs.*,
    mid.database_id, mid.[object_id]
    FROM sys.dm_db_missing_index_groups mig
    INNER JOIN sys.dm_db_missing_index_group_stats migs
        ON migs.group_handle = mig.index_group_handle
    INNER JOIN sys.dm_db_missing_index_details mid
        ON mig.index_handle = mid.index_handle
    WHERE CONVERT (decimal (28,1),
                   migs.avg_total_user_cost *
               migs.avg_user_impact *
              (migs.user_seeks + migs.user_scans)) > 10
    ORDER BY migs.avg_total_user_cost *
             migs.avg_user_impact *
         (migs.user_seeks + migs.user_scans) DESC

Windows Azure SQL Data Sync – The Maximum Limit For Index or Statistics Key Column List is 16

I recently used SQL Data Sync to sync a Windows Azure SQL Database with an on-premises database instance hosted in SQL Server 2008 R2. When SQL Data Sync tried to create the schema it threw the following exception.

Database provisioning failed with the exception "SqlException Error Code: -2146232060 – SqlError Number:1904, Message: The index ‘XI_IndexName’ on table ‘dbo.TableName’ has 21 column names in index key list. The maximum limit for index or statistics key column list is 16. " For more information, provide tracing ID ‘802aeb36-fa15-****-****-b8acc99125d3’ to customer support.

Confused as to where this exception came from, looked around and found a few posts (listed below) explaining restrictions for creating Indexes. 

Then I realized that my schema has already been created on Windows Azure SQL Database, so I tried publishing the  schema from my Database Project to my on-premises database instance and for some reason, the error was not raised.

Then I reinitiated the data sync process and was able to complete the initial synchronization.

My guess is that by publishing the schema myself, I was able to relieve SQL Data Sync from trying to create the schema and allowed it to directly start synchronizing the data.

 

More Posts About This Limitation

• Creating Indexes (Database Engine)
• SQL Azure Data Sync error – maximum limit for index key column list is 16?
• Any way around unique index 16 column max
• What are the maximum number of Columns per index key,foreign key,primary key in SQL Server
• SQL SERVER – Maximum Columns per Primary Key – Fix : Error : Msg 1904, Level 16, The index on table has column names in index key list. The maximum limit for index or statistics key column list is 16

Ingesting Massive Amounts of Relational Data With Windows Azure SQL Database (70+ Million records/day)

In a previous post I discussed using Table Valued Parameters and Stored Procedures to insert large amounts of data into Windows Azure SQL Database with reasonable  throttling by the SQL Database. Not long after that post, I rapidly came to the conclusion that it was a great solution for reasonable amounts of data. Then I wrote about using SqlBulkCopy to overcome the limitations imposed by the stored procedure approach and provided the ReliableBulkWriter.

This article will show how to organize a  database to enable the insertion of large amounts of  relational data. To illustrate the changes that were required I will be using the following model.

 

Don’t Forget About Index Maintenance on Windows Azure SQL Database

Granted that Azure SQL Database takes care of a lot of maintenance tasks usually done by DBAs, you should definitely keep an eye on those Indexes. Indexes get fragmented and eat up lots of precious space. Furthermore, fragmented indexes make for slower queries.

I’m currently using the following technique to keep my Indexes healthy so that I may offer a constant end user experience. Furthermore, giving my Indexes regular doses of love prevents my database from growing too fast, which results in drastic cost savings. Throughout my test period, I noticed that fragmented indexes for large amounts of records would eat up close to 4gb of extra data. Once the Indexes are rebuilt, that extra space is recuperated and I am able to push back the dreaded moment where I have to pay for more SQL Database storage.