Default compatibility level 140 for Azure SQL databases

As of this writing, the default compatibility level for new databases created in Azure SQL Database is 130. Very soon, we’ll be changing the Azure SQL Database default compatibility level for newly created databases to 140. The alignment of SQL versions to default compatibility levels are as follows: 100: in SQL Server 2008 and Azure… Read more

Public Preview of Compatibility Level 140 for Azure SQL Database

Today we are announcing the official public preview of compatibility level 140 in Azure SQL Database. Compatibility level 140 enables the following query optimizer changes: A trivial plan referencing Columnstore indexes will be discarded in favor of a plan that is eligible for batch mode execution. The sp_execute_external_script UDX operator is eligible for batch mode… Read more

“What are you waiting for?” – Introducing wait stats support in Query Store

Troubleshooting waits previously. Nobody likes to wait. SQL database is multithreaded system that can handle thousands of queries executed simultaneously. Since queries that are executed in parallel compete for the same resources (tables, memory, etc) they might need to wait for the resources to be available to proceed with execution. These cumulative waits can be… Read more

Azure SQL databases in logical servers, elastic pools, and managed instances

Azure SQL Database is a Database as a Platform service designed for applications that will use database as self-contained service. Databases can be grouped together to simplify management options or share the resources. There are different options that can be used to bound databases in the groups, which will be explained in this post…. Read more

What is plan regression in SQL Server?

Plan regression happens when SQL Server starts using the sub-optimal SQL plan to execute some T-SQL query. Usually you will see that some T-SQL query is executing really fast, but then it gets slower without any obvious reason. In this post you will see how can plan regression happen…. Read more

Use WITH clause in OPENJSON to improve parsing performance

OPENJSON function has a WITH clause where you can specify what fields should be extracted from input JSON. This might improve performance of your queries compared to the case where you use OPENJSON without schema and later extract information from the parsed JSON using JSON_VALUE function…. Read more

Automatic index management in Azure SQL database

Azure SQL Database has a built-in intelligence mechanism that can automatically tune and improve performance of your queries by dynamically adapting the database schema to your workload. In Azure SQL Database, you don’t need to worry about the index analysis and design, because Azure SQL Database learns about your workload and ensures that your data… Read more

Comparing performance of data access libraries using StackExchange/Dapper benchmark

One of the most important questions that you need to answer in your projects is what data access library you should use to access your data in SQL Server database. One of the benchmarks that you can use is StackExchange Dapper benchmark that checks how fast could different data access libraries fetch a single row from… Read more

SQL Server 2016 Developer Edition in Windows Containers

We are excited to announce the public availability of SQL Server 2016 SP1 Developer Edition in Windows Containers! The image is now available on Docker Hub and the build scripts are hosted on our GitHub repository. This image can be used in both Windows Server Containers as well as Hyper-V Containers. SQL Server 2016 Developer… Read more