Thursday, July 20, 2017
Updates from Storage Engine Team @ Microsoft
Facebook Twitter LinkedIn Google+ Addthis

Get Aggregated RSS

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

Thursday, July 20, 2017

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

Tuesday, July 18, 2017

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

Public Preview of Compatibility Level 140 for Azure SQL Database

Friday, July 07, 2017

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

Monday, July 03, 2017

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

Tuesday, June 13, 2017

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. Databases... Read more

What is plan regression in SQL Server?

Friday, June 09, 2017

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. Setup First I’m going to create a... Read more

Use WITH clause in OPENJSON to improve parsing performance

Monday, May 29, 2017

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. I have found some examples where people using OPENJSON... Read more

Automatic plan correction in SQL Server 2017

Wednesday, May 17, 2017

Automatic plan correction is a new automatic tuning feature in SQL Server 2017 that identifies SQL query plans that are worse than previous one, and fix performance issues by applying previous good plan instead of the regressed one. When you need a plan correction? SQL Server can use different strategies (or SQL plans) to execute... Read more

Automatic index management in Azure SQL database

Tuesday, May 16, 2017

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?

Tuesday, April 25, 2017

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. What is plan regression? Imagine that you execute the... Read more

Copyright [2014] by SQLCOMMUNITY.COM