Press "Enter" to skip to content

Category: Performance Tuning

Don’t Store Files in the Database

Published 2022-07-19 by Kevin Feasel

Josh Darnell provides timeless advice:

As Deborah’s invite post suggests, this is a “that one time at that client” story. I was working at a consulting firm, and we had written an app for a particular client. Part of this application’s workflow involved users uploading images alongside some other information. These were not particularly large images in the grand scheme of things – they were taken by a microscope, and were a few kilobytes each, maybe.

However, this app had been in use for a long time. And as you might have guessed from the title of this post, each of these images was stored in a single table in the database that backed this application

Yeah, that’ll be a problem… Read on for some recommendations on how to avoid the issue. One thing I would add is FileTable, which came out in SQL Server 2012. In that case, the files are actually stored on disk but are queryable via T-SQL. It introduces its own set of problems but I do have some fond feelings about having used FileTable in the past.

Comments closed

Filtered Statistics and Table Performance

Published 2022-07-11 by Kevin Feasel

Guy Glantser provides a use case for filtered statistics:

Let’s say you have a very large table on a SQL Server 2012 Standard Edition instance. This means: old cardinality estimator and no partitioning. The table has a DATETIME column, which is ever-increasing, and it contains 5 years of data (it has to, due to regulations). The auto-update statistics kicks in only every 4 days, more or less, even when trace flag 2371 is enabled. The problem is that users usually query the table for the last day, and only rarely need to access older data. Since auto-update statistics uses a very small sample rate for very large tables, the result is not very accurate. The bottom line of all this is that most of the time you get a poor execution plan, because the optimizer estimates very few rows, while in fact there are many rows. What can you do?

I’m not sure I’ve ever used filtered statistics but it is good to know such a thing exists.

Comments closed

Creating Goal Post Tables

Published 2022-07-08 by Kevin Feasel

Aaron Bertrand solves a problem of unchecked growth:

Many of us deal with logging tables that grow unchecked for years, while reporting queries against them are expected to continue running quickly regardless of the size of the table. A common issue when querying by a date range is that the clustered index is on something else (say, an IDENTITY column). This will often result in a full clustered index scan, since SQL Server doesn’t have an efficient way to find the first or last row within the specified range. This means the same query will get slower and slower as the table grows.

I like this solution but only in cases where you expect no after-the-fact updates to dates, such as late-arriving date information or “fixing” the date later. With Aaron’s log example, where we expect log entries to be immutable, this can work really well in a “pseudo-materialized view” sort of way.

Comments closed

DOP Feedback in SQL Server 2022

Published 2022-06-22 by Kevin Feasel

Erik Darling talks about a potentially exciting feature:

I’m not going to demo DOP feedback in this post, I’m just going to show you the situation that it hopes to improve upon.

To do that, I’m going to run a simple aggregation query at different degrees of parallelism, and show you the changes in query timing.

Figuring out where that elbow is (in other words, when you move from approximately-linear gains to sub-linear gains) can be extremely helpful. Of course, this is like solving a partial equilibrium problem: it’s part of the problem but there’s a whole separate general equilibrium problem from there—what’s the best number of cores for this query with the constraint that I have all of these other queries running on a busy server? But before I make it seem like I’m minimizing the value of this, the partial answer will, in many circumstances, be good enough.

Comments closed

Making DAX’s SWITCH Run Fastest

Published 2022-06-21 by Kevin Feasel

Marco Russo and Alberto Ferrari explain when SWITCH is fast and when it’s not so fast:

The SWITCH function in DAX is widely used to define different execution paths depending on the condition of an expression. A very common case is when we want to check the state of a slicer, so that the selection made by the report user directly affects the result of a DAX formula.

Read on for three separate cases and how they can affect the performance of the SWITCH function.

Comments closed

Automating Parallelism Decisions in Flink Batch Jobs

Published 2022-06-17 by Kevin Feasel

Lijie Wang and Zhu Zhu describe Apache Flink’s batch scheduler:

Deciding proper parallelisms of operators is not an easy work for many users. For batch jobs, a small parallelism may result in long execution time and big failover regression. While an unnecessary large parallelism may result in resource waste and more overhead cost in task deployment and network shuffling.

To decide a proper parallelism, one needs to know how much data each operator needs to process. However, It can be hard to predict data volume to be processed by a job because it can be different everyday. And it can be harder or even impossible (due to complex operators or UDFs) to predict data volume to be processed by each operator.

To solve this problem, we introduced the adaptive batch scheduler in Flink 1.15. The adaptive batch scheduler can automatically decide parallelism of an operator according to the size of its consumed datasets. 

Read on to see some of the benefits of using the adaptive batch scheduler, as well as some of the decision points it uses along the way.

Comments closed

Parameter Sensitivity Plan Optimization and Monitoring Scripts

Published 2022-06-16 by Kevin Feasel

Erik Darling gives us a warning:

You can read the full documentation here. But you don’t read the documentation, and the docs are missing some details at the moment anyway.

– It only works on equality predicates right now

– It only works on one predicate per query

– It only gives you three query plan choices, based on stats buckets

There’s also some additional notes in the docs that I’m going to reproduce here, because this is where you’re gonna get tripped up, if your scripts associate statements in the case with calling stored procedures, or using object identifiers from Query Store.

It’s not a deal-breaker but it does make things a lot harder for tool writers, as Erik points out. Hopefully there’s some way to tie this all together before GA.

Comments closed

Performance Concerns around GENERATE_SERIES()

Published 2022-06-10 by Kevin Feasel

Erik Darling looks the gift horse in the mouth:

Quite a while back, I blogged about how much I’d love to have this as a function. We… sort of got it. It doesn’t do dates natively, but you can work around some of that with date functions.

In this post, I want to go over some of the disappointing performance issues I found when testing this function out.

It’s not good news but it’s important to understand if you’re planning to use this when SQL Server 2022 comes out. Also, the things Erik mentions are easier to fix (potentially) than modifications in the signature of a function, as they’re internal. I wouldn’t guarantee that things will certainly be better but there are some good cases when we saw performance improvements between early CTPs and RTM.

Comments closed

Downplaying Logical Reads

Published 2022-06-07 by Kevin Feasel

Erik Darling lays out an argument:

I decided to expand on some scripts to look at how queries use CPU and perform reads, and found some really interesting stuff. I’ll talk through some results and how I’d approach tuning them afterwards.

Interestingly, I just dealt with a mini-consulting engagement in which I saw the opposite: CPU sitting there twiddling its thumbs because of I/O insanity—and not even slow disks. In that case, the advice generally was “add this obviously missing index from this rather large table and stop scanning when you get 1 row on these really busy queries.” There was a little more nuance than that—and in fairness, physical reads were bad as well—but that’s why we investigate systematically.

Also, I generally accede to Erik’s point: for most busy environments, logical reads are unlikely to be the constraining factor and there are plenty of times where I choose the query form with more logical reads because it reduces CPU and memory requirements.

Comments closed