October 17, 2013 at 11:33 am
Huh, wasn't aware of the OS memory limit. That would definitely explain the usage amount, though; we're on Standard edition. The actual physical box we're on has 98GB of RAM, and I'm fairly certain nothing's being done with the majority of that. Not sure if I can convince management to upgrade the OS, but it's definitely worth a negotiation attempt.
Still observing the effects of increasing the memory limit; things have started chugging a little faster now, but I'll keep an eye on things.
- 😀
October 17, 2013 at 11:41 am
I found this article on SQL Server requirements. It appears that STD version may see 64gb not 32gb. Maybe I misread it.
http://msdn.microsoft.com/en-us/library/ms143506(v=sql.105).aspx
None the less it is clear that there is memory pressure on this box? From what I gathered it was a CPU issue not memory.
My gut says it has more to do with the VM box and it's connection to the SAN. I would be interested in seeing some specs on the LUNs that make up the drives. Also I'd be interested in seeing how the other components of the box & SQL Server are configured.
I have had success allowing 2-3gb of memory for the OS, all depending on what else is on the box. More may be needed if there are other things happening on the box.
I've also seen really big boxes (64 cores, 32gb memory) come to a crawl only to find when the box was provisioned the defaults for TEMPDB were taken and left on the C: drive. That one made the hair on the back of my neck stand up! I reconfigured TEMDPB, creating 32 files on a separate LUN. Users of that box are much happier now the box isn't being strangled.
Kurt
Kurt W. Zimmerman
SR DBA
Lefrak Organization
New York, NY
http://www.linkedin.com/in/kurtwzimmerman
October 17, 2013 at 11:46 am
.... one more thought.... I saw a big performance hit when a new VM box was created with greater specs than the original box but performance suffered. Turned out the VM box was on a different subnet than the original one. Once it got move to the original subnet performance returned and turned out to be far better than before.
As I said before, there are so many moving parts to a VM server problems can arise just about anywhere.....
Keep at it and I'm sure you will find your bottleneck.
Kurt
Kurt W. Zimmerman
SR DBA
Lefrak Organization
New York, NY
http://www.linkedin.com/in/kurtwzimmerman
October 17, 2013 at 12:14 pm
SQLIO if there is thought of disk I/O performance being an issue.
Paging can be CPU intensive, especially on a VM.
I still think it is something simpler that isn't the same.
There is an art to troubleshooting on a VM.
Never assume, and a second set of eyes is always a good thing.
Spreading across multiple disks does bring up an interesting point.
Especially when you think in terms of host, VM, and SQL instance.
But back to the basics, think spindles, not logical partitions, as you sort through it.
i.e. - a drive logically split to C:\ and D:\ is really less than just 1 C:\ drive, while C:\ on a SAN is a whole other animal.
Interesting read is how they loaded 1 TB of data into SQL in 30 minutes.
But reality is likely very few ever need to go that far to have good enough performance to meet their needs.
October 17, 2013 at 12:14 pm
Greg Edwards-268690 (10/17/2013)
SQL can only see as much as the OS.Look at the compare link on this page.
OK, I see... as I reread all of the posts that is when I saw your post about STD vs Enterprise and I thought it was SQL Server and not the OS..... .My mistake.
Now that I'm on the same page... CX_PACKET may have a lot to do with Server connectivity to the LUNs, which I've seen in the past as well.
Kurt
Kurt W. Zimmerman
SR DBA
Lefrak Organization
New York, NY
http://www.linkedin.com/in/kurtwzimmerman
October 17, 2013 at 12:22 pm
Ah, looks like we're back to where we were when the problem started; things are moving pretty slowly again. It seemed that the max memory adjustment helped, but it was probably just the sweet song of confirmation bias luring me over for a bit :-P.
I think, at this point, it's best for me to just sit down the the sysadmin and talk over exactly what changed, piece by piece, with the move. Without much VM expertise or knowledge of what changed in the move, I think I've hit my limit on what I can troubleshoot on my own. Unfortunately, the sysadmin is usually preoccupied with a ton of stuff, so it might take awhile (largely the reason I haven't been able to get through to them as of yet as it is). In the meantime, I'll read up on VMs and subjects related to what's been mentioned here to try to grasp the topics a bit better.
Thanks again for all the help!
- 😀
October 17, 2013 at 12:24 pm
Check to make sure you have set a reservation for the memory in sql. Also see how much memory the host has left over after all the VMs are eating it up. We had a similar problem once which was a result in the guests being sized way too close to the host limit, a database server ballooned and the impact was felt.
You have to leave enough memory for the host and also watch for Ballooning
Why is it that people who can't take advice always insist on giving it? - James Bond, Casino Royale
October 17, 2013 at 12:48 pm
Just keep in mind, allocating 40 GB on the host, and only seeing 32 GB in the VM, means you are likely boxed in.
I always would make sure I understood my options, as things seem to grow pretty fast these days.
Although memory and disk are cheap these days, other things can factor in that make adding them a bit of a pain.
And Kurt -
many stumble over OS and SQL limits being different, when the versions have similar naming.
Why they do this is a mystery at times, or maybe it is just a test.
I remember having to always be specific with the network admins when talking to this point.
Especially if you need to plan for an upgrade.
In some cases, in may make more sense to install Ent OS right away when you think you may need to expand beyond 32 GB of RAM. I seem to recall there is not an upgrade in place for this. So a clean OS install, along with SQL server, etc. is a lot different than phyical insertion (or virtual allocation) of memory.
Viewing 9 posts - 16 through 23 (of 23 total)
You must be logged in to reply to this topic. Login to reply