I had a brief opportunity to do SQL Server workload testing on an eight socket server. It didn’t go well.
Sockets
I’ll give an extremely brief introduction to NUMA and sockets because I have a bit more free time these …
Author: Joe Obbish
The Trillion Row Operator
SQL Server friend and excellent webmaster Mr. Erik C. Darling showed me a query plan gone wrong:
48 billion rows for a single operator is certainly a large number for most workloads. I ended up completely missing the point and …
Using Exchange Demand Partitioning to Improve Parallel Query Scalability
One of our SQL Server workloads runs many concurrent parallel queries on a possibly already busy server. Other work occurring on the server can have dramatic effects on parallel query runtimes. One way to improve scalability of parallel query runtimes …
Trace flag 3656 is not sufficient for symbol resolution on SQL Server 2019
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 …
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