The Log Space Usage DMV, sys.dm_db_log_space_usage, returns one row that shows how big the transaction log is, how full it is, and how much log has built up since the last log backup. This post builds a demo, reads each column, and uses the value to decide when a log backup is worth taking. It also covers open transactions and the simple recovery model.
How to List Queries With Memory Grant and Execution Plan? – Interview Question of the Week #154
A workshop customer suspected one of their queries used too much memory. Here is the script I gave them to list running queries with Memory Grant and plan.
How to List All ColumnStore Indexes with Table Name in SQL Server? – Interview Question of the Week #153
Question: How to List All ColumnStore Indexes with Table Name in SQL Server? As ColumnStore Indexes are getting more and more popular.
SQL SERVER – List Users with System Admin (sysadmin) Rights – Part 2
After my post on listing users with System Admin (sysadmin) rights, Chris Mangrum shared a better script that also covers CONTROL SERVER. Here it is.
What are Forwarded Records in SQL Server? – Interview Question of the Week #145
What are forwarded records in SQL Server? It is an old concept, yet few new DBAs know it. Here is a short answer about heap rows that moved to a new page.
Stored Procedure Execution Count and Average Elapsed Time
To find a stored procedure execution count, query sys.dm_exec_procedure_stats and divide the total elapsed time by the count. This post builds a demo, shows the query, and explains why the numbers start again when a plan leaves the cache. It also fixes the object name lookup that returns NULL for other databases.
Querying a Histogram With sys.dm_db_stats_histogram Instead of DBCC
Query sys.dm_db_stats_histogram to inspect statistics steps, compare stored estimates with data, and identify values beyond the last step.