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 Tuesday, November 12, 2013 12:36 PM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 7:16 PM
Points: 4,471, Visits: 6,401
I don't have (unpaid ) time to give it a go, but I would look to unwind that logic into the SELECT statement if possible.

Also, to validate that the UDF is the cause of your perf issues, run the query with some action on that column. Check for blocking/locking, query plan, stats IO, etc. All the usual suspects...


Best,

Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru at GMail
Post #1513598
Posted Tuesday, November 12, 2013 12:47 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 6:17 AM
Points: 1,816, Visits: 5,913
I agree with Kevin that really you could do with refactoring this function, however that does not deal with the problem of why there is such a difference in performance between the two systems...

So, if you care to post up the two .sqlplan files from Production and Dev/Test we can see if the answer lies within...


MM


  • MMGrid Addin
  • MMNose Addin


  • Forum Etiquette: How to post Reporting Services problems
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • How to Post Performance Problems - by Gail Shaw

  • Post #1513600
    Posted Tuesday, November 12, 2013 1:46 PM
    SSChasing Mays

    SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

    Group: General Forum Members
    Last Login: Yesterday @ 8:26 PM
    Points: 636, Visits: 5,020
    TheSQLGuru (11/12/2013)
    I don't have (unpaid ) time to give it a go, but I would look to unwind that logic into the SELECT statement if possible.

    Also, to validate that the UDF is the cause of your perf issues, run the query with some action on that column. Check for blocking/locking, query plan, stats IO, etc. All the usual suspects...


    Thank you so much for your assistance!

    When I take out the UDF, the statement runs in 2 seconds flat. I am not sure what you mean by "some action on that column", can you please provide an example?

    I ran the query in production, it took 2 hours and 45 minutes. I had been running Profiler for the bach_completed event. Here is the result:

    CPU: 8,830,218
    Reads: 5,060
    RowCounts: 16,187,830


    I'm not sure why the row count is so high, total number of records that are returned by the query is just over 274K.

    The same query on staging had the following results:

    CPU:  29,796
    Reads: 63,060
    RowCounts: 16,168,656



    Further, the SET STATISTICS IO on prod shows:

    (274370 row(s) affected)
    Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
    Table 'table1'. Scan count 2, logical reads 962, physical reads 208, read-ahead reads 214, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
    Table 'table2'. Scan count 1, logical reads 4095, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.


    And on staging shows:

    (274045 row(s) affected)
    Table 'table2'. Scan count 1, logical reads 4094, physical reads 1, read-ahead reads 4006, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
    Table 'table1'. Scan count 1, logical reads 57686, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.


    It looks like due to the fact that the production SQL Server assigns less RAM to the query, it is forced to create the a work table in tempdb. Is that correct? And if so, how do I change this behavior? BTW, I have already run "update statistics with 100 percent" command on both tables in prod and staging.

    Also, there is a huge difference between the logical reads for table1. Not sure why or what that means!?

    Thanks again for everything, this has been an informative experience for me!



    Post #1513625
    Posted Tuesday, November 12, 2013 1:51 PM
    SSChasing Mays

    SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

    Group: General Forum Members
    Last Login: Yesterday @ 8:26 PM
    Points: 636, Visits: 5,020
    mister.magoo (11/12/2013)
    So, if you care to post up the two .sqlplan files from Production and Dev/Test we can see if the answer lies within...


    I wiiiish I could post the execution plans, it would make my life so much easier to have you guys look at the details (unpaid, as Kevin said ) while I drink some tea. But this financial institution is paranoid for some reason. J/K



    Post #1513626
    Posted Tuesday, November 12, 2013 2:38 PM


    SSCarpal Tunnel

    SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

    Group: General Forum Members
    Last Login: Today @ 7:16 PM
    Points: 4,471, Visits: 6,401
    Are you REALLY REALLY REALLY sure the code is the same on both servers?!?!

    I was just looking for some simple action on the column, like a mod or some such. Just to make sure the engine has to touch and do something with the column to get it more similar to what is going on when the UDF is in play.

    You cannot control the memory grant for a single query to my knowledge. Sure would be nice occasionally. Oh how I pine for the days of SQL Server 6.5!! MORE KNOBS I SAY!!


    Best,

    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru at GMail
    Post #1513638
    Posted Tuesday, November 12, 2013 3:29 PM
    SSChasing Mays

    SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

    Group: General Forum Members
    Last Login: Yesterday @ 8:26 PM
    Points: 636, Visits: 5,020
    TheSQLGuru (11/12/2013)
    Are you REALLY REALLY REALLY sure the code is the same on both servers?!?!

    I was just looking for some simple action on the column, like a mod or some such. Just to make sure the engine has to touch and do something with the column to get it more similar to what is going on when the UDF is in play.

    You cannot control the memory grant for a single query to my knowledge. Sure would be nice occasionally. Oh how I pine for the days of SQL Server 6.5!! MORE KNOBS I SAY!!


    Yes I am sure, I copy pasted from one window to the other.

    I queried "sql server uses large worktable" and the second link is from Paul White, it seems to deal with a large worktable and similar XML execution plans. I'm still reading through it to see if there is a good solution I can use. Here's the link in case you are interested:

    http://sqlblog.com/blogs/paul_white/archive/2013/03/08/execution-plan-analysis-the-mystery-work-table.aspx



    Post #1513646
    Posted Tuesday, November 12, 2013 4:49 PM


    SSCommitted

    SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

    Group: General Forum Members
    Last Login: Today @ 6:17 AM
    Points: 1,816, Visits: 5,913
    Here is a freebie....

    Try this version of the function to see if it helps / makes a difference

    create function [dbo].[CheckDigit](@vol varchar(50))
    returns table
    with schemabinding
    as
    return
    select
    case isnumeric(base.vol)
    when 1
    then
    case sum(c.digit) % 11
    when 0 then 1
    when 1 then 0
    else 11 - sum(c.digit) % 11
    end
    else 0
    end as CheckDigit
    from (select replace(@vol,'.','')) base(vol)
    outer apply (
    select top(datalength(vol)) M*cast(substring(base.vol,N,1) as int)
    from (values(1,3),(2,7),(3,1),(4,3),(5,7),(6,1),(7,3),(8,7),(9,1),(10,3),(11,7),(12,1),(13,3),(14,7),(15,1),(16,3),(17,7),(18,1),(19,3),(20,7),(21,1),(22,3),(23,7),(24,1),(25,3),(26,7),(27,1),(28,3),(29,7),(30,1),(31,3),(32,7),(33,1),(34,3),(35,7),(36,1),(37,3),(38,7),(39,1),(40,3),(41,7),(42,1),(43,3),(44,7),(45,1),(46,3),(47,7),(48,1),(49,3),(50,7)) x(N,M)
    ) c(digit)
    group by base.vol



    MM


  • MMGrid Addin
  • MMNose Addin


  • Forum Etiquette: How to post Reporting Services problems
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • How to Post Performance Problems - by Gail Shaw

  • Post #1513660
    Posted Tuesday, November 12, 2013 5:18 PM
    SSChasing Mays

    SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

    Group: General Forum Members
    Last Login: Yesterday @ 8:26 PM
    Points: 636, Visits: 5,020
    Thank you MM, I will try that first thing tomorrow morning. Very kind of you.


    Post #1513664
    Posted Tuesday, November 12, 2013 6:00 PM


    SSCarpal Tunnel

    SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

    Group: General Forum Members
    Last Login: Today @ 7:16 PM
    Points: 4,471, Visits: 6,401
    I just LOVE Paul's posts!!

    Best,

    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru at GMail
    Post #1513671
    Posted Friday, November 15, 2013 3:18 PM
    SSChasing Mays

    SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

    Group: General Forum Members
    Last Login: Yesterday @ 8:26 PM
    Points: 636, Visits: 5,020
    I'm so impressed with the code Paul, amazing work!

    The developer here decided to go another route and create a SP that runs in 1 minute.

    Thank you all for your patience and help!



    Post #1514873
    « Prev Topic | Next Topic »

    Add to briefcase «««123

    Permissions Expand / Collapse