I have come to understand the importance of using columnstore indexes when my queries are aggregating and scanning across many millions of rows.
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.
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.
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 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 reference the specified table. (Page 899 Microsoft SQL Server Internals 2012, Chapter 14, Page 899, Paul Randal)
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?
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.
So I had a corruption issue and I was thinking about running repair but I wanted to know what would potentially get deleted.
Let’s work through some code to do an encrypted backup. This feature is available to you if you are using SQL Server 2014 onwards but I decided to use SQL Server 2017.
To encrypt during backup, you must specify an encryption algorithm, and an “encryptor” to secure the encryption key. I have decided to use the following options:
- Encryption Algorithm: AES 256
- Encryptor: A certificate
Here is a quick Extended Events script I knocked up where I wanted to track Tempdb file size changes for both the data and log file. I wanted to know who caused the tempdb growth, when it was done, what the T-SQL was and what sizes were involved. Not exactly complicated but hopefully useful.