SQL Server 2014 Health check DMV queries Day 7

RSS

SQL Server 2014 Health check DMV queries Day 7

03 May

Hi guys in the Previous post i explained three DMV related to SQL Server cluster and AlwaysOn AG, Get information about your OS cluster , Cluster Node Properties and Failover Cluster Node with AlwaysOn AG put today in DAY 7 i will show new DMV in our track Instance Level configuration .

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

View all my tips , LinkedIn , Slideshare ,Youtube Channel.

SQL Server Consultation