Extended Events – Using Azure Storage

Last year I wrote about Azure SQL Database extended events (https://blobeater.blog/2017/02/06/using-extended-events-in-azure/)  and gave an example where I was capturing deadlocks via the ring buffer. Ever since then I wanted to do a follow-up post but using Azure storage as the target for my XEL files.

This is more complicated than using the ring buffer as the target and requires a couple of things:

  • Azure storage account where you create a dedicated container for the files.
  • SAS key.
  • Database master key.
  • Database scoped credential.

Continue reading

Azure SQL Database and Cost Threshold for Parallelism

We all know that the magic figure for cost threshold for parallelism is 5 by default, meaning if the estimated cost of a query is greater than 5 it may very well generate a parallel plan.

Does this apply to Azure SQL Database? Let’s check.

Continue reading

SQL Server DBCC CHECKDB – Going Parallel

DBCC CHECKDB has the ability to perform parallel checking of objects. However, it absolutely depends on the edition of SQL Server, it only happens when using enterprise edition.

Let’s see this in action. I propose the following tests for this blog post:

  • Test on a SQL Server Enterprise Edition.
  • Test on a non-enterprise edition of SQL Server.

Continue reading

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.

Continue reading

SQL Server – DBCC CHECKTABLE

If you know about DBCC CHECKDB then most likely you will know about DBCC CHECKTABLE. Quite simply this command performs primitive system-catalog consistency checks, per-table consistency checks on the single table specified, and cross-table consistency checks on indexed views that refer­ence the specified table. (Page 899 Microsoft SQL Server Internals 2012, Chapter 14, Page 899, Paul Randal)

Continue reading

Azure SQL Database Compatibility Levels

Let’s start off with a quick overview of SQL Server versions and compatibility levels.

  • 100 = SQL Server 2008 and Azure SQL Database
  • 110 = SQL Server 2012 and Azure SQL Database
  • 120 = SQL Server 2014 and Azure SQL Database
  • 130 = SQL Server 2016 and Azure SQL Database
  • 140 = SQL Server 2017 and Azure SQL Database

So with SQL Server 2017 now available to the public what level is a newly created Azure SQL Database set at?

Continue reading

SQL Server Phantom Reads

With Halloween around the corner what better topic to discuss than phantom reads. A phantom read occurs when rows have been inserted after a read operation and becomes visible in a follow-up read operation within the same transaction.

Continue reading