A Fine Slice Of SQL Server
Tom Zika continues a series on why user-defined scalar functions are such a bad idea:
I’ll cover several test scenarios and analyze the performance using different monitoring tools.
The results will be for the second executions of the queries, so we have compiled and cached plans and all pages in the buffer pool.
Even if you already hate seeing scalar user-defined functions in code, the occasional reminder of how poorly they perform helps focus the mind.
Comments closedErik Darling continues a series on Parameter Sensitive Plan Optimization. First up is a missed opportunity:
If we re-run the procedure from the section up above to search for VoteTypeId 2 a second time, Memory Grant Feedback will fix the spill at the Hash Join, and bring the total execution time down to about 15 seconds.
That is an improvement, but… Look at the plan here. If VoteTypeId 2 uses a plan more suited to the number of rows it has to process, the overall time is around 4 seconds, with no need for a memory grant correction.
Second, Erik asks the pressing questions:
Under more favorable circumstances, dynamic SQL gets run, executed, and plans cached and reused with the same frequency as stored procedures.
Now, dynamic SQL isn’t exactly the same as stored procedures. There’s a lot you can do with those that just looks a mess in dynamic SQL, especially longer bits of code.
In today’s post, we’re going to look at how the Parameter Sensitive Plan (PSP) optimization works with dynamic SQL.
Read on to see if PSP works with dynamic SQL.
Comments closedErik Darling ends on a high note:
I’ve used this proc as an example in the past. It’s a great parameter sniffing demo.
Why is it great? Because there’s exactly one value in the Posts table that causes an issue. It causes that issue because someone hated the idea of normalization.
The better thing to do here would be to have separate tables for questions and answers. Because we don’t have those, we end up with a weird scnenario.
Read on for an example of PSP at its best.
Comments closedErik Darling is less than enthused:
The results from my Extended Event session, and… I’ll talk through my disappointment in a moment, was not terribly spectacular.
There are some promising concepts in there but as a whole, it does seem like we’ll need fingers crossed that this improves for RTM.
Comments closedThe way this feature works is, rather than caching a single query plan for every other execution to use, it creates what’s called a Dispatcher plan (if your query qualifies).
You’ll see something like this in the properties of the root node of your query plan, and your query will have some additional funny business at the end of it.
Read on to see what information is available to us and current feature limitations.
Comments closedAs I was troubleshooting a performance issue, I noticed that there was an implicit conversion (SQL Server automatically converts the data from one data type to another) happening in my join. The join was on a column that was named the same in both tables, but one was datatype INT (integer) and the other was a datatype of VARCHAR(50) (variable character up to 50 places).
Read on for one way to resolve this issue…so long as no other calling code expects a string on a call.
Comments closedAjay Gupta gets into the topic of join performance:
Performing Joins on Skewed Datasets: A Dataset is considered to be skewed for a Join operation when the distribution of join keys across the records in the dataset is skewed towards a small subset of keys. For example when 80% of records in the datasets contribute to only 20% of Join keys.
Implications of Skewed Datasets for Join: Skewed Datasets, if not handled appropriately, can lead to stragglers in the Join stage (Read this linked story to know more about Stragglers). This brings down the overall execution efficiency of the Spark job. Also, skewed datasets can cause memory overruns on certain executors leading to the failure of the Spark job. Therefore, it is important to identify and address Join-based stages where large skewed datasets are involved.
Read on for five techniques which may help you out.
Comments closedTom Zika takes away the scalars:
I’m still surprised many people don’t realise how lousy Scalar functions are. So because it’s my current focus in work and this Stack Overflow question, I’ll be revisiting this topic.
The focus of part one is parallelism. Unfortunately, parallelism often gets a bad rep because of the prominent wait stats. Also, if there is a skew, it can run slow. But for the most part, it’s advantageous.
Whether or not you want parallelism should be an informed choice. But Scalar functions will enforce the query to run serially, even if you are unaware. That’s why I want to shine a light on this.
Read on for a demo of how even a no-op scalar function can affect query performance. Given the mess we normally see in scalar functions, it’s all downhill from there.
Comments closedErik Darling looks at another update in SQL Server 2022:
The thing is, the reason always seemed to be “Could Not Generate Valid Parallel Plan” for most of them, even though more explicit reasons were available.
They started cropping up, as things do, in Azure SQL DB, and have finally made it to the box product that we all know and mostly love.
Let’s explore some of them! Because that’s what we do.
Also check out Rob Volk’s comment, as he lists out all the ones he could find, noting that most of these do exist in SQL Server 2019.
Comments closedWhen you’re dealing with untouchable vendor code full of mistakes, ORM queries that God has turned away from, and other queries that for some reason can’t be tinkered with, we used to not have a lot of options.
In SQL Server 2022, Query Store gains a new super power: you can add hints to queries without intercepting the code in some other manner.
There are a couple of useful hints which won’t be available but Erik seems mostly upbeat about what is there.
Comments closed