SQL Server Performance

Capacity Planning For SQL Server 2005

Discussion in 'ALL SQL SERVER QUESTIONS' started by mz61s4, Mar 10, 2013.

  1. mz61s4 New Member

    Hi,

    I need to do a capacity plan for a existing SQL Server, which is currently having below Server Configuration.

    • 64 Bit Operating System
    • 8 GB Memory
    • Windows Server 2008 SP1
    • 2 Node Cluster Environment
    • Peak hours Physical Memory and Processor Utilization go up to 40-60-80%.
    • SQL Server 2005 Enterprises Edition
    • DB Size is 350 GB, which is the only user DB resides on this box.
    Now the current/Expected utilization of this DB is
    Data rate calculations



    Logs generated

    Daily (GB)

    Within working hours*

    Data rate (MB per minute)


    Current

    50

    48

    68


    Expected with 80000 users

    143

    137

    195


    I have tested various scenarios to determine the appropriate log file size and following are the results




    Log File Size Inc

    Processing speed (MB per minute)


    2 MB

    127


    5 MB

    261


    10 MB

    269


    20 MB

    128


    I further ran some tests to find out the processing speed if a report is generated simultaneously



    Log File Size

    Report Generated Simultaneously

    Processing speed (MB per minute)


    10 MB

    No

    269


    10 MB

    Yes

    201


    So now as per the existing utilization, how can I do a capacity plan to upgrade the Hardware like RAM/CPU for the expected requirement, so in future the system should not have any issue when the expected requirement are set from application side to process the data.

    Thanks in Advance

    Rahul
  2. mz61s4 New Member

    I think tables are making confusion so I am making a manual entry for that.

    Table 1
    Logs generated Daily (GB)Within working hours*Data rate (MB per minute)
    Current 50 48 68
    Expected with 80000 users 143 137 195

    Table 2
    Log File Size Processing speed (MB per minute)
    2 MB 127
    5 MB 261
    10 MB 269
    20 MB 128

    Table 3

    Log File SizeReport Generated Simultaneously Processing speed (MB per minute)
    10 MB No 269
    10 MB Yes 201
  3. Shehap MVP, MCTS, MCITP SQL Server

    Usually , log file is out of any DB capacity plan because it is supposed to be maintained periodically through 2 jobs (Transaction log backup & Truncate and Shrink) to control its storage usage .

    However to determine a clear storage capacity plan for any Data files ( .mdf or .ndf files ), you can use the attached DMV query to calculate DB sizes daily and store its results in any temporarily table to know clearly how much DB growth along with weeks, months, years ..?

    But for CPU capacity plan , it depends mainly on monitoring 2 things:

    · CPU utilization percent if It exceeded 70 % , then you should necessarily upgrade your CPU resources to reach to an ideal value (<30 %)
    · The alerts of Worker waits/sec and worker wait time (msec)

    But for RAM , you can determine a capacity plan for them according the alerts of Memory Grant waits/sec and Memory grant wait time (sec)

    Despite of this , I do recommend heavily to upgrade to SQL Server 2008 R2 to enjoy with the new rich powers of UCP (utility Control point ) monitor which can monitor the capacity usage of all your DB servers in one dashboard as follows:

    · CPU usage
    · Storage usage of each Database
    · Storage usage of each Database file
    · Storage usage of each drive

    If any further help is needed , please let me know

Share This Page