Yes, Dynamic Management PROCEDURES (DMPs)

… before you even ask…

No, they’re not MS_Shipped = 1.

The Dynamic Management Objects (Dynamic Management Views & Dynamic Management Functions) in Microsoft SQL Server provide a wealth of metafatastical metdata. However, they tread lightly in many areas. They don’t stray too close to the INFORMATION_SCHEMA views. Likewise those views – all about objects and properties – do not provide much, if any, performance-related information. The DMOs also don’t provide much in the way of information about any object outside the confines of the master database (as in SQL Agent jobs and backup information). For the past seven years I’ve been focused on what there is to be mined from the DMOs and have longed for more. Instead of waiting for Microsoft I think it’s time to create a few of my own. As I don’t want to tread on hallowed ground and cause confusion I’m going to steer clear of the use of views and functions (plus I will be relying on temp tables in the scripts and therefore that rules out views immediately.)

I do want to approach the naming convention used in the standard DMOs. Therefore here in this post and in posts to come in this series over time I will be using the naming convention of dbo.sp_ford_dm_*, where * will be a logical name for the results of the stored procedure call. You’ll see that like the DMOs I’ll be using the dm_* conventions for naming objects. At the same time I want to ensure that if Microsoft does have any plans in the works (and I’m not saying they are by any means) then adding the _ford_ to the naming should as well avoid any issues.

Before anyone says it I’ll raise the spectre first: I am creating these using sp_ for the prefix and I am creating these in the master database. Put down the flaming torches and troll masks. Many of these DMPs (Dynamic Management Procedures) will be tapping in to system catalog views across multiple databases as well as scanning the user databases. It’s the logical choice.

Below is the DDL command for creating the dbo.sp_ford_dm_log_metrics DMP. Results are returned based upon highest VLF count.

Since schemas are prone to change from version to version of Microsoft SQL Server the DMP code needs to take into consideration which version of SQL Server you’re running against as the diligent and handsome James Lean mentioned below. (Which prompted extra work on my part to update this post.) Nice catch Mr. Lean!

What the Results Look Like

Calling the DMP is as you would expect. This currently requires no parameters though I expect future upgrades to allow for dynamic sorting and filtering of databases.

EXECUTE dbo.sp_ford_dm_log_metrics;

On a “bad” instance your results may look as such. (Mine did on this nasty Test SQL Server I just “discovered” recently):

I’ve changed the names in this case to protect the blatantly guilty (and this DBA as well.) As you can see there are issues from multiple angles: high VLF count, poor auto-growth choices, and auto-growth as a percentage. I also can tell from looking at this that the logs are not being backed up and that we have test databases running under FULL recovery with no associated log backups occuring. Based upon this information I was able to correct most issues. You’ll notice I still need to deal with a couple situations where we have databases with large transaction logs and only a few VLFs as well as that tempdb issue (but it’s a test instance and I have better things to concern myself with):

(Note that the max_backup_size_mb and file_execess_mb columns will report NULL values when running under SIMPLE recovery for logical reasons.)

The following results are against one of my healthier instances (again, pertinent information changed to protect THIS GUY shows moderate VLF counts and no percentage growth settings. The auto-growth sizes are fitting for the usage patterns on this server as well. In this case you can see I’ve also performed some “surgery” on these log files by the fact that they are reporting negative file_execess_mb values. This will occur where historically the database’s log encountered a large, aberrant, activity period that was not indicative of normal load. After correcting the physical log file for this situation msdb.dbo.backupfile still retains that information until it falls outside my purging process for backups, which is 30 days. One thing I was able to determine in the course of writing this post was that the purge script was not installed on this SQL instance. That’s since been corrected. You learn something every day!

What’s Next

Expect to see future posts on Dynamic Management Procedures covering backup history, compression, SQL Agent jobs, and the like. Stay tuned. This is going to be fun. It’s not every day you get to build your own Dynamic Management “Objects”.