Read index seek and index scan plans accurately, test equality and ranges, and judge lookups and rows read instead of fixed percentages.
SQL SERVER – Mistake to Avoid: Duplicate and Overlapping Indexes – Notes from the Field #073
Indexes do not always improve performance. Kathi Kellenberger explains why duplicate and overlapping indexes are a mistake to avoid.
SQL SERVER – COLUMNSTORE and COLUMNSTORE_ARCHIVE data compression
SQL Server 2014 adds COLUMNSTORE_ARCHIVE data compression for rarely used data. Here is how to use it on older data, at the cost of slower queries.
Hash Index Bucket Count for Memory-Optimized Tables
Size hash index bucket count from distinct keys, inspect empty buckets and chains, rebuild a test index, and choose range indexes wisely.
SQL SERVER – What is Hypothetical Indexes?
Why does sys.indexes show 7 indexes when Object Explorer shows only one? The answer is hypothetical indexes. Here is what they are.
Finding Hot Tables With sys.dm_db_index_usage_stats
Identify hot tables with index usage, lock and latch counters, table sizes, and retained snapshots that describe a real workload interval.
REGEXP_LIKE Performance: Why Pattern Filters Cannot Seek
A short pattern can run a long CPU bill. Here is why REGEXP_LIKE scans, and how a plain prefix filter gives the index something to seek.