Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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.

share|improve this question

4 Answers 4

up vote 3 down vote accepted

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.

share|improve this answer

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.

share|improve this answer

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.

share|improve this answer
    
ah, nice one. if i get no reaction here i'll post it as a new question. –  Michel Sep 23 '10 at 11:15
    
You can use sql profiler to gather this information. –  Sam Sep 23 '10 at 20:23

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

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.