A Fine Slice Of SQL Server
Erik Darling shows off a nicety in SQL Server 2019 and later:
Note that this script does not assemble the missing index definition for you. That stuff is all readily available in the query plans that get returned here, and of course the missing index feature has many caveats and limitations to it.
You should, as often as possible, execute the query and collect the actual execution plan to see where the time is spent before adding anything in.
Read on for the script.
Comments closedKristyna Hughes calculates weekdays on the fly:
Knowing the days between events is a fairly common reporting request because a lot of reporting is created to track SLA’s (service level agreement) and other KPI’s (key performance indicators). While getting the days between two dates is fairly easy to achieve, they tend to follow up and ask how many week days there are between two timed events. For example, one company may have a SLA to ship an order within three week days of the order being placed or else a discount is applied to the order. In this case, I would highly recommend that the company have software that calculates these days in the background and stores the actual week days between order date and ship date in a database. Unfortunately, many companies create policies like this without considering future reporting needs and these values have to be calculated on the backend.
Click through for the code but be sure to read the note that this is all weekdays, including holidays.
Comments closedChris Webb gives us a primer on Power Pages:
So what? I’m not a web designer and I’m pretty sure most of you aren’t either, so why blog about it here? Most data and BI people don’t need to build web sites… do they?
Well I was playing around with it and noticed one important detail:
Read on to see what that one important detail is and how you can use it.
Comments closedColin Gillespie and Jack Walton tackle a common training problem:
As the number of courses we offer increased, so did the maintenance burden of our associated training materials (lecture notes, slides, exercises, and more). To ease this burden, and to assist in ensuring that our training materials build consistently, we developed an R package called {jrNotes2}. Amongst other things, this package ensures that all courses:
– have identical “template files”:
.gitlab-ci.yml,.gitignore,Makefiles,index.Rmd, …;– have the same directory structure, and
– pass a set of quality-assurance checks.
This is smart but read on to see why it’s still a challenge. This is especially true in the R and Python worlds, where breaking changes seem to be so common.
Comments closedRoman Khachatryan and Yuan Mei deal with transaction log issues:
State backends don’t start any snapshotting work until the task receives at least one checkpoint barrier, increasing the effective checkpoint duration. This is suboptimal if the upload time is comparable to the checkpoint interval; instead, a snapshot could be uploaded continuously throughout the interval.
This work discusses the mechanism introduced in Flink 1.15 to address the above cases by continuously persisting state changes on non-volatile storage while performing materialization in the background. The basic idea is described in the following section, and then important implementation details are highlighted. Subsequent sections discuss benchmarking results, limitations, and future work.
Read on to see what they did.
Comments closedKai Waehner can’t return to sender:
This article focuses on the data streaming platform Apache Kafka. The main reason for putting a message into a DLQ in Kafka is usually a bad message format or invalid/missing message content. For instance, an application error occurs if a value is expected to be an Integer, but the producer sends a String. In more dynamic environments, a “Topic does not exist” exception might be another error why the message cannot be delivered.
Therefore, as so often, don’t use the knowledge from your existing middleware experience. Message Queue middleware, such as JMS-compliant IBM MQ, TIBCO EMS, or RabbitMQ, works differently than a distributed commit log like Kafka. A DLQ in a message queue is used in message queuing systems for many other reasons that do not map one-to-one to Kafka. For instance, the message in an MQ system expires because of per-message TTL (time to live).
Hence, the main reason for putting messages into a DLQ in Kafka is a bad message format or invalid/missing message content.
Read on to learn the Kafka-based approach to dealing with bad messages rather than using a Dead Letter Queue.
Comments closedErik Darling delves into the depths of missing indexes:
Breaking each of those down, the only one that has a concrete meaning is Uses, but that of course doesn’t mean that a query took a long time or is even terribly inefficient.
That leaves us with Average Query Cost, which is the sum of each operator’s estimated cost in the query plan, and Impact.
But where does Impact come from?
Read on to learn where, as well as why you shouldn’t blindly trust that number.
Comments closedChad Baldwin checks out what’s new:
I’ve been exicted to play around with some of the new features and language enhancements that are available in SQL Server 2022 so I’ve been keeping an eye on the Microsoft Docker repository for a new 2022 image. Well, they finally added it to Docker Hub! I immediately pulled the image and started playing with it.
I want to focus on the language enhancements as those are the easiest to demonstrate, and I feel that’s what you’ll be able to take advantage of the quickest after upgrading.
Read on for a dozen or so language enhancements. This isn’t as big a change as what 2012 brought but there is a lot of useful stuff in here, as well as more that has been publicly announced like APPROX_PERCENTILE_CONT() (and _DISC(), yeah, but bah humbug).
Amit Khandelwal has notes on SQL Server 2022 on Linux:
In continuation of last week’s announcement of SQL Server 2022 public preview, we are pleased to announce availability of SQL Server 2022 on Linux/Containers for public preview. Here are the details for getting started with the SQL Server 2022 public preview packages on Linux/Containers.
As usual, the officially supported distributions are Red Hat Enterprise Linux and Ubuntu.
Comments closedPete Warden describes an interesting phenomenon:
I’ve been working on a new research paper, and a friend gave me the feedback that he was confused by the statement “memory accesses can be accurately predicted at the compilation stage” for machine learning workloads, and that this made them a poor fit for conventional processor architectures with predictive caches. I realized that this was received wisdom among the ML engineers I know, but I wasn’t aware of any papers that discuss this point. I put out a request for help on Twitter, but while there were a lot of interesting resources in the answers, I still couldn’t find any papers that focused on what feels like an important property for machine learning systems. With that in mind, I wanted to at least describe the issue as best as I can in this blog post, so there’s a trail of breadcrumbs for anyone else interested in how system designs might need to change to accommodate ML.
Read on for the explanation. My reading here is that this is a downside to having general-purpose compute: you run the risk of sub-optimal performance in certain circumstances, like training models using certain types of ML algorithms.
Comments closed