A Fine Slice Of SQL Server
Laurenz Albe offers up some thoughts:
Sometimes one of our customers looks at the most time consuming statements in a database (either with
pg_stat_statementsor with pgBadger) and findsCOMMITin the high ranks. Normally,COMMITis a very fast statement in PostgreSQL, so that is worth investigating. In this article, I will explore the possible reasons for a slowCOMMITand discuss what you can do about it.
Read on for those reasons.
Comments closedErik Darling makes a comparison:
I often see clients using forced plans or plan guides (yes, even still, to this day) to deal with various SQL Server performance problems with plans changing.
There’s usually an execution plan or two floating around that seems to be a good general idea for a given query, and a couple weird high-end and low-end outliers for very specific populations of values.
Read the whole thing, of course.
In defense of plan guides, the company I used to work for had a few—maybe three or four in total—because of really weird data skew problems on database 106 out of 700 (or so)—because there’s always one customer that makes wildly different use of the system than everyone else. And so a query that worked perfectly fine for 699 databases (or so) flops like a fish out of water for this one database with this one customer’s data in it. So the plan guide was a nicer expediency than optimizing for mediocre on all 700 (or so) databases.
1 CommentErik Darling gives us an overview:
We can surmise a few things from this plan:
- If there are good indexes, SQL Server isn’t using them
- That hash spill is some extra kind of bad news
- Spools remain a reliable indicator that something is terribly wrong
Okay, so I’m kidding a bit on the last point. Sorta.
Read on for the scenario and how Erik would handle this case.
Comments closedErik Darling notices me in a leg cast staring through his window with my telescope:
A long time ago, I complained that wait stats logged by actual execution plans don’t show lock waits. That seemed like a pretty big deal, because if you’re running a query and wondering why sometimes it’s fast and sometimes it’s slow, that could be a pretty huge hint.
Click through for the full story. Getting actual waits is indeed a big deal, and way easier than any of the alternatives like spinning up a special extended events session or yelling at everyone not to use the server for a few minutes while you ran your query.
Comments closedGabrielle Roth shares some advice:
For PGSQL Phriday #016, Ryan’s asked us to “…discuss your process for tuning difficult queries. Specifically, try to focus on that one problematic query that really challenged you and you always use it as an example when helping or teaching others your methods.”
Here are the generic steps I take, mostly in order.
Click through for those tips, as well as an example of using join_collapse_limit in practice to tame an unruly query.
Reducing waits is a great way to improve the performance of your SQL Servers. Minimizing PAGELATCH_EX and PAGELATCH_SH wait types are more involved than most. There are generally two causes; one of which is largely solved in recent versions, and one which requires real thought and planning to resolve.
Read on for both of those causes, as well as several tips you can use to minimize the risk of waiting.
Comments closedHugo Kornelis gives us a primer on cursors:
And yes, I know the mantra. Do not use cursors. They are slow. There is (almost) always a faster set-based alternative available. So why would I even waste blog space on cursors, when the only smart thing to do is to rip them out and replace them with a set-based alternative?
Well, there are, in fact, many reasons. The “almost” above suggests that there are still cases where row by row processing is in fact the most efficient method. There are cases where we use a cursor, even though set-based is slower, because we need to call a stored procedure for each row returned, and that stored procedure is too complex to be changed to process an entire set at once. Or, perhaps, you just inherited existing code that uses a cursor, and you need to fix the immediate performance issues now, so you cannot afford the time investment to rewrite the row by row logic to set-based logic.
Another example of this was calculating running totals prior to SQL Server 2012’s support of aggregate window functions. The cursor approach was considerably faster than the self-join approach because the self-join approach required joining on an inequality statement. Granted, there was the “quirky update” technique, but that depended on an accident of SQL Server internals that Microsoft never officially supported and could have broken at any time.
Comments closedChad Callihan hits the Turbo button:
How familiar are you with the OPTION (FAST N) query hint? It’s not one that I had ever used, so I decided to experiment with it a bit. Let’s look at what it does and how to apply it to a query.
My recollection is that this query hint highly prioritizes “streaming” operators and de-prioritizes operators that work on the whole dataset as a block, such as sorts. This means, for example, that you’ll get more nested loops joins and natural merge joins, but few sort + merge or hash match joins. It may also make some queries take considerably longer as a result.
Comments closedErik Darling has Blondie stuck in my head:
The OR operator is a perfectly valid one to use in SQL statements. If you use an IN clause, there’s a reasonable chance that the optimizer will convert it to a series of OR statements.
For example,
IN(1, 2, 3)could end up being= 1 OR = 2 OR = 3without you doing a darn thing. Optimizers are funny like that. Funny little bunnies.The problem generally isn’t when asking for IN or OR for a single column, with a list of literal values, the problem is usually when you:
- Use OR across multiple where clause columns
- Use OR in a join clause of any variety
- Use OR to handle NULL parameters or variables
This is an excellent, detailed article and well worth the read.
Comments closedErik Darling doesn’t focus on logical reads:
To summarize the video a little bit:
- High average or total logical reads isn’t a guarantee that a query is slow
- Looking for high average CPU and duration queries is a better metric
- You may see logical reads go up or down as you make queries faster
- For I/O bound workloads, you’re better off looking for queries with a lot of physical reads
I agree with Erik. Disk has gotten so much faster, especially if you’re on all-flash arrays or (even better) direct attached nVME storage. Even relatively old SSDs are still a couple orders of magnitude faster than the spinning rust we typically dealt with 15 years ago.
Sometimes, the faster query is one that requires more logical reads. APPLY-based queries typically fall into that category: I might have 10x as many logical reads but the query takes half the time (or less) to finish using a similar percentage reduction of CPU time.
Comments closed