Physical design and Testing with MySQL - 44150

Solution Posted by
Solution Detail
Price: $52.00
  • From: ,
  • Posted on: Thu 20 Feb, 2014
  • Request id: # 43859
  • Purchased: 0 time(s)
  • Average Rating: No rating
Request Description
Solution Description

Physical design

adding up the field sizes in the physical model. For example, each record in the PERSON table is 400 bytes long. With SQL Server 7.0, you need to add at least 10 bytes per record to account for administrative overhead. You can plug the numbers into this little formula:

(RecSizeBytes * EstNoRecs) / BlockSize =  Num_Blocks_Storage

Using a block size of 8096 bytes for SQL Server 7.0 and rounding up to the next integer value (SQL Server doesn't let you store part of a record on a block), you'll need 36 blocks to store the data in the PERSON table. To give you a starting point for figuring out how much disk space you'll need (and whether you want to use file groups or striped sets to enhance performance), perform the following calculations for each table. To get the total space requirements for the user data, add the number of blocks needed for each table in the physical model. Add 10 percent of this total number of blocks for the system and database catalogs (start with a minimum of 25MB), and add 35 percent of the user data size for the transaction log. Then add space for indexes, various log files, snapshot and archive relations, and database backup files. This additional space might be as little as 20 percent of the user data size, or as much as 100 percent, depending on how many indexes you want to create, how many snapshot and archive relations you plan to set up, and so on. The final value will help you select the best physical storage devices and estimate the cost of both immediate and long-term data storage. By knowing roughly how much user data you'll have, you can also better estimate the type and cost of your backup and recovery schemes.