Upgraded SQL server, performance plummets

Question

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.

SPECIFICATIONS:

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

SYMPTOMS:

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:

1. Capped the SQL instance at 10 gig to leave resources for other applications and overhead
2. 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.

1. Available server memory seems to be related. Agree/Disagree?
2. I have read about Windows file system cache, I would like advice on this.
3. 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.
4. 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?
5. What other things should I consider doing to improve performance.
6. 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.

Thank you.

Answer 1

Available server memory probably has nothing to do with the problem. The SQL Server is supposed to eat all the memory and keep it to itself. This is by design.

When you upgraded the SQL Server from SQL 2005 to SQL 2008 R2 did up update the statistics on the tables within the SQL Server database? This needs to be done as the statistics which were built within SQL Server 2005 aren't used by SQL Server 2008 and higher.

You need to look inside of SQL Server to see where the problems are coming from. By just looking at the CPU, memory and network stats you are only getting half of the picture. SQL Server execution plans will tell you most everything that you need to know about what's going on, specifically if statistics are out of date (which I'm guessing that they are), if indexes are missing. Additionally you need to look at the index fragmentation to see if your indexes are heavily fragmented causing wasted memory and additional physical IO.

What does your IO workload look like, specifically the response time and the IO per second, both for reads and writes?

What are the waitstats within the SQL Server? Is there blocking that's going on within the database when queries are being run?

Stop restarting the SQL Server instance? All that's doing is making things worse and it will make solving the problem harder not easier.

Is there a lot of dynamic SQL within the application or is it all done via stored procedures?

Answer 2

Throwing hardware at SQL server will only help so much. Misconfigured installations can kill performance with no apparent cause from tools like PerfMon. If you do not have significant database tuning experience, hire a SQL Server Consultant.

If the system was performing acceptably on less hardware, there is likely a configuration issue. A competent consultant should be able to get the issue cleared up in less than 5 hours. (https://www.google.com/?q=sql+server+consultant)

If you still want to try and figure things out, take a look at the following tutorials on basic performance troubleshooting:

• sp_blitz (automatic diagnosis script)
• Tips to using virtualization with SQL Server
• Use activity monitor, pay particular attention to waits, IO statistics and waiting tasks
• This walkthrough of most monitoring tools included with SQL server

Also, stop with the restarting - all you are doing is causing SQL Server to loose all of the helpful caches it uses to speed up execution.