Memory used by each table is the number of its pages in the buffer pool times 8 KB. This post adds them up with sys.dm_os_buffer_descriptors, shows why a plain division by 128 hides small tables, and compares the cached pages with the pages each table owns on disk.
Buffer Pool Pages for One Table in SQL Server
Buffer pool pages for one table show how much of one table sits in memory right now. This post lists them by index, page type and page level with sys.dm_os_buffer_descriptors, then shows which pages a seek, a scan and a large text read pull in. It uses the join that the documentation gives for each allocation unit type.
Memory-Optimized TempDB Tables: List Them and Check Status
Memory-optimized TempDB tables are the system tables that SQL Server 2019 and later can keep in memory. This post shows two queries: one that checks whether the feature is on, and one that lists the tables it covers. It also explains the limits that come with the feature.
Metadata Contention in TempDB: When Memory-Optimized Metadata Helps
Metadata contention in TempDB happens when many sessions create and drop temporary tables at the same moment. This post reads the waits that point to it, explains why RESOURCE_SEMAPHORE is a different problem, and shows how SQL Server 2019 and later can keep that metadata in memory.
What is the Difference Between sql_handle and plan_handle?- Interview Question of the Week #269
Question: What is the Difference Between sql_handle and plan_handle?
Answer: I was recently asked this question. Let us see the answer today.
Error 17138: Not Enough Memory to Start SQL OS Boot
Error 17138 stopped SQL Server from starting on a server with close to 2 TB of RAM after a client added trace flag 834 for large pages. The large page allocation failed during memory manager initialization. Check max server memory and restart the operating system.
Reasons for Slow Performance in SQL Server: The Top Five
The reasons for slow performance in SQL Server fall into five groups: schema design, T-SQL code, indexing, server deployment and hardware. This post tests three of them on a demo database, with logical reads as proof. It also shows a query that checks four deployment settings.