question

deepkalmeida avatar image
deepkalmeida asked

Database sizing

What is the best way estimate the size of the database.Any tool designed for it, Which is the best kind of disk to be used in the servers to get optimum IOPS.
sql-serverdba
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Blackhawk-17 avatar image
Blackhawk-17 answered
You are asking simple questions that end up with complicated answers. SQL Server is best treated in a holistic manner. As an example: disk IOPS won't matter if you don't have sufficient RAM... paging will occur and you lose your disk I/O benefits. As for sizing a database... create it in DEV and know your data. Estimate the retention periods of data and the change deltas that will occur. Then double it. Then baseline your PROD dB and monitor its growth. The bottom line is there is no magic formula that covers every situation. A few indexes added to a dB can throw out a lot of your previous assumptions. A new data retention policy can soon outgrow the hardware. 50 new users can be a tipping point for issues to arise. It also depends on the environment you work within. Are a lot of instances consolidated or is this a dedicated server? Are you using a SAN and able to grow as required? Are you running as a VM? Some smarter people than me will be able to give you some guidelines and formulas that can help but the important points for me are that you must have intimate knowledge of your data and the holistic nature of SQL Server.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Tim avatar image
Tim answered
The only way to forecast space needed for a database is to trend and bench mark growth of the existing database. That or use a crystal ball. Even forecasting can be very wrong if there is some other factor that would warrant drastic growth. The simplest way to track this is to create/use a utility database and write the file size of your mdf, ndf's, and ldf's to a table. Make sure to include a date time stamp. Then over time you can calculate how much space your database is growing week over week and month over month. To answer your question on what type of disk to give the optimum IOP's, well again, what kind of IOP's do you need? If you have a static database that is not heavily read/written, then you could get by with slower and fewer disk. If you have a high transaction database you need fast disk and lots of them (the more spindles the more IOP's) With the higher density disk, getting lots of IOP's is becoming much harder without wasting available storage. If you need lots of IOP's then solid state could be the answer if you have plenty of budget. I hope this helps, I could write a chapter on this topic.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.