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.
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.
SQL SERVER – Difference Between ROLLBACK IMMEDIATE and WITH NO_WAIT during ALTER DATABASE
We are going to discuss something very simple topic. Difference Between ROLLBACK IMMEDIATE and WITH NO_WAIT during ALTER DATABASE.
SQL SERVER – Checklist for Analyzing Slow-Running Queries
Before you call support, go through this Books Online checklist for analyzing slow-running queries: network, memory, statistics, indexes and more.
SQL SERVER – Force Index Scan on Table – Use No Index to Retrieve the Data – Query Hint
How do you force index scan instead of a seek, or read a table without using any index? Two reader questions with a similar answer, using a query hint.
SQL SERVER – Improve Performance by Reducing IO – Creating Covered Index
My T-SQL Tuesday #004 post on how a covered index can improve performance by reducing IO. Run the demo on a test server only, never on production.