No Way, No How
This is a list of things I see in data warehouses that make me physically ill:
- Unique constraints of any kind: Primary Keys, Indexes, etc. Make things unique during your staging process. Don’t make your indexes
Category: columnstore
Blocking on Columnstore Indexes that RCSI and NOLOCK Don’t Resolve
I recently ran into a production issue where a SELECT query that referenced a NOLOCK-hinted table was hitting a 30 second query timeout. Query store wait stats suggested that the issue was blocking on a table with a nonclustered columnstore …
Signs You Need Batch Mode To Make Your SQL Server Queries Faster
My Name Is
I speak with a lot of DBAs and developers who have either heard nothing about column store and batch mode, or they’ve only heard the bare minimum and aren’t sure where it can help them.
Here’s a …
Dealing With Wide Missing Index Requests In SQL Server
We’ve All Been There
You’re running a query that selects a lot of columns, and you get a missing index request.
For the sake of brevity, let’s say it’s a query like this:
SELECT
p.*
FROM dbo.Posts AS p
WHERE … I/O Bound vs CPU Bound Queries In SQL Server
Handcuffed
When you’re looking for queries to tune, it’s important to understand which part is causing the slowdown.
That’s why Actual Execution plans are so valuable in newer versions of SQL Server and SSMS. Getting to see operator timing and …
A Silent Bug With Clustered Column Store Indexes And Computed Columns In SQL Server 2019
Oh Boy
This bug has been reported to the proper authorities, and this blog post is not to dunk on the Summer Intern responsible for column store indexes, but to warn you to not do this, because I don’t know …
How Useful Is Column Store Indexing In SQL Server Standard Edition?
Speed Limit
When I’m blogging about performance tuning, most of it is from the perspective of Enterprise Edition. That’s where you need to be if you’re serious about getting SQL Server to go as fast as possible. Between the unrealistic …
Batch Mode On Row Store vs Batch Mode Tricks For Performance Tuning SQL Server Queries
Quiet Time
I think Batch Mode is quite spiffy for the right kind of query, but up until SQL Server 2019, we had to play some tricks to get it:
- Do a funny join to an empty table with a
Making The Most Of Temp Tables Part 4: Batch Mode
Le Big Zoom Zoom
When you have queries that need to process a lot of data, and probably do some aggregations over that lot-of-data, batch mode is usually the thing you want.
Originally introduced to accompany column store indexes, it …
Columnstore Loading on Eight Socket Servers
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 …