To see the memory used by each database, count its pages in the buffer pool with sys.dm_os_buffer_descriptors. This post builds a small demo database, shows buffer and dirty page sizes before and after a checkpoint, explains the row with no name, and covers what the buffer pool numbers leave out, such as the plan cache.
SQL SERVER – How to Know Transaction Isolation Level for Each Session?
An outsourced app had teams using different coding standards, and performance suffered. Here is how to find the transaction isolation level of each session.
SQL SERVER – AlwaysOn Automatic Seeding Failure – Failure_code 15 and Failure_message: VDI Client Failed
One database would not seed to the secondary replica. Here is how sys.dm_hadr_physical_seeding_stats showed failure_code 15 and VDI Client Failed.
Collecting Server Hardware Facts Before a Tuning Session
Capture server hardware facts from SQL Server views, keep an inventory, and distinguish visible resources from workload sizing evidence.
How to Get VLF Count and Size in SQL Server? – Interview Question of the Week #161
How do you get VLF Count and size in SQL Server? Newer versions add a Dynamic Management Function for it. Here is the query, plus links to my older methods.
How to Drop All the User Created Statistics by SQL Server? – Interview Question of the Week #158
SQL Server creates statistics on its own, but DBAs and the Tuning Advisor add more. Here is a script to drop all user Created Statistics safely.
SQL SERVER – Simple Script to Match Session ID to OS Thread ID
How is a SQL Server Session ID mapped to an OS Thread ID? Here is a simple script to match Session ID to OS Thread ID, with the scheduler and task state.