December 5, 2015 at 9:12 am
Last week a T-SQL batch suddenly took an incredibly longer time to complete, after a deep analysis I discovered the root cause: scalar functions.
All the statements (select, insert or update) using in some way those functions now were much longer than before (2-3 minutes to 20 minutes), as a workaround
I eliminated the function calls everywhere (copying the code directly on the statements) and execution time went back to smaller durations.
The two functions were both string manipulations with Ifs and CASE.
I had a look around on internet and found tons of articles about performance issues of scalar functions, but my case is different, they went ok for a few years,
I can't find a possible reason for this sudden change.
Thank you in advance for any contribution
December 5, 2015 at 1:43 pm
Scalar functions are slow. What probably happened is that the execution plan changed and as a result the function was being run many more times.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 6, 2015 at 11:43 pm
More or less at the time the problem occurred I rebuilt PKs, things should have gone better, that's what I was thinking.
That could have surely changed execution plans, but making them more actual for data, what am I missing?
December 7, 2015 at 12:15 am
PKs are not the only factor for bad plans caused by bad stats. Did you rebuild all indexes? All column statistics?
If you can manage it, port the scalar functions into inline table-valued functions and refactor the calling code. You'll have a great chance for an initial performance improvement and a much better chance at scaling when data volumes increase.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
December 7, 2015 at 1:52 am
I rebuilt all indexes, non only PKs, that causes stats update automatically, is it true?
December 7, 2015 at 3:51 am
Yes, that would have updated all index stats. Probably resulted in the optimiser realising there was more data than it had previously thought (due to stale stats) and so changing the plan. It doesn't cost UDFs properly (part of the reason why they're so slow), so the change in the plan could easily have resulted in the UDF running more often.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 7, 2015 at 4:53 am
Thank you Gail, it must have gone like you said, unfortunately now I can't verify if the stats were stale before, I tried asking to developers the percentage of daily record change of underlying tables, I don't know if I can do something more now to verify.
Thanks again
December 7, 2015 at 6:51 am
Fandy08 (12/7/2015)
I rebuilt all indexes, non only PKs, that causes stats update automatically, is it true?
All index statistics will be uodated when the index is rebuilt but column statistics need to be updated separately.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
December 7, 2015 at 8:52 am
Yes, I checked, a few column statistics are older than those of indexes, do you mean Orlando that performance slow down can be caused by these differences on update date?
December 7, 2015 at 2:00 pm
it can affect row estimates and therefore can result in inefficient execution plan choices
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
December 8, 2015 at 2:01 am
Fandy08 (12/7/2015)
Yes, I checked, a few column statistics are older than those of indexes, do you mean Orlando that performance slow down can be caused by these differences on update date?
Unlikely. If plans based on the older stats were slow, you'd have seen it before the rebuild of the indexes.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 8, 2015 at 8:11 am
So, definitively, the reason must have been bad plans after reorg of indexes, it remains mysterious for me how could things go fast before, with fragmented tables and stale stats, it is strange, isn't it?
December 8, 2015 at 9:03 am
agree it is counterintuitive. rebuilding indexes amd stats (even if only a subset of stats) should not hurt things.
any noticeable changes (increases or decreases) in data volumes on or arounf when performance changed?
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
December 8, 2015 at 9:11 am
On a side note, and I don't know if "the sudden slow down" happened when after the scalar udfs were added, but Scalar UDFs kill parallelism. Often the whole query plan (or part of it at least) can't be parallel. This will slow things down. I have seen scalar UDFs take 1 minute queries and turn them into 20 minute queries.
-- Itzik Ben-Gan 2001
December 8, 2015 at 9:32 am
Another point to consider is that scalar functions have to run once for each row. If you have two functions, each one has to run once for each row.
If you can convert your scalar functions to inline table-valued functions (ITVF), you're likely to see a serious performance boost because the function will only run once. Granted, there are some things that can't be re-written as an ITVF, but it's definitely worth investigating. Even if your ITVF only returns a single column, it'll still benefit.
I remember somewhere in Books Online (but I don't remember where) a reference to an Inline Scalar Function, but there is no such object type in SQL Server. Clearly, someone at Microsoft must have considered it at some point.
Viewing 15 posts - 1 through 15 (of 28 total)
You must be logged in to reply to this topic. Login to reply