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.
In-Memory OLTP in SQL Server: Memory-Optimized Tables Explained
In-Memory OLTP stores selected tables in memory and uses row versions instead of locks. This post builds a small demo database, compares a disk table with two memory-optimized tables, and tests a natively compiled procedure. It also covers durability, bucket counts and memory use.
Play by Play with Pinal Dave: Tuning SQL Server Performance
Play by Play with Pinal Dave was my birthday gift on July 30, 2014: a Pluralsight course that shows how I tune SQL Server performance on a real problem. This post tells why I made it and what each of the five modules covers.