November 4, 2008 at 3:50 am
Hi all,
I have tried sql server 2005 on two differrent machines with a same client load, the one of the machine gives the CPU which was almost plain ( 10 to 20 %) throughout the load duration and the other gives a pattern like high at the start (35%-40%) and gradually reduces to 1% and again it sharply peaks to (35%-40%) and start to reduces gradually upto that 1% and again it peaks to (35%-40%), it happens in the interval of 18 to 20 mins. Both machines configuration was same and on same network. i have noticed that only sql server uses the CPU resources at all the time on both machines.
I have attached the CPU Chart reference with this post.
Do anybody have any explanation for this. Please help me to fix this issue.Thanks in advance.
November 4, 2008 at 5:39 am
More information is needed. Can you capture a trace from SQL Server to see what's occuring within the database? Outside the database, you need to confirm that other processes aren't causing the problem. What about collecting all the counters on Process:%Processor Time? Then you can be sure that it is in fact SQL Server that's causing all the problem. If you're sure it's SQL Server, check for timed processes in SQL Agent or something like that. Other than that, it's hard to suggest more without more detail.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
November 4, 2008 at 5:50 am
Thanks for reply.
I have attached the perlogs for two machines, it has most of the counters in it. Whether it will be enough?
November 4, 2008 at 6:22 am
I'd still say something else is operating on that machine. If you look at the batch requests/second, transactions/second, user connections, full scans... everything is pretty flat. Nothing is going up & coming down with the CPU...
Locks though, you're getting a strange sort of growing pattern on locks that drops off suddenly, but it doesn't coincide with those strange cycles in CPU. But that's the same on both machines. As a matter of fact, the drop-off in locks on the machine with the light load coincides with it's sudden drop in CPU, not surprising.
I'm not sure. I'd want to see trace output too.
The other server has clear connections between batch requests and the processor workload. It doesn't look like the same apps or the same configuration between the two servers. Your disk queue is very different between the two. You didn't collect all the same counters between the two systems, but if you look at Current Disk Queue Length on both, the one with the CPU peeks & valleys has quite a bit more disk queueing than the other.
I'm inclined to think something is up in that area. You're doing a timed data load or a backup or something along those lines.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
November 4, 2008 at 6:51 am
for now i don't have trace file. i will come up with the file...
load was same , because its was designed so.Might have change in the CPU values but not the average. We tried few runs on both servers, the pattern was same as like i have posted for the respective servers.
November 28, 2008 at 5:48 am
sorry, inbetween these i wasn't working on this issue. The people work on this issue find that only difference between the two servers is one which has the CPU oscillating pattern has Indexing enabled on that disk, but indexing service was not on and they told us like it was not the cause for this. I have a question like defragmentation of server will help to solve this issue. Also i can't give the profiler logs.
thanks for ur reply
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply