| QUESTION & ANSWER |
SQL Server 2000 indexing dos and don'ts |
 |
By Jeremy Kadlec, Edgewood Solutions
14 Feb 2006 | SearchSQLServer.com |
|
|
In the second part of this three-part feature, Edgewood Solutions' Jeremy Kadlec outlines best and worst practices for indexing in SQL Server 2000.What are some indexing dos?
Kadlec: No system is stagnant, so do not fear making index changes to support a usage shift.
Index columns with highly selective (different) data. If the data selectivity is low, SQL Server cannot derive a great deal of value from the index.
If you have a very large database (VLDB), split the data and clustered index from the nonclustered indexes into a separate file group on separate physical disks.
Approach indexing from a holistic perspective to ensure that indexes that are beneficial in one portion of the application are not detrimental in another portion.
Balance the number of indexes per table to give SQL Server less work to do when performing transaction processing. That would require less overall storage to support it while still allowing for efficient processing in terms of time and I/O.
Balancing also helps ensure that new indexes do not benefit one portion of the application and unintentionally strangle another. Test the indexes thoroughly and have a second pair of eyes review the indexes to double check that a change in one place does not adversely impact another portion of the application.
Review query plans during the testing phase to ensure the index will improve the query time and resource use as expected.
Select an appropriate fill factor for each index. If the data has a minimal amount of changes to the middle of the table, configure the indexes to have a high fill factor, i.e., closer to 100%, which will save on the storage needed. If the data has many changes to the middle of the table, select a lower fill factor, i.e., 65% to 85%, so that as data is added to a page, page splitting is minimized until the indexes are rebuilt.
Issue the DBCC SHOWCONTIG command on a regular basis to determine the fragmentation of the table, index or data. Based on the fragmentation level, rebuild the indexes on a regular basis.
If indexes are experiencing a great deal of fragmentation in a short period of time, review how the data is inserted, updated and deleted to determine if you need a lower fill factor. Also, determine if the code can be changed to reduce the amount of fragmentation.
Capture a performance baseline as indexes are created, removed and/or rebuilt to establish the performance impact for each of these operations.
Follow a change management process with testing and deployment of new indexes. Make sure you document the overall change.
Perform database maintenance on a weekly, monthly or quarterly basis. Do not expect your SQL Server to run like a finely tuned machine unless you perform maintenance.
What are some indexing don'ts?
Kadlec: Avoid hot spots on your table. A hot spot occurs when all of the transactions are accessing the same location of the table (for example, the end of the table) and causing contention issues.
Do not use index hints to override the SQL Server optimizer index selection for the query unless all of the options have been exhausted.
Avoid page splitting, which happens when no room is available for a new row on an existing data page. When this occurs, SQL Server takes half of the data and moves it to a new page. The net result is the original page has the first half of the data and the new page has the second half of the data. This is a very resource-intensive process that SQL Server goes through to fit the row and continue processing.
Do not build or maintain duplicate indexes unnecessarily.
The art and science of SQL Server indexing
 Home: Introduction
Part 1: Indexing Q&A: Answers to your common indexing questions
Part 2: SQL Server 2000 indexing dos and don'ts
Part 3: Tricks for using the Index Tuning Wizard
| ABOUT THE AUTHOR: |
|
Jeremy Kadlec
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. Jeremy is also the SearchSQLServer.com Performance Tuning expert. Ask him a question here.
Copyright 2006 TechTarget
|
|
|
|
|
|
