An interesting way at looking how system views are built

I love metadata.  I think it’s really cool looking at the underpinnings of SQL Server.  While cruising through system views this morning, I came across one that I’d never seen before.  I’m not 100% certain that it is a Microsoft-supplied view, it’s possible that it was provided by one of the utility packages that I’ve installed.

Anyway, here’s the view:

SELECT [Datetime]
    ,[Instance name]
    ,[Storage errors]
    ,[Sql errors]
    ,[Credential errors]
    ,[Other errors]
    ,[Deleted or invalid backup files]
    ,[Number of backup loops]
    ,[Number of retention loops]
FROM [msdb].[dbo].[vw_autoadmin_health_status]

The result returned is also interesting:

Datetime Instance name Storage errors Sql errors Credential errors Other errors Deleted or invalid backup files Number of backup loops Number of retention loops
--------------------- --------------- --------------- ----------- ------------------ ------------- ------------------------------- ----------------------- --------------------------
Jun 10 2016 12:47PM NB-REF-8N4BWZ1 0 0 0 0 0 0 0

(1 row(s) affected)

Since this is my dev server, I’m not surprised these numbers are zero.  I think it would be much more interesting to run this on a production server.  I suspect that if you have multiple linked servers that you’ll get one row per server, I don’t have the environment to test this.

Here’s the odd bit: now I can’t find the view again!  But selecting from that view does return one row.  So I turned on Include Actual Execution Plan and the results were intriguing.

The first operator (bottom-most, right-most, was a table scan against a TVF called fn_get_health_status, which is part of the [msdb].[smartadmin] schema.

I’m running SQL Server 2014 Developer Edition, and Help for that TVF says “Smart Admin automates administration tasks on the SQL Server instance using minimal user configuration.”  Cool.  Definitely a good thing.  I’m used to monitoring a lot of things in SQL Server by myself, using tools that I’ve developed over the last 20ish years.  And I’d never heard of Smart Admin.

Poking around in Books Online and Google Search I came across this Microsoft page, talking about Smart Admin, talking about “The function is used to report health status of services under Smart Admin.

Definitely something worth looking in to with the intent of making some utilities to simplify monitoring servers.


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s