Discovering SQL server instance information using system views
Discovering SQL server instance information using system views
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 SQL server instance information using system views
February 28, 2017 by Gerald BrittonIntroduction
Out of the box, SQL Server comes with a substantial and – release by release – ever-growing set of system tables, views, stored procedures and functions. There’s a good chance you’ve never directly used more than a handful of them. That’s certainly the case with me! This is the first article in a series designed to explore this world that lives just below the surface of our everyday interactions with SQL Server through the same objects we create to enable the applications we write and support.Where in the world am I
Supposed you are starting your first day as a new DBA. You’ve been given a laptop to work with that has a current copy of SSMS installed on it. You’ve also been given a server name to connect to and told you have the necessary permissions to work with it. That’s it! So, you connect and begin looking at what you have. However, you’d like to know as much as possible. For example, what version of SQL Server is running? That one’s easy. You just run this query: 123 SELECT @@VERSION; You’re rewarded with the following output: Great! At least you now know something about the instance you’re connected to. You also have the service pack (SP1) and platform (X64) and you think it might be running on Windows Server 2012 R2, at least, according to Wikipedia! As it turns out, I ran that query on the laptop I’m using to write this article, and it is not running Windows Server. It’s actually running Windows 10! Can I coax that information out of SQL Server somehow? To find out, I’ll use the SQL Server Dynamic Management View 123 sys.dm_os_windows_info I simply run: 123 SELECT * FROM sys.dm_os_windows_info; and get this output: Well, OK, but how do I map the windows_release=6.3 and windows_sku=48 to human-readable information? The references section holds a link to an article that holds just such a mapping. The key is the SKU – the Stock Keeping Unit. Since the article shows the SKUs in hexadecimal, and I know that 48 = 0X30, I can scan down the table and see this row: So, now I know I’m running Windows 10 Pro. If I actually was running on Windows Server 2012 R2, I would have seen a SKU of 8.How did I get here
Now that I know I’m running SQL Server 2014 on Windows 10 Pro, the next thing I want to know is how the instance was started. Where is the executable that was launched? What options were used? Note that there are many arguments to sqlerver.exe that can affect how the database engine operates. The full list can be found in the references, but some notables are -s for the instance name -T for trace flags and -g for memory to reserve. To see these, you can use the DMV 123 sys.dm_server_registry Running this on my system: 123 SELECT * FROM sys.dm_server_registry WHERE registry_key LIKE '%contr%'; yielded (first two rows): So, I know that the instance name is MSSQLSERVER (the usual default name) and that there are no other arguments. Great! That’s a simple setup. Note that the first two columns are the registry key and value name. You can get the same information from the registry using those as locators, assuming you have the right permissions on the operating system itself. The next think you might want to know is how long SQL Server has been up or when it was last started to put it another way. There’s more than one way to do that. Here are three of them: 1234567 SELECT last_startup_time FROM sys.dm_server_services WHERE servicename = 'SQL Server (MSSQLSERVER)'; SELECT sqlserver_start_time FROM sys.dm_os_sys_info;SELECT login_time FROM sysprocesses WHERE spid = 1;What else can you tell me
Each of these views yields interesting information besides the last start time. For example: 123 SELECT * FROM sys.dm_server_services; Returns 3 rows and 11 columns on my system, starting with: Column 9 is named “filename” and it also shows the executable launched, with any options: Note that I can also see the service account the process is running under. 123 SELECT * FROM sys.dm_os_sys_info; Gives lots of information about the running environment. Here is just a little of what I get back: OK, OK! I’ve just revealed that I only have 8 MB installed on my laptop. Amazing what you can do with just a little, though! sysprocesses is central to the operation of the server. We’ll be using it again and again in this series. For now, keeping in mind that system processes have spids 1-50, let’s see what I can find out about anything above 50: 123 SELECT hostname, loginame, cmd FROM sys.sysprocesses WHERE spid > 50; As I write this, I get: Which tells me that I have three sessions going (true!) and one of them is running a SELECT. In fact, it’s the query used to produce this output.Tell me more
sp_server_info is a system stored procedure that can tell you more about how the server was configured. For example, on my test system I can see that I allow mixed-case identifies by that my default collation is case-insensitive and accent sensitive (this can be overridden at the database and column levels): While we are talking about configuration, we must dig into the system stored procedure sp_configure. It tells us current settings and can also be used to change them. When executed with no parameters, it returns a list of current settings: 123 EXEC sp_configure; Returns: But wait, there’s more! If I now run: 12345 EXEC sp_configure 'show advanced option', '1'; RECONFIGURE;EXEC sp_configure; I now have 70 rows returned. I won’t dig into all of them here, but want to highlight the last line: This option is used to control whether or not the named system extended stored procedure can be executed. This particular one is quite powerful since, if enabled, it allows a T-SQL query to execute arbitrary operating system commands in a command shell launched from with SQL Server. Many shops keep this one disabled (it is also disabled in my case, as indicated by the 0 in the fourth column, config_value). Some dispute whether disabling this option really provides any protection. See the article by Jen McCown in the references and draw your own conclusions. You can find out more about the options and current settings using the system view 123 sys.configurations The output includes descriptions of each option, for example: 1234 SELECT name, description FROM sys.configurations WHERE name = 'xp_cmdshell'; Returns:Who else is here
Since you’re the new kid on the block, it’s a good idea to find out who else might be using the server. You’ll likely want to get to know those people! The sys.syslogins view will help you here (also, see sys.server_principals, below): 123 SELECT * FROM sys.syslogins; This old view, though now deprecated, is useful for viewing logins and permissions at a glance. Even on a brand-new instance, you’ll see lots of logins used by the system. The basic permissions are all there too, as bit columns: So, you can see at a glance who is there and what they can do at the instance level. Instname, instgroup and instuser indicate if the login is a Widows user or group, a Windows group and a Windows user respectively. A setting of 0 indicates a SQL Server login. You will also find a related list of ids in the sys.server_principals view. You can query it based on the type column if you like, and restrict the output to: Type Description R Server Role C Certificate mapped login S SQL Server Login U Windows Login G Windows Group You might want to know which id is assigned to which role, especially for your new colleagues. This query should do the trick: 12345678910 SELECT role.name AS RoleName, member.name AS MemberNameFROM sys.server_role_members rmJOIN sys.server_principals AS role ON rm.role_principal_id = role.principal_idJOIN sys.server_principals AS member ON rm.member_principal_id = member.principal_idWHERE role.type = 'R'ORDER BY RoleName, MemberName; Armed with this output, you’ll know who to ask when something changes that falls with the responsibility of the various server rolesSummary
Using a few of the many system views and stored procedures available in SQL Server, you can get a handle on the setup of a server that is new to you. Try these out on any server to which you currently have access. The results will interest you and may even surprise you! Next 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