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.