If you decide to use IP addresses to control what services have access to your Azure SQL Database, then understanding firewall rules are important. Within this important area you have the ability to create firewall rules that are scoped to the database or server level as shown below.
One of the features Microsoft wants us to use for Azure SQL Database is Automatic Tuning. Automatic Tuning is a feature where you can think of it as entering the world of self-running and self-tuning databases.
It is said to be safe, reliable and proven using complex algorithms and built-in intelligence where it can do the following (see this link for more details: https://docs.microsoft.com/en-us/azure/sql-database/sql-database-automatic-tuning)
- CREATE INDEX – identifies indexes that may improve performance of your workload, creates indexes, and automatically verifies that performance of queries has improved.
- DROP INDEX – identifies redundant and duplicate indexes daily, except for unique indexes, and indexes that were not used for a long time (>90 days). Please note that at this time the option is not compatible with applications using partition switching and index hints.
- FORCE LAST GOOD PLAN – identifies SQL queries using execution plan that is slower than the previous good plan, and queries using the last known good plan instead of the regressed plan.
Forget about Adaptive Query Processing for a minute, what other feature have I been waiting for? SELECT INTO a specific filegroup, not the default filegroup! I have needed this feature many times in the past. Let’s take a look at it using the WideWorldImportersDW database.
So what is the default isolation level for Azure SQL Database? I ran the following code to check it out.
I do not always use the Azure portal to make database changes or to check for certain information. I use it a lot of for blogging purposes but for some tasks I rather just run code via SSMS – SQL Server Management Studio.
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.
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.