March 23, 2009 at 9:07 am
My company has recently installed a new SQL server on VM, and given this 2 3Ghz processors and 4GB memory.
On this for a number of reasons there is now running 5 instances of SQL Server 2005 Std Edition. Most of the hosted databases are for third party pieces of software. Previously these databases where on separate 2000 servers which are being decommissioned.
At this stage nothing has been done to limit the memory usage and AWE is off for each instance and we seem to be having some performance issues, with CPU being 100% for brief periods during the day and users experiencing time outs.
I don't really know what to start looking at or how to solve the problem. Naturally budgets mean we can't really spend any money.
March 23, 2009 at 9:14 am
Have you found what's causing the timeouts? That'll be a good place to start.
Coincidentally, there's actually a front-page article today on that exact subject. Check that out, it might help.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 23, 2009 at 9:22 am
Are you saying there's one VM on a server with 5 instances? Or 5 VMs with an instance each?
March 23, 2009 at 9:25 am
I haven't been able to find what is causing the time-outs, there doesn't seem to be anything in the logs I wouldn't expect but it seems to be happening for a number of applications.
It's one VM with 5 instances on it.
Thanks
March 23, 2009 at 9:27 am
If you have 5 SQL instances on one machine (virtual or not), you need to limit their memory. SQL is a memory hog and will take as much as it can. Without AWE (and assuming 32 bit), each of those instances can try to allocated up to 2GB memory. Since there is only 4 GB, that's a problem.
Set the max memory of each so that they can't starve each other of memory.
See if the timeouts correspond to jobs, to excessive blocking or to errors in any of the SQL error logs.
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
March 23, 2009 at 9:39 am
I am not 100% sure how to decide how to specify min and max values, for example one of the instances is hosting an HR databases which HR insisted was on its own instance so its access could be tied down. This one I assume I would allocate far less than the default instance which hosts most things?
Also our VM guy says windows server std can only see 4GB which is why he hasn't allocated this server any more - is that accurate, or is that what AWE does.
Many Thanks For You Help
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply