Discovering more SQL Server information using the built in dynamic management view DMVs
Discovering more SQL Server information using the built-in dynamic management view (DMVs)
Gerald specializes in solving SQL Server query performance problems especially as they relate to Business Intelligence solutions. He is also a co-author of the eBook "Getting Started With Python" and an avid Python developer, Teacher, and Pluralsight author.
You can find him on LinkedIn, on Twitter at twitter.com/GeraldBritton or @GeraldBritton, and on Pluralsight
View all posts by Gerald Britton Latest posts by Gerald Britton (see all) Snapshot Isolation in SQL Server - August 5, 2019 Shrinking your database using DBCC SHRINKFILE - August 16, 2018 Partial stored procedures in SQL Server - June 8, 2018
SQLShack
SQL Server training EspañolDiscovering more SQL Server information using the built-in dynamic management views DMVs
April 6, 2017 by Gerald BrittonIntroduction
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: 1234567891011 USE master; GO EXEC sp_configure 'show_advanced_options', '1'; RECONFIGURE;GO EXEC sp_configure 'xp_cmdshell', '1'; RECONFIGURE;GO Now, use the command line to get the information: 1234 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: 1234 MSSQLSERVER REG_SZ MSSQL12.MSSQLSERVERSQLEXPRESS2008R2 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: 12345 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: 123 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: 123456789 SELECT mf.name,mf.physical_name, mf.size, fs.size_on_disk_bytes FROM sys.master_files mfCROSS 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: 123 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: 123456789101112 SELECT c.session_id, c.protocol_type, c.auth_scheme, c.client_net_address, s.host_name, s.login_name, s.login_timeFROM sys.dm_exec_connections cJOIN 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: 123 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: 123 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! Other articles in this series: Discovering SQL server instance information using system views Discovering database specific information using built-in functions and dynamic management views (DMVs) How to track SQL Server database space usage with built-in functions and DMVs Author Recent Posts Gerald BrittonGerald Britton is a Senior SQL Server Solution Designer, Author, Software Developer, Teacher and a Microsoft Data Platform MVP. He has many years of experience in the IT industry in various roles.Gerald specializes in solving SQL Server query performance problems especially as they relate to Business Intelligence solutions. He is also a co-author of the eBook "Getting Started With Python" and an avid Python developer, Teacher, and Pluralsight author.
You can find him on LinkedIn, on Twitter at twitter.com/GeraldBritton or @GeraldBritton, and on Pluralsight
View all posts by Gerald Britton Latest posts by Gerald Britton (see all) Snapshot Isolation in SQL Server - August 5, 2019 Shrinking your database using DBCC SHRINKFILE - August 16, 2018 Partial stored procedures in SQL Server - June 8, 2018