Statistics updated automatically need two things: enough changed rows, and a query that uses the statistic. This post explains the old 500 plus 20 percent rule and the newer square root threshold, then shows a million-row demo where last_updated changes only after a query compiles. It also covers FULLSCAN, asynchronous updates and when to update by hand.
SQL SERVER – Configure Management Data Collection in Quick Steps – T-SQL Tuesday #005
A visual walk through to configure Management Data Collection in a few quick steps and get default reports on CPU, memory and disk for your server.
Correlated Columns and Multi-Column Statistics in SQL Server
Investigate multi-column statistics with correlated city and state values, density vectors, and plans before expecting better estimates.
What Is Locking and Blocking in SQL Server?
Locking and blocking are not the same thing, and the difference is the whole subject. I blocked a session on purpose and read it from the DMVs. Why short blocking is healthy, and why NOLOCK fixes nothing.
SQL SERVER – Introduction to Extended Events – Finding Long Running Queries
After reading Jonathan Kehayias on Extended Events, I added them to my performance tuning course. An introduction, with a way to find long running queries.
Composite Index Without the Leading Key: Seek, Scan and Residual Filters
A composite index orders rows by its leading key before the next key. Compare a date-only filter, region restrictions and an isolated date-first index. Verify complete results, including new regions and NULL, before interpreting actual plans.
SQL SERVER – Generate Report for Index Physical Statistics – SSMS
SSMS has a standard report for Index Physical Statistics at the database level. Right click the database, open Reports, and see index details at a glance.