Wednesday, October 13, 2010

SQL Server–Performance when using indexes

Just some rules of thumb: (left is better than right)

Seek < Scan

Index < Table

Clustered Index < Index (non-clustered)

Clustered Index Seek < Index Seek < Index Scan < Clustered Index Scan < Table Scan

  1. CLUSTERED INDEX SEEK - Very fast and efficient - the table is physically ordered according to specified column(s) and SQL can go find it AND since all of the data is phsically ordered right, can pull the data sequentially
  2. INDEX SEEK - fast and efficient.  The SQL server knows pretty much where the data is and can go directly to it and seek out the rows it needs.  The data isn't ordered in the DB by the fields in the index so it's likely not pulling the data sequentially like it is in the CLUSTERED INDEX SEEK, but it can still translate to a massive improvement in query execution cost/speed.
  3. INDEX SCAN - still uses the index, but it doesn't know exactly where the data is, so it may scan the whole index or a partial range of the index to find it's data. Can be very slow and costly, but still scans through the index as opposed to scanning the physical table.
  4. CLUSTERED INDEX SCAN - If you see a CLUSTERED INDEX SCAN, it's pretty much the same as a TABLE SCAN for performance because SQL has to physically search through every single row in the clustered index (which really IS the table). 
  5. TABLE SCAN - slow, inefficient.  SQL has to physically look at every single row in the table. Shouldn't see this often, if you do, you may need to rewrite something or add indexes.

No comments: