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:
|
1 2 3 |
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
|
1 2 3 |
sys.dm_os_windows_info |
I simply run:
|
1 2 3 |
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
|
1 2 3 |
sys.dm_server_registry |
Running this on my system:
|
1 2 3 |
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:
|
1 2 3 4 5 6 7 |
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:
|
1 2 3 |
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.
|
1 2 3 |
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:
|
1 2 3 |
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:
|
1 2 3 |
EXEC sp_configure; |
Returns:
But wait, there’s more! If I now run:
|
1 2 3 4 5 |
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
|
1 2 3 |
sys.configurations |
The output includes descriptions of each option, for example:
|
1 2 3 4 |
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):
|
1 2 3 |
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:
|
1 2 3 4 5 6 7 8 9 10 |
SELECT role.name AS RoleName, member.name AS MemberName FROM sys.server_role_members rm JOIN sys.server_principals AS role ON rm.role_principal_id = role.principal_id JOIN sys.server_principals AS member ON rm.member_principal_id = member.principal_id WHERE 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 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:
- 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
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
- 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