Seeking help with a new SQL server.
A client of mine was running an aging SBS 2003 server (Xeon 3060, 4 gig of RAM) and a line of business application with a 25 gig SQL Database using SQL Server 2005. They have 14 simultaneous users. The server had 1 gigabit NIC and everything is interfaced to a single 48 port gigabit switch.
I replaced the server with a new one (Xeon E5620, 48 gig of RAM) running Hyper-V Server Core 2012. The new server has 4 NIC's as follows:
- 2 Intel Gigabit NIC's configured as a LBFO team. The team is interfaced to a virtual switch, to which all of the VM's interface via Virtual NIC's.
- 1 Gigabit NIC for Hyper-V Administration
- 1 Dedicated NIC onboard for IPMI
The virtual machines and allocated memory are as follows:
- 'SBS2011' (16 gig) Is their DC, Exchange Server, DNS, etc. and also their general file share
- 'SERVER2' (16 gig) Is running the SBS Premium Add-On (Server 2008 R2 + SQL Server 2008 Standard) and is hosting their line of business database and another file share related to that database.
- A virtual Win7pro (4 gig) machine I use occasionally as a test workstation
- A virtual Win8pro (4 gig) machine I use for the Hyper-V administration (although I am finding I prefer the powershell commands better).
- This leaves 8 gig remaining for the Hyper-V core's overhead
When the first users come in and start working it functions acceptably but after only a 5-10 minutes the performance tanks. It then takes anywhere from 10-30 seconds for them to switch 'tabs' within their application. This happens with wired as well as wireless clients.
The SQL server is allocated 16 gig of RAM, of course the SQL service immediately took 15 gig. Since this server also serves two other small applications and a file share I figured it prudent to cap the SQL memory at 10 gig. The issue persists.
Figuring I needed to approach this in a logical manner I have installed software to monitor aspects of the network including memory & CPU usage on both servers, bandwidth for both servers and for the wireless. Now that it has gathered some data for me to work with I believe I am still having memory related issues on the SQL server.
Bear in mind the old server ran the same database along with SBS operating system, Exchange, all File Shares, and SQL 2005 on a total of 4 gig of RAM, so I figured 16 gig should be ok. Of course I realize this is a different OS and different software.
Things I have done so far:
- Capped the SQL instance at 10 gig to leave resources for other applications and overhead
- Configured the backup to shutdown the SQL service at 12:30am and to restart it at 1:00am (The idea was to release resources)
The client employees begin arriving to work at about 7:30am, performance tanks by 8:00am. You can see in the server memory graph below that the following is occurring:
The available memory is about 4 gig from the previous day 12:30am the SQL service stops and available memory becomes about 15 gig. 1:00am the SQL service starts and available memory becomes about 5.5 gig 7:30am employee's arrivce 7:50am available memory starts to dive (Employee's starting to log into applications) 8:10am performance is poor, employee's unhappy, boss unhappy = me unhappy
My thoughts and considerations:
This is a medical office that is seeing patients all day. More than half of the staff are accessing patient records for brief periods while the patient is there. Long term caching of this information is probably unnecessary. The rest of the staff is accessing schedules, billing, etc. All from the same application.
- Available server memory seems to be related. Agree/Disagree?
- I have read about Windows file system cache, I would like advice on this.
- I feel I should allocate more memory to the SQL Server virtual machine. I can nuke the Win8 & Win7 VM's down to 1 gig and leave 4 for the Hyper-V core. That would give me 10 gig to toss at the SQL server, bumping it to 26 gig.
- Pertaining to #3, I would like advice on how to to allocate this memory within the server. Should I give it to the 2008 R2 OS, give it to the SQL Instance, or spread it out among the two?
- What other things should I consider doing to improve performance.
- CPU usage and Bandwidth don't seem to be a problem from what I can see but please see resource screenshots and graphics at doaks.net/sqlhelp
Any advice would be appreciated.