A Fine Slice Of SQL Server
Eric Rouach replaces SQL Agent:
For future use in our PowerShell script and since we do not want our Server’s credentials to appear in clear text, our next step will the creation of a credential within the Automation account. This will hold the credentials (username and password) that we’d use for connecting to our SQL Server target.
Click through to learn more.
Comments closedPaul White begins a new series:
This is the first part of a series about simple parameterization and trivial plans. These two compilation features are closely connected and have similar goals. Both target performance and efficiency for workloads frequently submitting simple statements.
Despite the “simple” and “trivial” names, both have subtle behaviours and implementation details that can make how they work difficult to understand. This series doesn’t dwell too long on the basics but concentrates on less well-known aspects likely to trip up even the most experienced database professionals.
In this first part, after a quick introduction, I look at the effects of simple parameterization on the plan cache.
Read on for more detail.
Comments closedDave Saunders describes the concept of linear data structures:
As we said in the introduction, a data structure is ‘linear’ if the elements form a sequence.
That means that the data structure has a first and last element, and each element is connected to its previous and next element.
– An ‘array’ is a linear data structure; the items are stores sequentially.
– A ‘graph’ is not a linear data structure; any node can be linked to any other node in the graph – there is no fixed ‘sequence’.
Click through for several examples of linear data structures.
Comments closedTomaz Kastrun does a bit of benchmarking:
After an interesting conversation on using data. frames or strings (as a vector), I have decided to put this to a simple benchmark test.
The problem is straightforward and simple: a MapReduce or Count with GroupBy.
Click through for three potential solutions to the problem and how they compare in terms of performance.
Comments closedI was recently working on a client server and need to install the Az PowerShell module. I opened and administrator window and typed
Install-Module Azand received this error:
WARNING: Unable to find module repositories.
Turns out that was not the real answer. Click through to see what Frank ended up needing to do.
Comments closedMichael J Swart recommends a unique index:
To avoid deadlocks when implementing the upsert pattern, make sure the index on the key column is unique. It’s not sufficient that all the values in that particular column happen to be unique. The index must be defined to be unique, otherwise concurrent queries can still produce deadlocks.
Read on for Michael’s thoughts as well as some smart commenters adding on.
Comments closedLouis Davidson receives a nastygram from SQL Server’s graph functionality:
Just understand that if you need any of the graphDb underlying data structures, you will need to find their actual physical name and use it. I would definitely suggest never accessing these columns via any method other than the pseudocolmns for production code (because you have no way to predict the column names from dev to prod (you cannot specify the names when creating a table), but this following code does work:
Click through to see the issue and Louis’s workarounds.
Comments closedKendra Little announces a new article:
We’ve just published a new article in the SQL docs, Tune nonclustered indexes with missing index suggestions . The article explains what the missing index feature is, limitations of the feature, and how to use missing index DMVs and missing index suggestions in Query Store to tune indexes.
Read on to understand the impetus behind this article and then go check it out yourself.
Comments closedErik Darling continues a series on understanding execution plans. First up, what some of those warnings mean:
The good news is that SQL Server’s query plans will attempt to warn you about problems. The bad news is that most of the warnings only show up in Actual Execution Plans. The worse news is that a lot of the warnings that try to be helpful in Estimated Execution plans can be pretty misleading.
Next, Erik reviews some interesting properties:
There’s a lot of stuff flying around in a query plan. Data and what not.
Sure, you can hover over operators and arrows and see some stuff, but if you really wanna see stuff — I mean REALLY wanna see stuff — you gotta get into the properties.
Check out both posts.
Comments closedChris Webb looks at a new addition to Power Query:
Anyone with previous experience of Table.Buffer will see that BufferMode.Eager is the existing behaviour, but what is BufferMode.Delayed for?
It turns out that it’s there to make development faster.
Read on for the answer and what it does.
Comments closed