x

capacity planning

Dear Experts,

Is there any list of checks and for capacity planning in sql?

How we can do effective capacity planning ?

Regards,
more ▼

asked Sep 25, 2012 at 05:48 PM in Default

pits gravatar image

pits
830 79 91 92

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

Capacity planning falls into two general categories. Planning for existing systems and planning for systems under construction. For existing systems, you can gather metrics over time to see how the system is currently growing and then project that out in a line to see when you may need more disk storage, additional CPU capacity, etc. For systems under development, you have to get estimates on how much data will be stored, how big that data is, how many users will be accessing the system, what the anticipated transaction rate will be, etc.

There's no simple, straight-forward, if you have A, you need a server sized X. It really depends on the behavior and growth of the system you're managing. Best thing to do is start gathering metrics now.
more ▼

answered Sep 25, 2012 at 06:04 PM

Grant Fritchey gravatar image

Grant Fritchey ♦♦
98.7k 19 21 74

thank you Grant,but i am not quiet sure how to gather metrics, is there any script or method for the same? also how we can analise users who acceessing system in terms of capacity planning?
Sep 25, 2012 at 06:08 PM pits

Oh boy. There are a whole ton of scripts to capture those. The first two chapters in my book are about nothing but setting up methods for capturing performance metrics. It's not possible to provide you with a script in a post on a site like this.

First off, use Performance Monitor. That comes with the Windows operating system and is the one best place to gather operating system level information such as disk space, memory used, cpu used, etc. This also provides mechanisms for monitoring within SQL Server so you can understand how SQL Server is using the resources you provide it.

Then you can use Dynamic Management Objects to gather some metrics by running T-SQL statements. But these will only get some of the metrics. You can use these to augment the information gathered through Performance Monitor.

Finally you can capture information using Profiler or Extended Events (depending on the version of SQL SErver you're using, 2008+ you should use Extended Events) that relates to query performance, another measure for capacity and capacity planning.

This is not a small topic.
Sep 25, 2012 at 06:14 PM Grant Fritchey ♦♦
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x8

asked: Sep 25, 2012 at 05:48 PM

Seen: 537 times

Last Updated: Sep 25, 2012 at 06:14 PM