July 31, 2008 at 3:30 am
Hi,
I have been facing a issue on one of my production box.
The problem is most of the memory on this server is eating by SQL Server.
SO users are getting timeout and job used to fail into it with timeout message on its first step with is integrity check.
Most of the time slowness reported on the server - when checked found 1.6+ memory is used by sql server and the usage of CPU is up and down very frequently (most of the time 90-95 or 100%)
I’ve checked blocking, active processes, query wait time etc etc but couldn't found anything useful.
Others are blaming us coz its sql who is eating all the resources.
The server has 2 gb ram, and processor 2.41 GHz
Would like to mention its sql 2005, and having enough disk space. All the databases are very small except the two and their sizes are 8gb and 33gb.
Worth adding only one application is hosted into this database server, and approx 40 users use the application - but not all connect at the same time.
Is there any way to check the breakup of memory and cpu used by sql into box, so that i can show them. If its sql then all these who are using and capturing the resources etc.
Or if any other best way to look into the problem please.
Cheers,
DKG
July 31, 2008 at 4:20 am
I'd suggest you purchase, very quickly, an inside sql book - the 2005 ones tended to spawn so in a sense the inside 2000 is better as all the info is there in one place.
Failing that get on a training course as if you're asking these questions you shouldn't be in charge of a production system, sorry but I'd be very concerned - your employer should give you support and training.
OK,
so memory use is correct, sql server will take up to 1.6gb in bog standard x32, only worry if there is excessive paging and free memory has dropped to almost zero. It's not good but the o/s can run in 400mb , failing that set max sql memory to say 1.25Gb
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
July 31, 2008 at 4:23 am
sorry got pulled away and forgot your original Q's. cpu is bad, check your page life expectancy. the one thing which is hard to trim is cpu. I'd probably suggest you don't have a powerful enough server.
look for high io queries and check disk io latency and disk usage. isolate the queries - I've some rdls you can download on my webs ite which will help see http://www.grumpyolddba.co.uk
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
July 31, 2008 at 4:36 am
Is there anything other that SQL Server on that server?
I would suggest you run profiler for a while during the slow periods (or preferable a server-side trace) and look for slow running queries or queries taking a lot of resources. Those queries are prime candidates for optimisation.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 31, 2008 at 4:47 am
GilaMonster,
Checking Long running queries make sense to me.
I was Wondering
if anything else could be checked, thanks for your input.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply