A Fine Slice Of SQL Server
We have proof that Meagan Longoria is a consultant:
Question: When an activity in a Data Factory pipeline fails, does the entire pipeline fail?
Answer: It depends
Read on to understand the circumstances of when a pipeline activity failure will doom an entire pipeline run and when recovery might be possible.
Comments closedChad Callihan is looking for some data:
Searching for a value or group of values with a wildcard is more than just putting a % on both sides of a text string. If you know you’re looking for all strings in a name field that start with the name “Chad” then you are you really shooting yourself in the foot by using ‘%Chad%’ instead of ‘Chad%’ in your query. SQL Server is going to be scanning the table instead of being able to use an index to seek to the data. While that may work to get your result, it’s likely going to take longer and be more invasive than needed. I want to go through an example of how using a reversed column can improve SQL Server’s ability to build a better execution plan.
Read on to see how a computed reversal column can help. For more complex scenarios, an n-gram table (for example, a trigram) might help, though there’s a lot of setup involved there.
Comments closedRandolph West explains how the buffer pool handles low-memory situations:
One of the bigger clichés in the data professional vocabulary (behind “it depends”) is that you always give SQL Server as much RAM as you can afford, because it’s going to use it. But what happens when SQL Server runs out of memory?
Recently a question appeared on my post about how the buffer pool works, asking the following (paraphrased):
What happens if a data page doesn’t exist in the buffer pool, and the buffer pool doesn’t have enough free space? Does the buffer pool use TempDB, [and] does TempDB put its dirty pages into the buffer pool?
This is an excellent question (thank you for asking!). I spent 30 minutes writing my reply and then figured it would make a good blog post this week if I fleshed it out a little.
Read the whole thing.
Comments closedMonica Rathbun takes us through the DROP_EXISTING option when modifying an index:
When you are making changes to an existing Non-Clustered index SQL Server provides a wide variety of options. One of the more frequently used methods is DROP EXISTING; in this post you will learn all about that option. This option automatically drops an existing index after recreating it, without the index being explicitly dropped. Let us take a moment understand the behavior of this choice.
What I really want is DROP_IF_EXISTS. I want idempotent commands: if I run it once or a thousand times, I end up in the same state whether there was an index there at the start or not (or if attempt #793 failed due to running out of sort space in tempdb or something, leaving me with no index). DROP_EXISTING is only idempotent if the index already existed, but then you have to ask, why is it important if an index of that name is already there? The important part of the statement is that I want an end state which includes this index in this form.
The Hadoop in Real World team takes us through the selection criteria for join types:
There are several factors Spark takes into account before deciding on the type of join algorithm to use to join datasets at runtime.
Spark has the following 5 algorithms to choose from –
1. Broadcast Hash Join
2. Shuffle Hash Join
3. Shuffle Sort Merge Join
4. Broadcast Nested Loop Join
5. Cartesian Product Join (a.k.a Shuffle-and-Replicate Nested Loop Join)
Read on to learn which join types are supported in which circumstances, as well as rules of precedence.
Comments closedAttila Bukor takes us through the process of setting up role-based access controls on Impala tables:
After setting up the integration it’s time to create some policies, as now only trusted users are allowed to perform any action; everyone else is locked out. Resource-based access control (RBAC) policies can be set up for Kudu in Ranger, but Kudu currently doesn’t support tag-based policies, row-level filtering or column masking.
Click through for the process, as well as current limitations.
Comments closedNext, we want to assess the field-level, distributional health of our data. Distribution tells us all of the expected values of our data, as well as how frequently each value occurs. One of the simplest questions is, “how often is my data
NULL”? In many cases, some level of incomplete data is acceptable — but if a 10% null rate turns into 90%, we’ll want to know.
This covers a couple examples around data freshness and completeness, and I appreciate the level of detail in here. Nothing is earth-shattering, but at the same time, it’s important to have a catalog of the sorts of issues which can pop up. H/T Mark Hutchinson.
Comments closedRahul Ajmera fills us in on what they’ve been doing with SQL Server Big Data Clusters:
Today, we’re announcing the release of the latest cumulative update (CU9) for SQL Server Big Data Clusters, which includes important capabilities:
– Support to configure BDC post deployment.
– Improved experience for encryption at rest.
– Ability to install Python packages at Spark job submission time.
– Upgraded software versions for most of our OSS components (Grafana, Kibana, FluentBit, etc.) to ensure Big Data Clusters images are up to date with the latest enhancements and fixes.
– Miscellaneous improvements and bug fixes.This announcement highlights some of the major improvements, provides additional context to better understand the design behind these capabilities, and points you to relevant resources to learn more and get started.
Click through for more detail on a few of the items.
Comments closedJess Pomfret takes us through some SPN pains:
But instead of getting a quick answer to my question, I just got the following error:
WARNING: [15:19:49][Get-DbaDatabase] Error occurred while establishing connection to dscsvr1 | The target principal name is incorrect. Cannot generate SSPI context.
Just reading the article brought back some bad troubleshooting memories for me… But as usual, I’m impressed that dbatools has a cmdlet or two to help with that troubleshooting.
Comments closedErik Darling notes that memory-optimized table variables can be useful in specific circumstances:
First, yes, they do help relieve tempdb contention if you have code that executes under both high concurrency and frequency. And by high, I mean REALLY HIGH.
Like, Snoop Dogg high.
Because you can’t get rid of in memory stuff, I’m creating a separate database to test in.
Been there. When tempdb object creation causes massive contention, this certainly alleviates the stress.
As Erik notes, there are some tradeoffs to this, meaning that you have a real decision to make rather than simply using memory-optimized user-defined table types as a starting point.
Comments closed