October 26, 2011 at 5:42 am
AFAIK, those can't really be tuned other than changing the cursor type & locations and "batch" size.
However the query itself that fills those datasets is where you can make a big difference.
Again =>
If you don't want to go that route call in a profesionnal (3 very competent ones on this thread BTW). 1 guess to figure out what we'll do!
October 26, 2011 at 7:30 am
Lavanyasri (10/26/2011)
exec sp_cursorunprepare 187379994990 --rpc completedFETCH API_CURSOR00000000000000E1 --sp:stmt completed
sp_cursorclose 180156333 --SQL :stmt completed
exec sp_cursorfetch 180166335,1,0,138 --rpc : completed
You can't directly tune those. You need to find the associated sp_cursoropen, sp_cursorprepare or similar that has a T-SQL statement in it.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 26, 2011 at 10:01 am
Lavanyasri (10/26/2011)
HI Kevin,Thanks for the update .
I think u r right , it might a problem with Cursors. I ran a profiler with rpc completed ,sp:stmt completed ,SQL :stmt completed .
I found the below queries are running continuously .
exec sp_cursorunprepare 187379994990 --rpc completed
FETCH API_CURSOR00000000000000E1 --sp:stmt completed
sp_cursorclose 180156333 --SQL :stmt completed
exec sp_cursorfetch 180166335,1,0,138 --rpc : completed
Basd on this how can i provide suggestion from my side to application team on code changes .
Thanks,
Lavanya
Those can be the result of misconfigured ADO/ADO.NET. IIRC both default to server-side cursors. I had an ISV I did some work for change JUST that configuration and it dropped the waits on the server of their biggest client by 80%! Server-side cursors are just hideously bad.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 3 posts - 16 through 17 (of 17 total)
You must be logged in to reply to this topic. Login to reply