Category - Development

Development

SQL Server 2019 Query Store Defaults

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 version, you can see another Query Store report – Query Wait Statistics. When you click on it, you can see aggregate waits per category in a given time interval (default is last hour). Bl1

By clicking on the wait category bar, a new screen shows up with ids of queries that contribute to this wait category.

Bl2

You can also use tabular view for initial report (aggergated time for wait categories):

Bl3

The second change is related to default configuration of Query Store. When you enable Query Store for a database without specifying attributes, by using the Properties tab or command:

ALTER DATABASE WideWorldImporters SET QUERY_STORE = ON;

SQL Server adds default values for Query Store parameters. In SQL Server 2019 Microsoft decided to change default values for some of them. Here is the table with the attributes and former and current default values:

Attribute SQL Server 2017 SQL Server 2019
max_storage_size_mb 100 500
interval_length_minutes 60 15
query_capture_mode ALL AUTO
flush_interval_seconds 900 3,000
max_plans_per_query 200 1,000

 

Thanks for reading.

#Tarabica 18 Conference– Slide Decks and Sample Code

tarabicaoutoftheboxAs always, I had a great time in Belgrade, at the large IT conference in Serbia #Tarabica18. I was very happy to see so many people in the room for my session SQL Server 2017 za developere and want to thank all the attendees. It was really pleasure to deliver the session in that audience. Here you can download slides decks and sample code from it.

Regards,

M.

SQL PASS Austria Meeting April: SQL Server Extended Events & Simple Rules for Clear and Attractive Visuals

bwin_neutral_negative_rgbIm kommenden SQL Server Community Meeting am Mittwoch, 15. April 2015 geht es um SQL Server Extended Events und Daten Visualisierung. Ich freue mich sehr, dass diesmal das Meeting in meiner Firma bwin stattfindet. Wir werden um 17:30 starten, die Adresse lautet: Marxergasse 1B, 1030 Wien.

 

Making the Leap from Profiler to Extended Eventses1

If you’re like me, you’ve been using SQL Trace and Profiler since you started using SQL Server. They’re like old friends, tried and true. You know how they work, you know what you can capture, you know how to find the data you need. But now you’ve been told that your good friends have been deprecated in SQL Server 2012, and you need to become best friends with Extended Events. What is THAT all about? It’s ok. Really. In this session we’ll look at Profiler and Extended Events side by side to see how easy it is to transition to Extended Events. We’ll step through real world examples using Extended Events to do what you’re used to doing with Trace, and even more. You can find the time learn Extended Events…today.

presented by
Erin Stellato (blog | twitter), Principal Consultant @ SQLskills.com


My Favourite Pie (Chart): Simple Rules for Clear and Attractive Visuals

memjDo you want to deliver information in an effective and efficient way? Even when the attractiveness of a report is important, beauty is in the eye of the beholder. Join this session where Markus will show you some simple rules for helping end-users to understand the story their data is trying to tell.

You will see how you can implement those rules with different tools from Microsoft’s BI stack – resulting in clear and concise information delivered through beautiful dashboards. You will also learn how to identify sub-optimal dashboards and what you can do to improve them.

presented by
Markus Ehrenmüller-Jensen (blog | twitter), , Business Intelligence Architect @ runtastic

 

Anmeldung und weitere Details: https://passaustriameeting.eventbrite.de

Beyond Statistics Histogram – Part 3 (TF 2389 feat. TF 4139)

In the previous article we’ve shown how can we use TF 2390 to handle queries with literals beyond statistics histogram in SQL Server 2012. We saw that SQL Server creates some statistics on the fly for all columns used in filter expressions. If we would enable trace flag on the instance level that means that whenever SQL Server creates an execution plan, it will touch indexes on all filtered columns in all queries. And not all columns have problems with ascending statistics. It would make more sense to apply these action only on columns branded by SQL Server as “Ascending” columns. This is exactly use case for trace flag 2389. It performs the same action and ends up with the same result as TF 2390, but it is applied to columns that SQL Server knows as ascending columns.

SQL Server marks a column as ascending column in a little bit esoteric way. Statistics object on the column should be updated three times in a row with the option “FULL SCAN” and for each update more than 90% of newly added rows should have value in that column greater than the max value from the actual statistic histogram. If this is true the column is branded as ascending. If it’s not the column is marked as stationary.

To demonstrate this let’s create a table and populate it with 10M rows following the procedure we described above. Click here to download the code. After we have updated statistics three times we can see that SQL Server marked the statistics object on the orderdate column as ascending.

DBCC TRACEON(2388);
DBCC SHOW_STATISTICS ('OrdersAsc','ix_orderdate');
DBCC TRACEOFF(2388);

Here is the output that confirms that the leading column Type is Ascending.

1201

Now we add additional 1M rows into the table. Again, this is not enough for automatically statistics update and statistic object remains unchanged.

Let’s recall our query from the previous post, to get all orders for customer with ID 160 in 2014. We will create two queries: one that uses TF 2390 and the other with TF 2389 and an Extended Events session to check which calculations on the fly are performed during the query execution in both cases. Here is the code for the trace session:

CREATE EVENT SESSION TF2389 ON SERVER
ADD EVENT sqlserver.sp_statement_completed(SET collect_statement=(1)
WHERE (sqlserver.database_id=10))
ADD TARGET package0.ring_buffer
WITH (MAX_MEMORY=4096 KB,MAX_DISPATCH_LATENCY=1 SECONDS)
GO
ALTER EVENT SESSION TF2389 ON SERVER STATE = START;
GO

Let’s execute both queries:

SELECT * FROM dbo.OrdersAsc
WHERE custid = 160 AND orderdate >= '20140101' AND orderdate < '20150101' OPTION(QUERYTRACEON 2390, RECOMPILE);
SELECT * FROM dbo.OrdersAsc
WHERE custid = 160 AND orderdate >= '20140101' AND orderdate < '20150101' OPTION(QUERYTRACEON 2389, RECOMPILE);

As mentioned above we got exactly the same execution plan and execution details. The only difference we can see in the trace file:

1202

TF 2389 instructs SQL Server to check the value in the orderdate column, while TF 2390 checks the column custid, too. So, if you want to apply this workaround only against really ascending columns use TF 2389. However, TF 2389 works only for columns branded as ascending; if column status changes back to Unknown it does not work anymore. TF 2390 works in both cases. So, if you are sure that column will be always ascending, use TF 2389.

And what happens if the column is marked as Stationary? Well, neither 2390 nor 2389 can help in this case! The plan is suboptimal, the same as without trace flags, with a lot of logical reads and unacceptable long execution time. Until July 2014 SQL Server 2012 didn’t have a workaround for dealing with stationary columns. Since that you can use another trace flag (TF 4139) to simulate the same behavior with stationary columns. TF 4139 is available from SQL Server 2012 SP1 CU 10 and SQL Server 2012 SP2 CU 1. More info about it you can find in this KB article.

Use this code to populate a table identical to previous one, but where the orderdate column is marked as Stationary.

Now we’ll execute the same query, this time in three different versions:

SELECT * FROM dbo.OrdersStat
WHERE custid = 160 AND orderdate >= '20140101' AND orderdate < '20150101' OPTION(QUERYTRACEON 2390, RECOMPILE);
SELECT * FROM dbo.OrdersStat
WHERE custid = 160 AND orderdate >= '20140101' AND orderdate < '20150101' OPTION(QUERYTRACEON 2389, RECOMPILE);
SELECT * FROM dbo.OrdersStat
WHERE custid = 160 AND orderdate >= '20140101' AND orderdate < '20150101' OPTION(QUERYTRACEON 4139, RECOMPILE);

Let’s observe the execution plans:

1203

As we can see trace flags 2389 and 2390 are helpless, and only TF 4139 ensures the correct execution plan.

Conclusion

Let’s summarize dealing with ascending columns with the help of trace flags so far:

TF 2389 helps the Query Optimizer to come up with better estimations and correct execution plans only for columns branded as Ascending.

TF 2390 instructs SQL Server to perform on-the-fly calculation for statistics objects for columns marked as Ascending or Unknown.

TF 4139 works with all three Leading Column Types: Ascending, Unknown and Stationary. However, it is available from SQL Server 2012 SP1 CU10 or SP2 CU1

And one note regarding the status of the flags in the latest SQL Server version: SQL Server 2014 does not support these flags. It ignores all of them, since the behavior we described in this and previous post is implemented in the new database engine and SQL Server 2014 without trace flags comes up with similar execution details as SQL Server 2012 with the flags. It seems that we don’t need these flags in SQL Server 2014. It seems. Until the next post.

Thanks for reading.

Beyond Statistics Histogram – Part 2 (TF 2390)

In the previous article of this series we introduced the problem with queries using literals which are beyond statistics histogram, but exist in the table. We saw that this can be a serious problem in large tables in SQL Server 2012. We saw also that SQL Server 2014 handles it better.

In this article we’ll see how to solve this problem in previous versions of SQL Server.

Whenever performance problems are caused by stale statistics one of the possible solutions is to update statistic. However, this process is resource and IO intensive and as soon as the update is finished new rows come in the table and statistics is again out of date. Of course, the amount of newly added or updated data is significantly reduced with regularly updates.

It is recommended to schedule update statistics on most frequently used key ascending columns in volatile tables to reduce discrepancy between actual data distribution and those in the statistics histogram. Even if you schedule statistics update for a large table the most recent entries are again beyond statistics histogram. With the update statistics we can reduce impact of rows missing in the statistics histogram, but what we can do if update is too expensive? How can we help optimizer when statistics are stale on the key ascending columns? The answer: by using trace flags.

Let’s show again the query and the execution plan from the previous post:

SELECT * FROM dbo.Orders
WHERE custid = 160 AND orderdate >= '20140101' AND orderdate < '20150101';

1001

1002

If we would execute the same query with the trace flag 2390 the estimations and execution details looks significantly better:

SELECT * FROM dbo.Orders
WHERE custid = 160 AND orderdate >= '20140101' AND orderdate < '20150101' OPTION(QUERYTRACEON 2390);

1003

1004

Using TF 2390 helped the optimizer to choose the proper execution plan with an Index Seek on the custid column and execution details looks as we expected.

What is the estimation number of rows for the orderdate predicate only with the TF 2390? Let’s check the estimated execution plan:

SELECT * FROM dbo.Orders WHERE orderdate >= '20140101' AND orderdate < '20150101' OPTION(QUERYTRACEON 2390);

1005

The old CE in SQL Server 2012 with the TF 2390 estimates about 666K rows. Just to remind you that the new CE comes up with 90K. This difference is important and we’ll discuss it in the next post. For this query it’s clear that the other predicate is winner when we use selectivity as criteria and there is no doubt for the Query Optimzer regarding the index usage.

How it gets the devil number of Ks for the Estimated Number of Rows? What’s the magic behind the TF 2390?

To check this we’ll create an Extended Event Session and add the event sp_statement_completed. It is recommended to limit the session to get only events for the specific database. Here is what we collected when we executed the above query:

1006

We see two queries doing some statistics calculations by using the max value from the actual table for both columns involved in the query: custid and orderdate. The estimated number of rows of 666K strongly correlates to the expression (last order date from the actual tablemax RANGE_HI_KEY from stats histogram) * average number of orders per day. It assumes uniform distribution of data beyond the stats histogram which is in case of the ordering system a reasonable assumption. The estimation of 666K compared to 1M of actual rows is a good estimation. (Especially compared to the default estimation (without TF) of 1 or 90K from SQL Server 2014) Our statistics object has been created automatically when we created the index on the orderdate column (see appropriate code in the previous post). If we wold have only statistics object (auto- or manually created) but without an index, TF 2390 would not improve performance. It requires an index on the column; otherwise it is not able to efficiently find the maximum value. The statistics is created on the fly and does not persist in the system. For more details you can find in the Dmitry Pilugin’s (blog | twitter) excellent article series..

Now we have a good estimation for the orderdate predicate; for the custid we had it anyway. The literal used for the custid (160) isn’t behind the histogram. Why then SQL Server calculated statistics on the fly of it too? TF 2390 instructs SQL Server to perform this calculation against all columns which participate in the filter criteria regardless of used literals. For instance, even if we would look for orders from 2013 which are in the histogram, the stats calculation on the fly would be performed. Actually, not for all columns; it will work for all columns which Leading column Type is Ascending or Unknown. For columns with the leading type Stationary no stats will be calculated on the fly and therefore these columns cannot have benefits from the TF 2390. I will cover all three leading types and how to handle differences in the next post.

How to find out the column leading type? This is detailed described in the excellent article Statistics on Ascending Columns written by Fabiano Amorim (blog | twitter). Again we need a trace flag. This time TF 2388.

DBCC TRACEON(2388);
DBCC SHOW_STATISTICS(Orders, IX_Date);
DBCC TRACEOFF(2388);

When TF 2388 is switched on DBCC SHOW_STATISTICS shows additional info about statistics objects. One of additional attributes is leading column type.

1007

We can see that SQL Server does not know the leading column type for the column orderdate; it is marked as Unknown. Therefore TF 2390 does statistics calculation on the fly when this column participates in filter expressions. Default behaviour of the query optimizer is to use meta data and statistics information during the plan generation, the table or index structure are not touched at all. By using TF 2390 this is not true, SQL Server touches the appropriate index in order to calculate statistics on the fly as a part of good enough plan finding.

Conclusion

The problem with queries using literals which are beyond statistics histogram can be solved in SQL Server 2012 by using TF 2390. Instead of estimation of 1 row the QO estimates by non-persistent statistics calculation on the fly. This brings a small overhead since the calculation on the fly will be performed against all columns participating in filter expressions (not only on those where it is required), but it is anyway better as default CE behavior prior to SQL Server 2014. The solutions done by TF 2390 and old CE and new CE in SQL Server 2014 for this query pattern looks similar, but some companies don’t allow usage of trace flags in th eproduction system. Therefore for them the recommended solution is to use SQL Server 2014 CE. But again, for this query pattern, not for all of them!

Thanks for reading.