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.
I was creating some demo non-clustered indexes in one of my Azure SQL Databases and received the following warning when I executed this code:
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.
Creating copies of your Azure SQL Database is a common and relatively simple process. You can issue a TSQL statement on the master database such as:
CREATE DATABASE CodeDBcopy AS COPY OF CodeDB
Initially, the main focus of this post was going to be quick and about using the latest version of SSMS (SQL Server Management Studio) to check out execution plans for Azure SQL Data warehouse (DW). I got a little side tracked by a certain operation called – SHUFFLE, because, I like the name.
I have come to understand the importance of using columnstore indexes when my queries are aggregating and scanning across many millions of rows.
If I was a wizard I would make these 4 improvements to Azure SQL Database. They are in no particular order, in fact I could come up with about 10 features / improvements that I would like to see but I think 4 will be suffice. Some are more realistic than others, I am sure for some of them I am more likely to get a tiger as a pet.
I was looking forward to do some work with Azure SQL Data Warehouse (DWH) when I hit the new query button and had the following message pop up –
Cannot connect to database when it is paused. (.Net SqlClient Data Provider)
Back in September 2017 Microsoft announced a new security feature for Azure SQL Database called the SQL Vulnerability Assessment (VA). It is currently in preview mode where it has the ability to find, you can guess, security based vulnerabilities for your database such as misconfigurations, excessive permissions, and exposed sensitive data.
Let’s setup a scan. You can find this feature within the settings section of your database.
Times are changing, 10 years ago I would never have thought that self-tuning databases would be available as a packaged product. I was testing out SQL Server 2017 Automatic Tuning recently and I ended up with the following situation. Below shows an image from the query store.