asked
capacity planning
Dear Experts, Is there any list of checks and for capacity planning in sql? How we can do effective capacity planning ? Regards,
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.
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.
8 People are following this question.
