I have come to understand the importance of using columnstore indexes when my queries are aggregating and scanning across many millions of rows.
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.
If you know me by now I like rebuilding indexes and that is no different in Azure. Now we have the ability to resume a paused rebuilding operation rather than cancelling it (Feature currently in public preview). I like this because I have the flexibility to pause it if I feel that it is taking up too much DTU (Database Transaction Unit) usage hence I can free up resources for other operations.
Azure does a lot for your SQL Database, from backups to automatic tuning but it still doesn’t have an index maintenance policy straight out of the box via the portal. Some may not care about rebuilding your indexes but it is still something I like to do, the question is, how can I automate this because I am not a fan of manually running code for index rebuilds.
The answer is via Azure Automation.
They are watching me and my Azure SQL Database and recently I noticed a low impact performance recommendation was made. Naturally I became very interested. Within your database (when in the portal) under operations you may notice something similar to the below:
This is an entry level post and a response to SQLEspresso’s blog challenge(http://sqlespresso.com/2017/01/10/ooops-was-that-was-me-blog-challenge) where we share mistakes from our “younger” days. My post takes me back 11 years and while it is nothing ground breaking I still want to convey what shrinking does to your database. Why? Well it was something that I USED to do. ( Again – I will reinforce the point it was a long time ago)
Well not only does it generate a lot of I/O, consumes CPU but it also affects your fragmentation levels in indexes which is what we will look at today.
These tables and indexes are based from Jonathan’s script found here: https://www.sqlskills.com/blogs/jonathan/enlarging-the-adventureworks-sample-databases/
So I issued the below TSQL where I would expect to see no fragmentation after the rebuild (Index level 0 being the leaf node). Yes the page count is low but it’s the concept I want to talk about.
ALTER INDEX PK_SalesOrderHeaderEnlarged_SalesOrderID ON Sales.SalesOrderHeaderEnlarged REBUILD DECLARE @db_id SMALLINT; DECLARE @object_id INT; SET @db_id = DB_ID(N'AdventureWorks1997'); SET @object_id = OBJECT_ID(N'Sales.SalesOrderHeaderEnlarged'); IF @db_id IS NULL BEGIN; PRINT N'Invalid database'; END; ELSE IF @object_id IS NULL BEGIN; PRINT N'Invalid object'; END; ELSE BEGIN; SELECT * FROM sys.dm_db_index_physical_stats(@db_id, @object_id, NULL, NULL , 'detailed'); END; GO
Now let’s SHRINK. You can actually do this via SQL Server Management Studio.
DBCC SHRINKDATABASE(N'AdventureWorks1997' ) GO DECLARE @db_id SMALLINT; DECLARE @object_id INT; SET @db_id = DB_ID(N'AdventureWorks1997'); SET @object_id = OBJECT_ID(N'Sales.SalesOrderHeaderEnlarged'); IF @db_id IS NULL BEGIN; PRINT N'Invalid database'; END; ELSE IF @object_id IS NULL BEGIN; PRINT N'Invalid object'; END; ELSE BEGIN; SELECT * FROM sys.dm_db_index_physical_stats(@db_id, @object_id, NULL, NULL , 'detailed'); END; GO
99.85% avg_fragmentation, in essence the order has been reversed.
Ok, for a one-of activity I don’t mind you could just sort out the fragmentation afterwards, but to do it as part of a maintenance routine, not the best option out there. SHAME ON ME!
I know there are people out there that will be going from older versions of SQL to SQL Server 2016, yes it makes sense it is a great product with a lot of new features such as Query Store, Stretch DB to name but a few.
However don’t forget about the features that were available in 2014 that are naturally available in 2016, such as the ability to control blocking behaviors when rebuilding indexes.
Classically when you want to use the ONLINE mode of rebuilding indexes the code would look like:
ALTER INDEX [MCD] ON [dbo].[mcdaol] REBUILD WITH(ONLINE = ON)
Looking into the locking you will see that ONLINE operation uses (Sch-M) on the corresponding table as part of the process (actually takes Shared Table Lock (S) at the beginning of the operation, and a Schema Modification Lock (Sch-M at the end)).
So to be granted a SCH-M lock you can’t have any conflicting locks, so what happens when / if you have a process that is updating the table and you want to use the ONLINE rebuild? Yes you will be blocked. With 2014 onwards we can control what happens if we get into this situation and for this post I am going to abort the other query causing me to wait.
Let’s see what happens.
I have 1 window running an update such as:
— Open Trans
BEGIN TRAN UPDATE [dbo].[p_dinner] SET og_status = 1 WHERE og_status = 10
In a different window I am running the Index rebuild:
ALTER INDEX [p_dinner_idx] ON [dbo].[p_dinner] REBUILD WITH(ONLINE = ON)
Naturally this will be blocked.
So now we can control what happens here, for this example I want to kill the connection holding the locks that are conflicting with my rebuild i.e. the update (just an example).
So I run:
ALTER INDEX [p_dinner_idx] ON [dbo].[p_dinner] REBUILD WITH ( ONLINE = ON ( WAIT_AT_LOW_PRIORITY ( MAX_DURATION = 1, ABORT_AFTER_WAIT = BLOCKERS ) ) ) GO
From the above image you can see after 1 minute (MAX_DURATION) the query successfully executes because it killed the connection causing the blocking, which is done via the ABORT_AFTER_WAIT = Blockers clause.