DaveWentzel.com            All Things Data

Indexes

 

Useful DMV Queries :  are my indexes being used?  Index usage statistics, etc. 
 
Why Isn't My Index Being Used
It might not be selective enough...unique index values/number of rows = selectivity.  Density, found when running DBCC SHOW_STATISTICS, is the inverse to selectivity (ie, the lower the density, the more selective the index). 
 
My table is tiny, can't I just use a heap?
 
You should always index every table regardless of size.  Even the smallest tables will benefit.  Here's one reason...locking.  Locks taken will be at a lower granularity.  Indexes are also the only way to define uniqueness. 
 
The only allowable heaps should be tables used in bulk loading. 
 
 
 
Under what circumstances will a non-clustered index be rebuilt when various actions are taken against the base table data?
 
Case
SQL 2000 non-clustereds rebuit?
SQL 2005 non-clustereds rebuilt?
Why?
Heap to Clustered Index
YES
YES
heaps use physical RIDs, clustered indexes use a logical RID since ordering is implied in the index definition
Clustered Index to Heap
YES
YES
this is the opposite of the above case...the logical RID is replaced by the physical RID
Rebuilding a Unique Clustered Index NO NO the cluster key (the logical RID) will not change, hence no rebuild of the non-clustereds is necessary
Rebuilding a non-unique clustered index YES NO The logical RID must be unique.  To guarantee this a psuedo column called a uniquifier is added to the key.  In 2000 when the clustered is rebuilt the uniquifier is regenerated.  Not so on 2005. 
Change to the clustered index schema YES...if the change will change the logical RID NO In 2005...even if you are changing the partitioning scheme or moving filegroups the logical RID will NOT change, hence the non-clustereds will not be rebuilt. 
 
 
do I create clustered indexes before or after a bulk load?
Almost always after, but you need to test.  If the data coming in is ordered to the clustered index then the index before the load is faster.  Otherwise, add the clustered index after the load. 
 
 
regarding SARGs like LEFT(MyField,5) will they always scan? Is there no way to exploit indices?
If you have ANY computation on the column then SQL Server will absolutely force a scan.  This is b/c the engine must compute the value for every possible row. 
 
I took the specific case listed about LEFT(LastName, 5) = 'Something' and compared that to column like 'Something%' and the performance difference was staggering!
 
SELECT * FROM Member

WHERE LEFT(LastName,5) = 'Wentz'

-- Causes a TABLE SCAN

-- Performs 143 I/Os (small table, luckily J)

-- Showplan estimate is 92.71% overall cost between these two queries

SELECT * FROM Member

WHERE LastName LIKE 'Wentz%'

-- Uses an Index

-- Performs 9 I/Os

-- Showplan estimate is 7.29% overall cost between these two queries
 
Simple rule - try re-writing SARGs and get users to understand some of these benefits!
 
 
 
AttachmentSize
Binary Data DBA_index_defrag_2000_prc.sql13.21 KB

Add new comment