SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

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.


Filed under: Azure, Azure SQL DB Tagged: Azure, Azure SQL DB, Brent Ozar, Indexes, sp_BlitzIndex, TSQL

All About SQL

I am a Senior DBA with interest in MS technology especially SQL Server and Azure. During 2015 I was mentored by Paul Randal – Data Platform (SQL Server) MVP and during 2016 I completed my SQLskills Immersion training on Internals and Performance Tuning. When I am not working I am in the gym burning calories.

Comments

Leave a comment on the original post [blobeater.blog, opens in a new window]

Loading comments...