Developping for the translation industry RSS 2.0

 Friday, August 22, 2008

Here are some guidelines that I gathered on indexing and boosting SQL Server query performance. I hope that those tips will be as useful to you as they were to me!

  1. Periodically, run the Index Wizard or Database Engine Tuning Advisor against current Proiler traces to identify potentially missing indexes.
  2. Remove indexes that are never used.  This will enhance Inserts/Updates/Deletes performance because the database engine will have fewer indexes to maintain when those operations occurs.
  3. Normally, every table should have a clustered index. Generally, but not always, the clustered index should be on a column that monotonically increases — such as an identity column. In many cases, the primary key is the ideal column for a clustered index.
  4. Indexes should be considered on all columns that are frequently accessed by the JOIN, WHERE, ORDER BY, GROUP BY, TOP, and DISTINCT clauses.
  5. When creating indexes, try to make them unique indexes if at all possible. SQL Server can often search through a unique index faster than a non-unique index because in a unique index, each row is unique, and once the needed record is found, SQL Server doesn’t have to look any further.
  6. If a column in a table is not at least 95% unique, then most likely the query optimizer will not use a non-clustered index based on that column. Because of this, you generally don’t want to addnon-clustered indexes to columns that aren’t at least 95% unique.
  7. This seems obvious but some people forget to follow this simple advice : Don't automatically add indexes on a table because it seems like the right thing to do. Only add indexes if you know that they will be used by the queries run against the table.

If you are like me and want to know more about how SQL Server manages his indexes, take a look at the sysindexes table that is part of every database.  You simply have to run “SELECT * FROM sysindexes”.

Here are some of the more interesting fields found in this table:

  • dpages: If the indid value is 0 or 1, then dpages is the count of the data pages used for the index. If the indid is 255, then dpages equals zero. In all other cases, dpages is the count of the non-clustered index pages used in the index.
  • id: Refers to the id of the table this index belongs to.
  • indid: This column indicates the type of index. For example, 1 is for a clustered table, a value greater than 1 is for a non-clustered index, and a 255 indicates that the table has text or image data.
  • OrigFillFactor: This is the original fillfactor used when the index was first created, but it is not maintained over time.
  • statversion: Tracks the number of times that statistics have been updated.
  • status: 2 = unique index, 16 = clustered index, 64 = index allows duplicate rows, 2048 = the index is used to enforce the Primary Key constraint, 4096 = the index is used to enforce the Unique constraint. These values are additive, and the value you see in this column may be a sum of two or more of these options.  For example a valeu of 2066 means that the index is clustered, unique and that it’s used to enforce the Primary key constraint.
  • used: If the indid value is 0 or 1, then used is the number of total pages used for all index and table data. If indid is 255, used is the number of pages for text or image data. In all other cases, used is the number of pages in the index.

Other popular SQL Posts :

How to insert a file in an image column in SQL Server 2005

How to get the total number of rows in a database

How to remove leading zeros within an SQL Query

Friday, August 22, 2008 11:40:31 AM (Eastern Standard Time, UTC-05:00)  #    Comments [0] -

About the author/Disclaimer

The opinions expressed herein are my own personal opinions and do not represent my employer's view in any way.

© Copyright 2019
Stanislas Biron
Sign In
Total Posts: 135
This Year: 0
This Month: 0
This Week: 0
Comments: 1
All Content © 2019, Stanislas Biron