Paladin Consultants, LLC

Arguably, the most important factor is achieving optimum performance from SQL Server is the proper construction and maintenance of the indexes. The first task is to determine whether the indexes you will be creating will be used by SQL Server in processing queries. SQL Server will not use an index if the first column of the index is not used either in a search argument of a join clause.

The SQL Profiler is a very useful tool in helping to analyze indexes and queries. Indexes are used by SQL Server to enforce uniqueness of data in the database tables and to speed up searches. Creating a useful index is one of the most important tasks in designing a database. It is unprofitable to create indexes on every field in a table, so you must try to choose indexes on fields which are used in joins and search arguments, and which have a high selectivity ratio. The selectivity ratio of an index is calculated by dividing the total unique values in the field by the total number of rows in the table. If the selectivity is high (close to 1), then that field is a good candidate for indexing. Look for ratios higher than .8 to use an index.

SQL Server keeps a set of statistics on every index in the sysindexes system table. These are available through the command

dbcc SHOW_STATISTICS (tablename, indexname)

Stats available are the number of rows in the table and the number of rows sampled. The number of sample values (up to 200), average key length, the index densities (distinct range rows, and average range rows), and sampled value (the endpoint of each range of values) are the other characteristics of stats. Key density is the reciprocal of the count of distinct key values in the tables. The smaller the key density, the more effective the index is likely to be. SQL Server’s query optimizer uses these statistics to determine which indexes to use when executing a query.

The index statistics are run when the index is created, or when you update the stats by issuing the command

UPDATE STATISTICS table | view [ index | ( statistics_name [ ,...n ] ) ] [ WITH [ [ FULLSCAN ] | SAMPLE number { PERCENT | ROWS } ] | RESAMPLE ] [ [ , ] [ ALL | COLUMNS | INDEX ] [ [ , ] NORECOMPUTE ] ]

This updating of index statistics should occur as part of an ongoing program of database maintenance. Recent versions of SQL Server provide for automatic updating of the index statistics table. Another system table counts when rows have been added, deleted or updated in a table. There is another process that monitors those counts, and when certain thresholds are attained, statistics are automatically updated.

The SQL Profiler can show when an automatic update is happening. It is also possible to disable the auto update of the statistics table.

Index Design Principles

• Identify likely indexes by examining the most frequently used queries and transactions
• Index all columns used in joins.
• In a composite index, identify the most selective columns first.
• Don’t index on columns with low selectivity ratio.
• Be aware of the features of clustered and non-clustered indexes.
• Clustered indexing is generally faster because pointers are used for pages rather than individual rows. However, the overhead of maintaining a clustered index is significantly higher that that of a non-clustered one.
• Primary keys are usually clustered indexes by default, but this can be changed if the construction of the application suggests a better approach. You may want to make the primary key a non-clustered index, saving the clustered one for columns that have many duplicate values, like lastname, for instance. Columns that are called in an ORDER BY clause are also likely candidates, or those that are used in queries that call for a range of values, or columns that are used in a join clause.
• For non-clustered indexes, define indexes with high selectivity, as discussed earlier.
• Non-clustered indexes are good for single row lookups, and joins. Queries on ranges are better served with clustered indexes.
• Covered indexes are sometimes very efficient. A covered index is one where all of the fields being selected and searched are contained in the non-clustered index itself. These index pages can be scanned like a table itself, and because the pages are much smaller than the table, results are produced more quickly.
• Multiple indexes (where some of the required fields are contained in one index, and some in another) are also sometimes more efficient than composite indexes, especially if putting all the fields in one index would result in a wide index. The efficiency is obtained from the fact that the composite index consumes more space and allows fewer rows to a page, and therefore more pages may be required to execute the same query.
• Recent versions of SQL Server allow indexing of views. This will definitely improve the performance of queries based on the view, but there is considerable overhead requirement which is, indexes must be updated when the tables are updated, in addition to the indexes on the table itself.

The Index Tuning Wizard is an excellent way of analyzing a database and recommending a set of indexes to improve performance. It can even generate a SQL script to generate the suggested indexes. Just generate a trace file and start the Index Tuning Wizard from Enterprise Manager Tools menu. Or you can get there through the Query Analyzer. To automate and schedule the running of the Tuning Wizard, use the itwiz command or set a schedule in SQL Server Agent to regularly run itwiz.

Resources

• General Information on SQL.

This resource provides a general information on SQL.

• Tutorial: Introduction to Structured Query Language.

This is an excellent resource that discusses SQL in detail.

Metro NY / NJ SQL Server Consultants

We specialize is custom database software. Call us for a free consultation (973) 635 0080

Computer Consultants: Database Development
Computer Programming: Web Site Services
Computer Programming: Custom Software
Computer Consulting: IT Consulting
Paladin Consultants, LLC Home Page
Computer Consulting: Contact Us