how in increase performance on SQL Server?

Question

We are in the middle of 2012 udpates and we are thinking of doing the following:

Migrating to SQL Server 2008 from 2005 (i have read this will give us a small speed boost)

Increasing memory from 8GB to 32Gb (we were having some memory pressure and time out issues..) ($600)

Buying a SAN (dell md3000), for HA reasons, but also becuase I read it will help speed up the application. (this is a 6K investment...)

Replacing our 4 year old Dell 2950 with new R510 dual processor with 32gb ram. (4K for a new server)

Which one of these actions above will give us the most bang for the buck? Which one should we do first?

Thanks!

Answer 1

What is better, Pizza or Spaghetti?

Simply said - your DBA should be samrt enough to make basic assessments of the current server and find out where the bottlenecks are. That said, you waste a ton of money because "you heard" instead of making smart decisions.

Migrating to SQL Server 2008

Stupid. Sorry. 2008 R2 is current, SQL Server 2012 due next year. Why not schedule the gugprade so it goes directly to 2012?

Increasing memory from 8GB to 32Gb (we were having some memory pressure and time out issues..) ($600)

More important: use 64 bit OS and server. Always smart.

Buying a SAN (dell md3000), for HA reasons, but also becuase I read it will help speed up the application. (this is a 6K investment...)

Wasted.

Replacing our 4 year old Dell 2950 with new

What about you order a SuperMicro rack cage. 4 rack units high get you 72 (!) discs and the server in one nice case, or 2 rack units high 24 discs. Put in a high end RAID controller (adaptec) and put in a good combo of relatively fast discs (SATA: 300gb Velociraptors and some SSD for caching and logs). This will run circles around your system. For lower a price.

Which one should we do first?

Ask a proper DBA to analyze your current problems.

Answer 2

There's no single right answer in terms of "oh you should definitely buy x, that's always going to help". There's little benefit in buying memory if your system is bottlenecked at the processor, and so on. The real answer, I'm afraid is that you need to determine what your current performance issues are and address those.

Now you apparently know (how?) that you have memory issues, so this is one area that may be worthwhile, but I'm not sure I'd invest in new memory for the current server platform if you're considering replacing that anyway.

Have you considered using performance monitor or using scripts like Brent Ozar's 'blitz' script to tell you where your performance issues are?

Lastly, don't neglect looking at the application you're using in itself. if this is an in house database that isn't running optimally then it may be the best "bang for the buck" here is to improve that and re-check your performance figures before spending money on anything else.

Answer 3

You can't determine where to spend money on improving performance, until you know where the bottlenecks are. You mentioned you had memory pressure but not if it had a performance impact. You certainly should hold off on the upgrade to 2k8 since 2012 is around the corner. You should start testing your app now with the RC. You can also certainly do the hardware upgrade as 4 years is a fairly long server lifecycle.