Home

Database Optimization
Site hosted by Angelfire.com: Build your free website today!

 

The decisions that go into designing an optimized Database

 

Written By Julius Brown

 

Designing an efficient database can be an overwhelming task. A relational database relies solely on design in order to be efficient and cost effective. That is why database design is such an important part of database optimization. In this report I will describe four design variables that can effect database optimization Indexes, data types, file groups and transaction logs. I will first explain each term individually and then I will describe its effects on database size and what factors should you concentrate on when designing a database.

Indexes are a way to retrieve specific data from a database. Indexes perform a function much like an index in the back of a book. You make a request for some information. The database then checks its index structure and then tells you where that specific piece of information is located. An index is like a pointer when you look for specific data it searches the database until it finds it. A Index that is created correctly can optimize database performance.

Indexes must be created right or they can slow down the performance of your database. One best ways to make sure you index is running at optimum performance is to perform standard maintenance on your database. I list and briefly describe some basic maintenance techniques to insure optimum performance of your index.

 

 

 

 

Indexes in sql server 2000 do not effect space considerations very much. When you are creating a index sql server 2000 allows you to use two commands to optimize performance sp_configure and create index. These commands give you the ability to configure your database so that it performs at ideal level.

Sql server 2000 has a variety of different data types. Data types determine the values that a variable can contain and what operations can be performed on it. Data types give you a way to set standards in your database design (small int, big int for example). Data types also give you a way decrease the size of your database.

Example:

You are the database administrator of a large record company that consists of 30,000-artist worldwide. The cfo of the company want a database that consists of every artist their records and the amount of record sold in one year. After designing a database in columns you realize that to create this database would decrease the over all performance of the entire database. You decide to catalog all the records using the int form this will reduce the size the records take up by 4 bytes. This will effectively cut the size of the database in half and it will still maintain it functionality.

Data types can have a major effect on space considerations. It can give you the option to effectively cut your new database information in half. Data types give the ability create large databases and still maintain functionality.

A filegroup is a collection of individual files that groped together and named for the purpose of easy database administration .A file can only be a part of one file group. Tables, indexes, text, ntext and image data can only be associated with one particular filegroup. That means that all of their files will be retrieved from that filegroup. There are three separate filegroups that can be used by sql server they are:

 

 

 

 

Filegroups are an excellent way to distribute files throughout your database and eliminate device contention. Database files can be configured to grow and shrink automatically, reducing the need for database management and eliminating many problems that occur when databases run out of space. That why I believe that file groups are very important to the optimization of a database and overall space limitations.

Transaction log is the place that sql server writes all of its transactions before it writes them to the database. This includes any data that is associated with each other and any data that is not. The transactions log insures that all your data is intact in the case that you would have to restore your database. I believe the transaction log is most important consideration to a database and also can be the most detrimental to size consideration in your database. The size of the transaction log seems to be an ongoing problem when designing a databases but sql server has created effective way to shrink and administer the transaction so it does take up to much space in your database.

 

References

http://www.sqlmag.com/Articles/Index.cfm?ArticleID=9792

http://citeseer.nj.nec.com/330107.html

http://java.sun.com/docs/books/tutorial/java/nutsandbolts/datatypes.html

http://www.gis.com/data/data_types.html

http://www.mssqlcity.com/Tips/tipFile.htm

http://www.mssqlcity.com/Articles/Tuning/FileOptimTips.htm

http://www.sql-server-performance.com/indexes_not_equal.asp

http://www.sql-server-performance.com/optimizing_indexes.asp

 

 

Home