Very slow sp_cursorprepexec

Question

Very slow sp_cursorprepexec

andrew 67979

Ten Centuries

Points: 1268
More actions
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

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

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

SQLian SSC-Addicted Points: 413 More actions · Answer 1

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.

Mike01 SSChampion Points: 11395 More actions · Answer 2

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/

andrew 67979 Ten Centuries Points: 1268 More actions · Answer 3

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

Jo Pattyn SSC-Dedicated Points: 32345 More actions · Answer 4

"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?

andrew 67979 Ten Centuries Points: 1268 More actions · Answer 5

andrew 67979

Ten Centuries

Points: 1268

August 9, 2018 at 8:19 am

#2001026

Yes that is what I did

What is parameter sniffing ?

Jo Pattyn SSC-Dedicated Points: 32345 More actions · Answer 6

Some recent blogs about parametersniffing
Parametersniffing 1
Parametersniffing 2
Parametersniffing 3

andrew 67979 Ten Centuries Points: 1268 More actions · Answer 7

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

sgmunson SSC Guru Points: 110640 More actions · Answer 8

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) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

Jeff Moden SSC Guru Points: 1004324 More actions · Answer 9

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)