SQL Server Performance Forum – Threads Archive
server side cursor
Recently, I am troubleshooting a server side cursor performance issue, here is the trace. For an insert operation it took CPU:15, Read:102,Write:18, Duration: 17exec sp_cursor 180150007,4,1,N”,@TreatmentDate=”2007-05-08 00:00:00:000”,
For an update operation, it took CPU:9140, Read:699384,Write:2539, Duration: 14570
exec sp_cursor 180150111,33,1,N”,@EmployeeNo=’112′,@DateInjured=NULL Anybody here show me some ideas, how to troubleshoot this server side cursor? ——————
Bug explorer/finder/seeker/locator
——————
SQL Profiler is the best option in this case…
sp_cursor uses handle to execute the queries/proedures…
You need to capture the sp_cursorprepexec calls to see what it is trying to execute…
then tune that query/procedure..
MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided “AS IS†with no warranties for accuracy.
Is that a statement from a stored procedure? If not, then how is the client app handling this? Updates take a lot of time compared to inserts, so you want to avoid cursor-based operations.
By looking at the SQL Server:Cursor Manager By Type – Cursor Requests/Sec counter, you can get a general feel for how many cursors are being used on the system by looking at this performance counter. Determine if cursors are the most appropriate means to accomplish the processing or whether a set-based operation, which is generally more efficient, is possible. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
Guys, your quick response and nice words always encourage me to come here more often, here seems becoming my favorite technical home, just want to say your inputs are highly valued and appreciated. Thank you guys, ——————
Bug explorer/finder/seeker/locator
——————
Guys, please help. Details from the profiler For UPDADT
—————–
1. create the cursor
exec sp_cursoropen @p1 output,N’SQL Query’,@p3 output,@p4 output,@p5 output This cursor is a Keyset-driven cursor with optimistic concurrency_options. The SQL Query used to create the cursor is "SELECT FROM VIEW" , but this query only cost 21 disk read because of the clustered index seek. the underneath base table has 300000 rows and the averge row size is 185B. 2. fetch the row
exec sp_cursorfetch 180150109,16,1,1 3. Doing the update exec sp_cursor 180150109,33,1,N”,
@EmployeeNo=’BK2769′,
@DateInjured=NULL,
@Occupational=0,
@Jurisdiction=NULL,
@GeographicLocation=’HOME00′,
@Department=NULL,
@Division=’HOME00′,
@Location=NULL,
@Organization=’STFRM’,
@CurrentPosition=NULL,
@CaseManaged=-1,
@Supervisor=NULL,
@Problem=’Wellness Exam’ This update takes 8532 CPU, 699958 Read, 2546 Write, 13996 duration.
Guys, the customer said in 2K environement, it does not take that long, when we migrated to 2K5, it began to take longer. Any start point to tuning? My guess is UPDATE via VIEW causing problem? Do I need more frequent reindex, update stat?
——————
Bug explorer/finder/seeker/locator
——————
It could be parameter sniffing issue… http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=11611
http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=11993
http://blogs.msdn.com/khen1234/archive/2005/06/02/424228.aspx
MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided “AS IS†with no warranties for accuracy.
There is a cumulative hotfix specific to the cursors KBAhttp://support.microsoft.com/default.aspx/kb/917738 &http://support.microsoft.com/kb/917905 check it out. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
Guys, Thanks for the sharing, I will work on get the hotfix applied and will let you know the results. 1. Do you guys think can I do something on UPDATE VIA VIEW to improve the performance.
——————
Bug explorer/finder/seeker/locator
——————
If this database falls under OLTP system with many updates & writes with frequent updates, may not be able to take advantage of indexed views because of the increased maintenance cost associated with updating both the view and underlying base tables. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
]]>