You may have noticed that TF 3656 appears to no longer work in SQL Server 2019 RC1. Symbols are not resolved in Extended Events event data even with that trace flag enabled. Trace flag 2592 must also be enabled to …
Author: Joe Obbish
What is the PVS_PREALLOCATE wait type?
I was workload testing on SQL Server 2019 RC1 when I ran into a wait type I’d never noticed before: PVS_PREALLOCATE. Wait seconds per second was about 2.5 which was pretty high for this workload. Based on the name it …
Columnstore Precon Details
I thought it could be helpful to go into more detail for what I plan to present at the columnstore precon that’s part of SQL Saturday New York City. Note that everything here is subject to change. I tried …
Thoughts on MAXDOP
Microsoft recently published new guidance on setting server level MAXDOP. I hope to help the community by analyzing the new guidance and offering some of my own thoughts on query parallelism.
Line by line
Documentation is meant to be shared …
T-SQL Protip: watch those TOPs without ORDER BY
In the documentation for TOP, the following is listed as a best practice:
…In a SELECT statement, always use an ORDER BY clause with the TOP clause. Because, it’s the only way to predictably indicate which rows are affected
Why is SYSDATETIME() slower than SYSUTCDATETIME()?
Consider the following code that calls SYSDATETIME() 10 million times in a loop:
GO CREATE OR ALTER PROCEDURE #p_local AS BEGIN SET NOCOUNT ON; DECLARE @dummy DATETIME2(7), @loops INT = 0; WHILE @loops <= 10000000 BEGIN SET @dummy = SYSDATETIME();…
Breaking Columnstore Delta Rowgroups
The documentation on delta rowgroups says:
…A delta rowgroup is a clustered index that’s used only with columnstore indexes. It improves columnstore compression and performance by storing rows until the number of rows reaches a threshold and are then moved
SQL Server 2017 CU14 Change to CCI Insert Memory Time-out Behavior
A KB article was recently published that suggested that memory time-out behavior was changed for CCI inserts:
…When you try to create a Columnstore Index on a table in Microsoft SQL Server 2016 or 2017, the request may fail after
Extra Documentation for KB Article 4462481
SQL Server KB 4462481 is a bit light on details:
Assume that you use Microsoft SQL Server data warehousing. When you run parallel insert queries to insert data into a clustered columnstore index, the query operation freezes.
To quote one …
Resource Governor MAXDOP Setting Can Lead to Poor Plan Choices
Resource Governor can be used to enforce a hard cap on query MAXDOP, unlike the sp_configure setting. However, query plan compilation does not take such a MAXDOP limit into account. As a result, limiting MAXDOP through Resource Governor can …