Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Trouble shooting cursor Expand / Collapse
Author
Message
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: Yesterday @ 8:10 AM
Points: 51, Visits: 305
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


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 7:12 PM
Points: 115, Visits: 823
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
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, August 11, 2014 6:37 AM
Points: 299, Visits: 561
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.

MCITP SQL 2005, MCSA SQL 2012
Post #1504392
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse