how can i see what sql server is doing?

Question

as i am a dev i am a user of SqlServer on my dev machine, and i rarely get to see what is happening on the server.

But at this project the client also manages it's own production SqlServer, and sometimes we see that the machine is 'busy'.

And with busy i mean it's longer than 30 seconds at 100% CPU. So first of all, is that normal behaviour, that when a server is running normally, it sometimes sets all cores at 100%?

As we (as non-sql experts) concluded it is not, we wanted to check 'what is sql server doing?'

There are maybe 6 applications running at the same server, some will run every 5 minutes, one is a website which is connected continuously, one is calculating data every minute etc.

So when the cpu is at 100%, how can we check which app's are causing this?

We can offcourse use the sql profiler to check what is sent to sql server, but that contains so much lines we don't know where to start analyzing.

Is there a way to get us started?

(it's like the thing you sometimes have on your destop at home: you're doing nothing, the cpu is at 50% and you hear the harddrive rattling and you think: what are you doing!?)

the server is one of a 2 machine cluster (one is used, one is fail over), both quad core with 16GB of RAM.

Answer 1

So first of all, is that normal behaviour, that when a server is running normally, it sometimes sets all cores at 100%?

As a developer you would and should know that the only answer to that is "it depends". is it possible a process uses up all CPU for a significant amount of time - guess what, the answer is yes.

So, the question mostly is - what is SQL Server doign there (and tehre are tools to find that out). Connect, look what SQL Statements are executed how long.

So when the cpu is at 100%, how can we check which app's are causing this?

Connect using the SA Account, look at the dynamic views to find out what SQL Server is busy processing at the moment. Possibly attach a trace.

But first make sure it is SQL Server. it is pretty rare for a SQL Server to be CPU bound. The first bottleneck normally is the disc subsystem.

Answer 2

With SQL Profiler is can be handy to filter out the noise - add a filter to only show statements that took longer than x seconds to execute. That way you can spot the longer running stuff and miss out all the transactions that are completed quite happily.

An even simpler approach in many cases is to have a look at SQL Activity Monitor (or execute sp_who2) when you're in one of these periods of 100% CPU time. Browse the connections that are listed to see what is active. Are you seeing things waiting? Are there items in there getting blocked and, if so, what's doing the blocking?

If you can't nail the problem down to a specific app or set of statements then you could start digging in to running performance monitor to look at some counters to try and spot a hardware bottleneck or possibly a fault brewing.

The dynamic views also offer a handy route to query what's been happening.

Then maybe look through some of the tips here.

Answer 3

Maybe a longshot, but MySQL has a feature called slow query log which logs any queries that take more than 1 second (configurable) to execute. Probably SqlServer has the same feature?

Using slow query log you could determine if you have any queries that are running slow on your system, or at least you could verify that you don't.

Answer 4

If you are using SQL Server 2005 or great (which I assume you are based on your tag), you can use the SQL Server Data Management Views (often referred to as DMVs). These will give you a whole range of views into what is going on in your system. In your particular case you might want to run the following query (or some offshoot of it):

SELECT total_physical_reads ,total_logical_reads ,total_logical_writes , execution_count , total_worker_time , total_elapsed_time , total_elapsed_time / execution_count avg_elapsed_time , creation_time , last_execution_time , st.text
FROM sys.dm_exec_query_stats AS qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st ORDER BY total_elapsed_time / execution_count DESC;

-- the order by clause will order by the highest average elapsed time first.

HTH, Dan