SQL Server Capacity Planning

  • Jeswanth Jaishanker

    SSCommitted

    Points: 1869

    Dear All,

    I am trying to create a Server configuration for one of the fortcoming Projects where A SQL server Instances will have 70 - 100 Database Running and for each database there could be 50-60 Users connected Concurrently and each db could be between 3 to 4 GB of Size (Maximum). Is there formula for calculate the Processors, RAM and HDD Requriements ? Any help would be greatly appreciated.

    Thanks in Advance.

    Jeswanth

    --------------------------------

  • CrazyMan

    SSChampion

    Points: 12560

    Hmm nice one, I can roughly calculate the Configuration, but i would look into various other factors as well , It will be interested to know if there are formulas for this, if i am not wrong i read something like this on MS website long age, i will let you know if i can find this 🙂

  • EdVassie

    SSC Guru

    Points: 60260

    Ther are some specialist forums dealing with Capacity Planning and Capacity Management topics. Google should help you find these.

    The main thing you need to focus on at the start is your methodology, not on the maths. The above forums should help with the methodology.

    From what I know about CP, you need to model what resources an actual instance will take and extrapolate from there. If you have anything running at present that is a useable model then use it, otherwise you will have to build a model. Anything else is guesswork.

    When you have your model, look at how much CPU, I/O per second, disk space, etc is being used for a single instance. Then multiply that out to give what you need for the whole application.

    You then need to slice that up by what your equipment can handle. If you need 1m I/O per second, do you need multiple HBAs into your SAN to achieve this, or do you even need multiple SANS. Can yuor favourite server handle the CPU and I/O load, or do you need multiple servers. Do the same with every part of the infrastructure. Eventually you will get a shopping list of what to buy.

    One of the top CP experts in the UK told me some time ago that the best you will get with this planning is +/- 25% of reality. For a newcomer to this work, +/- 50% would be a good result. Make sure your management have the right expectations about the result of this exercise. If they do not like your figures, suggest they either pay for experienced consultants or accept the best you can offer.

    Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.

    When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara

  • Jeswanth Jaishanker

    SSCommitted

    Points: 1869

    Hi Edvassie/Crazy Man,

    Thanks for the replies, as i am from a developmet background dont have much knowledge in Implementation aspect of the SQL Server, So googling for all teh methodologies and documents and learning the basics of CP, looks like this is much more tougher and interesting than what i thought 🙂

    Thank You

    Jeswanth

    --------------------------------

  • SQLactions

    Old Hand

    Points: 324

    Hi,

    Check out this blog, it may help you asses the current capacity limits of your 'model' and further be able to determine what resources you need to build the new server. May not be a complete solution but hope it would get you started.

    http://sqlactions.com/2012/05/15/collection-and-reporting-of-perfmon-data-for-sql-server-capacity-planning-and-trend-analysis

    Prashant
    Check out my blog at http://sqlactions.com

  • webrunner

    One Orange Chip

    Points: 29838

    sqlactions (6/4/2012)


    Hi,

    Check out this blog, it may help you asses the current capacity limits of your 'model' and further be able to determine what resources you need to build the new server. May not be a complete solution but hope it would get you started.

    http://sqlactions.com/2012/05/15/collection-and-reporting-of-perfmon-data-for-sql-server-capacity-planning-and-trend-analysis

    Thanks!!

    -------------------
    A SQL query walks into a bar and sees two tables. He walks up to them and says Can I join you?
    Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply