Tag Archives: Indexes

Azure SQL Database Clustered Columnstore Index

Posted on by
2

You can read about columnstore indexes here (https://azure.microsoft.com/en-gb/blog/transforming-your-data-in-azure-sql-database-to-columnstore-format/). I won’t rehash the material but high level, these index types are optimized for analytical queries and high compression of data (up to 100x). This format is perfect for the large data sets that can be efficiently compressed using this format and analytical queries with complex calculations that use subset of the table columns.

Continue reading →

Advertisement

Azure SQL Database – Using sp_BlitzIndex

Posted on by
8

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.

Continue reading →

Azure SQL Database Resumable Online Index Rebuild

Posted on by
3

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.

Continue reading →

Azure Robots – Index Performance Recommendation

Posted on by
2

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:

Continue reading →

I used to shrink my databases!

Posted on by
1

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

shrink

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.

shrinkafterOk, 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!

ALTER INDEX….ABORT!

Posted on by
2

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

lock1

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.

lock2

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

lock4

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.