| Author |
Topic |
|
bcanonica
Starting Member
35 Posts |
 Posted - 2009-07-13 : 14:19:49
|
| I have a server with sql server that runs real slow despite not having a large number of connections, running queries, or high cpu usage. We ruled out hardware and query performance early on. What are the best processes for troubleshooting issues like this? Any advice on where I should start when trying to fix issues like this?TIA |
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2009-07-13 : 15:21:52
|
ruled out hardware and query performance? that pretty much rules out everything, box must be performing well  is this a dedicated SQL server? anything else running on the box?what perfmon counters are you measuring?did performance suddenly get worse? |
 |
|
|
bcanonica
Starting Member
35 Posts |
Posted - 2009-07-14 : 09:25:05
|
| OK have some updated info. This randomly happens every day or two. I checked to see if the server has any connections or queries running and it has nothing, but the CPU on the server shows that 40% of the processor is being used when it gets into this workable slow state. Ideas? |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2009-07-14 : 10:35:11
|
| what process is using it?is virus scan running?are u saying query performance degrades when this happens? |
|
|
|
bcanonica
Starting Member
35 Posts |
Posted - 2009-07-14 : 17:18:12
|
| No virus scan and yes the query performance is degraded to nearly a halt when this happens. |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2009-07-14 : 23:55:51
|
| find out what process is consuming the cpu cycles. if it is sqlserver.exe, then there's something going on. if it's something else, also let us know what it is.use taskmgr.exe or processExplorer.exe to find out what is consuming the resources. |
|
|
|
bcanonica
Starting Member
35 Posts |
Posted - 2009-07-15 : 09:10:48
|
| sqlserver.exe is the service that is using 40% of the cpu when no transactions or connections are doing anything. It only happens every few days, but makes the server unusable at that point. Then we restart the sql server and everything works again for a few days. |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2009-07-15 : 10:29:18
|
| you'll need to find out what's happening at the time. may want to run a trace, but possibly you can see it in sp_who2.something is happening on the server...user queries, jobs, internal tasks...? see what SPID is consuming resources and find out what it is doing. check for blocking too.your databases aren't by chance set to auto_shrink or auto_close are they? |
|
|
|
chih
Posting Yak Master
154 Posts |
Posted - 2009-07-16 : 01:04:24
|
| is sql doing statistic update? |
|
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2009-07-16 : 03:02:39
|
| Have you checked the sql server agent of there are any sql server jobs running? Maybe you're doing index rebuilds or something? If you're positive that it's the sqlserver process that's doing something you will be able to find it using profiler.- Lumbago |
|
|
|
bcanonica
Starting Member
35 Posts |
Posted - 2009-07-16 : 12:21:46
|
I have narrowed it down to two issues. One query related and the other one cpu performance related. Below are two images that will describe my issues compliments of Idera diagnositics manager a pretty slick tool.Any ideas in resloving these below critical issues. A bunch of the items are running incredibly slow. my guess is the sync schemas having an issue is the main culprit.  Secondly my cpu is getting overworked. By looking at the below graph it is getting hit hard, but only 10% actually by sql server. Any ideas how to find what other processes are overworking the CPU? |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2009-07-16 : 15:06:40
|
| you have a transacton that has been running for over 4 and a half hours and don't think maybe that needs to be looked at?use sp_who2 to find out if it is being blocked. if it is, find out what spid is blocking it and what its doing using DBCC INPUTBUFFER. if it isn't see what the heck that 4.5 hour (so far) query is doing. also that job.You have 2 blocked sessions. this is going to be what u need to dig into. find out what process is doing the blocking and why. |
|
|
|
tripodal
Constraint Violating Yak Guru
259 Posts |
Posted - 2009-07-17 : 12:00:06
|
| Task manager will tell you the ther users of the cpu. Be sure to include Kernel time. Running your data on a software raid would cause something similar.What Edition of SQL do you have |
|
|
|
bcanonica
Starting Member
35 Posts |
Posted - 2009-07-18 : 14:19:07
|
| Sql server 2005. I originally thought it was just that the agent job was an issue, but after restarting sql server other jobs have had similar issues and the resync schemas job runs fine. For some reason my cpu usage just keeps growing and growing until the server is unusable. Any suggestions? |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2009-07-18 : 15:33:34
|
Are all your transactions closed correctly?
N 56°04'39.26"E 12°55'05.63" |
|
|
|
bcanonica
Starting Member
35 Posts |
Posted - 2009-07-20 : 13:19:50
|
| Yes all transactions are closed correctly. |
|
|
|
|
Sql server 2005 runs slow