Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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:

memory usage

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.

share|improve this question
    
You'll probably need to attack it from multiple angles. 3rd party LOB app support, SQL troubleshooting, app/event logging, possibly even wireshark. Maybe the app is old and isn't agreeing with SQL 2008 in its current revision? –  TheCleaner Jun 13 '13 at 18:04
    
Odds are this can all be solved within SQL Server. 3rd party LOB support isn't going to be needed, and odds are that WireShark is way overboard for solving this. –  mrdenny Jun 13 '13 at 18:23
1  
This is not a memory shortage problem. From your graphs, the system has 4 GB of memory free (used by cache) at all times. BTW: SQL Server is using most of the memory you assign to it for caching as well. It does not need 10 GB, it just speeds up matters if a query's indexes do not have to be read off disk. You should find out what exactly happens if the application starts slowing down - whatever you looked at so far does not indicate problems as such. I would take a closer look at I/O utilization as this often enough is a nasty bottleneck in virtualized setups. –  the-wabbit Jun 13 '13 at 18:30
    
It is funny - and likely a clue - how the poster talks about memory and CPU, bu totally says NOTHING about - Discs. I would somehow be teh server is IOPS starving. –  TomTom Jun 13 '13 at 18:36

2 Answers 2

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?

share|improve this answer

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:

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.

share|improve this answer
    
Thank you all for your advice. I am working closely a technician at the software vendor that seems to have great knowledge of SQL and troubleshooting. We are working on diagnosing the issue. I will report back. Again, thanks for all your feedback. It really set me in the right direction. –  user177731 Jun 13 '13 at 23:50

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.