Misconceptions about running DMVs on databases with lower compatibility levels

It's lunchtime for my class – time for a blog post! This is an interesting one that crops up every so often (just an hour ago on SQL Server Central) and is not known very widely at all.

There's a misconception that you cannot run DMVs in databases that have compatibility levels of 80 or less. It's not true.

Here's an example:

USE master;
GO
EXEC sp_dbcmptlevel AdventureWorks, 80;

USE AdventureWorks;
GO

SELECT [object_id], [index_id]
FROM sys.dm_db_index_physical_stats (
    DB_ID ('AdventureWorks'), NULL, NULL, NULL, NULL)
WHERE [avg_fragmentation_in_percent] > 30;
GO

You get the incredibly useful and explanatory message:

Msg 102, Level 15, State 1, Line 2
Incorrect syntax near '('.

What the heck does that mean? It really means that the query processor refused to recognize the DMV name because the database is running in 80 or lower compatibility level.

Here's the trick – execute the DMV in the context of database that's in 90 compatibility level or higher, and it will let you do it – even if you're actually targeting a database in a lower compatibility level.

USE master;
GO

SELECT [object_id], [index_id]
FROM sys.dm_db_index_physical_stats (
    DB_ID ('AdventureWorks'), NULL, NULL, NULL, NULL)
WHERE [avg_fragmentation_in_percent] > 30;
GO

object_id   index_id
———– ———–
18099105    2
30623152    2
30623152    3
30623152    4
66099276    1
66099276    2

Note that I executed the DMV from the context of master, but targeted AdventureWorks, which is in the 80 compatibility level. 

Have fun!

6 thoughts on “Misconceptions about running DMVs on databases with lower compatibility levels

  1. Excellent. I just ran into this issue last week and went through countless files to make sure nothing broke when I set the compatibility level to 90.

  2. I use that DMV to join with various system views and thus this trick won’t work unless I specify 3 part names. Since I want to use this code as a ‘snippet’ in SSMS I don’t want to have to modify it at all when running it in different db contexts. Since compatibility 80 mode really only pukes on the inline function call it’s easy to just use a variable.
    Using your example from above it would look like this:

    DECLARE @db_id int;
    SELECT @db_id = db_id();

    SELECT [object_id], [index_id]
    FROM sys.dm_db_index_physical_stats (
    @db_id, NULL, NULL, NULL, NULL)
    WHERE [avg_fragmentation_in_percent] > 30;
    GO

    It’s an easy work around too! :)

  3. DMV works well in compatibility mode 80, but it was actually the function that caused the misconception. Its mentioned in the BOL that DB_ID function must specify database name when used against compatibility level lower than 90.

    BOL on sys.dm_db_index_physical_stats
    The built-in function DB_ID can be specified. When using DB_ID without specifying a database name, the compatibility level of the current database must be 90.

  4. Interestingly, (and perhaps surprisingly) this also works with databases that were created in SQL 2000, then migrated by SQL 2008 via backup and restore – i.e this database has never had the DMV’s created in it, as you would get by creating a database in SQL 2008 and downgrading it to compatibility level 80.

    Steve G.

Leave a Reply

Your email address will not be published. Required fields are marked *

Other articles

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.