How to scale a SQL 2008 R2 server

Posted on 2012-09-18
Last Modified: 2012-09-18
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!
Question by:sqlagent007
    LVL 7

    Accepted Solution

    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.
    LVL 1

    Author Closing Comment

    LVL 7

    Expert Comment

    You're welcome, let us know how it goes.

    Featured Post

    How to improve team productivity

    Quip adds documents, spreadsheets, and tasklists to your Slack experience
    - Elevate ideas to Quip docs
    - Share Quip docs in Slack
    - Get notified of changes to your docs
    - Available on iOS/Android/Desktop/Web
    - Online/Offline

    Join & Write a Comment

    This is basically a blog post I wrote recently. I've found that SARGability is poorly understood, and since many people don't read blogs, I figured I'd post it here as an article. SARGable is an adjective in SQL that means that an item can be fou…
    SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
    It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
    Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

    755 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    21 Experts available now in Live!

    Get 1:1 Help Now