 |
|
| SQL Server Tips: |
|
|
|
DATABASE ADMINISTRATION
SQL Server indexes made handy
Jeremy Kadlec of Edgewood Solutions
09.01.2006
Rating: -2.00- (out of 5)
|
When starting an application, you should be able to identify many of the indexes based on a reasonable set of rules. As the application grows and changes, the indexes should be reviewed to ensure no good index candidates are overlooked. It should be based on how the application is used not based on theory. In the same light, make sure erroneous, duplicate or valueless indexes are removed. This is a precautionary measure to make certain your SQL Server does not have to manage unneeded indexes. In this tip we will identify index recommendations, index creation and index validation.
Commonly Indexed Columns
A common question for many DBAs and developers, relates to the traditional columns recommended for indexes. The general recommendations are:
Additional index considerations should be:
The volume of data – With a small number of rows, a table scan may be as fast and cost less than traversing an index to access the data.
The selectivity of the data – When the data has low selectivity, i.e. the same data is stored in the column, the value of the index can be minimal.
The processing type – With a transaction processing system, the indexes should be minimized to support insertions as fast possible, compared to a reporting system where querying the data in new and different ways will benefit from extensive indexes.
SQL Server 2000 indexing dos and don'ts
Indexing Q&A: Answers to your common indexing questions
How can I identify the needed indexes for my application?
Once the generally recommended indexes are identified, then it is time to determine the best indexes for your application. Below is a list of the SQL Server tools that will help in the identification process.
[TABLE] How do I build my indexes?
Once the generally recommended indexes are identified, then it is time to determine the bes
To continue reading for free, register below or login
To read more you must become a member of SearchSQLServer.com
t indexes for your application. Below is a list of the SQL Server tools that will help in the identification process.
[TABLE]
How can I validate the indexes are beneficial?
It is great to build indexes, but it is not worth the time to build and maintain indexes without determining if you are benefiting from all of that work. One common scenario you do not want to fall victim to is building indexes that benefit one portion of the application and hinder another portion of the application. One approach to prevent this problem is to build a baseline and as changes are made to the database, run the data collection scripts and compare the results to the baseline. For additional information about baselines, check out this tip on Developing a performance baseline.
Adding some handy indexes to your environment should be a significant performance boost. The challenge is making sure all generally recommended indexes are covered and then to move on to more advanced ways to determine which indexes to select. If you have taken the time to analyze and build the necessary indexes, be sure to validate the performance improvements with a quantifiable measure.
About the author: Jeremy Kadlec is the Principal Database Engineer at Edgewood Solutions, a technology services company delivering professional services and product solutions for Microsoft SQL Server. He has authored numerous articles and delivers frequent presentations at regional SQL Server Users Groups and nationally at SQL PASS. He is the author of the Rational Guide to IT Project Management. Jeremy is also the SearchSQLServer.com Performance Tuning expert. Ask him a question here.
More information on SearchSQLServer.com
Tip: Stored procedures: Create and delete constraints and indexes
Book Excerpt: Quick steps: 12 indexing tips in 12 minutes
Book Excerpt: How to apply indexes in the real world
|
|
|
|
|
|
|
|
|
| TechTarget provides technology professionals with the information they need to perform their jobs - from developing strategy, to making cost-effective purchase decisions and managing their organizations' technology projects - with its network of . |
|
| |
All Rights Reserved, , TechTarget |
|
|
|
|
|
