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

Monitoring automatic tuning actions using XEvents

SQL Server 2017 can automatically tune your queries by identifying and fixing SQL plan change regressions. SQL Server tracks last known good plans for each query, and if the plan for the query changes, last know good plan will be used if performance of the new plan is worse than the performance of the last… 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

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

“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

Introducing Interleaved Execution for Multi-Statement Table Valued Functions

For SQL Server vNext and Azure SQL Database, the Microsoft Query Processing team is introducing a new set of adaptive query processing improvements to help fix performance issues that are due to poor cardinality estimates. Improvements in the adaptive query processing space include batch mode memory grant feedback, batch mode adaptive joins, and interleaved execution. … Read more

Introducing Batch Mode Adaptive Joins

For SQL Server 2017 and Azure SQL Database, the Microsoft Query Processing team is introducing a new set of adaptive query processing improvements to help fix performance issues that are due to inaccurate cardinality estimates. Improvements in the adaptive query processing space include batch mode memory grant feedback, batch mode adaptive joins, and interleaved execution. … Read more