RSS

Category Archives: SQL Server 2014

SQL Server 2014 Health check DMV queries Day 7

 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

Read the rest of this entry »

 
Leave a comment

Posted by on May 3, 2015 in SQL Server 2014

 

Tags: , , , , , ,

SQL Server 2014 Health check DMV queries Day 6

Hi guys in the Previous post i explained How we can Getting the Server Model Number ,Getting the Processor Description  and get SQL Server Error Log Properties put today in DAY 6 i will show new DMV in our track Instance Level configuration .

in this post i will explain three new DMV related to SQL Server Cluster and AlwaysOn AG You will see no results if your instance is not using Clustering or AlwaysOn AG.

DMV#14 Get information about your OS cluster

If your SQL server is cluster not standalone you can use this DMV to get very useful information about the OS (Operation System)

SELECT VerboseLogging, SqlDumperDumpFlags, SqlDumperDumpPath,
 SqlDumperDumpTimeOut, FailureConditionLevel, HealthCheckTimeout
FROM sys.dm_os_cluster_properties WITH (NOLOCK) OPTION (RECOMPILE);

DMV#15 Cluster Node Properties

by this DMV we can return the node name and the current status of it so know you can now easily SQL Server cluster running in which node .

SELECT NodeName, status_description, is_current_owner
FROM sys.dm_os_cluster_nodes WITH (NOLOCK) OPTION (RECOMPILE);

DMV#16 Failover Cluster Node with AlwaysOn AG

by this DMV you can return the Cluster name , quorum_type_desc  and  quorum_state_desc for SQL Server AlwaysOn

SELECT cluster_name, quorum_type_desc, quorum_state_desc
FROM sys.dm_hadr_cluster WITH (NOLOCK) 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.

 
1 Comment

Posted by on May 2, 2015 in SQL Server 2014

 

Tags: , , , ,

SQL Server 2014 Health check DMV queries Day 5

Hi guys in the Previous post i explained How we can get the SQL Server NUME Information and How to get Hardware Information  put today in DAY 5 i will show new DMV in our track Instance Level configuration .

DMV#11 Getting the Server Model Number

by the below DMV we can know the capacities of database System Manufacturer

EXEC xp_readerrorlog 0, 1, "Manufacturer";

DMV#11

DMV12# Getting the Processor Description

You should know the type of the Processor  of your Windows Server that the SQL Server instance Hosted on it based on that check the DMV

Read the rest of this entry »

 
1 Comment

Posted by on April 28, 2015 in SQL Server 2014

 

Tags: , ,

SQL Server 2014 Health check DMV queries Day 4

Hi guys in the Previous post i explained How we can get the Global Trace Flag Information ,  Getting Information About Windows and How to return SQL Server Services Information  put today in DAY 4 i will show new DMV in our track Instance Level configuration 

DMV#9 SQL Server NUMA Info

by the below DMV we can return more helpful information related to SQL Server NUMA info and it will return also the Node status , and load balance .


SELECT node_id, node_state_desc, memory_node_id, processor_group, online_scheduler_count,
 active_worker_count, avg_load_balance, resource_monitor_state
FROM sys.dm_os_nodes WITH (NOLOCK)
WHERE node_state_desc <> N'ONLINE DAC' OPTION (RECOMPILE);


DMV#9

DMV#10 Getting Hardware Information

very fantastic DMV and very helpful by the below T-SQL we can return lot of information about Hardware like Logical CPU count , Physical Memory , Physical CPU count , SQL Start time …ETC

virtual_machine_type_desc of HYPERVISOR does not automatically mean you are running SQL Server inside of a VM
It merely indicates that you have a hypervisor running on your host

SELECT cpu_count AS [Logical CPU Count], scheduler_count, hyperthread_ratio AS [Hyperthread Ratio],
cpu_count/hyperthread_ratio AS [Physical CPU Count],
physical_memory_kb/1024 AS [Physical Memory (MB)], committed_kb/1024 AS [Committed Memory (MB)],
committed_target_kb/1024 AS [Committed Target Memory (MB)],
max_workers_count AS [Max Workers Count], affinity_type_desc AS [Affinity Type],
sqlserver_start_time AS [SQL Server Start Time], virtual_machine_type_desc AS [Virtual Machine Type]
FROM sys.dm_os_sys_info WITH (NOLOCK) OPTION (RECOMPILE);

DMV#10

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 Website Slideshare ,Youtube Channel.

Follow up us on

Facebook Page Link

 
1 Comment

Posted by on April 26, 2015 in SQL Server 2014

 

Tags: , , ,

SQL Server 2014 Health check DMV queries Day 3

Hi guys in the Previous post i explained How we can get the Server Properties , returning SQL Server Agent Job Information put today and How we can return SQL Server Agent Alert Information put today in DAY 3 i will show new DMV in our track Instance Level configuration

DMV#6 Global Trace Flag Information
Very useful to know what global trace flags are currently enabled as part of the diagnostic process if not trace flag enable it will not return any thing for more information about trace flag look Here

DBCC TRACESTATUS (-1);

DMV#7 Getting Information About Windows

by the below DMV we can return the major OS version, Service Pack, Edition, and language info for the operating system

SELECT windows_release, windows_service_pack_level,
windows_sku, os_language_version
FROM sys.dm_os_windows_info WITH (NOLOCK) OPTION (RECOMPILE);

DMV#8SQL Server Services Information

by the below DMV we can return very important information like this instance is clustered or No , processid , when they were last started, and their current status , the start up type of the SQL server services and SQL server agent services .

SELECT servicename, process_id, startup_type_desc, status_desc,
last_startup_time, service_account, is_clustered, cluster_nodename, [filename]
FROM sys.dm_server_services WITH (NOLOCK) OPTION (RECOMPILE);

SQL Server Services Information

SQL Server Services Information

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 Website Slideshare ,Youtube Channel

 
1 Comment

Posted by on April 25, 2015 in SQL Server 2014

 

Tags: , ,

SQL Server 2014 Health check DMV queries Day 2

images (1)

Hi guys in the Previous post i explained How we can get the SQL Server instance version and How we can know the installed date for SQL Server instance put today is DAY 2 and i will show new DMV in our track Instance Level configuration Queries

DMV#3 Server Properties

below DMV is more helpful and useful this DMV This gives you a lot of useful information about your instance of SQL Server like (Computer name , ProcessID , SQL Server collation  , Product level…etc)

DMV#4 SQL Server Agent Job Information

below DMV it will return for us basic information about SQl Server agent and the user name configure it to review it one by one.

DMV#5 SQL Server Agent Alert Information 

by the below DMV we will return the basic information about SQL Server agent alerts

Check the Full posts and the scripts : http://sqlserver-performance-tuning.net/?p=5924

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 Website Slideshare ,Youtube Channel.

 
1 Comment

Posted by on April 23, 2015 in SQL Server 2014

 

Tags: , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,

SQL Server 2014 Health check DMV queries Day 1

Hello Guys Follow Me from today and over the next 30 days i will share with in this 30 days 70 DMV each day we will have at least 2 new DMV this DMV Library used for healthy check in SQL Server 2014 and it covered 3 tracks (Instance configuration , instance Performance , Database Performance) Follow Us and here is the day number one : (SQL Server 2014 Health check DMV queries Day 1)

http://sqlserver-performance-tuning.net/?p=5901

Health check DMV queries

Health check DMV queries

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 Website Slideshare ,Youtube Channel.

 
Leave a comment

Posted by on April 22, 2015 in SQL Server 2014

 

Tags: , ,