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.
How to Determine Read Intensive and Write Intensive Tables in SQL Server? – Interview Question of the Week #251
Question: How to Determine Read Intensive and Write Intensive Tables in SQL Server?
Answer: The script is very simple and lets us see in the blog.
Missing Index Suggestions: Three Checks Before You Create One
Review missing index suggestions against existing indexes, key order, and INCLUDE size before adding extra write overhead.
Session Wait Stats: Find the Query Behind a Wait Type
Session wait stats tell you which session produced a wait type, and the input buffer tells you which query that session ran. This post shows how to see who waits right now, how to read the wait history of each session, and how to join them to the query text. It also explains the THREADPOOL wait.
Memory-Optimized Files: List Logical and Physical Names
To list memory-optimized files, join sys.dm_db_xtp_checkpoint_files to sys.database_files. This post builds a small memory-optimized database, shows the container’s logical and physical name and counts its checkpoint files by type and state. It also explains why a container holds far more disk space than its data uses.
Counting Locks per Session With sys.dm_tran_locks
Count locks per session by resource and mode, connect open transactions, and recognize escalation while keeping snapshot limits clear.
Listing Driver, Encryption and Auth Scheme for Every Connection
Inspect the auth scheme, encryption, driver interface, and protocol for current SQL Server connections, then group the results clearly.