Indexes: An Overview and Maintenance for Performance

Written by Desiree Harris

Many people knowrepparttar importance of creating indexes on SQL Server database tables. Indexes greatly improverepparttar 133386 performance of a database. However, while many people create indexes on their SQL Server tables, many people don't maintain them properly to ensure queries run efficiently as possible. I'll begin by giving a quick overview of how SQL Server 2000 stores data and how indexes improve performance. Then, I'll spend quite a bit of time explaining why, when, and how to maintain indexes with DBCC SHOWCONTIG and DBCC INDEXDEFRAG to ensure queries run inrepparttar 133387 most efficient manner.

SQL Server 2000 stores data into what is known as a heap. A heap is a collection of data pages containing rows for a table. The data isn't stored in any particular order andrepparttar 133388 data pages themselves aren't in any sequential order. The data is just there with no real form or organization. When SQL Server accesses data in this form, it does a table scan. This means SQL Server starts reading atrepparttar 133389 beginning ofrepparttar 133390 table and scans every page until it findsrepparttar 133391 data that meetsrepparttar 133392 criteria ofrepparttar 133393 query. If a table is very large, this could greatly decreaserepparttar 133394 performance of queries.

Indexes will hastenrepparttar 133395 retrieval of data. It is important to understand how data is used,repparttar 133396 types of queries being performed andrepparttar 133397 frequency ofrepparttar 133398 queries that are typically performed when planning to create indexes. An index is far more efficient whenrepparttar 133399 query results return a low percentage of rows andrepparttar 133400 selectivity is high. High selectivity means a query is written so it returnsrepparttar 133401 lowest number of rows possible. As a rule, indexes should be created on columns that are commonly searched; this includes primary and foreign keys. It follows that columns that contain few unique values should never be indexed; this will increaserepparttar 133402 number of rows returned in a query.

There are two types of indexes to consider when planning: Non-Clustered and Clustered Indexes.

A non-clustered index stores data comparable torepparttar 133403 index of a text book. The index is created in a different location thanrepparttar 133404 actual data. The structure creates an index with a pointer that points torepparttar 133405 actual location ofrepparttar 133406 data. Non-clustered indexes should be created on columns whererepparttar 133407 selectivity of query ranges from highly selective to unique. These indexes are useful when providing multiple ways to search data is desired.

A clustered index stores data similar to a phone directory where all people withrepparttar 133408 same last name are grouped together. SQL Server will quickly search a table with a clustered index whilerepparttar 133409 index itself determinesrepparttar 133410 sequence in which rows are stored in a table. Clustered indexes are useful for columns searched frequently for ranges of values, or are accessed in sorted order.

Each table can have only one clustered index, however up to 249 clustered indexes can be added per table. For more information on how Clustered and Non-Clustered indexes store data visit

While I could go on and on about how SQL Server 2000 stores and accesses data in a heap and in an Index architecture, I will move on to discuss maintaining indexes with DBCC SHOWCONTIG and DBCC INDEXDEFRAG.

Once indexes have been created, it is important to maintain indexes to ensurerepparttar 133411 best possible performance. If indexes are not maintained, over timerepparttar 133412 data will become fragmented. Fragmentation isrepparttar 133413 inefficient use of pages within an index*. There are a number of tools available that will help with optimizing indexes to ensure they are running well, however I will only discuss DBCC SHOWCONTIG and DBCC INDEXDEFRAG in this article.

The DBCC SHOWCONTIG command will provide fragmentation information on data and indexes within a specified table and it will also determine ifrepparttar 133414 data and index pages are full. If a page is full, SQL Server must splitrepparttar 133415 page to make room for new rows. This statement should be run on heavily modified tables, tables that contain imported data, or tables that seem to cause poor query performance. Whenrepparttar 133416 statement is executed, here is what will be returned: Statistic Description Pages Scanned Number of pages inrepparttar 133417 table or index. Extents Scanned Number of extents inrepparttar 133418 table or index. Extent Switches Number of timesrepparttar 133419 DBCC statement moved from one extent to another while it traversedrepparttar 133420 pages ofrepparttar 133421 table or index. Avg. Pages per Extent Number of pages per extent inrepparttar 133422 page chain. Scan Density [Best Count: Actual Count] Best count isrepparttar 133423 ideal number of extent changes if everything is contiguously linked. Actual count isrepparttar 133424 actual number of extent changes. The number in scan density is 100 if everything is contiguous; if it is less than 100, some fragmentation exists. Scan density is a percentage. Logical Scan Fragmentation Percentage of out-of-order pages returned from scanningrepparttar 133425 leaf pages of an index. This number is not relevant to heaps and text indexes. An out of order page is one for whichrepparttar 133426 next page indicated in an IAM is a different page thanrepparttar 133427 page pointed to byrepparttar 133428 next page pointer inrepparttar 133429 leaf page. Extent Scan Fragmentation Percentage of out-of-order extents in scanningrepparttar 133430 leaf pages of an index. This number is not relevant to heaps. An out-of-order extent is one for whichrepparttar 133431 extent containingrepparttar 133432 current page for an index is not physicallyrepparttar 133433 next extent afterrepparttar 133434 extent containingrepparttar 133435 previous page for an index. Avg. Bytes free per page Average number of free bytes onrepparttar 133436 pages scanned. The higherrepparttar 133437 number,repparttar 133438 less fullrepparttar 133439 pages are. Lower numbers are better. This number is also affected by row size; a large row size can result in a higher number. Avg. Page density (full) Average page density (as a percentage). This value takes into account row size, so it is a more accurate indication of how full your pages are. The higherrepparttar 133440 percentage,repparttar 133441 better.

The DBCC INDEXDEFAG command will rebuild a specified index or all indexes for a specific table. This command also allows use ofrepparttar 133442 fillfactor option which reducesrepparttar 133443 number of page splits per data or index page. Usingrepparttar 133444 fillfactor option increases performance on insert and upstate statements. If a data page is full, SQL Server must splitrepparttar 133445 page to make room forrepparttar 133446 new rows. The fillfactor allows specification of a percentage of space to leave available onrepparttar 133447 data pages for inserts and updates.


Written by Ben Higgins

If you are looking for a component to enhance your calendar or date/time selection web page, has released a new component called RichDatePicker. It provides a point-and-click popup calendar window that allows you to select a variety of date and time appearances. This feature will resolve any issues end users might have with date/time formats and will also improverepparttar accuracy ofrepparttar 133385 dates entered into your online forms.

I couldn't keep this example any more simpler to show you how easy it is to use this component. First either copyrepparttar 133386 RichDatePicker.dll file to your application's bin folder or intorepparttar 133387 GAC. Then addrepparttar 133388 following line torepparttar 133389 top of your ASPX file.

<%@ Register TagPrefix="DP" Assembly="RichDatePicker" Namespace="RicherComponents.DatePickerControl" %>

Withinrepparttar 133390 body of your HTML addrepparttar 133391 RichDatePicker Tag as shown below. I have also addedrepparttar 133392 built in .NET calendar tag for this example.

Cont'd on page 2 ==> © 2005
Terms of Use