Scheduler ID values in dm_os_schedulers run from 0 up to a few thousand, and then jump past one million. This post groups the rows into user, dedicated admin connection and hidden schedulers, and shows the filter that keeps CPU scripts honest. It also shows why filtering on the number alone is fragile.
Active and Inactive VLFs: List Them for Every Database
To list active and inactive VLFs for every database, count the rows of sys.dm_db_log_info by vlf_active and read the log reuse wait next to them. This post builds a demo log with 24 VLFs, shows how an open transaction and small autogrowth steps add more, and fixes the count with one shrink and one regrow.
DROP STATISTICS Script: Remove Auto-Created Statistics
A DROP STATISTICS script can remove every auto-created statistic in a database, and SQL Server builds the useful ones again on demand. This post previews the statements, runs them behind a switch and shows what happens to the next query. It also says when the cleanup helps.
How to Measure Transactions Per Seconds in SQL Server? – Interview Question of the Week #262
Question: How to Measure Transactions Per Seconds in SQL Server?
I have updated the script for this for Comprehensive Database Performance Health Check.
What are Different Methods to Know the Row Count of Table? – Interview Question of the Week #261
Question: What are Different Methods to Know the Row Count of Table? The answer to this question is very simple and many.
Lightweight Query Profiling: Live Progress Without the Old Overhead
Use lightweight query profiling to inspect live operator rows and in-flight plans, while avoiding misleading progress percentages.
Queries Waiting for Memory: Read the Grant Counters
Queries waiting for memory are counted by the Memory Grants Pending counter and listed by sys.dm_exec_query_memory_grants. A tested demo holds a sort grant of 41,848 KB with a blocked query and reads it from the view. The post explains how to spot a waiting request and what causes the wait.