Tags: SQL Server Internals, webinar
Thanks to everyone who attended! You can get the slides and demo files here: Webinar 1.2 — SQLServer DMVs files
If you missed the session, or would like to watch the recorded version, here’s the link.
(Once you complete the registration information, you will get an email with a link to the webinar. Yes, it’s a little tedious, but the marketing team says I have to use this link.)
Following are solutions to the homework exercises for Webinar 1.2:
1. Find a DMV that is not documented. Why can you see the name in sys.all_objects if it’s not documented?
There is no programmatic way to find the DMVs that are not documented. You’ll just have to take a look at what’s available in the documentation and see if you can find any that are not returned when you run this query:
1 2 3 | SELECT name FROM sys.system_objects WHERE name LIKE 'dm[_]%' ORDER BY name; |
One object is sys.dm_db_database_page_allocations, which we’ll be looking at in detail in an upcoming webinar.
The objects visible in sys.all_objects or sys.system_objects are all the ones that exist. SQL Server doesn’t know whether its view and functions are documented or not. The question of WHY Microsoft choose NOT to document certain DMVs is another question entirely.
2. Write a query that will show you the session_id, login_name, host_name, request start_time and sql_handle for all active user requests.
There are no metadata columns that indicate that a session is active. Basically, if the session has a row in sys.dm_exec_requests, it is active. But that view doesn’t indicate user requests, and also doesn’t contain login or host information, so we need to join sys.dm_exec_sessions with sys.dm_exec_requests. My solution looks like this:
1 2 3 4 5 6 | SELECT s.session_id, login_name, host_name, start_time, sql_handle FROM sys.dm_exec_sessions s JOIN sys.dm_exec_requests r ON s.session_id = r.session_id WHERE is_user_process = 1; |
3. Without using the documentation, find a column in a DMV that shows the time your SQL Server instance started, and write a query to display that start time.
You could first search through sys.all_columns to find any column names that seem like they fit the bill, and then look at the object_name for each of the columns returned. So you might start with something like this:
1 2 3 | SELECT object_name(object_id), name FROM sys.all_columns WHERE name LIKE '%start%time%'; |
On my system, that query returns 56 rows. That is really not all that many to visually examine and see which ones are promising. Or you could try to eliminate a few others that you can guess belong to certain components and don’t apply to the SQL Server instance as a whole.
1 2 3 4 5 6 7 8 9 10 | SELECT object_name(object_id), name FROM sys.all_columns WHERE name LIKE '%start%time%' AND object_name(object_id) NOT LIKE '%pdw%' AND object_name(object_id) NOT LIKE '%query_store%' AND object_name(object_id) NOT LIKE 'dm_fts%' AND object_name(object_id) NOT LIKE 'dm_hadr%' AND object_name(object_id) NOT LIKE 'plan_persist%' AND object_name(object_id) NOT LIKE 'dm_hadr%' AND object_name(object_id) NOT LIKE 'dm_%rda%'; |
Those filters restrict my output to 23 rows, so I still need to do some visual inspection and maybe even run some SELECT queries to look at those views. But you should narrow down the options to the one called sqlserver_start_time which is in the view sys.dm_os_sys_info. So just selecting that column from that view will return your SQL Server instance start time. However, I like to create a special stored procedure that I can easily run any time I need to see when my SQL Server started. So here it is:
1 2 3 4 5 6 | USE master GO CREATE PROC dbo.sp_starttime AS SELECT create_date FROM sys.databases WHERE name = 'tempdb'; GO |
Then I can just execute sp_starttime to see when my last restart was.
Next week’s webinar will cover some internal aspects of Metadata, including the Dedicated Administrator Connection (DAC) and the Resource database. I’ll be bringing it to you from London!
