December 22, 2006 at 11:20 am
Hi,
My production sql server cpu is at 100% and i am trying to trobuleshoot the issue but not sure what causing this.
I have profiler and checking if any query is taking longer to run and i have almost index on all my tables.. I performed the reindex on table, set the fill factor, backup the master, msdb,model table as well other database.
I am also running sp_who to see if any spid is blocking or not but nothing is seems to be obvious. however, I found tempdb checkpoint and cpu is at 4562
is it something causing this???
December 22, 2006 at 12:18 pm
run sp_who2 and look at the CPU time column to see which SPID is responsible for the highest CPU usage. Also, look at the Windows task manager to see if there are any non-SQL Server processes using CPU.
December 22, 2006 at 1:21 pm
Hi,
I ran sp_who2 stored procedure and also checked the task manager to see what causing the cpu high. I ran sql profile to see if any spid causing the cpu hight but it's not. I am trying to locate and read the sp_who2 result. I found that there is status:dormant login=linked and cputime=62.. if you don't mind, can you please tell me what dormant? I googled but did not find good article on this.
This has been happening since 2 weeks. After I reboot the server, everything looks normal but I would like to find that root cause what's causing it..
I have reindex my tables, and trying to read sql profile to find any cause..
I had question regarding reads & writes,, when you see high read, does it mean your table is fragmented??
thanks for all the advice..
December 23, 2006 at 10:19 pm
Check if any frequently executing procedure is recompiling or using incorrect query plan...
I have seen this behaviour when the procedure was not using incorrect plan...
MohammedU
Microsoft SQL Server MVP
December 27, 2006 at 5:40 am
That's a lot of questions!! Is it sql server that is using the cpu - you need to monitor cpu of the sqlserver process vs 100%, just in case you have another process using cpu. The cpu values in sp_who / sysprocesses are cumlative values and have little relationship to actual %age.
You'll need to plot active processes vs cpu usage - better with the 2005 tools which allow you to overlay perfmon and profiler traces.
Reads and writes relate to the amount of data being accessed, on a modern server cpu should not be affected by disk subsystems. I'd suggest you get hold of the SQL Server 2000 performance tuning reference ( ms press ) it's a good start to understanding performance tuning etc.
The other point about high cpu use is that your box might just be at its limits or configured wrong. I trust you have all settings "out of the box" ?
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
January 4, 2007 at 9:33 am
HI,
I have ran sp_who2 but i don't see any high cpu hold for any spid. I just found that i don't have installed the service pack 1. if you don't mind, can you please advise how's the service pack 1 runs on sql 2005 or if you have seen any errors?
January 4, 2007 at 12:23 pm
As Colin stated, are you sure it is the Sql Service that is running and using all of the CPU. Go into task manager and make sure no other os processes are running and taking all of the cpu time. Once that is determined I would run a trace to capture any long running queries on the machine.
Tom
January 5, 2007 at 8:02 am
I checked the task manager to see if it was running any other services and occupying the cpu. it's not. This is where i checked first and then checked the system event log to see any unusual errors but no erros..
I am monitoring the profiler to see if any long running query occupying the server but it's not..i am not sure why the cpu goes back to 100%..
January 5, 2007 at 9:05 am
Do you have any maintenance plans running index builds or consistency checkers? Any other maintenance that could be happening when this is going on. One last thing is to look at virus scanning software on the machine. Is it running and causing a problem with the backend files.
Tom
January 5, 2007 at 9:42 am
could you give some idea of the hardware and settings of your server, is it oltp or dw ?
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
January 5, 2007 at 10:23 am
I have windows 2003 and installed the sql 2005 enterprise..
I have configured the sql server configuration manager to allow tcp/ip and named piple connection.
I have virsus scan but it only runs once a week ar midnight..
I also set unlimited connection for my server and remote connection. The database that i have lots of inserts which has fill factor of 70
January 10, 2007 at 4:57 am
a fill factor of 70% could increase your scans and cause excessive i/o with selects. I figure you'll have to profile to see the nature of your queries and see. I have a similar situation with an 8 way box which maxes its cpu - there's no real culprit as such, just the general nature of less than optimal sql which doesn't scale well with 2000 connections.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
January 10, 2007 at 1:41 pm
HI,
What I understood that when there is lots of inserts on table, set the fill factor of 70%. I don't query anything from the table..this is just audit table and we just log everything who logs to our server. I am not sure if i should remove the fill factor or not?
I also found that there is service pack 1 out there and i have not applied latest service pack1. does it resolve the issue with cpu@100% with service pack 1. just trying all possible way to bring my cpu down..
January 10, 2007 at 2:00 pm
When considering fill factor, it is more that whether there are a lot of inserts or not. It depends on the key of the clustered index. If you have an identity field as a key then I always set my fill factor at 100%. The only place that will get the next insert is at the end of the file, there is no need to have space free on other pages if there is no chance EVER that it is going to be used. The same can be said for date fields, or any other field that increments out from where you start (this is dependent on your situation). If the key is SSN and you have many inserts/updates than setting a lower fill factor might have benefits on those operations, often times at the detriment of the select operations (more pages to scan). I don't have many 24/7 databases where I am at now and have the ability to run reindexing nightly. I have the fill factors set at 95% on my databases and reorg constantly. I feel in my situation that I have the best of both worlds. Fast selects and reasonable update/inserts.
Tom
January 10, 2007 at 3:41 pm
yeah agreed,an audit table should be a heap ( with or without a clustered index - in the true meaning of a heap ) A fill factor of 70% will bloat the table. It would increase allocations too. Doesn't help with your 100% cpu - think you need to look elsewhere unless your clustered key isn't sequential.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply