Gerald Britton

Discovering SQL server instance information using system views

February 28, 2017 by

Introduction

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:

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

I simply run:

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

Running this on my system:

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:

What else can you tell me?

Each of these views yields interesting information besides the last start time. For example:

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.

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:

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:

Returns:

But wait, there’s more! If I now run:

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

The output includes descriptions of each option, for example:

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):

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:

Armed with this output, you’ll know who to ask when something changes that falls with the responsibility of the various server roles

Summary

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:

Gerald Britton
Latest posts by Gerald Britton (see all)
Views

About Gerald Britton

Gerald 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

168 Views