Cached data per object tells you which tables and indexes fill the buffer pool. This post builds two demo tables, empties their cache and reads part of the data. A query then lists the pages, megabytes and share of each index in memory. It also explains why the view is costly on a big server.
Disable Adaptive Join for One Query or a Whole Database
To disable adaptive join for one query, add a USE HINT to it. This post also shows a Query Store hint for code you cannot edit and the database setting that switches the feature off for everything. Each switch is tested on a small demo database and checked in the plan.
Job Steps That Fail While the Agent Job Reports Success
A green job history does not mean every step worked. Decode the failure routing and pair failed steps with their outcomes.
Enable Adaptive Join in SQL Server: Why It Does Not Appear
To enable adaptive join in SQL Server, the compatibility level, the database setting and the query must all agree. This post builds a demo database and probes the plan under each condition. It also corrects older advice whose statements switch the feature off.
Adaptive Threshold Rows: How an Adaptive Join Chooses
Adaptive threshold rows is the row count at which an adaptive join switches from nested loops to a hash join. This post runs two queries on WideWorldImporters, reads each threshold from the cached plan, and shows which branch ran. The first query stays below its threshold and the second goes above it.
Find Expensive Queries – SQL in Sixty Seconds #159
Earlier I wrote a blog post about SQL SERVER – List Expensive Queries – Updated March 2021. Let us learn how to Find Expensive Queries.
CHECKPOINT Covers One Database: Flush Data to Disk
CHECKPOINT covers one database, so to flush data to disk you run it in each database. A demo with two databases shows one flushed and one still dirty. A short script then flushes every database in one pass, and the post says when a flush is worth it.