A Fine Slice Of SQL Server
Gilbert Quevauvilliers runs a test:
In this blog post I am going to determine which item workload uses the least amount of Capacity Units when copying the same data from an On-Premises SQL Server.
The item workloads that I can use to copy data are Dataflow Gen1, Dataflow Gen2 and Pipelines.
Read on for the results, as well as one caveat about them.
Comments closedStephen Planck reads a report:
SQL Server includes an Extended Events session called system_health, which runs by default and, among other things, captures information about deadlocks as they occur. When two or more sessions block each other in such a way that no progress can be made (a deadlock), SQL Server chooses one session as the “victim,” rolls back its transaction, and frees resources so other sessions can continue. By reviewing the deadlock report in the system_health session’s XML output, you can see precisely why the deadlock happened and identify which queries or procedures were involved.
Below is a walkthrough of how to interpret a sample XML deadlock report, followed by a brief note on how to access this output.
Read on for that walkthrough.
Comments closedChad Callihan engages the limit breaker:
I’m familiar with using the OVER clause and don’t think it’s too uncommon to see it used for including row numbers by using ROW_NUMBER() and aggregating data. But even though they’ve been around since SQL Server 2012, I’m not too familiar with using the OVER clause with the UNBOUNDED PRECEDING and UNBOUNDED FOLLOWING to affect the window being queried.
Let’s take a look at a couple of examples using UNBOUNDED PRECEDING and UNBOUNDED FOLLOWING.
Click through for those examples. The default ranges for window functions usually make a lot of sense, but it’s good to understand your options for frames: ROWS vs RANGE, as well as the frame values (UNBOUNDED PRECEDING, {N} PRECEDING, CURRENT ROW, {N} FOLLOWING, and UNBOUNDED FOLLOWING).
In this video, I demonstrate how much information we can gain from one sample of a distribution.
Some aspect of this is “that’s a neat parlor trick” but it does speak to the marginal information gain of a small amount of data.
Comments closedTomaz Kastrun checks what time it is:
Ever wanted to have a clock on the wall or in the office, that is not binary. But it is QR-Code clock. Well, now you can have it.
This useless R function generates new QR Code for every given period and tells the time.
Click through for the code. I could see this being useful in scenarios where you want to avoid people copying the QR code, so you embed the time in there. Then, your reader service can check to see if the time is within some valid boundary, returning an error if not.
Comments closedBen Johnston takes us through a scenario:
I recently had an interesting production SQL Server issue that turned out to be very easy to fix. The fix doesn’t fit every workload or server, but for the limited use cases described below, it’s a simple configuration change. The general best practice for server level configurations is to leave things at default, unless there is a specific reason for changing the settings. This will outline a use case for using memory-optimized tempdb metadata.
This covers a very specific scenario and workload. You won’t use this on a typical server, which is why it isn’t a default setting. You will see this for very specific server workloads, those with many transactions and high temp table usage. Most systems can better use the memory for the regular workload instead of optimizing tempdb metadata, so don’t use this as a default setting for your servers.
Click through for the scenario.
Comments closedJosephine Bush has an after-action report:
I did a couple of posts previously on dumping/restoring Azure PostgreSQL databases and also using the Azure migration tool. I had to ultimately do a combo of those because of the limitations of the Azure migration tool.
Read on for the process that Josephine went through to complete migration.
Comments closedBertrand Drouvot looks at some new statistics that will be available in Postgres 18:
It means that, thanks to the new pg_stat_get_backend_io() function, we can see the I/O activity in each backend (in addition to the overall aggregate still available through the pg_stat_io view).
Click through for some examples of what you can see and how it works. This seems like it’d be pretty helpful in a clustering scenario to make sure that you’re getting the load balancing that you expect.
Comments closedRecently, I was asked to compare data from a table in Snowflake with the same table’s data from a few hours before. As always, no one had thought about copying the data into another table before they started working with it. They just remembered an approximate time they started working with the table — 10 AM — and the table’s name — Public.WorkingTable. So, let’s see what we can do, shall we?
Read on for the process, as well as circumstances in which cloning might fail.
Comments closedTomaz Kastrun hides the words:
Let’s play with some words. More in particular with vanishing words.
Using two packages: ggplot2 and gganimate we will construct a animation (looped), where sentences will be vanishing, word by word. A nice way to train the ggplot animations.
Click through for the code.
Comments closed