Category - SQL Server

General about SQL Server

Speaking at IT-Tage 2019 Conference in Frankfurt

I am honored to have been selected to speak at one of the largest database conferences in the German-speaking area “IT-Tage 2019” in Frankfurt. The 3-day conference will be held from 10th to 12th December 2019, with a pre-con day on 9th December.

itt1I’ll be presenting features within Intelligent Query Processing in SQL Server 2019. The session will cover briefly all these features: Batch Mode on Rowstore, Batch and Row Mode Memory Grant Feedback, Scalar UDF Inlining, Table Variable Deferred Compilation, and Approximate Query Processing. If time permits (session takes 45 minutes only), I’ll mention Batch Mode Adaptive Join and Interleaved Execution.

There are 160 sessions on the three conference days, the conference agenda you can see here.

See you in Frankfurt!

Slovak SQL Server & BI Meeting on 19th November

Next week I’ll be back in Bratislava. I have the honor to present at the Slovak SQL Server & BI User Group‘s meeting. This time, I am gonna talk about developer’s features in SQL Server 2019.

BratislavaWe’ll go through all important new features: Intelligent Query Processing, Memory-Optimized TempDB Metadata, Accelerated Database Recovery, Optimize for Sequential Key, SQL Graph and other “small” database engine and programming enhancements.  After the session, attendees should have a clue, which of new features would bring benefits for their workloads, and where and why they could have regressions and how they can mitigate them.

You can use this link to register for the event. Uvidíme sa v Bratislave!

Miracle of OPTIMIZE_FOR_SEQUENTIAL_KEY

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 it does not affect business logic at all.
To get exhaustive information about this feature (motivation and implementation details), I would recommend the excellent article Behind the Scenes on OPTIMIZE_FOR_SEQUENTIAL_KEY, written by Pam Lahoud, Program Manager with SQL Server Engineering Team. In this post, I will share with you some very exciting (and surprising) test results.

I used the following code to create and populate sample tables and create procedures. To compare the execution times for the INSERT statements, I used Query Store with the default settings, except for the INTERVAL_LENGTH_MINUTES attribute – just to easily compare the runtime stats.

ALTER DATABASE OptSeqKey SET QUERY_STORE =ON (INTERVAL_LENGTH_MINUTES = 5);

To produce a last page contention friendly workload, I used the ostress tool. I ran the following commands with about 5 minutes interval distance. By taking this approach, I can easily analyze wait stats – all entries I’ll find there belongs to a specific testing scenario. Here are my test scenarios:

  • First test: calling the P1 stored procedure that performs 50 inserts into a “normal, non-optimized” disk table with an ascending clustered key with 300 parallel sessions.

    .\ostress -E -dOptSeqKey -Q"EXEC dbo.P1" -SMySQL2019Server -r100 -n300 -q
  • Second test: calling the P2 stored procedure that performs 50 inserts into a table optimized for sequential key, with an ascending clustered key with 300 parallel sessions.

    .\ostress -E -dOptSeqKey -Q"EXEC dbo.P2" -SMySQL2019Server -r100 -n300 -q
  • Third test: calling the P3 stored procedure that performs 50 inserts into each of the two tables, with 300 parallel sessions.

    .\ostress -E -dOptSeqKey -Q"EXEC dbo.P3" -SMySQL2019Server -r100 -n300 -q

That means, the first test inserts 1.5M rows into the normal table, the second 1.5M rows into the optimized table, while the third inserts 3M rows (1.5M rows in each table). Now, the exciting part – results.

In my Query Store, the plan with the plan_id of 1 inserted a row into the normal table, the plan with the id of 5 was used to insert into the optimized table. The following query returns the average execution time for two inserts statements:

SELECT plan_id, runtime_stats_interval_id, count_executions, CAST(avg_duration AS INT) avg_duration
FROM sys.query_store_runtime_stats WHERE plan_id IN (1, 5) ORDER BY runtime_stats_interval_id, plan_id;


BlogPost1

And here are results in chart format:

BlogPost2

As you see, the average execution time for a single INSERT (the first blue bar, 16.5 ms) is very high with 300 parallel sessions, I definitely hit the contention. When I ran the same workload against the table with the OPTIMIZE_FOR_SEQUENTIAL_KEY turned to ON, the throughput looks better – the average execution time (the first orange bar) is reduced about 28%. This is what we expected (from Microsoft documentation), and it shows that this feature helps, with contention convoys.

I tested it also with different number of parallel sessions, and here are results of one my tests in tabular and chart format:

BlogPost3


BlogPost4.png

The results show that the feature should be used for tables, where latch convoys already happen or are about to happen. In a not-so-heavy workloads, it brings a small overhead. You can see that inserts with 100 parallel sessions are 16% slower for a table with the optimized sequential key. However, when you hit the convoy, the feature definitely helps.

Let’s analyze now waits captured by Query Store. The following figure shows waits for the first two tests, respectively:

BlogPost5.png

As we assumed, we hit the convoy; the wait stats shows clearly that inserts into a normal table were waiting for resources from the Buffer Latch category. From the lower part of the figure, we can find out that the BTREE_INSERT_FLOW_CONTROL wait type seems to be categorized as Unknown. Nevertheless, the overall results are better.

All mentioned results and numbers are more-less expected. What I definitely did not expect are significant improvements for inserts into the optimized table in my third test, where I executed both procedures. With this tests, inserts into a normal table behaved similar to the first test, where we inserted in the normal table only (16.5 ms vs. 17.1ms). However, inserts into the optimized table were significantly faster – the orange bar is 6x smaller than the blue one (11.9ms vs. 2.7ms)! When we compare the average execution time for the insert with the initial execution time for non-optimized table, we see an incredible throughput improvement of 84%!

When we look into the Query Store, we can see that BTREE_INSERT_FLOW_CONTROL is significantly reduced compared to the case where we ran only optimized inserts:

BlogPost6.png

EDIT:

It seems I was too excited about the feature and wanted to believe that in a mixed contention workload, the code that deals with latch convoys in the optimized for sequential key table somehow interferes with the poor latch contention in a non-optimized table. However, the numbers in the third tests were too good to be fully true. The numbers are correct, but in the 3rd test, where both inserts are invoked, the insert in the normal table introduces a delay to the second call, therefore reduces the frequency of the incoming calls that insert into the optimized table (as the opt_for_seq_key does for the incoming threads). The exec time for the second INSERT dropped because the incoming calls are slowed down, and this time is, of course, not taken in these numbers.

However, all my tests with 200+ parallel sessions on the 8-core machine for different tables clearly show that inserts into a table with the optimized key are 20-30% faster.

Thanks for reading!

Dude, Where’s My Forced Plan?! – Part 2

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 is a part of the availability group. Within a database we need only one sample table, one user table type, and one stored procedure:

DROP TABLE IF EXISTS dbo.T;
CREATE TABLE dbo.T(
id INT IDENTITY(1,1) NOT NULL,
c1 INT NOT NULL,
c2 INT NULL,
CONSTRAINT PK_T PRIMARY KEY CLUSTERED (id)
);
GO
INSERT INTO dbo.T(c1)
SELECT 1 + ABS(CHECKSUM(NEWID())) % 10000
FROM dbo.GetNums(100000);
GO


CREATE TYPE dbo.Tvp AS TABLE(
Id INT NOT NULL PRIMARY KEY CLUSTERED
)
GO
CREATE OR ALTER PROCEDURE dbo.GetList
(@tvp AS dbo.Tvp READONLY)
AS
SELECT t.*
FROM dbo.T t
INNER JOIN @tvp tvp ON t.id=tvp.Id;
GO

Let’s ensure that Query Store is empty and turned on:

ALTER DATABASE A SET QUERY_STORE CLEAR;
GO
ALTER DATABASE A SET QUERY_STORE = ON (QUERY_CAPTURE_MODE = AUTO);
GO

Now, we can execute our sample query multiple times:

DECLARE @t AS dbo.Tvp;
INSERT @t SELECT TOP (10) id FROM dbo.T ORDER BY 1 DESC;
EXEC dbo.GetList @t;
GO 3

The execution is fast, with a Nested Loop join based execution plan. Let’s suppose we want to be sure that this plan will be used for all further calls, and we want to achieve this by forcing the plan. This is not always a good idea, but in this demo, we want to have any forced plan. When we execute the query again, we should see in Query Store something like this:

post1
The plan forcing is respected, all fine.

One of the main advantages of Query Store is that data belongs to the database. Thus, it should survive server crashes and failovers. In case of a failover, we should see a similar screen when we run the query on another server, which takes the role of the primary server after the failover. After the failover, I executed the same query again (against a new primary):

DECLARE @t AS dbo.Tvp;
INSERT @t SELECT TOP (10) id FROM dbo.T ORDER BY 1 DESC;
EXEC dbo.GetList @t;
GO 3

When I looked at the Query Store report, I saw no new executions for this query:

post2.png

I checked then the sys.query_store_query DMV and figured out, why these executions are not captured:

SELECT * FROM sys.query_store_query WHERE query_hash = (SELECT query_hash FROM sys.query_store_query WHERE query_id = 35);



post3

Query Store has instantiated new query_id (57) for my query, and executions are associated to it:

post21

Let’s look closer at the batch_sql_handle attribute. In the following figure, you can see the only difference:

post4.png

The selected number are hex numbers with value of 20 and 16 respectively. Where is this number coming from? This is the database_id attribute. And yes, in this example, my databases have different database_ids:

post5

We have again a situation where query_id has been changed for a query that we did not touch at all, this time because of different database_id attributes on primary and secondary.

Conclusion

When you force a plan for a query that uses table variables or table-valued parameters, you have to check frequently whether it is still in use, since the uniqueness of query_id is very weak and can be affected even if you did not change the code.

Thanks for reading.

SQL Server 2019 CE, Local Variables and Nullable Columns

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 'test',
CONSTRAINT PK_T PRIMARY KEY CLUSTERED (id)
);
GO
CREATE INDEX ix1 ON dbo.T(c1);
GO
CREATE INDEX ix2 ON dbo.T(c2);
GO
INSERT INTO dbo.T(c1)
SELECT 1 + ABS(CHECKSUM(NEWID())) % 10000
FROM dbo.GetNums(1000000);
GO

--update the c2 column to not null for about 5% rows
UPDATE dbo.T SET c2 = c1 WHERE c1 >= 9500;
GO

Ensure that the database runs under the compatibility level 140 and consider the following simple query with a local variable:

ALTER DATABASE New SET COMPATIBILITY_LEVEL = 140;
GO
DECLARE @v INT = 600000;
SELECT * FROM dbo.T WHERE c1 = @v;
Here is its execution plan:

e00
This is a very known situation – an estimation for an equality predicate with local variable. The estimated number of rows is calculated as the All density attribute from the stats density vector multiplied by the Rows from the stats header.

e02


SELECT 0.00010002*1000000;
--100.02000000

The c1 column does not allow NULLs. What would be the estimation for a nullable column? The c2 column in our table is nullable. Let’s have a check:

DECLARE @v INT = 600000;
SELECT * FROM dbo.T WHERE c2 = @v;


e01

The estimation is significantly higher and even lead to a plan with Clustered Index Scan! However, it follows the same formula for the estimation. Let’s have a look at the statistics object:

e03

By using the same formula, the estimated number of rows is:

SELECT 0.001992032*1000000
--1992.032000000

This is exactly what we see in the execution plan. OK, that was CL 140, let’s see how SQL Server 2019 handles this simple case.

When we switch to CL 150, the plan and estimations for the c1 column (non-nullable) are the same. However, the estimation for the nullable column is changed!

ALTER DATABASE New SET COMPATIBILITY_LEVEL = 150;
GO
DECLARE @v INT = 600000;
SELECT * FROM dbo.T WHERE c2 = @v;
Here is the plan:

e04

This time, the estimation is not so high, and we are getting more appropriated plan, based on the Nested Loop join operator. But, where is 104.397 coming from? Let’s look at the statistics again:

e05

Since the column is nullable, CE apply the All density against non-nullable rows only. Thus, the formula seems to be All density x (RowsEQ_ROWS for RANGE_HI_KEY = NULL).

SELECT 0.001992032*(1000000-947697);
--104.189249696

Well, it is not exactly 104.397, but very, very close to it.  I did not find official documentation for this, therefore cannot confirm that the mentioned calculation is always correct. However, this new behavior makes more sense: the average value from the stats density vector refers to non-nullable set of values, and it’s wrong to multiply it with all rows.

Funfact – the behavior is actually not new at all – that’s exactly how the old CE (under the compatibility levels <=110) handles this case.

I would like to thank my colleague Vladimir Đorđević for making me aware of this behavior.

Thanks for reading.

Dude, Where’s My Forced Plan?! – Part 1

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 the final fix. Forcing plan acts (should act) as “a temporary solution” for plan regression issues. But until you finally fix the underlying issue, it should be considered as a solution:  a temporary, but stable and steadily solution. However, forcing plans does not always work. You can find interesting articles about plan forcing in the excellent series of Query Store articles written by Erin Stellato (blog | twitter). In this article, I won’t write about forcing plan failures, where a plan fails because of missing index or objects, but describe a situation where everything seems to be fine – no failure at all – but it does not work.

To demonstrate the issue, we need to create a simple user table type and use it in a stored procedure in the WideWorldImporters sample database.

CREATE TYPE dbo.IntList AS TABLE(
fValue INT NOT NULL PRIMARY KEY CLUSTERED
)
GO
CREATE OR ALTER PROCEDURE dbo.GetOrderList
(@tvp AS dbo.IntList READONLY)
AS
SELECT o.*
FROM Sales.Orders o
INNER JOIN @tvp t on o.OrderID=t.fValue;
GO

It’s very simple: we’ll invoke the stored procedure to get order details for a given list of order ids. Let’s ensure that Query Store is empty and turned on:

ALTER DATABASE WideWorldImporters SET QUERY_STORE CLEAR;
ALTER DATABASE WideWorldImporters SET QUERY_STORE = OFF;
GO
ALTER DATABASE WideWorldImporters
SET QUERY_STORE = ON (QUERY_CAPTURE_MODE = AUTO);
GO

Now, run the following code that calls the stored procedure with 10 order Ids, and after clearing the cache calls it again, but this time with 2000 order ids:

DECLARE @t AS dbo.IntList;
INSERT @t SELECT TOP (10) OrderId FROM Sales.Orders ORDER BY 1 DESC;
EXEC dbo.GetOrderList @t;
GO 3
DBCC FREEPROCCACHE;
GO
DECLARE @t AS dbo.IntList;
INSERT @t SELECT TOP (2000) OrderId FROM Sales.Orders ORDER BY 1 DESC;
EXEC dbo.GetOrderList @t;

We expect here two plans: a Nested Loop Join based one for the first query and a Merge or Hash Join plan for the low selective second query. Let’s look at the Query Store to see, how this stored procedure executes. Since Query Store works with queries rather than with procedures, we need to identify the query_id of our SELECT statement:

SELECT qs.query_id, q.query_sql_text, qs.query_hash, qs.batch_sql_handle
FROM sys.query_store_query AS qs
INNER JOIN sys.query_store_query_text AS q ON qs.query_text_id = q.query_text_id
WHERE object_id = OBJECT_ID('dbo.GetOrderList')
ORDER BY qs.last_execution_time DESC

I got the value 1 for the query_id  (in your case it could be different, depending on your workload). Here is what we can see in the Tracking query report:

P1


P2

As we expected, we see two different plans: Nested Loop Join for 10 Ids and Merge Join for 2000 order ids.

Let’s assume now that we want to have the Nested Loop Join plan for our query, and that we have no time or access to the code to achieve this otherwise than forcing the plan. The assumption is not realistic for this simple query, but it’s reasonable in case of complex queries with complex plans on production system. Let’s force the lower plan and run the following code:

DECLARE @t AS dbo.IntList;
INSERT @t SELECT TOP (10) OrderId FROM Sales.Orders ORDER BY 1 DESC;
EXEC dbo.GetOrderList @t;
GO
DECLARE @t AS dbo.IntList;
INSERT @t SELECT TOP (2000) OrderId FROM Sales.Orders ORDER BY 1 DESC;
EXEC dbo.GetOrderList @t;
GO

What we expect to see now is the same execution plan for both executions. Let’s check in the Query Store report:

P3

We see that SQL Server reused the same plan for both calls, exactly one that we forced. All fine.

What would happen, when we clear the cache and call the stored procedure with parameters in the opposite order, i.e:

DBCC FREEPROCCACHE;
GO
DECLARE @t AS dbo.IntList;
INSERT @t SELECT TOP (2000) OrderId FROM Sales.Orders ORDER BY 1 DESC;
EXEC dbo.GetOrderList @t;
GO
DECLARE @t AS dbo.IntList;
INSERT @t SELECT TOP (10) OrderId FROM Sales.Orders ORDER BY 1 DESC;
EXEC dbo.GetOrderList @t;
GO

Let’s observe the Query Store report:

P4

What we see now is the third execution plan. The plan that we forced is not reused this time, SQL Server has created a new one. But this plan is also a Nested Loop join plan; it reflects the shape defined by forcing the plan. When you look at its XML representation, you would find the UsePlan attribute with the value True. Since there was no plan in cache, a new one is created, with the same shape but different estimations. Although it’s not exactly the same plan, it is still the plan we want to see – a Nested Loop Join based one. Still all fine – forcing plan works.

Let’s imagine now that we rollout a new version of the application. There were no changes in our procedure, but somebody included it in the installation and this statement has been executed.

CREATE OR ALTER PROCEDURE dbo.GetOrderList
(@tvp AS dbo.IntList READONLY)
AS
SELECT o.*
FROM Sales.Orders o
INNER JOIN @tvp t on o.OrderID=t.fValue;
GO

From a business logic point of view, there are no changes, altering a procedure with the same text should only mean that the new plan will be generated for it. And this should not be a problem because with forcing plan we ensured that the “good plan” will be used. Let’s confirm this by executing the following code:

DBCC FREEPROCCACHE;
GO
DECLARE @t AS dbo.IntList;
INSERT @t SELECT TOP (2000) OrderId FROM Sales.Orders ORDER BY 1 DESC;
EXEC dbo.GetOrderList @t;
GO 10
DECLARE @t AS dbo.IntList;
INSERT @t SELECT TOP (10) OrderId FROM Sales.Orders ORDER BY 1 DESC;
EXEC dbo.GetOrderList @t;
GO 10

Let’s have a look at the Query Store:

P5

When you observe this screenshot, you might think – hey dude, it seems you used the old screenshot, it does not show new executions! No, I did not, I refreshed the report, the report is correct. But, where are the recent 20 executions? Well, let’s execute again the statament looking for query_id:

P6

Ooops… Our SELECT query got a new query_id! And since a forced plan is associated not to a particular query, but to a given query_id, it’s clear why it does not affect the execution. The forcing plan is correctly configured – there is no forced plan failure, it is just waiting for execution of the query with the query_id 1. If this would happen, it would work correctly, but since a new query_id is associated with our query, the forced plan has nothing to do with it. We can confirm this by observing the Query Store report:

P7

You can see that all 20 executions are done with the Merge Join based execution plan, although we have forced the plan and our query was not changed at all!

Forced plans are stable and will remain in the system only if query_id does not change. And query_id depends on the following five parameters:

  • query_hash  – hash value of query text
  • object_id of the object (e.g. stored procedure) where the query has been executed (0 for standalone queries)
  • context_settings_id (e.g. ANSI standard, etc.)
  • query_parameterization_type – indicating how the query is parameterized
  • batch_sql_handle –  ID of the statement batch the query is part of. Populated only if the query references temporary tables or table variables

Since we call the stored procedure by using table variables, the batch_sql_handle attribute has been changed after we issued the ALTER PROCEDURE statement. Here is a query that returns two queries in the Query Store for the same query text:

P8

You can see that the values in these two rows are slightly different and this small difference canceled all our efforts with forcing plans and revitalized our issue.

Conclusion

Be very careful with forcing plans against queries referencing table variables and table-valued parameters. The query_id attribute for such queries is very unstable and forcing plans even as a workaround could have a very short lifetime. You should use a different approach to fix such issues.

Thanks for reading.

 

Quo vadis iliti j*** te fičer koji sve radi umesto mene a ja moram ništa

Ovaj post ima previše psovki da bi bio napisan na engleskom jeziku, al računam da nije malo i to što će dopreti do onih koji se služe srpskohrvatskim jezikom.

G1Postalo je gotovo nesnosno kako predstavnici mnogog proizvođača softvera (ne samo iz ogranka marketinga) najnovije fičere, alate i servise prodaju koristeći sintagmu “to sve radi za vas ispod haube,  ne morate kurcem mrdnuti, ne morate ništa znati o tome, ne morate programirati, sve je transparentno, dva-tri klika, malo drag and drop i fičer će sve uradit za vas”. Tome se pridružuje sve više predavača i tzv. eksperata ističući u prvi plan da je neki fičer do jaja jer njegov korisnik gotovo ništa ne mora da zna i ne mora više da brine o sistemu. Doduše i ne mora da brine, al sve dok ga sistem kurčinom ne tresne po čelu.

Toliko je novih stvari koje ne znaš, da prosto ne znaš gde i kako da počneš da ih učiš, a vremena nikad dovoljno. I onda ti neko kaže e ovo uopšte ne moraš da znaš, to radi samo, naravno da ćeš to malo vremena da posvetiš nečemu drugom, potpuno se oslanjajući na taj moćni fičr. Dok god ta stvar radi ono što korisnik očekuje i to radi dovoljno brzo, nema nikakvih problema. Dođe li do neželjenih efekata ili je stvar prespora onda kompania od tebe očekuje da nešto preduzmeš. A ako ne znaš kako to radi, ako ne razumeš concept, nemaš workaround, nisi se pripremio za tu situaciju onda možeš kurac.

Nove softverske verzije, novi alati samo su oruđe koje vam može pomoći da dođete do proizvodnog cilja: bržeg razvoja, skalabilnog sistema, boljih performansi. Ali to su samo jebeni alati. Oni ne rade za vas ili umesto vas; vi njih koristite i vi treba da radite sa njima. Treba dobro da ih poznajete, da znate šta i koliko mogu i možda još bitnije šta ne mogu. I valja gvirnuti poda haubu da vidiš kako to sve radi da bi znao da li je to za tebe.

Kad javno kažeš ovaj fičer je do jaja, biće ti query do 100 puta brži, a ne kažeš kad neće (a većini jebeno neće) i da može pod nekim uslovima i da bude i sporiji time si uljuljkao posetioca svoje sesije i potencijalno ga naveo na lošu poslovnu odluku. On obično nema vremena da nakon konferencije dalje razrađuje tu temu – bio je, čuo od eksperta i uzeće to kao istinu. I kad se suoči sa teškoćama u vezi sa tim fičerom, vendor ili spiker će bu obećati noviji, brži, jači i bolji.

Zato treba ljude podsećati na to da je baš lepo imati super fičere i alate, i da nesumljivo u njih treba investirati, ali da je još uvek jebeno najbitnije znanje i da treba da što više uče i znaju i da se prema svemu odnose kritički, a ne da se poput šiparica primaju na pojedine stvari pa se onda u javnim blog-postovima razočaravaju. I da treba da investiraju u znanje.

 

SQL Server 2019 CTP3.0 Query Store Changes

SQL Server 2019 CTP 3.0 brings small changes in the Query Store. Two parameters have new default values compared to previous SQL Server version:

Attribute SQL Server 2017 SQL Server 2019
MAX_STORAGE_SIZE_MB 100 1000
QUERY_CAPTURE_MODE ALL AUTO

In addition to default value, the minimum number of query executions in the AUTO query_capture_mode for storing the query, its plan(s) and runtime statistics in SQL Server 2019 has been increased from 3 to 30. That means, Query Store does not store anything for first 29 query executions. It reserves query_ids, but it starts storing execution plan and runtime stats from 30th execution in a single day. When you would run the following code, you would find nothing in Query Store:

IF DB_ID('Z') IS NULL
CREATE DATABASE Z;
GO
USE Z;
GO
ALTER DATABASE Z SET QUERY_STORE CLEAR;
GO
ALTER DATABASE Z SET QUERY_STORE = ON;
GO
CREATE TABLE dbo.T(id INT);
GO
SELECT COUNT(*) FROM T;
GO 29
SELECT SUM(id) FROM T;
GO 29

With the next execution of a query, its details, plan and runtime stats will be stored in Query Store:

SELECT SUM(id) FROM T;

Bl1

As you can see, the count_executions attribute shows 1; all previous executions are ignored; only query_id is preserved, but not stored; therefore, our query has id 2 because it was second in the sample code sequence.

There is another enhancement for the query_capture_mode attribute. In SQL Server 2017 it can have the following values: ALL, AUTO and NONE. SQL Server 2019 brings a new one – CUSTOM. SQL Server Management Studio 18 (SSMS) does not support it, but you can configure this feature by using Transact-SQL code. The CUSTOM mode configures QUERY_CAPTURE_POLICY options. The following options can be used to define the minimum condition for a query to be captured by Query Store:

EXECUTION_COUNT – minimal number of query executions for a query to be captured. As we saw earlier, default is 30 execution per day.

TOTAL_COMPILE_CPU_TIME_MS – minimum total query compilation time in a day to be persisted in Query Store. Default is 1000 milliseconds of CPU time.

TOTAL_ EXECUTION_CPU_TIME_MS – minimum total query CPU time spent for query execution per day to be persisted in Query Store. Default is 100 milliseconds.

A query will be captured if at least one of these three conditions is fulfilled; i.e. they are evaluated as EXECUTION_COUNT OR TOTAL_COMPILE_CPU_TIME_MS OR TOTAL_ EXECUTION_CPU_TIME_MS.

The fourth option is STALE_CAPTURE_POLICY_THRESHOLD which defines the evaluation interval period to determine if a query should be captured. The default is 1 day, and it can be set from 1 hour to 7 days.

Let’s try to reconfigure Query Store to capture queries after 20 executions:

USE Z;
GO
ALTER DATABASE Z SET QUERY_STORE CLEAR;
GO
ALTER DATABASE Z SET QUERY_STORE = ON
(
QUERY_CAPTURE_POLICY =
(
EXECUTION_COUNT = 20,
TOTAL_COMPILE_CPU_TIME_MS = 1000,
TOTAL_EXECUTION_CPU_TIME_MS = 100,
STALE_CAPTURE_POLICY_THRESHOLD = 1 day
)
);
GO
SELECT COUNT(*) FROM T;
GO 29
SELECT SUM(id) FROM T;
GO 29

EDIT: When you execute this code, you see no errors, but Query Store is still empty, so, the CUSTOM mode does not work in SQL Server 2019 CTP3.0 even with Transact-SQL.. The QUERY_CAPTURE_POLICY attribute settings are implemented only if you set the QUERY_CAPTURE_MODE to CUSTOM:

ALTER DATABASE Z SET QUERY_STORE = ON
(
QUERY_CAPTURE_MODE = CUSTOM,
QUERY_CAPTURE_POLICY =
(
EXECUTION_COUNT = 20,
TOTAL_COMPILE_CPU_TIME_MS = 1000,
TOTAL_EXECUTION_CPU_TIME_MS = 100,
STALE_CAPTURE_POLICY_THRESHOLD = 1 day
)
);
GO

So, my initial  claim is wrong – the CUSTOM mode for the QUERY_CAPTURE_MODE attribute works in SQL Server2019 CTP 3.0, you just need to use proper syntax.

Thanks for reading.

Common Database Design Mistakes u Kragujevcu

DBDes

U četvrtak 18. aprila 2019. u saradnji sa KG Code, a u organizaciji brta Đorđa Đokića (twitter) upriličen je juzer grup sastanak u Kragujevcu na temu dejtabejz dizajna (Database Design – najčešći mitovi i greške), na kome je moja neznatnost izložila svoja iskustva, viđenja i ostala smatranja u vezi sa tipičnim problemima koji prate dizajn relacionih baza podataka.

Moram da priznam da me je zaista iznenadio broj prisutnih s obzirom da tema nije nimalo atraktivna te da su mi broj učesnika, topao prijem i spremnosti na interakciju pričinili veliko zadovoljstvo. Kad te u rodnom gradu toko ljudi dočeka na predavanju može da znači da se postigo neki …to u životu. A svakako da znači i da je brat Đoca odradio odličan marketing, pa braćo i sestre ako vam treba neko da vam podigne prodaju znate kome da se obratite; ako je uspo da proda diskusiju o dejtabejz dizajnu prodaće i to vaše, šta god da je.

Ovde možete da downloadujete slajdove sa prezentacije.

Veliko hvala svima koji su došli, a posebno onima koji su omogućili da se sve to desi: Đorđe Đokić, Ivan Mihajlović, KG Code & co. I, razume se, ugostiteljskom objektu Stara Srbija.

Videćemo se mi opet, nećete me se tako lako otarasiti.

M.