A Fine Slice Of SQL Server
Disclaimer: I am using the word shake by my own initiative and no Microsoft Documentation ever to my knowledge ever mentioned that situation. Those shakes are represented most of the time as health events to the cluster, such as the Lease Timeout resulting in a sudden attempt of Failover.
Why did I choose that word ? I don’t know. Honestly. 🙂
Read on to see it in context around hosts, CPU, and especially I/O.
Comments closedRobert Blackburn has a few tips for us:
Adding bigger or more nodes to your cluster increases costs. There are also diminishing returns. You do not need 64 cores if you are only using 10. But you still need a minimum that matches your processing requirements. If your utilization looks like this, you must increase the size of your cluster.
Click through for several good tips.
Comments closedJosh Darnell has a tip for Entity Framework users:
In Entity Framework, it’s easy to write code that will result in a “chatty” interaction with the database server. I wrote previously about being chatty in the context of read queries (lazy loading). This time, let’s talk about write queries.
Click through for some solid advice.
Comments closedHugo Kornelis conclues a mini-series on foreign key constraints:
Welcome to part fourteen of the plansplaining series, where I wrap up the mini-series on how simple foreign keys have huge effects on execution plans for data modifications.
We already looked at inserting data in the referencing (child) table, and at deleting data from the referenced (parent) table as well as updates in the child table. We did not and will not look at deleting from the child table or inserting in the parent table: those operations can by default never violate the foreign key constraint, so no additional logic is needed.
So that means there is only one thing left to explore: updating the parent. Perhaps surprisingly, this is actually quite complex, so it warrants an entire post of its own.
Read on to see why.
Comments closedItzik Ben-Gan has a challenge for us:
I always used my solution with a base table value constructor cardinality of 2, but Marcos’ comment made me think. This tool is so useful that we as a community should join forces to try and create the fastest version that we possibly can. Testing different base table cardinalities is just one dimension to try. There could be many others. I’ll present the performance tests that I’ve done with my solution. I mainly experimented with different table value constructor cardinalities, with serial versus parallel processing, and with row mode versus batch mode processing. However, it could be that an entirely different solution is even faster than my best version. So, the challenge is on! I’m calling all jedi, padawan, wizard and apprentice alike. What’s the best performing solution that you can conjure? Do you have it within you to beat the fastest solution posted thus far? If so, share yours as a comment to this article, and feel free to improve any solution posted by others.
Give it a try.
Comments closedHugo Cornelis continues a dive into execution plan operators:
The Filter operator is used to remove rows from a result set that do not satisfy a specified condition.
Its most common usage is to implement a WHERE clause, although many elements of a WHERE clause get implemented in other ways (e.g. by pushing them down into an Index Seek or Index Scan operator), and Filter operators can also be used when the optimizer transforms other query elements into any type of filtering.
The Filter operator can only verify conditions based on the data in the current row; if data from different rows needs to be compared for the filter, the optimizer generates an execution plan that first adds the data to be compared to the row, or it uses different operators.
Click through for Hugo’s normal in-depth look.
Comments closedJeffrey Hicks has an improvement to Get-Counter in Powershell:
I wanted to tell you about another addition to the latest release of the PSScriptTools module. This is something I’ve written about before but I decided to add the function to the module. I hope you find it a much easier way to work with performance counters. And it works in Windows PowerShell and PowerShell 7.x.
Click through to see what has changed.
Comments closedErik Darling compares cardinality estimators:
Look, I’m not saying there’s only one thing that the “Default” cardinality estimator does better than the “Legacy” cardinality estimator. All I’m saying is that this is one thing that I think it does better.
What’s that one thing? Ascending keys. In particular, when queries search for values that haven’t quite made it to the histogram yet because a stats update hasn’t occurred since they landed in the mix.
Read the whole thing.
Comments closedKathi Kellenberger has a follow-up of some questions after a PASS Virtual Summit session:
Last week, I presented a session on Intelligent Query Processing for the first ever Virtual PASS Summit. This summit had a mix of live and pre-recorded session. During the pre-recorded sessions, the speaker could hang out with the attendees in the discussion room and join a virtual chat room at the end. My session was live, so I answered questions a few questions during the session. There were a couple of questions that I couldn’t answer fully during the session, but all the questions were interesting, so I’ll go through them here.
Click through for the questions and answers.
Comments closedErik Darling talks about the intersection of two performance tuning topics:
Yesterday we saw a case where the Gather Streams operator was costed quite highly, and it prevented a parallel plan from being chosen, despite the parallel plan in this case being much faster.
It’s important to note that costing for plans is not a direct reflection of actual time or effort, nor is it accurate to your local configuration.
They’re estimates used to come up with a plan. When you get an actual plan, there are no added-in “Actual Cost” metrics.
Read on to see how you can monkey’s paw your way through this problem by introducing exciting, new problems.
Comments closed