Azure SQL Database – Using sp_BlitzIndex

I was doing some normal activities on one of my Azure SQL Databases, I went to make a cup of tea and returned to the following message:

The statement has been terminated. Msg 40544, Level 17, State 12, Line 15 The database ‘TestDB’ has reached its size quota. Partition or delete data, drop indexes, or consult the documentation for possible resolutions.

Obviously I have met my size quota. The options given by the message are quite varied. Partitioning is not exactly easy to do straightaway, deleting data is not usually possible and dropping indexes would make sense if I had many duplicate indexes (or un-used ones). The question now is; how do I find duplicate indexes within Azure SQL Database?

The answer is sp_BlitzIndex. You can find this free tool from this link: https://www.brentozar.com/blitzindex/. It is a stored procedure that quickly does a sanity check on your database and diagnoses your indexes major disorders where then it reports back to you and yes it does work on Azure which I didn’t know until today.

So, I re-connect to Azure SQL Database and issue the command.


 SELECT @@VERSION
EXEC dbo.sp_BlitzIndex

azureindex

If you scroll across you will see a More Info column with extra T-SQL to run. For example:

EXEC dbo.sp_BlitzIndex @DatabaseName='TestDB', @SchemaName='dbo', @TableName='Audit';

This is where all the powerful information is held. I have LOADS of duplicates.

Duplicates

Removing these will reclaim disk space. Let’s see the before and after effect.

Under monitoring within the Azure portal you can see the Database Size option. (Okay I know this example is over simplified with the 2GB limit but the concepts stay the same).

presize

I then use the information from sp_BlitzIndex and drop the duplicates.

postsize

I re-run sp_BlitzIndex.

noissues

Brent (and everyone else involved) tells me I have done nice work – I feel good now.

8 thoughts on “Azure SQL Database – Using sp_BlitzIndex

  1. Excellent weblog right here! Also your web site quite a bit up very fast! What host are you using? Can I am getting your associate link to your host? I want my web site loaded up as quickly as yours lol

    Like

  2. You really make it seem so easy together with your presentation but I to find this matter to be really something that I think I might by no means understand. It sort of feels too complex and extremely wide for me. I’m having a look ahead on your subsequent submit, I will try to get the cling of it!

    Like

  3. You actually make it appear so easy together with your presentation however I find this topic to be actually something which I believe I might by no means understand. It sort of feels too complex and very vast for me. I am looking ahead for your subsequent publish, I will attempt to get the dangle of it!

    Like

  4. You actually make it seem so easy along with your presentation however I to find this matter to be actually something which I feel I would by no means understand. It sort of feels too complicated and very huge for me. I am taking a look forward on your next submit, I will try to get the hold of it!

    Like

  5. You really make it seem so easy with your presentation but I to find this matter to be really something which I believe I’d by no means understand. It seems too complex and extremely vast for me. I’m taking a look forward to your next publish, I will try to get the grasp of it!

    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 )

Google+ photo

You are commenting using your Google+ 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 )

w

Connecting to %s