A Fine Slice Of SQL Server
Aaron Bertrand answers a question:
Recently someone posted a question where they couldn’t quite figure out how to construct a predicate based on a
bitparameter. They tried to write a procedure like this, which wouldn’t parse, of course:
At first, I thought Aaron meant querying integer bitmasks in T-SQL, in which case, the best strategy is “don’t.” But this is a different and much more useful scenario.
Comments closedRob Farley eliminates a self-join:
Sometimes query tuning involves taking a different approach to a problem. And given that other tuning options might be creating index(es) or redesigning tables – both of which are much more permanent changes to an environment – rewriting a query can often be just right.
Window functions seem to pop up quite often when rewriting queries, and an example around this would be appropriate for this month’s T-SQL Tuesday, hosted by Steve Jones (@way0utwest at X/Twitter).
Read on for the all-too-common scenario and how Rob improves an existing query.
Comments closedReitse Eskens phones a friend:
In my previous blogpost (Click here to read) I wrote about a query that just wouldn’t go parallel. This sparked some discussion and interest from a few people who were very kind and helpful with their suggestions and even deep dives into the query plans, execution statistics etc.
To make one thing very clear, this blog is 99% their work, only the typing and rephrasing is mine. This also means that mistakes are mine as I’m trying to join the different inputs together into a logical story. So let me introduce you to the heroes, order by first name ascending.
Read on to see what people suggested and the effect that had.
Comments closedPaul White unravels a mystery:
Batch mode sorting was added to SQL Server in the 2016 release under compatibility level 130. Most of the time, a batch mode sort will be much faster than the row mode equivalent.
This post is about an important exception to this rule, as recently reported by Erik Darling (video).
No doubt you’ll visit both links before reading on, but to summarize, the issue is that batch mode sorts are very slow when they spill—much slower than an equivalent row mode sort.
Read the whole thing. Paul does a great job illuminating us.
Comments closedSo what’s up this time. Our client has moved to Azure in classic lift and shift scenario. Well, almost. They’ve deployed new VM’s and installed SQL Server 2019 Standard in nice DTAP setting. The VM’s are standard E16-4as-v4 SKU. 4 vcpus and 128 GB memory. The disks are Premium SSD LRS ones with 2300 Max IOPS.
Their on-premises environment was a SQL Server 2016 standard edition running on a virtualisation layer with 128 GB of memory and 8 cores.
In both cases there are 2 numa nodes dividing the amount of cores between them.
Read on to learn more about the problem and what Reitse & co did to resolve it. Also check out the comments—Daniel Hutmacher, in particular, I think has the reason nailed.
Comments closedEtienne Lopes has some fun with a feature:
Before 2012, creating analytical queries (that usually scan many rows and have lots of aggregations) from big OLTP databases to feed real-time based reports used in decision making processes, could be quite challenging. Then ColumnStore Indexes arrived and they’ve been enhanced overtime, offering amazing gains both in performance and storage.
Unfortunately, regarding pure OLTP databases, there are many situations in which ColumnStore Indexes can’t (or won’t) be used. There are some great performance enhancements present in columnstore that’s for sure and today I’m going to speak about one that became automatically available since SQL Server 2019 for “traditional” RowStore tables. It’s called “Batch Mode on Rowstore” and it can really boost some of our analytical queries over the “traditional tables” without any effort from our side!
There are a series of specific rules you need to hit but if you hit them, I’ve noticed about a 3x performance gain with you doing nothing at all.
Comments closedMuhammad Ali explains explain plans:
In a previous post titled Exploring Postgres Performance: A Deep Dive into pg_stat_statements, we discussed the utility of pg_stat_statements as a tool for identifying resource-intensive queries in terms of memory, CPU, or I/O usage.
Now, let’s suppose you’ve pinpointed certain queries you’d like to investigate further. The EXPLAIN command is used to generate execution plans. It includes:
Read on to see what it includes, how you can generate an explain plan, and some of the optional settings.
Comments closedMarco Russo and Alberto Ferrari speed things up a bit:
Pushing calculations down to the VertiPaq storage engine is always a good practice. Sometimes this is not feasible. However, carefully analyzing the aggregated expression can lead to optimization ideas that produce excellent query plans.
DAX developers should not be scared of iterators. Their performance is great as long as the expression computed during the iteration can be pushed down to the VertiPaq storage engine.
Read on to understand what they mean by callback and the enormous performance cost you’ll want to avoid.
Comments closedPaul White explains the performance benefits of fast key optimization and when you can get them:
SQL Server can be called upon to sort a variety of data types. To facilitate this, the sorting code normally calls out to a specific comparator to determine how two compared values should sort: lower, higher, or equal.
Although calling comparator code has low overhead, performing enough of them can cause noticeable performance differences.
To address this, SQL Server has always (since at least version 7) supported a fast key optimization for simple data types.
Click through to learn which data types support fast key optimization and to gain a feeling of the performance impact.
Comments closedDavid Klee lays out an argument:
SQL Server query developers, listen up! Query execution time is not everything you should be worried about. You need to examine the parse and compilation time for each of your queries too.
Read on for the brunt of David’s argument. There are things you can do about query compilation time, starting with database design (normalize tables and include key constraints, include appropriate indexes, etc.) and continuing with query design (keep queries simple, limit use of functions, limit use of nested views, break complicated queries into multiple steps and use temp tables as intermediaries, etc.). One thing about compilation time, however, is that it doesn’t matter as much if you retain that plan for a while and reuse it a lot.
Comments closed