Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

Scalar UDF different performance on different server Expand / Collapse
Author
Message
Posted Thursday, November 7, 2013 3:46 PM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Thursday, August 21, 2014 2:00 PM
Points: 627, Visits: 4,978
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.



Post #1512477
Posted Thursday, November 7, 2013 4:09 PM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 3:12 PM
Points: 4,348, Visits: 6,157
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 at GMail
Post #1512485
Posted Thursday, November 7, 2013 4:24 PM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Thursday, August 21, 2014 2:00 PM
Points: 627, Visits: 4,978
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?



Post #1512492
Posted Thursday, November 7, 2013 7:17 PM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 3:12 PM
Points: 4,348, Visits: 6,157
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 at GMail
Post #1512517
Posted Friday, November 8, 2013 3:34 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 3:15 AM
Points: 15,661, Visits: 28,048
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 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1512582
Posted Friday, November 8, 2013 4:02 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 12:26 AM
Points: 2,840, Visits: 3,963
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
Post #1512588
Posted Friday, November 8, 2013 10:28 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Thursday, August 21, 2014 2:00 PM
Points: 627, Visits: 4,978
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?



Post #1512760
Posted Friday, November 8, 2013 3:50 PM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 3:12 PM
Points: 4,348, Visits: 6,157
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 at GMail
Post #1512838
Posted Friday, November 8, 2013 5:09 PM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Thursday, August 21, 2014 2:00 PM
Points: 627, Visits: 4,978
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.



Post #1512855
Posted Friday, November 8, 2013 5:19 PM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Thursday, August 21, 2014 2:00 PM
Points: 627, Visits: 4,978
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') "



Post #1512859
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse