Hi everyone,

got a strange occurrence that takes place often and I wondered if anyone had come across this type of behavior before.

I have a test server that is running SQL 2005 sp4 (don't believe the version is the cause), it's running on a esxi VM which I don't actually have control over.

every couple days, the customer will report that the system is running slow. I check the server, and find that SQL is running at 90%+ of the CPU...constantly.

checking activity monitor, I see that the queries are the same select queries that the site would run every day for testing. Except, that now, it's taking a minute plus to execute.

I have to then run a reindex (with online= ON, for them to continue using the DB), and that takes about 2 minutes, and then the CPU is back to normal and the same select queries take less than a second to complete.

This is a very small DB (maybe about 3 gigs), and the fragmentation of the indexes are hardly anything to note.

My initial thought was that maybe the site is using Vmotion or some ESXi feature that's somehow causing the DB to get into this state. But, it's only a theory at this point.

wondering if anyone has come across this type of behavior before and can shed some light or tips for me to track the issue down.

thanks!

can you track the wait event that's causing the select query to run slower.

you can use - Adam Machanic's sp_whoisactive -google for it

or you can see the wait type for session- while the query is running-- select * from sys.dm_exec_sessions where session_id=<<your session id>>

how often do you index maintenance? do you do reindex everytime there is this issue - does it fix everytime--

could it be page splits- thats making the query perform bad over a period - due to logical fragmentation...

your wait event for the query should give some insight.

Also, is there anything else going on the server outside of SQL Server. did you check performance counters values such as page life exceptancy, total memory,target memory,free pages, pages stall, memory grants p

great suggestions.

and no, I have not checked the page splits or the wait event, but will do so the next time it occurs.

I have to reindex the db every time the issue comes up because it's the only way I know to help get the site back to a working state.

I did check perf. counters and memory is well within the range.

I will check the points you suggested and google the SP.

Thanks for the input!!

Note that higher CPU and response times for the same workload may be a symptom that the VM isn't getting the same number of CPU cycles from the hypervisor.  Check with your virtualization administrators to see of that is the case.

In addition

---This first thing to check if CPU is at 100% is to look for parallel queries:

-- Tasks running in parallel (filtering out MARS requests below):
select * from sys.dm_os_tasks as t
 where t.session_id in (
   select t1.session_id
    from sys.dm_os_tasks as t1
   group by t1.session_id
  having count(*) > 1
  and min(t1.request_id) = max(t1.request_id));

-- Requests running in parallel:
 select *
   from sys.dm_exec_requests as r
   join (
           select t1.session_id, min(t1.request_id)
          from sys.dm_os_tasks as t1
         group by t1.session_id
        having count(*) > 1
           and min(t1.request_id) = max(t1.request_id)
      ) as t(session_id, request_id)
     on r.session_id = t.session_id
    and r.request_id = t.request_id;