Test index key order with equality, range, and OR predicates, then use seek predicates and logical reads to choose the lead column.
Does Sort Order in Index Column Matters for Performance? – Interview Question of the Week #199
Does the sort order in an index column matter for performance? Yes, in some specific scenarios, and here is an example from a recent Health Check question.
What is Read Ahead Read in SQL Server? – Interview Question of the Week #197
Question: What is Read Ahead Read in SQL Server?
Answer: This is SQL Server’s feature which brings data pages into the buffer cache.
Do Index Reorganization Update Statistics? – Interview Question of the Week #196
Question: Do Index Reorganization Update Statistics?
Answer: No. Index Reorganization does not update Statistics of your database.
First Queries to Run on an Unfamiliar SQL Server
Inspect an unfamiliar SQL Server with read-only queries for version, uptime, database size, waits, backup history, and settings.
Checking Optional Features Installed on an Instance From T-SQL
A 1 in SERVERPROPERTY does not mean the feature runs. Here is how I line up the flag, the setting and the service in one query.
Asynchronous Statistics Updates: When They Help and When They Hurt
Evaluate asynchronous statistics updates against compile latency, data-change timing, and low-priority metadata locking in SQL Server.