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
TheSQLGuru
TheSQLGuru
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12238 Visits: 8541
I don't have (unpaid Cool ) 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 on googles mail service
mister.magoo
mister.magoo
SSCarpal Tunnel
SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)

Group: General Forum Members
Points: 4070 Visits: 7865
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


select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);




  • 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

  • shahgols
    shahgols
    SSC Eights!
    SSC Eights! (929 reputation)SSC Eights! (929 reputation)SSC Eights! (929 reputation)SSC Eights! (929 reputation)SSC Eights! (929 reputation)SSC Eights! (929 reputation)SSC Eights! (929 reputation)SSC Eights! (929 reputation)

    Group: General Forum Members
    Points: 929 Visits: 5738
    TheSQLGuru (11/12/2013)
    I don't have (unpaid Cool ) 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!



    shahgols
    shahgols
    SSC Eights!
    SSC Eights! (929 reputation)SSC Eights! (929 reputation)SSC Eights! (929 reputation)SSC Eights! (929 reputation)SSC Eights! (929 reputation)SSC Eights! (929 reputation)SSC Eights! (929 reputation)SSC Eights! (929 reputation)

    Group: General Forum Members
    Points: 929 Visits: 5738
    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 :-D) while I drink some tea. But this financial institution is paranoid for some reason. J/K



    TheSQLGuru
    TheSQLGuru
    SSChampion
    SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

    Group: General Forum Members
    Points: 12238 Visits: 8541
    Are you REALLY REALLY REALLY sure the code is the same on both servers?!?! Whistling

    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!! :-D

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service
    shahgols
    shahgols
    SSC Eights!
    SSC Eights! (929 reputation)SSC Eights! (929 reputation)SSC Eights! (929 reputation)SSC Eights! (929 reputation)SSC Eights! (929 reputation)SSC Eights! (929 reputation)SSC Eights! (929 reputation)SSC Eights! (929 reputation)

    Group: General Forum Members
    Points: 929 Visits: 5738
    TheSQLGuru (11/12/2013)
    Are you REALLY REALLY REALLY sure the code is the same on both servers?!?! Whistling

    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!! :-D


    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



    mister.magoo
    mister.magoo
    SSCarpal Tunnel
    SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)

    Group: General Forum Members
    Points: 4070 Visits: 7865
    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


    select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);




  • 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

  • shahgols
    shahgols
    SSC Eights!
    SSC Eights! (929 reputation)SSC Eights! (929 reputation)SSC Eights! (929 reputation)SSC Eights! (929 reputation)SSC Eights! (929 reputation)SSC Eights! (929 reputation)SSC Eights! (929 reputation)SSC Eights! (929 reputation)

    Group: General Forum Members
    Points: 929 Visits: 5738
    Thank you MM, I will try that first thing tomorrow morning. Very kind of you.



    TheSQLGuru
    TheSQLGuru
    SSChampion
    SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

    Group: General Forum Members
    Points: 12238 Visits: 8541
    I just LOVE Paul's posts!!

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service
    shahgols
    shahgols
    SSC Eights!
    SSC Eights! (929 reputation)SSC Eights! (929 reputation)SSC Eights! (929 reputation)SSC Eights! (929 reputation)SSC Eights! (929 reputation)SSC Eights! (929 reputation)SSC Eights! (929 reputation)SSC Eights! (929 reputation)

    Group: General Forum Members
    Points: 929 Visits: 5738
    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!



    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