Searching DMFs and DMVs

Document Type: Tip

Released: 2024-09-13

Spelunking through SQL Server Meta-Data

Every once in a while, when I'm trying to solve some sort of problem that has to to with tuning, optimization, or some other bit of nitty-gritty that involves SQL Server Internals, I find it nice to be able to search through DMVs and DMFs for any column-names that might overlap with whatever it is I'm looking for.

Granted, if your google-fu is solid enough, you can probably find what you need without this, but:

• sometimes my google-fu isn't as strong as it needs to be
• or, at other times, whatever I'm searching for has too many overlaps with something that has been blogged or documented to the point where there's SO MUCH NOISE that I simply can't quickly find what I'm looking for.

DMVs to the Rescue

In cases like the above, I've found that the following query works quite well to let me know if there's something I might be able to "dig into" a bit further - with a very minor amount of effort (i.e., just have to get the POTENTIAL column-names/matches right, and then I'm usually able to find a few rabbit-holes):

SELECT 
	OBJECT_NAME([c].[object_id]) [dmv], 
	[c].[name] [column_name] 
FROM 
	sys.[all_columns] [c]
WHERE 
	[c].[object_id] < 0
	AND [c].[name] LIKE N'%lsn%'
ORDER BY 
	OBJECT_NAME([c].[object_id]);

e.g., in the query above, I'm looking for anything that might give insight into LSNs - or, I could change the above to something like %clock% if I'm digging for info on cache pressure, and so on.

What's Taters, Precious?

The query above is honestly NOTHING SPECIAL, just a simple query against sys.all_columns (as sys.columns ONLY returns information about (user AND system) tables, views, and functions - whereas sys.all_columns pulls back EVERYTHING - including columns within DMVs and DMFs) - and then a simple WHERE clause to predicate such that only parent objects with a NEGATIVE object_id are returned (so that you're only searching amongst DMFs and DMVs).

Note that I'm not aware of any explicit documentation that says that DMVs/DMFs use negative object_ids, but they DO (as you can see if you take this for a test drive), and it's worth pointing out that OBJECT_NAME() ALSO has no problems with lookups against negative object_ids, e.g.,

SELECT OBJECT_NAME(-537);

So, presumably, DMFs and DMVs are (obviously) OBJECTS, but to prevent them from 'interfering' with outputs of 'normal' (user) objects, someone at Microsoft at some point took the approach of assigning them negative ids.

And, ironically enough, I'm guessing that this insight into SQL Server internals is probably documented somewhere by Microsoft - my google-fu just hasn't been strong enough to find it.