for Application Developers
In this article, I am gonna discuss one of the Query Store’s most important parameters – QUERY_CAPTURE_MODE. The QUERY_CAPTURE_MODE parameter defines which queries will be captured and later stored by Query Store. In SQL Server 2016 and 2017, you can choose from three options: ALL, AUTO i NONE. In SQL Server 2019, Microsoft added a […]
I wanted to write an article about the interleaved execution, but what you’ll see here is a good illustration of the improvements in SQL Server database engine since the version SQL Server 2012. As you might know, the Interleaved Execution is the member of the Intelligent Query Processing family of features. It has been introduced […]
In this short post, I will demonstrate (for me) an unexpected behavior of one query hint. In August 2018, Microsoft introduced a new query hint QUERY_OPTIMIZER_COMPATIBILITY_LEVEL_n to let you force another query optimizer behavior at a query level. The query will be processed as if was compiled with the compatibility level given in the hint, […]
SQL Server 2019 provides another option to deal with the last-page insert contention – OPTIMIZE_FOR_SEQUENTIAL_KEY. It improves throughput for high-concurrency inserts into an ascending clustered index. Unlike the solutions dealing with hash keys, partitioning or reverse indexing, this one does not change the nature of the clustered key – it remains ascending – and thus […]
In my previous post about Query Store, you saw that the persistence of forced plans does not imply that they are used. This post brings another example where forced plans are still in the system, with no failures, but they are not applied against the desired query. For this demo, we need a database that […]
In this article, I will demonstrate a small change in the Cardinality Estimator component under the latest compatibility level – 150 (SQL Server 2019). Let’s first create and populate a sample table: DROP TABLE IF EXISTS dbo.T; CREATE TABLE dbo.T( id INT IDENTITY(1,1) NOT NULL, c1 INT NOT NULL, c2 INT NULL, c3 CHAR(5) DEFAULT […]
Query Store is a great performance troubleshooting tool. I like it; in the past three years, it saved me a lot of time and money. It allows you to quickly identify exec plan regressions. By forcing an old plan, you can almost instantly implement a workaround for the issue and buy a time necessary for […]
When you look at articles, posts and documents about new features and enhancements in SQL Server 2019 CTP2, you will find nothing about Query Store. However, there are some graphical enhancements in SQL Server Management Studio in the version 18.0, also default configuration for Query Store attributes is changed too. First SSMS 18.0. From this […]
As part of the SQL Saturday Linz conference, I will have a performance tuning workshop on 17th January in Tabakfabrik in Linz. The full workshop name is Performance and Query Tuning with SQL Server 2017 and 2019. As the name suggests, the workshop covers performance tuning techniques specific for two recent SQL Server versions. It […]
I’m very glad that I will present again at Microsoft Sinergija 18 conference in Belgrade on October 25th 2018. Microsoft Sinergija 18 will be held from October 24 through 26 at the Crowne Plaza hotel in Belgrade under the slogan “Digital breakthrough”. This time, my topic is related to cardinality estimation issues, and I will […]
SQL Server 
Recent Comments