Category: SQL Server 2022

Improving AT TIME ZONE Performance

Posted on by Joe Obbish

Queries that use the AT TIME ZONE operator may perform worse than desired. For example, Jonathan Kehayias blogged about performance struggles with AT TIME ZONE at sqlskills.com. One key difference between Jonathan’s opinion and my own is that I enjoy writing these kinds of posts.

Test Data

The test data consists of one million rows per day over the month of January 2022 for a total of 31 million rows.

DROP TABLE IF EXISTS dbo.CCIForBlog;

CREATE TABLE dbo.CCIForBlog (
    SaleTimeUTC DATETIME2 NOT NULL,
    WidgetCount BIGINT NOT NULL,
    INDEX CCI CLUSTERED COLUMNSTORE
);

GO

SET NOCOUNT ON;

DECLARE
    @StartDate DATETIME2 = '20220101',
    @DaysToLoad INT = 31,
    @DaysLoaded INT = 0;

WHILE @DaysLoaded < @DaysToLoad
BEGIN
    INSERT INTO dbo.CCIForBlog (SaleTimeUTC, WidgetCount)
    SELECT DATEADD(SECOND, q.RN / 11.5, @StartDate), q.RN / 100000
    FROM
    (
        SELECT TOP (1000000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
        FROM master..spt_values t1
        CROSS JOIN master..spt_values t2
    ) q (RN)
    OPTION (MAXDOP 1);

    SET @StartDate = DATEADD(DAY, 1, @StartDate);
    SET @DaysLoaded = @DaysLoaded + 1;
END;

CREATE STATISTICS S1 ON CCIForBlog (SaleTimeUTC);

Data is saved to a table with a clustered columnstore index. Of course, this is a small amount of data for a columnstore table. However, it is more than sufficient to demonstrate AT TIME ZONE as a performance bottleneck.

Filtering

Suppose an end user wants a count of widgets sold between January 3rd and January 6th. A first attempt at this query could look like the following:

SELECT SUM(WidgetCount)
FROM dbo.CCIForBlog c
WHERE c.SaleTimeUTC >= '20220103'
AND c.SaleTimeUTC < '20220106'
OPTION (MAXDOP 1);

This query plays to all of columnstore’s strengths and it only takes around 10 milliseconds to execute. The data was loaded in date order so most of the rowgroups are eliminated. However, end users don’t think in terms of UTC time. The end user actually wanted Central Standard Time. After a bit of research, the query is changed to as follows:

SELECT SUM(WidgetCount)
FROM dbo.CCIForBlog c
WHERE SWITCHOFFSET(c.SaleTimeUTC, 0) AT TIME ZONE 'Central Standard Time' >= '20220103'
AND SWITCHOFFSET(c.SaleTimeUTC, 0) AT TIME ZONE 'Central Standard Time' < '20220106'
OPTION (MAXDOP 1);

The AT TIME ZONE operator is useful when the number of offset minutes is unknown. UTC always has an offset of 0 so it is better to use SWITCHOFFSET(datetimeoffset_expression, 0)as opposed to AT TIME ZONE ‘UTC’. Even so, the query still takes over 3 minutes to execute on my machine. Nearly all of the execution time is spent on performing AT TIME ZONE calculations:

Note that using c.SaleTimeUTC AT TIME ZONE 'UTC' AT TIME ZONE 'Central Standard Time' would double the execution time.

One idea is to rewrite the filtering so that the time zone conversions are done on the constant values instead of the column:

WHERE c.SaleTimeUTC >= SWITCHOFFSET('20220103' AT TIME ZONE 'Central Standard Time', 0)
AND c.SaleTimeUTC < SWITCHOFFSET('20220106' AT TIME ZONE 'Central Standard Time', 0)

I strongly recommend against this approach. It can lead to wrong results for some time zones and boundary points. Instead, you can use the fact that datetimeoffset only supports an offset of up to +-14 hours. That means that (local – 14 hours) < UTC < (local + 14 hours) is true for any time zone and any point in time. A logically redundant filter can be added to the query:

SELECT SUM(WidgetCount)
FROM dbo.CCIForBlog c
WHERE SWITCHOFFSET(c.SaleTimeUTC, 0) AT TIME ZONE 'Central Standard Time' >= '20220103'
AND SWITCHOFFSET(c.SaleTimeUTC, 0) AT TIME ZONE 'Central Standard Time' < '20220106'
AND c.SaleTimeUTC >= DATEADD(HOUR, -14, '20220103')
AND c.SaleTimeUTC < DATEADD(HOUR, 14, '20220106')
OPTION (MAXDOP 1);

The newly improved query finishes in around 26 seconds. It is able to benefit from rowgroup elimination and performs significantly fewer time zone conversions compared to the original query. In this case, we were able to use knowledge about time zones and a bit of date math to improve performance from over 3 minutes to about 26 seconds.

Grouping

Suppose that an end user wants widget counts summarized by date. A first attempt at this query could look like the following:

SELECT ca.ConvertedDate, SUM(WidgetCount)
FROM dbo.CCIForBlog c
CROSS APPLY (
    SELECT CAST(c.SaleTimeUTC AS DATE)
) ca (ConvertedDate)
GROUP BY ca.ConvertedDate
OPTION (MAXDOP 1);

This query takes about 1 second on my machine. However, once again, the end user wants the data to be in CST instead of UTC. The following approach takes around 3 minutes:

SELECT ca.ConvertedDate, SUM(WidgetCount)
FROM dbo.CCIForBlog c
CROSS APPLY (
    SELECT CAST(SWITCHOFFSET(c.SaleTimeUTC, 0) AT TIME ZONE 'Central Standard Time' AS DATE)
) ca (ConvertedDate)
GROUP BY ca.ConvertedDate
OPTION (MAXDOP 1);

This should not be a surprise because the bottleneck in query performance is performing 31 million AT TIME ZONE calculations. That doesn’t change if the query performs filtering or grouping.

Historically, governments only perform daylight saving time or offset switches on the minute. For example, an offset won’t change at 2:00:01 AM, but it might change at 2:00:00 AM. The source data has one million rows per day, so grouping the date truncated to the minute, applying the time zone conversion to the truncated distinct values, and finally grouping by date should lead to significant performance improvement. One way to accomplish this:

SELECT ca.ConvertedDate, SUM(SumWidgetCount)
FROM 
(
    SELECT DATEADD(MINUTE, DATEDIFF(MINUTE, '20000101', c.SaleTimeUTC), '20000101'), SUM(WidgetCount)
    FROM dbo.CCIForBlog c
    GROUP BY DATEADD(MINUTE, DATEDIFF(MINUTE, '20000101', c.SaleTimeUTC), '20000101')
) q (SaleTimeUTCTrunc, SumWidgetCount)
CROSS APPLY (
    SELECT CAST(SWITCHOFFSET(q.SaleTimeUTCTrunc, 0) AT TIME ZONE 'Central Standard Time' AS DATE)
) ca (ConvertedDate)
GROUP BY ca.ConvertedDate
OPTION (MAXDOP 1);

The new query takes around 4 seconds on my machine. It needs to perform 44650 time zone conversions instead of 31 million. Once again, we were able to use knowledge about time zones and a bit of date math to improve performance.

Functioning

I’ve developed and open sourced replacement functions for AT TIME ZONE to provide an easier way of improving performance for queries that use AT TIME ZONE. The TZGetOffsetsDT2 function returns a pair of offsets and the TZFormatDT2 function transforms those offsets into the desired data type. The filtering query can be written as the following:

SELECT SUM(WidgetCount)
FROM dbo.CCIForBlog c
OUTER APPLY dbo.TZGetOffsetsDT2 (c.SaleTimeUTC, N'UTC', N'Central Standard Time') o
CROSS APPLY dbo.TZFormatDT2 (c.SaleTimeUTC, N'UTC', N'Central Standard Time', o.OffsetMinutes, o.TargetOffsetMinutes) f
WHERE f.ConvertedDateTime2 >= '20220103'
AND f.ConvertedDateTime2 < '20220106'
OPTION (MAXDOP 1);

The new query takes around 10 seconds to execute. The new query plan is able to use batch mode processing at every step:

 

If desired, performance can be further improved by adding the same redundant filters as before:

SELECT SUM(WidgetCount)
FROM dbo.CCIForBlog c
OUTER APPLY dbo.TZGetOffsetsDT2 (c.SaleTimeUTC, N'UTC', N'Central Standard Time') o
CROSS APPLY dbo.TZFormatDT2 (c.SaleTimeUTC, N'UTC', N'Central Standard Time', o.OffsetMinutes, o.TargetOffsetMinutes) f
WHERE f.ConvertedDateTime2 >= '20220103'
AND f.ConvertedDateTime2 < '20220106'
AND c.SaleTimeUTC >= DATEADD(HOUR, -14, '20220103')
AND c.SaleTimeUTC < DATEADD(HOUR, 14, '20220106')
OPTION (MAXDOP 1);

The most optimized version takes around 1 second to execute. Quite an improvement compared to 3 minutes!

The grouping query can also be rewritten using the new functions:

SELECT f.ConvertedDate, SUM(WidgetCount)
FROM dbo.CCIForBlog c
OUTER APPLY dbo.TZGetOffsetsDT2 (c.SaleTimeUTC, N'UTC', N'Central Standard Time') o
CROSS APPLY dbo.TZFormatDT2 (c.SaleTimeUTC, N'UTC', N'Central Standard Time', o.OffsetMinutes, o.TargetOffsetMinutes) f
GROUP BY f.ConvertedDate
OPTION (MAXDOP 1);

This version takes about 7 seconds to execute. This is slower than the date truncation method which took 4 seconds but still much faster than the original AT TIME ZONE method.

Unfortunately, SQL Server limitations require a pair of functions to get the best performance. The open source project does provide a simpler function that can be called by itself but it is only eligible for nested loop joins.

Upgrading

SQL Server 2022 RC1 has signs of improvement for AT TIME ZONE. The basic queries that use AT TIME ZONE in this blog post take about 75 seconds to execute on my machine, so it could be estimated that SQL Server 2022 will reduce the CPU footprint of AT TIME ZONE by 60%. Release candidates are not fully optimized so it’s possible that final performance numbers will be different once the product goes GA. I suspect that these performance improvements are already present in Azure SQL Database but I can’t find any documentation for the change.

Final Thoughts

Please try my open source project if you’re experiencing performance problems with AT TIME ZONE. Thanks for reading!

Some Notes on sp_prepare And SQL Server 2022

Posted on by Erik Darling

OldBad

I admit that sp_prepare is an odd bird, and thankfully one that isn’t used a ton. I still run into applications that are unfortunate enough to have been written by people who hate bloggers and continue to use it, though, so here goes.

When you use sp_prepare, parameterized queries behave differently from normal: the parameters don’t get histogram cardinality estimates, they get density vector cardinality estimates.

Here’s a quick demo to show you that in action:

CREATE INDEX 
    p 
ON dbo.Posts
    (ParentId) 
WITH
(
    SORT_IN_TEMPDB = ON, 
    DATA_COMPRESSION = PAGE
);

DECLARE 
    @handle int = 
        NULL,
    @parameters nvarchar(MAX) = 
        N'@ParentId int',
    @sql nvarchar(MAX) = 
        N'
        SELECT
            c = COUNT_BIG(*)
        FROM dbo.Posts AS p
        WHERE p.ParentId = @ParentId;
        ';

EXEC sys.sp_prepare 
    @handle OUTPUT,
    @parameters,
    @sql;

EXEC sys.sp_execute 
    @handle, 
    184618;
 
EXEC sys.sp_execute 
    @handle, 
    0;
 
EXEC sys.sp_unprepare 
    @handle;

OldPlan

The plans for the two executions have the same poor cardinality estimate. In this case, since we have an ideal index and there’s no real complexity, there’s no performance issue.

But you can probably guess (at least for the second query) how being off by 201,694,000% might cause issues in queries that ask a bit more of the optimizer.

The point here is that both queries get the same incorrect estimate of 3 rows. If you add a recompile hint, or execute the same code using sp_executesql, the first query will get a histogram cardinality estimate, and the second query will reuse it.

SQL Server Query Plan
one up

Given the historical behavior of sp_prepare, I was a little surprised that the Parameter Sensitive Plan (PSP) optimization available in SQL Server 2022 kicked in.

NewDifferent

If we change the database compatibility level to 160, the plans change a bit.

ALTER DATABASE StackOverflow2013 SET COMPATIBILITY_LEVEL = 160;

Now we see two different plans without a recompilation, as well as the plan per value option text at the end of the queries, indicating the PSP optimization kicked in.

SQL Server Query Plan
two up

The differences here are fairly obvious, but…

  • Each plan gets accurate cardinality
  • The second plan goes parallel to make processing ~6 million rows faster
  • Different aggregates more suited to the amount of data in play are chosen (the hash match aggregate is eligible for batch mode)

Thanks for reading!

Going Further

If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount on to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that, and need to solve database performance problems quickly.

SQL Server 2022 CTP 2.1 Improvements To Parameter Sensitive Plan Optimization

Posted on by Erik Darling

Hop On Your Good Foot

Several weeks back, I blogged about a missed opportunity with the new parameter sensitive plan feature. At the time, I thought that there was indeed sufficient skewness available to trigger the additional plan variants, and apparently some nice folks at Microsoft agreed.

If we step back through the old demo, we’ll get different results.

EXEC dbo.VoteSniffing 
    @VoteTypeId = 4;

EXEC dbo.VoteSniffing 
    @VoteTypeId = 2;

I’m not gonna go into a lot of depth here, because I’m writing this while traveling, but we get the (mostly) desired outcome of two different plans being generated. Each plan is more suitable to the amount of data that the query has to process.

SQL Server Query Plan
home improvement

I’d consider this a success, even if it adds more queries to the unidentifiable corpse-pile.

Thanks for reading!

Going Further

If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount on to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that, and need to solve database performance problems quickly.

SQL Server 2022 Introduces IS [NOT] DISTINCT FROM; World Underwhelmed

Posted on by Erik Darling

Back In Time

Feeling quite old while remembering the first time I ever read this, and this note at the end:

This is the SQL language IS DISTINCT FROM feature —implemented in the query processor, but not yet available in the T-SQL language.

Groovy. Since at least 2011, this has been in the Query Processor, and here in 2022 we finally get the linguistic support.

The thing is, it’s pretty underwhelming, and I’m going to show you why I think so.

First, in the docs for SQL Server, all the examples use a single literal value, like so:

SELECT * FROM #SampleTempTable WHERE id IS DISTINCT FROM 17;

I went looking for other docs examples from vendors who have had the syntax around for 10+ years, and there wasn’t anything all that much more interesting.

Mostly case expressions and whatnot.

Big deal.

Alignment

First, if I try to run either of these queries, I’ll get an error after about 6 seconds.

SELECT
    c = COUNT_BIG(*)
FROM dbo.Comments AS c
WHERE c.UserId IS DISTINCT FROM 
               (
                   SELECT
                       v.UserId
                   FROM dbo.Votes AS v
               );

SELECT
    c = COUNT_BIG(*)
FROM dbo.Comments AS c
WHERE c.UserId IS NOT DISTINCT FROM 
               (
                   SELECT
                       v.UserId
                   FROM dbo.Votes AS v
               );

Why does it take 6 seconds to get an error? Because a few parts of the query plan have to do some work, and then finally:

Msg 512, Level 16, State 1, Line 1
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

Sort of like how sometimes you try to SUM a column and after a while you get an error about arithmetic overflow.

This is a bit annoying, because that means we need a way to return a single value to evaluate.

So Yeah…

We can’t even rewrite the queries like this to get around the error, but I do want to show you the plans.

This is why we have to wait several seconds to get an error (unless you change it to IS DISTINCT FROM ALL/ANY):

SELECT
    c = COUNT_BIG(*)
FROM dbo.Comments AS c
WHERE c.UserId IS DISTINCT FROM 
               (
                   SELECT
                       v.UserId
                   FROM dbo.Votes AS v
                   WHERE v.UserId = c.UserId
               );

SELECT
    c = COUNT_BIG(*)
FROM dbo.Comments AS c
WHERE c.UserId IS NOT DISTINCT FROM 
               (
                   SELECT
                       v.UserId
                   FROM dbo.Votes AS v
                   WHERE v.UserId = c.UserId
               );

Adding a where clause inside the subquery doesn’t help.

But these query plans are total motherchucking disasters, anyway. We’ll get into indexing later, but right now they both have the same shape and operators, though slightly different semantics to deal with is/is not distinct.

SQL Server Query Plan
is not cool

Both plans run single threaded, and using Nested Loops as the physical join type, which stinks because we’re putting together two pretty big tables.

Not to mention that Eager Index Spool. What a filth.

Adding Indexes

We need these indexes to make things go any faster. Before we do anything else, let’s create these so we’re not just sitting around thumb-twiddling.

CREATE INDEX
    c
ON dbo.Comments
    (UserId)
WITH
(
    SORT_IN_TEMPDB = ON,
    DATA_COMPRESSION = PAGE
);

CREATE INDEX
    v
ON dbo.Votes
    (UserId)
WITH
(
    SORT_IN_TEMPDB = ON,
    DATA_COMPRESSION = PAGE
);

Thinking About It

Okay, so writing the query like we did up there isn’t going to get us anything. Perhaps my expectations are a bit too exotic.

Let’s try something a bit more austere:

SELECT
    c = COUNT_BIG(*)
FROM dbo.Comments AS c
JOIN dbo.Votes AS v
    ON c.UserId IS DISTINCT FROM v.UserId;

SELECT
    c = COUNT_BIG(*)
FROM dbo.Comments AS c
JOIN dbo.Votes AS v
    ON c.UserId IS NOT DISTINCT FROM v.UserId;

The first thing to be aware of here is that the IS DISTINCT FROM is an inequality predicate, so you’re stuck with Nested Loops as the physical join type:

SQL Server Query Plan
nightmare

I ran out of care-juice waiting for this to finish, so all you’re getting is an estimated plan. The lack of an equality predicate here means you don’t have Hash or Merge join as an option.

Following up on bad ideas, the IS NOT DISTINCT FROM is an equality predicate, but the plan chosen is a serial Merge Join variety, which drags on 14 seconds too long:

SQL Server Query Plan
change up

Drop An Index

If we simulate not having any useful indexes on one table or the other by hinting the clustered index, the performance outlook does not improve.

SELECT
    c = COUNT_BIG(*)
FROM dbo.Comments AS c WITH(INDEX = 1)
JOIN dbo.Votes AS v
    ON c.UserId IS NOT DISTINCT FROM v.UserId;

SELECT
    c = COUNT_BIG(*)
FROM dbo.Comments AS c
JOIN dbo.Votes AS v WITH (INDEX = 1)
    ON c.UserId IS NOT DISTINCT FROM v.UserId;

No useful parts of the first query happen in Batch Mode, but the second query is rescued by two hash aggregates happening in batch mode.

SQL Server Query Plan
ouching

An odd point to make on a blog focused on SQL Server performance tuning is that sometimes not having a useful index gets you a better plan.

Anyway, I’m going back to my vacation.

Thanks for reading!

Going Further

If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount on to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that, and need to solve database performance problems quickly.

SQL Server 2022: Introduces the DATETRUNC Function, So You Can Truncate Your Dates And Stuff

Posted on by Erik Darling

Underscoring The Importance

When I first sat down to write about this, I made a funny mistake: I kept writing DATE_TRUNC over and over again.

In SQL Server it’s DATETRUNC.

Why? Because that’s the way it’s implemented in Postgres and DB2. Oracle, of course, just calls it TRUNC.

So, while it’s nice to have (what appears to be) the same behavior, it doesn’t exactly help to not have a 1:1 calling equivalent to other platforms.

I assume most of these additions to T-SQL are for cross-platform development and migrations.

Of course, Microsoft being so gosh darn late to this game means folks have probably been rolling-their-own versions of these functions for years.

If they went and called their system function DATE_TRUNC or even TRUNC, they might have some object naming issues to contend with.

Well, okay. But how does it work?

Childish Games

Here are some quick examples of how you call it.

SELECT TOP (10)
    u.DisplayName,
    year = 
        DATETRUNC(YEAR, u.LastAccessDate),
    quarter = 
        DATETRUNC(QUARTER, u.LastAccessDate),
    month = 
        DATETRUNC(MONTH, u.LastAccessDate),
    dayofyear = 
        DATETRUNC(DAYOFYEAR, u.LastAccessDate),
    day = 
        DATETRUNC(DAY, u.LastAccessDate),
    week = 
        DATETRUNC(WEEK, u.LastAccessDate),
    iso_week = 
        DATETRUNC(ISO_WEEK, u.LastAccessDate),
    hour = 
        DATETRUNC(HOUR, u.LastAccessDate),
    minute = 
        DATETRUNC(MINUTE, u.LastAccessDate),
    second = 
        DATETRUNC(SECOND, u.LastAccessDate),
    millisecond = 
        DATETRUNC(MILLISECOND, u.LastAccessDate),
    microsecond = 
        DATETRUNC(MICROSECOND, u.LastAccessDate) /*Doesn't work with datetime because there are no microseconds*/
FROM dbo.Users AS u;

And here are the results:

SQL Server Query Results
workin’

The thing to note here is that there’s no rounding logic involved. You just go to the start of whatever unit of time you choose. Of course, this doesn’t seem to do anything to the millisecond portion of DATETIME, because it’s not precise enough.

But for anyone out there who was hoping for a SOMONTH function to complement the EOMONTH function, you get this instead.

Works well enough!

But does it perform, Darling?

UnSARGable?

To make any test like this worthwhile, we need an index to make data searchable.

CREATE INDEX
    v
ON dbo.Votes
    (CreationDate)
WITH
(
    SORT_IN_TEMPDB = ON,
    DATA_COMPRESSION = PAGE
);

You know, because that’s what they do. To make searching faster. Hello.

So look, under these perfect circumstances, everything performs well. But we have to do a lot of typing.

SELECT
    c = COUNT_BIG(*)
FROM dbo.Votes AS v
WHERE v.CreationDate >= DATETRUNC(YEAR, CONVERT(datetime, '20130101 00:00:00.000'));

Note here that we’re working on a literal value, not a column value, and we have to tell the datetrunc function which type we want via the convert function so that we get a simple seek plan:

SQL Server Query Plan
grin

Without all that, we get a dynamic seek plan:

SELECT
    c = COUNT_BIG(*)
FROM dbo.Votes AS v
WHERE v.CreationDate >= DATETRUNC(YEAR, '20130101 00:00:00.000');

This has some… obvious performance issues compared to the above plan with correct data types.

SQL Server Query Plan
query pan

Query Pranks

Frequent blog readers will not be surprised that wrapping a table column in the new DATETRUNC function yields old performance problems:

SELECT
    c = COUNT_BIG(*)
FROM dbo.Votes AS v
WHERE DATETRUNC(YEAR, v.CreationDate) >= CONVERT(datetime, '20130101 00:00:00.000');

This is particularly annoying because we’re truncating the column to the beginning of the year, which should be searchable in the index since that’s the sort order of the data in the index.

SQL Server Query Plan
shined out

Like most functions, these are fine in the presentation layer, but terrible in the relational layer. There’s no warning about this performance degradation in the documentation, either at the example of using the function in a where clause, or in the final closing remarks.

But that’s par for the course with any of these built-in functions.

Thanks for reading!

Going Further

If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount on to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that, and need to solve database performance problems quickly.

SQL Server 2022’s GENERATE_SERIES Doesn’t Suck Anymore

Posted on by Erik Darling

Life Comes At You Fast

The summer intern at Microsoft was hard at work between CTPs. Last time around, there were some serious performance issues with our dear new friend GENERATE_SERIES.

With the release of CTP 2.1, the problems that we saw the first time around are all gone. But there are still a couple small caveats that you should be aware of.

There’s also been a change in the way you call the function, too. You not longer need the START and STOP identifiers in the function.

You just put in whatever numbers you’re into.

That’s probably a good thing.

Caveat #1: Parallelism Restrictions

Loading data into tables that have row store indexes on them can’t go parallel.

DROP TABLE IF EXISTS
    dbo.art_aux;

CREATE TABLE 
    dbo.art_aux
(
    id int NOT NULL PRIMARY KEY CLUSTERED
);

INSERT INTO
    dbo.art_aux WITH(TABLOCK)
(
    id
)
SELECT
    gs.value
FROM GENERATE_SERIES
     (
         1, 
         10000000
     ) AS gs
OPTION
(
    MAXDOP 8, 
    QUERYTRACEON 8649
);

But performance is still pretty good, here. Much better than prior.

SQL Server Query Plan
cooperation

I think most folks out there would be okay waiting a few seconds for 10 million rows to show up in a table for them.

So sequential. Much unique.

But, what if you want to load those rows in faster?

Caveat #2: Use A Heap Or SELECT INTO Instead

If you create a heap, or just SELECT INTO on instead, you’ll get a parallel load into the table.

For 10 million rows, there’s an improvement of about 1.7 seconds on my sort of crappy travel VM with 4 available CPUs.

DROP TABLE IF EXISTS
    dbo.art_aux;

CREATE TABLE 
    dbo.art_aux
(
    id int NOT NULL
);

INSERT INTO
    dbo.art_aux WITH(TABLOCK)
(
    id
)
SELECT
    gs.value
FROM GENERATE_SERIES
     (
         1, 
         10000000
     ) AS gs;

Here’s the plan:

SQL Server Query Plan
hot swap

Yes, dear reader, I even blog on vacation. That’s how much I love and care about you.

Thanks for reading!

Going Further

If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount on to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that, and need to solve database performance problems quickly.

Some New Stuff In SQL Server 2022 CTP 2.1

Posted on by Erik Darling

New Baby

SQL Server 2022 CTP 2.1 dropped recently, and there’s some new stuff in it! What kind of new stuff?

Well, there’s some linguistic stuff:

  • APPROX_PERCENTILE_DISC
  • APPROX_PERCENTILE_CONT
  • DATETRUNC
  • IS [NOT] DISTINCT FROM
  • Bit manipulation:
    • LEFT_SHIFT
    • RIGHT_SHIFT
    • BIT_COUNT
    • GET_BIT
    • SET_BIT

How useful any of that will be depends on how you use SQL Server. I’ve never heard anyone yearning for bit manipulation functions, but perhaps I travel in the wrong circles.

Inside You

As far as “unannounced” stuff, here’s some of what I noticed:

There’s a new database scoped configuration called LEDGER_DIGEST_STORAGE_ENDPOINT. The name seems fairly self-documenting, but I can’t picture myself using ledger enough to explore it further.

There are a bunch of new objects, too:

+------------------------------------------------------------+----------------------------------+
|                            name                            |            type_desc             |
+------------------------------------------------------------+----------------------------------+
| dm_db_xtp_hash_index_approx_stats                          | SQL_INLINE_TABLE_VALUED_FUNCTION |
| dm_tran_distributed_transaction_stats                      | VIEW                             |
| extgov_attribute_sync_objects_synchronizing                | VIEW                             |
| sp_change_feed_configure_parameters                        | SQL_STORED_PROCEDURE             |
| sp_dw_physical_manifest_file_table_insert                  | EXTENDED_STORED_PROCEDURE        |
| sp_dw_physical_upsert                                      | EXTENDED_STORED_PROCEDURE        |
| sp_help_change_feed_table                                  | SQL_STORED_PROCEDURE             |
| sp_manage_distributed_transaction                          | EXTENDED_STORED_PROCEDURE        |
| sp_md_discovery_stats_staleness_detection                  | EXTENDED_STORED_PROCEDURE        |
| sp_reset_dtc_log                                           | EXTENDED_STORED_PROCEDURE        |
| sp_trident_create_credential_to_access_internal_md_storage | EXTENDED_STORED_PROCEDURE        |
+------------------------------------------------------------+----------------------------------+

Again, their usefulness will depend on how you use the product.

Here are some new extended events:

  • query_ce_feedback_telemetry
  • query_feedback_analysis
  • query_feedback_validation
  • resumable_add_constraint_executed

I don’t think I’ll need to add any of these to sp_HumanEvents, but I’m sure you’ll see them in lots of Microsoft demos.

There are also some new wait stats, but I think I’ll be ignoring them.

  • CDC_SCAN_FINISHED
  • CORRUPTED_PAGE_PROCESS
  • DW_DB
  • DW_WS_DB_LIST
  • NATIVE_SHUFFLE_WRITE_BUFFER_DEQUEUE
  • PARQUET_INDEX_BUILD_MANIFEST_SYNC
  • PURVIEW_POLICY_SDK_PREEMPTIVE_SCHEDULING
  • PWAIT_S3_TEMP_CREDENTIAL
  • RBAC_AAD_GROUP_INFO
  • SPINLOCK_EXT
  • WAIT_EXTGOV_PERMCACHE_DECISIONLOCK
  • WAIT_EXTGOV_PERMCACHE_RESOURCELOCK

Nothing all that juicy this time around. Maybe next time!

Thanks for reading!

Going Further

If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount on to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that, and need to solve database performance problems quickly.

How SQL Server 2022’s Parameter Sensitive Plan Feature Can Make Query Store Confusing

Posted on by Erik Darling

Psssssp


Thanks for watching!

Going Further

If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount on to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that and need to solve performance problems quickly.

SQL Server 2022: Database Scoped Configurations And Feature Accessibility

Posted on by Erik Darling

Overlooked

In my quest to tell you about new stuff in SQL Server 2022, I forgot to take a look at new things in sys.database_scoped_configurations.

It turns out that’s important, because some stuff has to be turned on if you want it to work. Here’s a list of new database scoped configurations for SQL Server 2022:

+------------------+-----------------------------------------------+-------+------------------+
| configuration_id |                     name                      | value | is_value_default |
+------------------+-----------------------------------------------+-------+------------------+
|               29 | ASYNC_STATS_UPDATE_WAIT_AT_LOW_PRIORITY       |     0 |                1 |
|               31 | CE_FEEDBACK                                   |     1 |                1 |
|               37 | DOP_FEEDBACK                                  |     1 |                0 |
|               26 | DW_COMPATIBILITY_LEVEL                        |     0 |                1 |
|               27 | EXEC_QUERY_STATS_FOR_SCALAR_FUNCTIONS         |     1 |                1 |
|               34 | MEMORY_GRANT_FEEDBACK_PERCENTILE_GRANT        |     1 |                1 |
|               33 | MEMORY_GRANT_FEEDBACK_PERSISTENCE             |     1 |                1 |
|               35 | OPTIMIZED_PLAN_FORCING                        |     1 |                0 |
|               28 | PARAMETER_SENSITIVE_PLAN_OPTIMIZATION         |     1 |                1 |
|               25 | PAUSED_RESUMABLE_INDEX_ABORT_DURATION_MINUTES |  1440 |                1 |
+------------------+-----------------------------------------------+-------+------------------+

Some stuff is on by default, some stuff isn’t. You might notice DOP_FEEDBACK and OPTIMIZED_PLAN_FORCING are both off by default, here (even though I turned them on).

ALTER DATABASE SCOPED CONFIGURATION SET DOP_FEEDBACK = ON;
ALTER DATABASE SCOPED CONFIGURATION SET OPTIMIZED_PLAN_FORCING = ON;

Invitation Only

You might think that Microsoft is so excited about new features (especially in CTP builds) that they want them to be easy to preview and test by people who care enough to do that stuff.

No so much, it turns out.

Heck, I’ve seen several presentations that said Query Store would be on by default in new databases created on SQL Server 2022 instances, but I haven’t had that happen yet and that’s not even a new feature.

Perhaps, like the daftly-hidden Azure Nonsense© in the installer, this will be changed in a future version, but no one tells me anything.

Anyway, not much here beyond this. Mind your database scoped database configurations when you’re testing out new features. Also make sure to check for extended events that help you track down if/when heuristics for those features kick in.

Thanks for reading!

Going Further

If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount on to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that and need to solve performance problems quickly.

New Wait Stats In SQL Server 2022

Posted on by Erik Darling

Patiently Waiting

This is just a quick post to list out new wait stats in SQL Server 2022. How many will be useful, time will tell.

Some waits that I think might be interesting:

  • CXSYNC_CONSUMER (Currently in Azure)
  • CXSYNC_PORT (Currently in Azure)
  • PARALLEL_DB_SEEDING_SEMAPHORE
  • PLPGSQL

It looks like maybe automatic seeding for Availability Groups is getting the ability to process multiple databases at once, and we’re getting some parallel query waits that used to be Azure only.

I’m really scratching my head about PLPGSQL though. That’s the “programming language” that Postgres supports, which is sort of like what Oracle supports.

Time will tell!

+-----------------------------------------------------+
|                      wait_type                      |
+-----------------------------------------------------+
| ARC_IMDS_RESOURCE_INFO                              |
| BABYLON_POLICY_UPDATE                               |
| BABYLON_PULL_TASK                                   |
| BACKUP_BACKUP_MGR_MIHYBRIDINFO_RWLOCK               |
| BACKUP_LOG_IO_STALL                                 |
| BLOB_LIST_LIMIT_IO_REQUESTS                         |
| BLOB_LIST_RWLOCK                                    |
| BUFFERPOOL_SCAN                                     |
| CDC_SCHEDULERCACHE_ACCESS                           |
| CDC_THROTTLE_LOG_RATE_LOG_SIZE                      |
| CMEMDETOUR                                          |
| COLLECTOR_VIEW_LIST                                 |
| COLUMNSTORE_CSI_CACHE                               |
| COSMOSDB_INIT_MUTEX                                 |
| CXSYNC_CONSUMER                                     |
| CXSYNC_PORT                                         |
| DATA_EXPORT_COMPLETION_SYNC                         |
| DIFF_BACKUP_SELF_THROTTLING                         |
| DIRECTORY_CONTENT_LIST_CLERK                        |
| DIRTY_PAGE_THROTTLING                               |
| DISPATCHER_JOIN                                     |
| DTC_INFO_DMV                                        |
| DTC_PRECOMMIT                                       |
| DTCNEW_DWSHELLDB_PROPERTIES                         |
| EDC_DOPP_BACKGROUND                                 |
| EDC_DOPP_LOCK                                       |
| EDC_INIT_RWLOCK                                     |
| EMC_EXEC                                            |
| EMC_FABRIC_RESOLVE_SERVICE_URI                      |
| EMC_INIT_MUTEX                                      |
| EXECUTED_REQ_TABLE_STATE_RWLOCK                     |
| EXTERNAL_SCRIPT_CREATE_CERTIFICATE                  |
| EXTERNAL_SERVICE_BLOB_MESSAGE                       |
| EXTERNAL_SERVICE_CONNECTION_CLOSE                   |
| EXTGOV_ATTR_SYNC_BACKGROUND                         |
| FABRIC_ENDPOINT_SYNC_EVENT                          |
| FCB_DISKSPACE_COUNTERS_RWLOCK                       |
| FIDO_AUTOSTATISTICS_TASK                            |
| FIDO_CLIENT_STARTUP                                 |
| FOREIGN_FILE_VALIDATION                             |
| FULL_BACKUP_SELF_THROTTLING                         |
| GLOBAL_QUERY_EXTRACTOR_EXECUTE                      |
| HADR_THROTTLE_LOG_RATE_MISMATCHED_SLO               |
| HADR_THROTTLE_LOG_RATE_SLO_DOWNGRADE                |
| HADR_THROTTLE_REFRESH_MAX_SIZE                      |
| HTTP_EXTERNAL_CONNECTION                            |
| HTTP_EXTERNAL_CONNECTION_ALLOW_LIST                 |
| HTTP_EXTERNAL_CONNECTION_IPV4_BLOCK_LIST            |
| INDEX_BUILD_BUCKETIZATION_BARRIER                   |
| INDEX_BUILD_BUCKETIZATION_INFO_MAP_SYNC             |
| INDEX_BUILD_BUCKETIZATION_INFO_SYNC                 |
| LCK_REQ_TSK_PROXY                                   |
| LEDGER_BLOCK_GENERATION                             |
| LEDGER_TRUNCATION                                   |
| LOCK_SAFEMODE                                       |
| LOCK_UPDATE                                         |
| LOGPOOL_CONSUMER_DELETABLE                          |
| LSN_LOC_MAP_LOCK                                    |
| MANAGED_DISKS_CONFIGURATION                         |
| MULTITHREADED_VERSION_CLEANUP_WAIT_WORK             |
| NATIVE_SHUFFLE_OPEN_HANDLE                          |
| ORDLOCK_POPULATE_SYNC                               |
| PARALLEL_BPOOL_DEALLOCATION_WORKER                  |
| PARALLEL_DB_SEEDING_SEMAPHORE                       |
| PLPGSQL                                             |
| POLARIS_TSQL_TASK                                   |
| POPULATE_LOCK_ORDINALS                              |
| PREEMPTIVE_AAD_HTTP_EVENT_WAIT                      |
| PREEMPTIVE_COSMOSDB                                 |
| PREEMPTIVE_EMC                                      |
| PREEMPTIVE_FILE_MAPPING                             |
| PREEMPTIVE_HTTP_EXTERNAL_CONNECTION_EVENT_WAIT      |
| PREEMPTIVE_OS_GETQUEUEDCOMPLETIONSTATUS             |
| PREEMPTIVE_OS_PDH_WMI_QUERY                         |
| PREEMPTIVE_PREDICT_API                              |
| PREEMPTIVE_REPORTING                                |
| PREEMPTIVE_RG_HTTP                                  |
| PREEMPTIVE_SNI_SOCKET_BIND                          |
| PREEMPTIVE_SNI_SOCKET_LISTEN                        |
| PREEMPTIVE_SYNAPSESTREAMING_HTTP_EVENT_WAIT         |
| PREEMPTIVE_XCS_SNAPPY                               |
| PREEMPTIVE_XCS_THRIFT                               |
| PRU_PAGE_LSN_CACHE_LOCK                             |
| PVS_TRACK_PAGES_MUTEX                               |
| PWAIT_AUTO_START_AUDIT_SESSIONS                     |
| PWAIT_DBCC_FREEZEIO_MUTEX                           |
| PWAIT_DBCC_THAWIO_MUTEX                             |
| PWAIT_EXTERNAL_SERVICE_HUB                          |
| PWAIT_EXTERNAL_SERVICE_SEND_MESSAGE                 |
| PWAIT_FCS_MD_READ_AHEAD                             |
| PWAIT_FIDO_INDEXSTORE_CONNECTIONS_MANAGER_HASHTABLE |
| PWAIT_LATCH_ONLY                                    |
| PWAIT_PREEMPTIVE_OS_AUTHENTICATEDWEBCALL            |
| PWAIT_PREEMPTIVE_OS_AUTHENTICATIONTOKEN             |
| PWAIT_PREEMPTIVE_OS_CRYPTOPENSTORAGEPROVIDER        |
| PWAIT_PREEMPTIVE_OS_VSMATTESTATIONSERVICE           |
| PWAIT_PS_RBPEX_HOT_PAGES_RWLOCK                     |
| PWAIT_RBIO_IC_ACQUIRE_PAYLOAD                       |
| PWAIT_SBS_API_STATS_PUBLISH                         |
| PWAIT_SBS_IOAPI_STATS_PUBLISH                       |
| PWAIT_SYNAPSE_LINK_CAPTURE                          |
| PWAIT_SYNAPSE_LINK_COMMIT                           |
| PWAIT_SYNAPSE_LINK_DATA_EXPORT_SESSION              |
| PWAIT_SYNAPSE_LINK_DB_CLEANUP                       |
| PWAIT_SYNAPSE_LINK_END_HISTORY_SESSION              |
| PWAIT_SYNAPSE_LINK_GET_CURRENT_DB_LSN               |
| PWAIT_SYNAPSE_LINK_GET_DB_LIST,                     |
| PWAIT_SYNAPSE_LINK_GET_TABLE_HASHTABLE,             |
| PWAIT_SYNAPSE_LINK_LZN_API_CALL                     |
| PWAIT_SYNAPSE_LINK_MEM_CAP_THROTTLE                 |
| PWAIT_SYNAPSE_LINK_POPULATE_METADATA                |
| PWAIT_SYNAPSE_LINK_PUBLISH                          |
| PWAIT_SYNAPSE_LINK_UPDATE_TABLE_STATUS              |
| PWAIT_TOAD_CELL_ZONE                                |
| PWAIT_TOAD_DELTA_FORCE_ZONE                         |
| PWAIT_TOAD_OCCI_ZONE                                |
| PWAIT_TOAD_STAR_CELL_ZONE                           |
| PWAIT_TOAD_TUNING_ZONE                              |
| PWAIT_VLDB_PLANNED_FAILOVER_FORWARDER_THROTTLING    |
| PWAIT_VLDB_PLANNED_FAILOVER_START_THROTTLING        |
| PWAIT_VLDB_PLANNED_FAILOVER_STOP_THROTTLING         |
| PWAIT_XIO_REQUEST_IN_PROGRESS_LOCK                  |
| PWAIT_XLOG_POOL_EVICT_SLEEP                         |
| RBIO_AWAIT_RESPONSE                                 |
| RBIO_COMM_RETRY                                     |
| RBIO_COMM_UNINITIALIZE                              |
| RBIO_COMPLETE_LOG_READ                              |
| RBIO_CONNECTION_MGR                                 |
| RBIO_DB_RESTART                                     |
| RBIO_DB_TRANS_PRIMARY                               |
| RBIO_DBTRANSPRIMARY_SYNC                            |
| RBIO_FCB_DEFERRED_IO                                |
| RBIO_FCB_DEFERRED_IO_FN                             |
| RBIO_INITIALIZE_MUTEX                               |
| RBIO_PS_ACTOR_COLLECTION_RWLOCK                     |
| RBIO_RG_DESTAGE                                     |
| RBIO_RG_GEOREPLICA                                  |
| RBIO_RG_LOCALDESTAGE                                |
| RBIO_RG_MIGRATION_TARGET                            |
| RBIO_RG_REPLICA                                     |
| RBIO_RG_STORAGE                                     |
| RBIO_RG_STORAGE_CHECKPOINT                          |
| RBIO_UNINITIALIZE                                   |
| RBPEX_CHANGE_FILE_SIZE_MUTEX                        |
| RBPEX_CREATESNAPSHOT_RETRY                          |
| RBPEX_WRITEBEHIND_DB_STATE                          |
| RBPEXSHRINKTASK_SHUTDOWN                            |
| REPORTING_EXEC                                      |
| RG_MANAGER_VHD_GROWTH                               |
| RG_SERVER_CONFIGS                                   |
| ROW_GROUP_POPULATION                                |
| SBS_CONTEXT                                         |
| SBS_LRU_EVICTION                                    |
| SEEDING_COMPLETED_MUTEX                             |
| SEEDING_SELF_THROTTLING                             |
| SESSION_MGR                                         |
| SLEEP_RBPEXSHRINKTASK                               |
| SLEEP_SAFEMODE                                      |
| SOS_RG_MEM_TARGET_LOCK                              |
| SP_RESOLVE_DEFERRED                                 |
| SQLPAL_PREEMPTIVE_WAIT                              |
| SQP_STATS_REPORTING                                 |
| START_BACKGROUND_TASK_MUTEX                         |
| STREAMING_SERVICE_RESTART_FINISHED                  |
| STREAMING_SERVICE_SEND_KILL_PROCESS                 |
| STRIPE_META_UPDATE                                  |
| SYNAPSELINK_CAPTURE_JOBTASK_ACCESS                  |
| SYNAPSELINK_COMMIT_JOBTASK_ACCESS                   |
| SYNAPSELINK_FAILBATCH_ACCESS                        |
| SYNAPSELINK_PUBLISH_JOBTASK_ACCESS                  |
| SYNAPSELINK_SNAPSHOT_JOBTASK_ACCESS                 |
| TELEMETRY_SNAP                                      |
| THROTTLE_LOG_RATE_LOG_STORAGE                       |
| TOAD_AUTOSTATISTICS_ZONE                            |
| TOAD_DELETEBITMAP_ZONE                              |
| TOAD_DISCOVERY                                      |
| TOAD_FUTURE                                         |
| TOAD_QUEUE                                          |
| TOAD_RESOURCE_SEMAPHORE                             |
| TOAD_SHUTDOWN                                       |
| VERSION_LEASE_HASH_LOCK                             |
| VLDB_DUMP_LOG_LOCK                                  |
| VLDB_SNAPSHOT_MUTEX                                 |
| WAIT_AE_KEYADD                                      |
| WAIT_AETM_CRITICAL_SECTION                          |
| WAIT_AETM_ENCLAVE_WORKER_SLEEP                      |
| WAIT_AETM_HOST_WORKER_SLEEP                         |
| WAIT_DELTA_CACHE                                    |
| WAIT_DW_TX_EXTERNALIZATION_IO_COMPLETION            |
| WAIT_FCS_MD_RESOLVE                                 |
| WAIT_FIDO_GLMS_ASYNC_WORKER                         |
| WAIT_FIDO_GLMS_SYNC                                 |
| WAIT_FIDO_GLMS_UT                                   |
| WAIT_FOR_MS_POLL                                    |
| WAIT_FULLTEXT_CRAWL_MANAGER                         |
| WAIT_GC_IO_COMPLETION                               |
| WAIT_GLM_CONTROLLER                                 |
| WAIT_GLM_DB                                         |
| WAIT_GLM_DEK_LIST                                   |
| WAIT_GLM_SYNC_CLIENT                                |
| WAIT_GLMS_ACCESS                                    |
| WAIT_GLMS_LOG                                       |
| WAIT_GLMS_LOG_BLOCK_IO                              |
| WAIT_INDEXSTORE_COMPUTE_PARTITION_BUCKETS           |
| WAIT_INDEXSTORE_LIMIT_REQUESTS                      |
| WAIT_LCKMGR                                         |
| WAIT_LM_CREATE                                      |
| WAIT_LOG_REPLICA_MGR_HASH_LOCK                      |
| WAIT_LOG_REPLICA_ROLE_STABILITY_LOCK                |
| WAIT_LOG_REPLICA_WRITE_LEASE_PROPERTY_HASH_LOCK     |
| WAIT_ODBC                                           |
| WAIT_PHYSICAL_CATALOG                               |
| WAIT_RBAC_PROVIDER                                  |
| WAIT_RBAC_SESSION_AUDIT                             |
| WAIT_RBAC_SESSIONS_AUDIT                            |
| WAIT_RBPEX_WRITEBEHIND_CKPT_CONSISTENCY_LOCK        |
| WAIT_RETENTION_POLICY                               |
| WAIT_RM_RBIOCONNECTION_INIT                         |
| WAIT_SEQUENCE                                       |
| WAIT_SYNC_LAG_PARTNERS_LIST                         |
| WAIT_TRANSPORT                                      |
| WAIT_TSQL_TASK                                      |
| WAIT_VLF_IO_TRACKER_DRAIN_IO                        |
| WAIT_XCS_LOCATOR_FETCH                              |
| WAIT_XLOG_REPLICA_BG_TASK_LOCK                      |
| WMI_REGISTRATION                                    |
| XDB_PKG_LAUNCHER_CONNECTION                         |
| XE_LIVE_TARGET_MUTEX                                |
| XE_PREDICATE_HEAP_ALLOC                             |
| XE_PREDICATE_HEAP_FREE                              |
| XE_RING_TARGET_MUTEX                                |
| XE_SQL_TEXT_HEAP_ALLOC                              |
| XE_SQL_TEXT_HEAP_FREE                               |
| XE_SQL_TEXT_PREDICATE                               |
| XFILE_CACHE_XACT                                    |
| XFILE_DISPATCH                                      |
| XFILE_OBJECT_POOL                                   |
| XFILE_TASK_PROXY_ABORT                              |
| XLOG_BGTHREAD                                       |
| XLOG_BROKER_UNLINKING_IN_PROGRESS                   |
| XLOG_BROKER_WAIT_PAGE                               |
| XLOG_BROKER_WAITFULL                                |
| XLOG_BROKER_WAITMAXALLOWED                          |
| XLOG_EOL_REQUEST_NOTIFICATION                       |
| XLOG_GAPFILLERTHREAD                                |
| XLOG_LC_FWD_SEEDING                                 |
| XLOG_LC_REVERSE_SEEDING                             |
| XLOG_LC_SEEDING_VDL_ADVANCE                         |
| XLOG_POOL_FILLER_SLEEP                              |
| XLOG_POOL_QUERY_WAIT                                |
| XLOG_POOL_SHUTDOWN                                  |
| XLOG_SPACEMGR_INITIALIZE                            |
| XLOG_TOSFILE_GET_ITER_PROXY                         |
| XLOGREAD_SIGNAL                                     |
+-----------------------------------------------------+

Thanks for reading!

Going Further

If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount on to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that and need to solve performance problems quickly.