Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase

Trouble shooting cursor Expand / Collapse
Posted Wednesday, October 9, 2013 7:07 AM

Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, October 26, 2016 6:26 AM
Points: 72, Visits: 441
I am wondering if Cursors translate query statements, I am getting alerts for high CPU usage(Session CPU time is 1685.042 seconds for session ###). The process is running on a SQL2008 Server, but originating on a sql2000 server, via a linked server call(i assume) application name is simply 'Microsoft SQL Server', Unfortunately my organization in the past used a very generic sql userid and so the credentials do not not narrow down where this comes from. I ran a trace on the process, which runs for 1.5 hours. and this is the code i am catching that repeats. and based on this code i can't locate the code/job/process submitting this. This just started two days ago, and no know claims to have changed anything.

the tbl0172 portion of this query is what i find troubling, does a cursor do some sort of translation on column names?

SELECT Tbl1072."LASTDAYWORKED_I" "Col1719",Tbl1072."BENADJDATE" "Col1718",Tbl1072."EMPLCLAS" "Col1677",Tbl1072."EMPLOYID" "Col1676" FROM "TECH"."dbo"."UPR00100" Tbl1072

declare @p1 int
set @p1=-1
declare @p2 int
set @p2=NULL
declare @p5 int
set @p5=557064
declare @p6 int
set @p6=98305
declare @p7 int
set @p7=NULL
exec sp_cursorprepexec @p1 output,@p2 output,NULL,N'SELECT Tbl1399."ADRSCODE" "Col2627",Tbl1399."EMPLOYID" "Col2626",Tbl1399."PHONE1" "Col2634",Tbl1399."ZIPCODE" "Col2633",Tbl1399."STATE" "Col2632",Tbl1399."CITY" "Col2631",Tbl1399."ADDRESS3" "Col2630",Tbl1399."ADDRESS2" "Col2629",Tbl1399."ADDRESS1" "Col2628" FROM "CAN"."dbo"."UPR00102" Tbl1399',@p5 output,@p6 output,@p7 output
select @p1, @p2, @p5, @p6, @p7
Post #1503078
Posted Sunday, October 13, 2013 2:26 PM



Group: General Forum Members
Last Login: Wednesday, July 20, 2016 11:43 AM
Points: 163, Visits: 1,130
sorry please delete, I mis-read question.

...0.05 points per day since registration... slowly crawl up to 1 pt per day hopefully
Post #1504289
Posted Monday, October 14, 2013 4:42 AM


Group: General Forum Members
Last Login: Monday, November 28, 2016 3:36 AM
Points: 474, Visits: 1,066
This looks very like the type of code LYNQ \ Nhibernate generates. It may not be that someone has coded a cursor but the application is generating cursor code.

Post #1504392
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse