Groove Is In The Heart
When Brent posted about the availability of, and disappointment with creating ordered column store indexes in SQL Server 2022, I got to work.
I can’t have my dear friend Brent being all distraught with …
Category: columnstore
Ordered Columnstore Indexes on SQL Server 2022 CTP 2.0
Brent recently blogged about ordered columnstore indexes in SQL Server 2022 and had some trouble with them, so I decided to take a look into the mechanics of the feature. I’m testing on SQL Server 2022 CTP 2.0.
What does
…What Does And Doesn’t Belong In A SQL Server Data Warehouse
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
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