We have a production SQL Server 2005 64bit Standard Edition with SP3. We recently moved the database to a virtual machine, after which we are facing a lot of problems with SQL Queries timing out which has brought down the performance. The SQL Server usually
runs with 100% CPU usage. While trying to debug the problem we came across a procedure 'sp_cursoropen' in the SQL Server profiler.
declare @p1 int
declare @p3 int
declare @p4 int
declare @p5 int
exec sp_cursoropen @p1 output,N'select * from StatusMessages',@p3 output,@p4 output,@p5 output
select @p1, @p3, @p4, @p5
The procedure is executing a select query on a table 'StatusMessages'. The profiler says the query is being executed by the 'Operating System'?
When i ran the standard report 'Performance - Top queries by total CPU time & Average CPU time' the results show that the query taking up maximum cpu is the select query on the StatusMessages table 'select * from StatusMessages'. I'm not sure why this
query is being executed? We use ADO to connect to the database from our applications. Most of the applications are doing a insert into 'StatusMessages' table. We rarely do a select from this table and when we do there is always a
View Complete Post