ansaurus

Question

Activity Monitor Problems in SQL Server 2005

Answer 1

A:

This sounds like some default SQL command that is ran by SQL Server on a ongoing basis. Probably via MSSQLServer.

What it could be is that particualar SQL statement is run many times but is just summing up the total, giving the impression that its taking up a lot of time.

kevchadders 2009-12-22 16:23:09

Answer 2

A:

Look at sys.dm_exec_sessions and sys.dm_exec_connections for the session ids that take up CPU. You'll find the application name, host name and process id of the client.

Remus Rusanu 2009-12-22 16:32:51

You can get all of those from the activity monitor. My question is what is causing this SQL to run because it is is using an exorbitant amount of CPU. So much so that it is causing problems.

Jason 2009-12-22 16:45:16

Well then, *what* application is running these? The answer to your question is really dependent on finding out the origin of the queries first, don't you agree? Are they some sort of monitoring doing a too tight loop, are they some applications too many started, is there more to the requests than you posted?

Remus Rusanu 2009-12-22 17:01:56

The problem is that the the SQL above is being shown from multiple applications (all web apps). However, as stated in the question, those statements are not located in any code. Is this something that SQL Server could be doing?

Jason 2009-12-22 20:07:38

No, SQL would not be running statements on its own. Those statements come from the web apps. They may not exist explicitly as SqlCommand text in your app, but there are several tens of thousands of lines of code from various frameworsk and ORMs between your code and what actually reaches SQL Server. A trivial example is the bening SqlConnection.Open() which sends a pretty hefty batch to initialize the session settings, a batch that ends in `set transaction isolation level read committed`...

Remus Rusanu 2009-12-22 20:56:42

Answer 3

A:

This is the default transaction isolation level for ADO.NET and most OR/M frameworks. Chances are this is in fact coming for your code, you just don't know it.

I think this is the wrong question to ask anyways - the real question is, why is this rather common TSQL instruction causing your database CPU to spike?

Bryan Batchelder 2009-12-22 22:56:25

ansaurus

tags:

views:

answers:

Activity Monitor Problems in SQL Server 2005

related questions