Scalar functions sudden slow down

Question

Scalar functions sudden slow down

Viewing 15 posts - 1 through 15 (of 28 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 1

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Fandy08 Old Hand Points: 336 More actions · Answer 2

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?

Orlando Colamatteo SSC Guru Points: 182293 More actions · Answer 3

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

Fandy08 Old Hand Points: 336 More actions · Answer 4

I rebuilt all indexes, non only PKs, that causes stats update automatically, is it true?

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 5

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Fandy08 Old Hand Points: 336 More actions · Answer 6

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

Orlando Colamatteo SSC Guru Points: 182293 More actions · Answer 7

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

Fandy08 Old Hand Points: 336 More actions · Answer 8

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?

Orlando Colamatteo SSC Guru Points: 182293 More actions · Answer 9

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

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 10

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Fandy08 Old Hand Points: 336 More actions · Answer 11

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?

Orlando Colamatteo SSC Guru Points: 182293 More actions · Answer 12

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

Alan Burstein SSC Guru Points: 61152 More actions · Answer 13

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.

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

Ed Wagner SSC Guru Points: 287026 More actions · Answer 14

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.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

Fandy08 Old Hand Points: 336 More actions · Answer 15

Alan.B (12/8/2015)
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.

"The sudden slow down" happened after indexes reorg, the UDFs have been running for about one year before it.