Discovering more SQL Server information using the built-in dynamic management view (DMVs)

Introduction

This is the second article in a continuing series on the many system tables, views, procedures and functions available in SQL Server. In the first part of this series, Discovering SQL server instance information using system views, you learned how to discover many attributes of a SQL Server instance you have been given access to. In this part, we will continue the journey and see what else we can find.

Who else is here?

You have access to at least one instance of SQL Server on one host. Are there any others? If so, can you connect to them and find out about them too? Let’s find out!

SQL Server stores instance information in the Windows registry under the key:

HKLM\Software\Microsoft\Microsoft SQL Server\Instance Names\SQL

Where “HKLM” is an abbreviation for the Registry section called “HKEY_LOCAL_MACHINE”. If you can access the registry on the server running the instance you are connected to, it might look like this in regedit:

This is from my laptop, which shows that there are two instances of SQL Server defined. However, maybe you cannot login to the host running SQL Server. Still, there may be a way for you to get the list of instances.

In the previous article, I mentioned the system stored procedure xp_cmdshell and the controversy surrounding it. If you are convinced, as I am, that the controversy is really a tempest in a teapot, we can use the command line version of regedit to get the same information. First, enable xp_cmdshell:

USE master;

EXEC sp_configure 'show_advanced_options', '1';

RECONFIGURE;

EXEC sp_configure 'xp_cmdshell', '1';

RECONFIGURE;

Now, use the command line to get the information:

EXEC xp_cmdshell 'reg query "HKLM\Software\Microsoft\Microsoft SQL

Server\Instance Names\SQL"';

When I run this on my laptop, I receive output that contains:

MSSQLSERVER REG_SZ MSSQL12.MSSQLSERVER

SQLEXPRESS2008R2 REG_SZ MSSQL10_50.SQLEXPRESS

It’s easy to see that the instance names are in the first column of data returned. If you are able to see other instances, go ahead and see if you can connect to them. You may have permission to do and you will have a new world to explore!

Now, if you’re still worried about xp_cmdshell, go ahead and turn it off, using the same script as above but setting the new value for xp_cmdshell to 0 instead of 1.

Where are my files?

We’re not done with the registry, though. There is actually a whole set of undocumented, extended stored procedures that can be used to discover details about a SQL Server instance. Note that, because they are undocumented, they may change at any service pack or cumulative update without notice. Do not build production scripts using them!

To discover how your SQL Server instance was set up, try this query:

EXECUTE master.sys.xp_instance_regenumvalues

'HKEY_LOCAL_MACHINE',

'Software\Microsoft\MSSQLSERVER\Setup';

On my laptop, this query produced 17 lines of output. Your mileage may vary. One item that I found interesting is this:

This is the default location for new data files created by SQL Server, say when a new database is created. Of course that can be overridden when you create a database, so what if you want to know where the databases are really located? The view sys.master_files holds the answer:

SELECT * FROM sys.master_files;

Yields, for me (partially):

Here, we can see where the files are really located for each database. The file_id can be used to get usage information about each file:

SELECT mf.name,mf.physical_name, mf.size, fs.size_on_disk_bytes

FROM sys.master_files mf

CROSS APPLY

(

SELECT * from sys.dm_io_virtual_file_stats(DEFAULT, DEFAULT) fs

WHERE fs.file_id = mf.file_id

) fs;

This uses the sys.dm_io_virtual_file_stats data management function. On my system, my results begin:

Which shows me the database name, the file path, the current size used in SQL Server pages (a page is 8 KB) and the current size on disk in bytes. Virtual file statistics also include the number of reads and writes against each file and the master file view also has columns showing the auto-grow parameters. See the references for more details.

The data_space_id column in sys.master_files identifies the filegroup. That means we can get a bit more information from the sys.filegroups view. On my laptop that’s not too exciting:

On a large system, with many filegroups (perhaps to support table partitioning), this would be a longer list. Note that the data_space_id column is there to join on, should you wish to.

Is anyone else connected?

Another interesting aspect of a SQL Server instance is connection possibilities. While you’re discovering things about an instance, it’s good to check out these as well. Try this query on any instance of SQL Server:

SELECT * FROM sys.dm_exec_connections;

If I do this on my laptop, it’s a little boring:

Though on a busier system things are more interesting:

(For this screenshot, I connected to an AdventureWorks database hosted on Azure. You can see differing connection protocols (shared memory for my laptop, TCP for Azure), authentication, encryption and so on.

Even more interesting is to combine this with active session information like this:

SELECT c.session_id,

c.protocol_type,

c.auth_scheme,

c.client_net_address,

s.host_name,

s.login_name,

s.login_time

FROM sys.dm_exec_connections c

JOIN sys.dm_exec_sessions s

ON c.session_id = s.session_id;

On the AdventureWorks connection, that gives me:

If you want to find out what those clients are doing, you could use sp_who2. That gives me:

(There are more columns to the right.) If I may plug a fellow MVP, though, I’d recommend Adam Machanic’s excellent sp_whoisactive, which can give you much more information. Find a link to it in the “See Also” section below.

What are the connection possibilities?

Using some of the queries above, you’ve found out who else is currently connected to the same instance as you. Maybe you’d like to know how many ways connections can be made? The place to start is with sys.endpoints. On my laptop:

SELECT * FROM sys.endpoints;

Yields:

You may see other endpoints, especially on an HADR (High Availability, Disaster Recovery) systems. Here though, I see 5 methods I can use to connect to SQL Server, TCP, shared memory, named pipes and VIA (Virtual Interface Adapter). There are actually two TCP endpoints, a normal one and one for the Dedicated Admin connection. If you want to know the port numbers for the TCP connections, you can find them here:

SELECT * FROM sys.dm_tcp_listener_states;

Which gives me:

On my local system. There are also a number of specialized endpoint views:

sys.service_broker_endpoints and sys.conversation_endpoints, for Service Broker
sys.http_endpoints for endpoints using the HTTP protocol.
sys.soap_endpoints and sys.endpoint_webmethods for soap connections, but note that those these views are deprecated and should not be used for new work.
sys.database_mirroring_endpoints for – you guessed it! – database mirroring (and also for Availability Groups).
sys.via_endpoints for VIA endpoints. Note that the VIA protocol is deprecated and should not be used for new work.

Summary

In this article, we’ve continued our exploration of SQL Server using the built-in dynamic management views. If you were just starting out at a new company, using the techniques in this article and its predecessor would give you a well-rounded understanding of your new environment, and we haven’t even started to look at the databases yet!

SQLShack

Discovering more SQL Server information using the built-in dynamic management views (DMVs)

Introduction

Who else is here?

Where are my files?

Is anyone else connected?

What are the connection possibilities?

Summary

Introduction

Who else is here?

Where are my files?

Is anyone else connected?

What are the connection possibilities?

Summary

Related posts: