February 27, 2014 at 1:11 pm
Hello
What ways would you go about attempting to quantify what performance improvements that can be expected from improved hardware.
For example lets say we decide to
1. Get a processor with high clock speed
2. Get lower latency disks
What ways would there be to calculate the performance gains expected?
March 2, 2014 at 9:47 pm
It depends on the kind of system you're talking about: OLAP/OLTP? What are the demands on the system on daily/weekly/monthly basis? How many databases/applications are supported by the server? What version of SQL Server? 64 or 32 bit?
I'll assume you've got a busy 64-bit Enterprise SQL server with thousands of connections and where speed is critical (and these few lines are only scratching the surface)...
Lower latency disks in SANs or Raided storage arrays will increase your IOPS. This will help with the number of reads/writes your SQL server can perform - more disks = more IOPS. In a perfect world you'd want SSD's for your tempdb files, separate drives (15K rpm minimum) in RAID-10/or luns for each data file and each log file in a database...yada, yada, yada - of course this all depends on how the disks are set up (whether or not they are properly aligned, formatted in the proper file system and block size). Basically the faster your storage, the more you can do with it (i.e. More databases, more applications can run queries against it, etc).
Again in perfect world, RAM should always be maxed out. Since RAM is relatively inexpensive you should buy as much of it as your budget will allow or your server can handle. More RAM will allow you to do more on your server. If you have reporting, it will allow you the freedom to allot more memory to SSRS, if other applications run on the server they might not be starved for memory, the OS itself won't stress under memory pressure, and in a nutshell will allow SQL Server to have the memory it needs to cache/store more things in memory. This will increase your PLE (Page Life Expectancy) - and this is a good thing. The faster SQL can fetch what it needs from memory, instead of having to look it up from the actual physical disk is always a good thing.
CPUs? Again, the more the merrier. IMHO multiple sockets with multiple cores will outperform a single processor box with a higher number of cores. If you have the budget for it go for 2-4 sockets with no less than 8 cores. The more CPU power you have the better your processor queue length will be. If you have a bunch of developers who like to do everything for a report in their TSQL (sorting, formatting, aggregates, etc) then extra CPU horsepower = good things. If you don't have enough CPU power to handle your daily server load (ie, standard database activity, plus backups, maintenance tasks, etc) then at busier times, things will really slow down for you.
You should really look into PerfMon and capture a couple weeks of information to monitor your CPU, memory and other metrics to actually find out what hardware changes could actually improve your situation. You may even find your current hardware is just fine, you just need to focus on performance tuning with existing processes running on your server. In any event, check out this link to using perf mon - http://www.brentozar.com/archive/2006/12/dba-101-using-perfmon-for-sql-performance-tuning/
It may help get you going in the right direction...
Hope this helps.
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
March 7, 2014 at 5:37 am
Very good reply Thanks
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply