SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Hardware v SQL Tuning


Hardware v SQL Tuning

Author
Message
tct_sql_admin
tct_sql_admin
SSC Rookie
SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)

Group: General Forum Members
Points: 45 Visits: 133
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?
MyDoggieJessie
MyDoggieJessie
SSCertifiable
SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)

Group: General Forum Members
Points: 6624 Visits: 7390
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" ;-)
tct_sql_admin
tct_sql_admin
SSC Rookie
SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)

Group: General Forum Members
Points: 45 Visits: 133
Very good reply Thanks
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search