Query regarding SQL Server Performance with ADO

hi all,

we have production sql server 2005 64bit standard edition sp3. moved database virtual machine, after facing lot of problems sql queries timing out has brought down performance. sql server runs 100% cpu usage. while trying debug problem came across procedure 'sp_cursoropen' in sql server profiler. 

 

declare @p1 int

set @p1=180150021

declare @p3 int

set @p3=1

declare @p4 int

set @p4=16388

declare @p5 int

set @p5=287164

exec sp_cursoropen @p1 output,n'select * statusmessages',@p3 output,@p4 output,@p5 output

select @p1, @p3, @p4, @p5

the procedure executing select query on table 'statusmessages'. profiler says query being executed 'operating system'?

when ran standard report 'performance - top queries total cpu time & average cpu time' results show query taking maximum cpu select query on statusmessages table 'select * statusmessages'. i'm not sure why query being executed? use ado connect database our applications. of applications doing insert 'statusmessages' table. select table , when there clause. mention table not indexed. table has 500000 rows. i'm not sure why 'operating system' doing select on table , why bringing down performance?

i going through sql server forums , came across server side cursors , client side cursors , performance being better client side cursors. record, using client side cursors. 

any in solving problem highly appreciated.

thanks,

bharath

 

no, not how work. filter clause , non-qualifying rows not returned. however, major difference can whether client issue 1 network trip server *for each qualifying row* or whether can fetch several rows @ time. there different cursor types, , recall "firehose" cursor data in 1 network trip (the actual name in ado might else, long time ago...).

---

tibor karaszi, sql server mvp | web | blog

SQL Server  >  SQL Server Database Engine