Capacity Management/Planning - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

SQL Server Administration (2008)

Capacity Management/Planning

Author

Topic

Peter99
Constraint Violating Yak Guru

498 Posts

Posted - 2012-02-29 : 12:10:53

Hi,

In SQL Server capacity management/planning what factors I have to take care or from where I can start this work. (which report give this data, any script that may help etc).

How to analyze current capcaity and performance of the system and based on current reports forecast for future increase or degrease of resources to maintain capacity and peformance.

Thanks

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2012-02-29 : 12:33:19

Have you been monitoring your servers for Disk usage, I/O, CPU and memory utilization?

Peter99
Constraint Violating Yak Guru

498 Posts

Posted - 2012-02-29 : 12:37:20

I will start this work. Just want to know any script, tools or what methods you are using? First to establish a baseline, then monitor, then forecast.

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2012-02-29 : 15:52:04

Using Perfmon, I start with these to establish a baseline

\Memory\*
\Network Interface\Bytes Recvd/sec
\Network Interface\Bytes Sent/sec
\Physical disk\*
\Processor\(total) % Processor Time
\SQL Server Buffer Manager\*
\SQL Server General Statistics\User Connections
\SQL Server Latches\*

For the objects that I take all of, I'm particularly interested in
Memory\% committed Bytes in use
Memory\Available MB
Memory\Cache Faults/sec
Memory\Available MB
Physical Disk\(total) Avg Disk Queue Length
Physical Disk\(total) Avg Disk Read Queue Length
Physical Disk\(total) Avg Disk Write Queue Length
Physical Disk\(total) Avg Disk Sec/Read
Physical Disk\(total) Avg Disk Sec/write
SQL Server Buffer Manager\Page Life Expectancy
SQL Server Buffer Manager\Cache Hit Ratio
SQL Server Latches\Latch Waits/sec
SQL Server Latches\Avg Latch Wait Time

I'll capture all of the above and write to a trace file, then periodically run RELOG and pop it into a database. Alternatively you can write it directly to a database but there may be a performance impact on your network.

For disk usage, I wrote a custome script to log that to a database daily. Also for each individual database file.

Makes it a lot easier to trend and to predict future growth.

Peter99
Constraint Violating Yak Guru

498 Posts

Posted - 2012-02-29 : 16:09:32

Russell: Thanks for reply.

For disk usage, do you have script?

Thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources