[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 480
  • Last Modified:

How to scale a SQL 2008 R2 server

I have a SQL server 2008 R2 server that hosts our production database. We are getting ready to take on the biggest load we have ever seen in our history. I am trying to gather some information to decide how much RAM \ CPU \ DISK \ ect that I need to add to accomplish this task. I am also trying to be a little proactive on some configuration things to do so we can get through these peak times (increase the DBCC buffer cache, etc).

It's been a while since doing this....What should I be gathering in perfmon \ SQL profiler to get a good guess as to what my big production load will be like?

Our intent is to put a stress test on the front and back end, then gather metrics during this test...I just need a clue as to what some good metrics are to gather.

Thanks experts!
0
sqlagent007
Asked:
sqlagent007
  • 2
1 Solution
 
MrAliCommented:
some good metrics to collect on each indv. drives are:
-Disk Sec/Read
-Disk Sec/Write
-Disk Write Bytes/Sec
-Disk Read Bytes/Sec

Total Memory used
Total Memory Free

CPU %


Compare these results and compare them on your user experience.  You now have a baseline.  You should also look into many things you can google such as:
-VLF management.  Do you have fragmented log files?  
-Poor performing Indexes
-Locking/Blocking issues will become more noticeable with more load
-Your most expensive queries, can you fix those so they aren't as much as a load?

Those would be my initial steps off the top of my head.  Let us know if you have any questions.
0
 
sqlagent007Author Commented:
Thanks!
0
 
MrAliCommented:
You're welcome, let us know how it goes.
0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now