Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Hardware v SQL Tuning Expand / Collapse
Author
Message
Posted Thursday, February 27, 2014 1:11 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Saturday, July 26, 2014 2:28 PM
Points: 13, Visits: 75
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?
Post #1546097
Posted Sunday, March 2, 2014 9:47 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Wednesday, August 20, 2014 9:31 AM
Points: 3,900, Visits: 7,138
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; They'll drag you down to their level and beat you with experience"
Post #1546773
Posted Friday, March 7, 2014 5:37 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Saturday, July 26, 2014 2:28 PM
Points: 13, Visits: 75
Very good reply Thanks
Post #1548694
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse