Мониторинг SQL server

Статья про мониторинг использования памяти, метрики буферного кэша, DROPCLEANBUFFERS, средняя продолжительность жизни страниц памяти

Insight into the SQL Server buffer cache

SQL Server: The Important Performance Counters (dm_os_performance_counters)

SQL Server In-Memory database internal memory structure monitoring

Полезные скрипты для оценки занимаемой памяти

IF (     SELECT COUNT(1)     FROM sys.data_spaces     WHERE type = 'FX' ) > 0     BEGIN         SELECT OBJECT_NAME(object_id) AS tblName,                CAST(memory_used_by_table_kb / 1024.00 AS DECIMAL(10, 2)) [Total used Memory MB],                CAST(memory_allocated_for_table_kb / 1024.00 AS DECIMAL(10, 2)) - CAST(memory_used_by_table_kb / 1024.00 AS DECIMAL(10, 2)) [Total Unused Memory MB],                CAST(memory_used_by_indexes_kb / 1024.00 AS DECIMAL(10, 2)) [Index used Memory MB],                CAST(memory_allocated_for_indexes_kb / 1024.00 AS DECIMAL(10, 2)) - CAST(memory_used_by_indexes_kb / 1024.00 AS DECIMAL(10, 2)) [Index Unused Memory MB]         FROM sys.dm_db_xtp_table_memory_stats;     END;

Место, занимаемое таблицами

select
     s.Name                                        as SchemaName
     ,t.NAME                                       as TableName
     ,p.rows                                       as RowCounts
     ,SUM(a.total_pages) * 8                       as TotalSpaceKB
     ,SUM(a.used_pages) * 8                        as UsedSpaceKB
     ,(SUM(a.total_pages) - SUM(a.used_pages)) * 8 as UnusedSpaceKB
 from
     sys.tables t
     inner join sys.schemas s on
         s.schema_id = t.schema_id
     inner join sys.indexes i on
         t.OBJECT_ID = i.object_id
     inner join sys.partitions p on
         i.object_id = p.OBJECT_ID and i.index_id = p.index_id
     inner join sys.allocation_units a on
         p.partition_id = a.container_id
 where
     t.NAME not like 'dt%' -- filter out system tables for diagramming
     and t.is_ms_shipped = 0
     and i.OBJECT_ID > 255
 group by
     t.Name
     ,s.Name
     ,p.Rows
 order by
     s.Name
     ,t.Name

Место, занимаемое индексами

select distinct
     OBJECT_NAME(i.OBJECT_ID) as TableName
     ,i.name                   as IndexName
     ,i.index_id               as IndexID
     ,8 * SUM(a.used_pages)    as 'Indexsize(KB)'
 from
     sys.indexes as i
     join sys.partitions as p on
         p.OBJECT_ID = i.OBJECT_ID and p.index_id = i.index_id
     join sys.allocation_units as a on
         a.container_id = p.partition_id
 where
     i.name is not null
 group by
     i.OBJECT_ID
     ,i.index_id
     ,i.name
 having
 ( 
     sum(a.used_pages) != 0
 )
 order by
     OBJECT_NAME(i.OBJECT_ID)
     ,i.index_id