A Fine Slice Of SQL Server
Vlad Drumea tests Betteridge’s Law of Headlines:
It’s 3AM and I’m curious if Query Store plan forcing works on queries that use the RECOMPILE hint (aka OPTION(RECOMPILE) ).
So might as well make a blog post out of it.
Hmm, on the one hand, Vlad blatantly violates Betteridge’s Law of Headlines, which loses points. On the other hand, Vlad dunks hard on LLMs, gaining points. I’m so conflicted right now…
But not about reading Vlad’s post. Go read it.
Leave a CommentForrest McDaniel has a public service announcement:
In case you haven’t heard, moral decline is everywhere. The latest generation of query operators is a prime example of this rot. “But this is programming” you say, “what do morals have to do with anything?” No. This is SQL. And for us, we have the term morally equivalent plans which allows me to finally have an ethical excuse for sprinkling a post with degenerate puns.
I bit my tongue specifically to avoid doing exactly what Forrest does. So click through to read all of the terrible puns.
Leave a CommentKendra Little does a bit of index management:
Dear Postgres, Why won’t you use my covering index?
Lately I’ve been learning to tune queries running against PostgreSQL, and it’s pretty delightful. One fun question that I worked through struck me something that most Postgres users probably encounter one way or another: sometimes you may create the perfect index that covers a given query, but the query planner will choose to ignore it and scan the base table.
Why in the world would it do that?
Read on to learn why.
Leave a CommentKellyn Gorman continues a series on PostgreSQL for Oracle DBAs:
As an Oracle DBA venturing into the world of PostgreSQL, one of the most important areas to get comfortable with is performance tuning. While Oracle’s Cost-Based Optimizer (CBO) is a well-known powerhouse that many DBAs have learned to both respect and wrestle with, PostgreSQL offers its own sophisticated query planner that behaves differently. Understanding these differences is key to becoming proficient with PostgreSQL as we step into tuning.
Read on to gain an overview of what options are available on the PostgreSQL side and how they differ from what’s available in Oracle.
Comments closedGrant Fritchey knows one way to solve the problem:
A client asked us to tell them when a query ran long. Simple. We have a long running query alert, all built in to Redgate Monitor, so, done. No, see, we like getting alerted when queries run long, but not really long, plus we’re more concerned with just one database.
Click through for the story and how Grant was able to help out the client. Also, read the comments for an entry by Special Guest Star Erik Darling.
Comments closedLouis Davidson gives it the college try:
When I was perusing my LinkedIn feed the other day, I came across this thread about using SELECT *. In one of the replies, Aaron Cutshall noted that: “Another real performance killer is
SELECT DISTINCTespecially when combined withUNION. I have a whole list of commonly used hidden performance killers!”To which started my brain thinking… What does happen when you use these together? And when you use UNION on a set with non-distinct rows, what happens. So for the next few hours I started writing.
Read on for Louis’s findings.
Comments closedBrent Ozar is down with OPP(O):
SQL Server 2025 improved PSPO to handle multiple predicates that might have parameter sensitivity, and that’s great! I love it when Microsoft ships a v1 feature, and then gradually iterates over to make it better. Adaptive Memory Grants were a similar investment that got improved over time, and today they’re fantastic.
SQL Server 2025 introduces another feature to mitigate parameter sniffing problems: Optional Parameter Plan Optimization (OPPO). It ain’t perfect today – in fact, it’s pretty doggone limited, like PSPO was when it first shipped, but I have hopes that SQL Server vNext will make it actually usable. Let’s discuss what we’ve got today first.
Okay, I really had to stretch the truth to make my lead-in work, but I’m too proud of it to change anything. Click through to see where OPPO is today. Even with just one optional parameter working well, there is still a class of stored procedures that this can help: the “get by one ID, or get me all of them” type.
Comments closedIn this video, I show how we can use the APPLY operator to operate on ad hoc functions. That leads to a powerful use case: pre-aggregating data.
Every once in a while, this tip will save a considerable amount of CPU time and database effort.
Comments closedShayon Mukherjee continues pulling on a thread:
A few days ago, I wrote about a surprising planner behavior with CTEs, DELETE, and LIMIT in PostgreSQL, a piece I hastily put together on a bus ride. That post clearly only scratched the surface of a deeper issue that I’ve since spent way too many hours exploring. So here are some more formed thoughts and findings.
Click through for a deeper dive into the topic, including some key takeaways.
Comments closedMehdi Ghapanvari compares two ways of holding temporary data:
Do you ever wonder why your query is slow when you use a table variable in a join operation? If you are interested in why this happens and how to improve query performance in such a scenario, keep reading to learn more.
Click through for the full article. This focuses entirely on classic table variables and not memory-optimized table variables, the latter of which can significantly improve performance in specific circumstances.
Comments closed