April 1, 2008 at 12:28 pm
Can anyone give me a formula for determining how man processors and how much RAM a server should have given the average number or max number SQL Server connections the database has? Or some other indicator? I have a 7GB DB that averages 400 connections that is just on its knees due to lack of hardware resources and I need some technical fodder to throw at my MIS department in order to get some action.
Thank you,
Dan
April 1, 2008 at 1:10 pm
Dan,
I don't think there is a set formula like what you are asking for. You may be able to find information on how to initially size a SQL Server, but it sounds to me like you just need proof that the box is undersized as it is now, correct? I would recommend running Profiler and Windows Perfmon on the DB server and watch the server counters for SQL Server Target/Total memory, Buffer Cache Hit ratio, Page Life Expectancy, Lazy Writes/sec, Batch Requests/sec, % Processor Time, and Current disk queue length.
Basically, watch CPU, memory, and disk for bottlenecks. If you find what you think may be a bottleneck, pay close attention to what the application was doing at the time. Many percieved hardware problems are SQL code tuning opportunities.:)
April 1, 2008 at 2:31 pm
John,
That is what I figured but just wanted to get other opinions. MIS gave me a blade server with three VMs on it. I got one of the VMs. They initially allocated 10GB of local hard drive space, one Xeon processor and 1GB of RAM for a 7GB, 400 connection replicating database. What a nightmare. When I jumped up and down and yelled and screamed they gave me a half a GB more RAM and bumped my hard drive to 20GB. Unbelievable. This just goes against common sense. Not to mention the other two VMs and whatever is running on those. To make it worse, we are running the free version of VMware so there is no dividing of resources, only limiting.
Thanks for your input and the info about which counters to watch is helpful.
Dan
April 1, 2008 at 3:13 pm
Wow. I feel for you. I've seen this before and I'd have to say that from my experience, I would be very wary about running a multi-user SQL Server database server on a virtual server. Scary.
From your description of the limited resources that you have, I would focus more on looking at the perfmon data than profiler. It sounds like you're already on the level, but you'll need to prove that your system is starved of resources and then you can come up with some recomendations on what kinds of resources your application requires.
Just a note, have a peek at the attachment. This is a little doc. that I keep around that summarizes which counters I usually watch for a first go 'round at server performance. Have fun.
April 1, 2008 at 4:01 pm
I'm not as concerned about SQL Server on a VM. I have friends doing it and it can be done, but you have to understand it's a shared resource. First and foremost you need good I/O, which is probably your first bottleneck. IF you have a busy server, it needs to have it's own IO path and spindles. It's not like any other server, so I'd get that sorted first. From what I've heard, a VM takes a 20% or so IO hit, if it's on it's own drives, to be sure you can handle that.
Second, no SQL Server runs on less than 2GB in any size production environment. It's just not good practice to do this if you have more than a couple users, so I'd look for the buffer cache hit ratio and I'd get a minimum of 4GB to start with.
As far as CPU, you'll have to solve those issues first.
April 1, 2008 at 4:16 pm
I suppose I should have qualified that more Steve. The reason I have the concern is because what you've stated in that the resources are shared and many folks don't think of that when they choose to go that route. I find this similar to using a SAN. A well configured SAN will perform just as good or better than well configured direct storage, but many times the folks configuring it don't know the importance of a database server's having dedicated resources. Then, just like in this case, you get shared disks/spindles.
April 2, 2008 at 8:02 am
All,
Thank you all for this information. It has given me enough cannon fodder for my meeting with MIS tomorrow. I appreciate the timely input.
Can I get one more piece of info? I am good with the completeness of this topic that I posted. Should I do something to remove it, to keep the house clean?
Thanks again
Dan
April 2, 2008 at 8:12 am
No... just leave it like the other million or so posts. It's a resource that others can search on. Your post and it's answers might help someone else along the way.
--Jeff Moden
Change is inevitable... Change for the better is not.
April 2, 2008 at 8:15 am
On the subject of VM's... heh... Steve said he's got friends doing it... reminds me of what everyone's Mother in the World has probably said at one time or another...
"If your friends jumped off a cliff, would you jump, too?"
😉
--Jeff Moden
Change is inevitable... Change for the better is not.
April 2, 2008 at 8:31 am
I think that's "if your friends jumped bridge..." 😉
It can be done and it can work, but it has to be done right. I've seen people try to move their server to a VM, not expecting to take any hit on IO or CPU, and have it fail. If you give it enough resources it will work well.
You don't want to move heavily used servers over to a VM. You want to move those instances that very much underutilize the hardware they are on.
April 2, 2008 at 8:39 am
Concerning the VM:
It is working, but not very well. I feel it would work with the right amount and configuration of resources. In my case, I'm running a freight train on a VW Bug engine. If the other two VMs are in use (I know they are running but I don't know what they are being used for, if anything) then you can say that I am running 3 freight trains on a VW Bug engine. Another issue is that the software vendor that created the software that is using the SQL Server doesn't support VM installations. Its crazy.
Thank you all again,
Dan
April 2, 2008 at 2:31 pm
Steve Jones - Editor (4/2/2008)
Heh... nope... me and my friends used to jump off bridges for fun... Mom didn't mind because we never hit anything hard enough where she had to play Doctor. We did end up on the wrong side of a cliff every now and then on our bicycles... Mom would get mad because she had to glue us back together... Dad would get mad because he'd help us glue our bikes back together. Bridges were a lot more safe, it appears :hehe:
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 12 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply