SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Scalar UDF different performance on different server


Scalar UDF different performance on different server

Author
Message
shahgols
shahgols
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2265 Visits: 5738
Hi all,

I have a query that calls on a UDF. In staging, has the same number of records and schema, it runs in 25 seconds. On production it used to run in seconds, but for some reason today it is very slow, to the point that if I let it run, it could run for a day or two. The execution plans are the same, the same indexes are being called, etc. The only difference I see is that the call to the UDF on production executes much slower (verified with Profiler) and the CPU usage on production is way, way high, compared to staging. Has anyone ever seen this sort of behavior??

Thanks.



TheSQLGuru
TheSQLGuru
SSC-Dedicated
SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)

Group: General Forum Members
Points: 32551 Visits: 8675
shahgols (11/7/2013)
Hi all,

I have a query that calls on a UDF. In staging, has the same number of records and schema, it runs in 25 seconds. On production it used to run in seconds, but for some reason today it is very slow, to the point that if I let it run, it could run for a day or two. The execution plans are the same, the same indexes are being called, etc. The only difference I see is that the call to the UDF on production executes much slower (verified with Profiler) and the CPU usage on production is way, way high, compared to staging. Has anyone ever seen this sort of behavior??

Thanks.


Are you absolutely, 100% positive that a) the code is exactly the same, b) the server builds are the same, c) the execution plans are the same (be sure to compare the XML and look for SET differences, etc)?

I wrote a chapter for the SQL Server MVP Deep Dives 2 book entitled "Death by UDF". It is very aptly named and I STRONGLY encourage you to factor out the UDF and watch your queries run orders of magnitude faster!!!

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
shahgols
shahgols
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2265 Visits: 5738
Hi Kevin, yes, I agree with you, UDFs are a nightmare for performance. I have replaced them before and have looked like a hero. haha. And when I take out the UDFs from the query, the query runs in 2 seconds flat! With UDFs, it may run for 1 or 2 days! Horrible!

And your idea to compare the xml execution plan was right on. Most of the differences were too small (for example, EstimateCPU =0.000441494, EstimateCPU = 0.000440599), but the following sticks out:

Staging server:

<OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="707788" EstimatedPagesCached="88473" EstimatedAvailableDegreeOfParallelism="2" />

Production server:

<OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="353894" EstimatedPagesCached="176947" EstimatedAvailableDegreeOfParallelism="4" />

Do you see any problems with such large differences? Any idea what I can do to get these numbers to match?



TheSQLGuru
TheSQLGuru
SSC-Dedicated
SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)

Group: General Forum Members
Points: 32551 Visits: 8675
I am not at all convinced this is a plan thing. I would be more interested in waits on the production server. latching, locking, IO, CPU, etc.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Grant Fritchey
Grant Fritchey
SSC Guru
SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)

Group: General Forum Members
Points: 99093 Visits: 33014
I'm with Kevin. It's very likely to be contention.

But, from the differences you're seeing, I'd also suggest it's possibly statistics being different between the two. Those differences in estimations the plans can add up quite a bit.

----------------------------------------------------
The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
Theodore Roosevelt

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
Bhuvnesh
Bhuvnesh
SSChampion
SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)

Group: General Forum Members
Points: 13400 Visits: 4077
shahgols (11/7/2013)
but for some reason today it is very slow.
There must be any reason.. some immediate action done like
1 data migration (heavy DML on related tables) which disturbed the statistics
2 some indexe dropped (accidently)
3 or any process running behind (not under consideration during this issue on prod).

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
shahgols
shahgols
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2265 Visits: 5738
Hi everyone and many thanks for your responses.

I had already checked on differences between table schemas, indexes, data, and also on wait/locks/etc. but didn't find any differences. I ran a update statistics statement in both environments with sample of 100%. I checked index fragmentation, which is about 5% on both servers. Any other checks I can do?

One more question, based on the xml data I posted, it looks like the staging environment assigns a lot more RAM for the same query, isn't that correct? Could that be the reason why?



TheSQLGuru
TheSQLGuru
SSC-Dedicated
SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)

Group: General Forum Members
Points: 32551 Visits: 8675
shahgols (11/8/2013)
Hi everyone and many thanks for your responses.

I had already checked on differences between table schemas, indexes, data, and also on wait/locks/etc. but didn't find any differences. I ran a update statistics statement in both environments with sample of 100%. I checked index fragmentation, which is about 5% on both servers. Any other checks I can do?

One more question, based on the xml data I posted, it looks like the staging environment assigns a lot more RAM for the same query, isn't that correct? Could that be the reason why?


Sure the memory grant can be important because it can lead to significantly more PHYSICAL IO as you get stuff spooled to disk for hashes, sorts, etc. What were the IOs for each query (and CPU)? I said you should check those. :-) NOTE though that you MUST use Profiler - the engine LIES to SSMS when UDFs are in play.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
shahgols
shahgols
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2265 Visits: 5738
I really appreciate your response and patience!

If I run Profiler, the sql statement will never finish. haha

I am using sp_whoisacitve. the CPU usage is much, muuuuch higher on production than it is on staging. And the "reads" column is somewhat higher. On staging, where the script runs in 25 seconds, the CPU shows 19K cycles, and reads shows 45K reads. On production, after 3 hours of running the same script, the CPU is at 9M cycles, and reads is at 64K reads.

I hope that answers your question, otherwise let me know what to do (that doesn't involve Profiler) please.



shahgols
shahgols
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2265 Visits: 5738
This is a shot in the dark, but I am putting it out there just in case.

I compared the actual execution plans and the XML of the staging execution plan contains these weird characters after the WHERE clause that don't exist in my query (or the query would obviously fail). Anyone know what these are?

StatusId not in ('S','O','L') "



Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search