DMV#17 Instance Configuration Properties
now we can know easily all the instance configuration properties like (backup compression, Ad Hoc Distributed Queries,affinity I/O mask,max server memory (MB) and max degree of parallelism…etc
SELECT name, value, value_in_use, minimum, maximum, [description], is_dynamic, is_advanced
FROM sys.configurations WITH (NOLOCK)
ORDER BY name OPTION (RECOMPILE);
DMV#18 Buffer Pool Extension Properties
As we know Buffer Pool is the place in system memory that holds data and index pages read from disk and by the below DMV we can return the properties for it
SELECT [path], state_description, current_size_in_kb,
CAST(current_size_in_kb/1048576.0 AS DECIMAL(10,2)) AS [Size (GB)]
FROM sys.dm_os_buffer_pool_extension_configuration WITH (NOLOCK) OPTION (RECOMPILE);
DMV#19 Buffer Pool Extension Usage
You will see no results if BPE is not enabled or if there is no BPE usage
SELECT DB_NAME(database_id) AS [Database Name], COUNT(page_id) AS [Page Count],
CAST(COUNT(*)/128.0 AS DECIMAL(10, 2)) AS [Buffer size(MB)],
AVG(read_microsec) AS [Avg Read Time (microseconds)]
FROM sys.dm_os_buffer_descriptors WITH (NOLOCK)
WHERE database_id <> 32767
AND is_in_bpool_extension = 1
GROUP BY DB_NAME(database_id)
ORDER BY [Buffer size(MB)] DESC OPTION (RECOMPILE);
To check all my posts you can direct to my personal website or you can download this Excel sheet My Technical POSTS
