Useful new DMV’s in SQL Server 2012 and SQL Server 2008 R2 SP1

There are couple of new DMV’s which came to my attention today and I would like to share them with you all.

sys.dm_os_windows_info: This DMV will get you OS related information of the Server.

Sample result is –

windows_release	  windows_service_pack_level	windows_sku	os_language_version
5.1	          Service Pack 3	        NULL	        1033

sys.dm_server_registry: This DMV will get you information on registry keys which are used by the server.

Sample result is(I have trimmed the data) –

registry_key	                                        value_name	value_data
HKLM\SYSTEM\CurrentControlSet\Services\MSSQL$DASANUP	ObjectName	LocalSystem
HKLM\SYSTEM\CurrentControlSet\Services\MSSQL$DASANUP	ImagePath	"C:\Program Files\Microsoft SQL Server\MSSQL10_50.DASANUP\MSSQL\Binn\sqlservr.exe" -sDASANUP

sys.dm_server_memory_dumps : This DMV will get you information on dumps which are generated by the server.

Sample result  is –

filename	                                                                        creation_time	                        size_in_bytes
C:\Program Files\Microsoft SQL Server\MSSQL10_50.DASANUP\MSSQL\LOG\SQLDump0001.mdmp	2011-08-29 14:39:31.2129902 -07:00	6015847
C:\Program Files\Microsoft SQL Server\MSSQL10_50.DASANUP\MSSQL\LOG\SQLDump0002.mdmp	2011-08-29 14:44:06.7232483 -07:00	6526667
C:\Program Files\Microsoft SQL Server\MSSQL10_50.DASANUP\MSSQL\LOG\SQLDump0003.mdmp	2011-08-29 14:47:11.7133910 -07:00	6429912

sys.dm_server_services: This DMV will get you information on services and startup’s.

Sample result is –

servicename	         startup_type	 startup_type_desc status status_desc	process_id  last_startup_time	                service_account	filename	                                                                                is_clustered	cluster_nodename
SQL Server (DASANUP)	        3	 Manual	             4	  Running	 2252	    2011-10-27 15:53:26.3520221 -07:00	LocalSystem	"C:\Program Files\Microsoft SQL Server\MSSQL10_50.DASANUP\MSSQL\Binn\sqlservr.exe" -sDASANUP	N	        NULL
SQL Server Agent (DASANUP)	2	 Automatic           4	  Running	 4308	    2011-10-27 15:54:01.0705499 -07:00	LocalSystem	"C:\Program Files\Microsoft SQL Server\MSSQL10_50.DASANUP\MSSQL\Binn\SQLAGENT.EXE" -i DASANUP	N	        NULL




Leave a Reply

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

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

Facebook photo

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

Connecting to %s