Post reply

Very slow sp_cursorprepexec

  • August 9, 2018 at 3:15 am

    #407693

    Hi All

    I'm trying to debug/understand some very slow performance in some SQL executed sent via an application

    I'm running in a test environment so there are no external load factors on the server

    I am using the SQL server profiler to try and understand things

    If I prime everything to the point that the next mouse click in the application will start the slow proces

    Then if I execute the same SQL code via SSMS and look at the result in profiler I get ....

    SQL Batch Starting/Completed and it all completes in less than a second

    The Profiler columns say
    CPU       281
    Reads    15655
    Write      3
    Duration 640

    If I then click the mouse in the application it sends a sp_cursorprepexec with the same SQL and this takes 13 and a half MINUTES to execute

    The Profiler columns say
    CPU       806734
    Reads    77115033
    Write      8
    Duration 808340

    What type of thing would cause such a drastic increase in work load ?

    The nature of the query itself is a straightforward on selecting from a SQL View which may or may not be considered complex, one mans simple is another's complex

    Why would one version of the operation only need 15 thousand reads and the other 77 million ! ?

    Thanks in advance for any assistance

  • August 9, 2018 at 7:09 am

    #2000997

    Assuming the query is the same in both instances I would probably be looking at the connection from the app to SQL Server. You have given no clue as to what tech the app is using though.

  • August 9, 2018 at 7:30 am

    #2001003

    can you grab the actual query that the app is sending (the sp_cursorprepexec statement)?  Run that in ssms to see how it performs. At least then it's apples to apples.  Then you can examine the execution plan to see what it is doing.  Maybe something simple as adding with recompile might work

    For better, quicker answers, click on the following...
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • August 9, 2018 at 7:36 am

    #2001005

    Yes the query is the same in both instances 
    It's just a cut and paste from the profiler and obviously replacing the params with the actual values
    In SSMS it runs in less than one second ... as opposed to over 13 minutes as reported in both cases by the Profiler

  • August 9, 2018 at 8:16 am

    #2001024

    "obviously replacing the params with the actual values"
    do you mean

    SELECT *
    FROM  A
    where col1=@param1

    with

    SELECT *
    FROM  A
    where col1=1

    In the latter case you don't have parameter sniffing and you have the actual value the optimizer kan optimize for.
    (otherwise it picks the one of the first  execution)

    Did you execute a parameterized query?

  • August 9, 2018 at 8:19 am

    #2001026

    Yes that is what I did

    What is parameter sniffing ?

  • August 10, 2018 at 2:06 am

    #2001171

    Some recent blogs about parametersniffing
    Parametersniffing 1
    Parametersniffing 2
    Parametersniffing 3

  • August 10, 2018 at 6:39 am

    #2001200

    Thanks for the links

    Just so I don't misunderstand this does it definitely apply to sp_cursorprepexec ?

    The reason I ask is are these calls not 'one-off' queries i.e. the sp is not stored on the sever and callled the SQL is sent each time with this command ?

    I certainly see the same slow performance each time

    Thanks

  • August 10, 2018 at 7:45 am

    #2001228

    Just an fyi ... If memory serves me correctly, sp_cursorprepexec is there to get ready to serve up the result set as a cursor that your application can "hold on to", as long as it keeps the connection open...   If that's the case, then running the query, unless you also include the sp_cursorprepexec; inside of SSMS, is a very different animal.   It just isn't going to do the same thing.   If my recall on this is correct, then you might have some kind of capacity constraint that you're not aware of, and was doing a pretty good job of hiding itself until now...

    Because I wasn't sure, I looked it up, and here's where you can find info on it:

    https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-cursorprepexec-transact-sql?view=sql-server-2017

    That's Microsoft's documentation.   Just a quick web search on sp_cursorprepexec provided a lot more links to look at...

    Steve?(aka sgmunson)?:) 🙂 :)?
    Health & Nutrition
    Rent Servers for Income (picks and shovels strategy)

  • August 11, 2018 at 5:13 pm

    #2001384

    It's not just parameter sniffing to be concerned with.  You need to check the datatypes of the parameters in the sp_cursorprepexec with those in the table.  For example, if Entity Framework or some other god-forsaken orm is passing parameters as NVARCHAR() and your table column is VARCHAR(), you'll have an implicit conversion where the entire column in the tables must be converted before the comparison can be made.  I'm going through that again (for the 253,786th time) with 3rd party software.

    It prevents index seeks and can cause some seriously incorrect plans.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

Viewing 10 posts - 1 through 9 (of 9 total)

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