capacity planning

Dear Experts,

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

How we can do effective capacity planning ?


more ▼

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

avatar image

830 89 93 95

(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

avatar image

Grant Fritchey ♦♦
137k 20 46 81

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.

Follow this question

By Email:

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



Answers and Comments

SQL Server Central

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



asked: Sep 25, 2012 at 05:48 PM

Seen: 719 times

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

Copyright 2018 Redgate Software. Privacy Policy