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!
LVL 1
sqlagent007Asked:
Who is Participating?
 
MrAliConnect With a Mentor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.