IMPLEMENTING INDEXES
INTRODUCTION TO INDEXES
Indexes
- Indexes improve query performance by reducing I/O operations ( select, update, delete )
- Indexes are built on one or more columns in a table or view ( keys)
- Indexes are automatically created for primary key and unique constraints
Index Types
- Clustered index ( like phone book ) –binary tree
- Leaf nodes contains actual data pages
- Root, intermediate, look
- Data sorted and stored based on index key
- Non clustaered index binary tree
- Intermediate nodes contain pointers to actual data pages
- Data not sorted based on key
- Unique, included, full text, xml
Index Guidelines- query optimizer
Non-Clustered
- Queries that do not return large data sets
- Queries that return exact matching ( where )
- Covered querry
- Columns with high degree of uniqueness (1-1 relationship)
- Group by columns
Clustered
- Queries that return large data sets
- Qeures that return ranges
- Columns with a high degree of uniqueness
- Foreign key columns
- Group by and order by
Database
- Avoid on indexing heavily updated tasks
- Use many indexes on tables with little to no data modifications
- Avoid indexing small tables
Practical WORKING WITH INDEXES
- Primary key creates clustered index
- Tables à Indexes à right click and New index
- Fill factor work with leaf level node while pad index works with intermediate level node
- Covered index– cover all columns in index=add non-key column into index
MAINTAINING & OPTIMIZING INDEXES
1)
Index Property à fragmentation à if total fragmentation <30% re organize( ), if >30% rebuild ( drop and created )
2)Database Engine tuning advisor run and see recommendation
3) SQL Profiler
Display estimated query plan, select and execute
CLUSTERED INDEX , Non Cluster Idnex, unique Index ( just check box)
Tables –TableAà Indexà Right click and New
Optons: left all default except
Set fill factor specify how full sql server should make leave level of each index page when creating the index Pad Index specify space to leave open on each page in intermediate levels of the index Allow Online Indexing
—Creating covered indexes
Include include columns ( non covered key or non-key files)
Maintaining Index – rebuild or re organize
Tags: Indexes