JSON vs CLR UDT – performance comparison

Ability to create custom user defined types in CLR and use them in SQL Server is introduced in SQL Server 2005. In SQL Server 2016 is added JSON support that might be alternative for storing complex objects. Since JSON is stored as NVARCHAR and CLR is a type, it is questionable what would be better… Read more

JSON parsing is faster than Spatial equivalent

One of the most common assumption when people talk about JSON in SQL Server is that it is slow because it is placed in “plain text” and there is no native type. Instead of the assumption it is better to experiment and compare performance of JSON with some real native type. In this post, I… Read more

JSON parsing 10x faster than XML parsing

Usually, when people talk about the performance of JSON in SQL Server, they are starting with “JSON is not actually a native type, it is just a plain text” and imply that it is probably too slow. It is true that JSON is stored as NVARCHAR and that there is no special type like for… Read more

Demo: Identify and fix plan change regression in SQL Server 2017 RC1

Plan change regression happens when SQL Database changes a plan for some T-SQL query, and the new plan has the worse performance than the previous one. SQL Server 2017 has Automatic Tuning feature that enables you to easily find plan change regressions and fix them. In this post you will see the demo script that you… 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

“You Shall Not Regress!!!” – How SQL Server 2017 prevents plan regressions?

SQL plan regression is a case when a T-SQL query that executed fine becomes much slower or uses more resources because the underlying SQL plan changed. SQL Server 2017 would not allow that kind of regressions if you enable new automatic tuning feature on your database…. Read more