Azure SQL Database – Using sp_BlitzIndex

Posted on by

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”

  3. 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

    Reply ↓

  4. 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

    Reply ↓

  5. 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

    Reply ↓

  6. 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

    Reply ↓

  7. 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

    Reply ↓

Leave a Reply