2

First off, I'm not a sys admin, just a programmer frustrated by poor server performance…

We’re currently running a VM (I believe the host is Win Server 2008) on a newer E5-2650 CPU, 24 GB RAM, at least according to the System Properties dialog. I can’t say for sure what resources the SQL VM has because I only have access to it and not the host. Sorry, I don’t know my way around that well. I also don’t want to make our current sys admin feel like I’m stepping on his toes by asking a bunch of questions if I don’t have to. I just want to make a case for upgrading if it will help...

We are running SQL Server 2008 r2, all of our clients are either Win 7 or RC via a terminal server running 2008. Our application is very DB heavy, particularly when you consider the size of our organization.

So my main question is, we’re currently running Windows Server 2003 r2 sp2, could upgrading to Server 2008 r2 (or newer) noticeably improve performance? I’ve read that because of its newer features it can improve performance elsewhere, but I haven’t seen anyone comment on DB performance.

If so, is there any evidence I can use to support my argument?

Additionally, since we are running SQL 2008 r2 and our clients are Win 7, what DB drivers should our clients have installed? What should the server have installed?

If I need to elaborate on anything, please comment.

jreed121
  • 129
  • 2
  • 8
  • 2
    Any Windows server that's older that 2008 R2 should be upgraded to at least 2008 R2. Well, assuming you care about having it work. And the impending end of Extended support on Server 2003. And the improved stability, reliability and performance. And the supportability of Server 2008 R2 vs Server 2003 (which is not just about whether it's vendor-supported, but about how hard it is to find qualified assistance for a platform.) Assuming there's nothing (like a #$&^@ing "legacy" application) preventing you from upgrading, of course. – HopelessN00b May 08 '14 at 19:07
  • 1
    Ask yourself *why* SQL Server would become faster. What operation that SQL Server does would become faster? I cannot think of any that would become faster in a meaningful way. SQL Server mostly uses the CPU scheduler and IO subsystem of the OS. The OS has limited opportunity to make these go faster. – usr May 08 '14 at 19:31
  • Get with the sysadmin team and give them [this set of links](http://www.brentozar.com/sql/virtualization-best-practices/). I think that'll provide you with more results than just upgrading the OS (although upgrading off 2003 is a good idea for support reasons). – Katherine Villyard May 08 '14 at 22:48

2 Answers2

5

Possible duplicate of : How do you do load testing and capacity planning for databases?

You need to work with the sysadmin team to measure and identify the current bottleneck(s). If he's difficult to engage with, I sympathize and that sucks; you'll need to figure out how to best work with him. It will be a very good idea to have a solid description of the problem, with reproducible test cases, rather than "it's slow!" Also, use Perfmon and SQL profiler; from the little you told us, the problem could be bad indexes and then it's on you. Once you know what SQL thinks is slow, you can find out if it's the code, the DB layout, slow IO, swamped CPU, etc.

Once you know what the bottlenecks are, you can see if Win2k8 R2 will help.

Hint : probably not.

Additional thought : why are you looking at 2008 R2 and not 2012 R2?

Community
  • 1
mfinni
  • 35,711
  • 3
  • 50
  • 86
  • We've had problems in the past leapfrogging versions - not really technical problems, but personnel problems. So I figured 2012 would be out of the question. While I do a lot of SQL development, we don't have any control over the DB structure because it's proprietary, third-party software. I’m stuck with indices as they are. With that said, it is a popular application and simple tasks take far longer than they should – I can’t imagine that this app is returning data anywhere near how fast it should be. Also, given the operations it grinds on, it has to be the DB. – jreed121 May 08 '14 at 21:09
  • 2
    1. You're going by "feel", not metrics. You're a programmer, so you must be able to appreciate the scientific method - observe, hypothesize, test, analyze. – mfinni May 08 '14 at 21:20
  • 1
    2. If it's MS SQL, you are *not* stuck with the indices as they are. Engage the vendor. You should be doing this anyway. – mfinni May 08 '14 at 21:20
  • @mfinni, my first thought when I read the question was "Why not Windows Server 2012?" or even "Why not 2013?" so thank you for asking the obvious. – trysis May 09 '14 at 00:29
  • @jreed121 My dad, who has worked at Prudential for a long time, has said Microsoft has always supported the company when it asked. MS even helped them make older versions of IE very secure, according to him. So what mfinni says about talking to the vendor seems very warranted (if you're willing to pay MS for the support, I imagine). – trysis May 09 '14 at 00:33
  • @trysis - I'm talking about the vendor of his 3rd-party app, not the vendor for SQL. – mfinni May 09 '14 at 02:40
  • Oh, well MS may still be able to help. But yes, talk to the vendor, @jreed121, if they can't move to Windows Server 2012 or 2013, I'm not sure they're worth your time. – trysis May 09 '14 at 03:37
-2

Ok, basics:

Unless yo u talk of 32 bit 2003 R2 the differences will be minimal.

Rule one for databases - the live and die by IO, and normal VM's are totally inadequate for heavy duty IO work. Not because VM's suck, but because a VM setup is a general setup. That like getting a standard rental car and trying to participate in a race - SQL database serves often are designed AROUND the disc subsystem.

Likely you have a totally undersized disc subsytem - try getting it's latency (ms/request), if that is higher than low single digit it impacts query performance. SOmetimes that is ok (if you do a reporting query you basically WILL overload it), but for anything transactional.... I also bet you have the log on the same discs. Because in a VM you have no real control of where stuff is stored.

The OS will make a difference, but it will be quite small compared to that fact. Not that I would run anything on a 2003 server these days, and I would fire any admin upgrading to 2008 R2 instead of 2012 R2. But there may be policies in larger companies getting in the way of that ;)

TomTom
  • 50,857
  • 7
  • 52
  • 134
  • 1
    `Because in a VM you have no real control of where stuff is stored.` That's not true at all. The person in charge of the vm and storage infrastructure *should* absolutely be able to provide separate spindles for DBs vs temp vs log, assuming that they're available. – mfinni May 08 '14 at 18:32
  • In charge, yes. But from WITHIN the VM you have zero control. You are a passenger of whatever the host has set up - nothing else I do say. You may THINK you have multiple discs, but you have no clue whether they are real or not. – TomTom May 08 '14 at 18:32
  • Yes, if the DBA is not allowed to talk to the server/infrastructure people, to provide requirements, you will have a problem. You can even have the same problem on a physical server using shared storage; has nothing to do with virtualization. – mfinni May 08 '14 at 18:34
  • Except by my experiecne a physical server would request serious IO space by the SAN people. VM's often run on "by the mill standard hardware" and someone requests "a VM" (without speciying the IO budget) and then some discs and all ends up on a totally inadequate standard raid array. Seen that in quite some companies. Heck, guilty in some, too. One more - often critical - layer of people in between. And "VM's don't need super fast IO hardware to start with" attitude. – TomTom May 08 '14 at 18:38
  • I'm going to have a tough time arguing with your generalizations. I also can't make DBAs and sysadmins play well together if they don't know how. – mfinni May 08 '14 at 18:39
  • The problem is often they do not even talk on a VM level. YOu order the VM in a standard form or self service portal - and there you go. FOr a physical server the process would involve SAN planning, but QUIT E often that is lost in the "we have a VM infrastructure" stuff. There simply is no way to order high performance storage space ;) – TomTom May 08 '14 at 18:42