Implicit transactions make SQL Server open a transaction on the first data statement and keep it open until a COMMIT or ROLLBACK. This post shows what starts one, how @@TRANCOUNT counts it, why a second session waits, and how to find and clear the sessions that left work open.
PSPO Plan Variants: One Query, Several Plans in SQL Server
PSPO plan variants let SQL Server cache up to three plans for one parameterized query when the data is very skewed. This post builds a skewed table, shows two variants with different reads for three calls, compares the same calls with the feature off, and explains when PSPO does not apply.
Finding Queries That Spill With total_spills in Query Stats
Rank total_spills and recent counters, inspect plans, compare Query Store tempdb use, and investigate estimates before increasing grants.
Live Plans for Running Queries With sys.dm_exec_query_statistics_xml
Read sys.dm_exec_query_statistics_xml for live plans, compare profile counts with estimates, and interpret partial progress carefully.
Find Database File Latency – SQL in Sixty Seconds #198
Lots of people asked me to create a video on my earlier blog post SQL SERVER – Script to List Database File Latency. Here is the video.
Database File Latency: Read and Write Stalls per IO
Database file latency is the stall time per read or write. This post divides the IO stall columns by the call counts for every file, explains the zero queued columns and says when a number is worth acting on.
Reads and Writes per File: Rank Files by Bytes Moved
Reads and writes per file show which data or log file SQL Server uses the most. This post ranks the files of a demo database by bytes moved with sys.dm_io_virtual_file_stats and explains what the tally can and cannot tell you.