Azure SQL Database – Who Changed My Auto Tuning Settings?

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)

  1. CREATE INDEX – identifies indexes that may improve performance of your workload, creates indexes, and automatically verifies that performance of queries has improved.
  2. 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.
  3. 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.

Continue reading

Azure SQL Elastic Pools – Moving Databases

If you read official Microsoft documentation about naming conventions for your SQL elastic pools, it is hard to find any guidelines on the subject. You are probably thinking it is not that big of a deal. I thought the same until I tried to execute some TSQL to move a database into an elastic pool.

Continue reading

SQL Server 2017 – SELECT INTO [A SPECIFIC FILEGROUP]

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.

Continue reading

Linked Server to Azure SQL Database

You may (or may not) have a requirement to setup a linked server to Azure SQL Database from a locally installed SQL Server. One reason could be to pull down some reports from an Azure SQL Database to a local file share. Whatever your reason is hopefully you will find this blog post useful because I ran into some complications on the way.

Continue reading

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