Azure SQL DB support

After identifying a slow-running SQL Server query, ANTS Performance Profiler allows you to view its execution plan. This has been great for on-premise SQL Server, but until now it hasn’t been possible for those using Azure SQL DB, primarily due to a different security model on Azure.

The latest version of ANTS has awareness of the different permissions structure, enabling it to fetch and show you an execution plan from Azure SQL DB. With Microsoft announcing 1.5 million databases in Azure back in October, we hope this capability will be useful to an increasing number of developers.

Query Store

Each time you run a query, SQL Server generates an execution plan and stores it in the plan cache for future reuse. When you look at a plan in ANTS, this is where it is retrieved from. Over time, factors like memory pressure or server restarts leads to plans being removed from the cache, meaning the plan can be unavailable for viewing.

Azure SQL DB v12 and SQL Server 2016 introduce a new feature called Query Store. Microsoft present this as a “Flight data recorder for your database”. After enabling it (you can do this by running ALTER DATABASE MyDatabaseName SET QUERY_STORE = ON), Query Store captures information about queries which are run and how they perform, including their query plans. The general idea is that if performance inexplicably degrades, you’ll be able to understand how the plan has changed. Enrico van de Laar has written about it in depth so I won’t write more here, other than to say that although the visualization capabilities on top of this technology aren’t really there yet, I’m really excited about its future potential.

The new version of ANTS is able to retrieve and display execution plans held in the Query Store (it will continue to look in the cache first, and will only fall back to the Query Store if a plan isn’t available there). Note that Query Store must have be running when your query was originally executed. For those using Query Store, this should provide significant resiliency against the possibility of plans not being available to view.

As usual, any feedback is greatly appreciated – just email dotnetteam@red-gate.com.

Thanks!

Ben