Azure SQL Database – Optimizing For Ad-hoc Workloads

Today I found out that it is now possible to enable the setting optimize for ad-hoc workloads at the database level when using Azure SQL Database. Traditionally this was always set at the server level for locally based SQL Servers.

Continue reading

Azure SQL Analytics

Azure SQL Analytics is currently in preview mode, still it is very impressive. The goal of this feature is to visualize important SQL performance metrics for your Azure SQL Database. There are a couple of things you need to do first.

  • Setup a Log Analytics workspace.
  • Enable diagnostics for your SQL Databases and/or elastic pools.

Please see the prerequisites section within this document – YOU MUST do this else you will not be able to use this feature. https://docs.microsoft.com/en-us/azure/log-analytics/log-analytics-azure-sql#prerequisites

Once setup it should take approximately 15 minutes to start capturing and rendering back some data. Don’t be surprised if it does take a little longer as was the case for myself.

Continue reading

Moving to an Azure SQL Database via Command Line

I have moved many databases to Azure via different methods but I recently came across a new way. Well technically it’s not new, I should say, newly found. The migration was done via the command line which is not exactly ground breaking but it’s nice to have another option.

The idea behind this is simple. Create the bacpac via command line using sqlpackage.exe with the action as export then do an import action into Azure.

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

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

Azure SQL Database Automatic Tuning – Questions Answered

I used to be on the fence regarding whether or not Automatic Tuning should be on as the default when creating Azure SQL Databases. A part of me never liked the idea of Azure creating/dropping indexes or forcing plans without my prior approval but then again if it happens to do the right thing at the right time then it’s a pleasurable experience.

Continue reading

Query Store – SQL Server 2017 vs Azure SQL Database

I love the query store, it is powerful (can be dangerous) , easy to use and packed full of information. I use it frequently across my local SQL Servers and Azure SQL Database.

Continue reading