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#17

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.

 
Leave a comment

Posted by on May 3, 2015 in SQL Server 2014

 

Tags: , , , , , ,

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s