(Look in the Misconceptions blog category for the rest of the month’s posts and check out the 60-page PDF with all the myths and misconceptions blog posts collected together when you join our Insider list, plus my online Myths and Misconceptions training course on Pluralsight.)
This blog post is part of two series – my Myth-A-Day series and the monthly T-SQL Tuesday series that fellow-MVP Adam Machanic (twitter|blog) organizes. This month’s T-SQL Tuesday is being run by Aaron Nelson (twitter|blog) and is on the subject of reporting – see this blog post for details.
Myth #13: you cannot run DMVs when in the 80 compat mode.
To start with, there’s a lot of confusion about what compat mode means. Does it mean that the database can be restored/attached to a SQL Server 2000 server? No. It means that some T-SQL parsing, query plan behavior, hints and a few other things behave as they did in SQL Server 2000 (or 2005, if you’re setting it to 90 on a 2008 instance).
In SQL Server 2008 you can use ALTER DATABASE SET COMPATIBILITY_LEVEL to change the compatibility level; in prior versions you use sp_dbcmptlevel. To see what the compatibility level controls, see the Books Online entry ALTER DATABASE Compatibility Level.
Compatibility level has no effect on the database physical version – which is what gets bumped up when you upgrade, and prevents a database being restored/attached to a previous version – as they have a maximum physical version number they can understand. See my blog post Search Engine Q&A #13: Difference between database version and database compatibility level for more details, and Msg 602, Level 21, State 50, Line 1 for details on the error messages you get when trying to attach/restore a database to a previous version.
But I digress, as usual :-)
One of the things that looks like it doesn’t work is using DMVs when in the 80 compat mode. Here’s a simple script to show you, using SQL Server 2005:
CREATE DATABASE [DMVTest]; GO USE [DMVTest]; GO CREATE TABLE [t1] ([c1] INT); CREATE CLUSTERED INDEX [t1c1] on [t1] ([c1]); INSERT INTO [t1] VALUES (1); GO EXEC sp_dbcmptlevel DMVTest, 80; GO SELECT * FROM sys.dm_db_index_physical_stats ( DB_ID (N'DMVTest'), -- database ID OBJECT_ID (N't1'), -- object ID NULL, -- index ID NULL, -- partition ID 'DETAILED'); -- scan mode GO
And the really useful error I get back is:
Msg 102, Level 15, State 1, Line 2 Incorrect syntax near '('.
How incredibly useful is that? It pinpoints the problem exactly – not.
Edit: After writing this I realized I’d fallen victim to my own myth too! DMVs *are* supported in the 80 compat-mode completely. What’s *not* supported is calling a function (e.g. OBJECT_ID) as one of the DMV parameters. Thanks to Aaron Bertrand for point this out! (Apparently he pointed that out in the recent Boston class we taught, but I missed it.)
Here’s the trick to using the functions as parameters. You change context to a database in the 90 or higher compatibility level – and then you can point the DMV at the database in the 80 compatibility level.
Very cool. Check it out:
USE [master]; GO</span> SELECT * FROM sys.dm_db_index_physical_stats ( DB_ID (N'DMVTest'), OBJECT_ID (N'DMVTest..t1'), NULL, NULL, 'DETAILED'); GO
And it works, even though the database DMVTest is in the 80 compatibility level.
One thing to be *very* careful of – you need to make sure you’re using the correct object ID. If I’d just left the second parameter as OBJECT_ID (‘t1’), it would have tried to find the object ID of the t1 table in the master database. If it didn’t find it, it will use the value NULL, which will cause the DMV to run against all tables in the DMVTest database. If by chance there’s a t1 table in master, it’s likely got a different object ID from the t1 table in DMVTest, and so the DMV will fail.
And sys.dm_db_index_physical_stats isn’t a true DMV – Dynamic Management View – it’s a Dynamic Management Function which does a *ton* of work potentially to return results – so you want to make sure you limit it to only the tables you’re interested in. See my recent blog post Inside sys.dm_db_index_physical_stats for details of how it works and how expensive it can be.
So, you’ll need to use the new 3-part naming option of OBJECT_ID in SQL Server 2005 onward to make sure you’re grabbing the correct object ID when going across database contexts.
Another way to do it is to use variables and pre-assign the values to them, which you can do from within the 80 compat-mode database:
DECLARE @databaseID INT; DECLARE @objectID INT; SELECT @databaseID = DB_ID (N'DMVTest'); SELECT @objectID = OBJECT_ID (N't1'); SELECT * FROM sys.dm_db_index_physical_stats ( @dbid, @objid, NULL, NULL, 'DETAILED'); GO
Bottom line: another myth bites the dust!
4 thoughts on “A DBA myth a day: (13/30) you cannot run DMVs when in the 80 compat mode (T-SQL Tuesday #005)”
I mentioned this in our class, but you must have been twittering. :-) You can get around this error even if all of the databases on your system are in 80 compat mode. The syntax error is because of the way you are calling a function within a table-valued function call, not because it’s a DMV. Here is how to call it for this object within this database (even in 80 compat mode):
DECLARE @dbid INT, @objid INT;
@dbid = DB_ID(‘DMVTest’),
@objid = OBJECT_ID(‘DMVTest.dbo.t1’);
SELECT * FROM sys.dm_db_index_physical_stats (
@dbid, — database ID
@objid, — object ID
NULL, — index ID
NULL, — partition ID
‘DETAILED’); — scan mode
Sorry about the formatting. And no you don’t need the three-part name to assign the object_id.
Yes, you’re absolutely right – and I missed that in the class. I’ll update the post. Thanks Aaron!
I have encountered the same behavior with all functions in 2000-compatible databases, namely: you cannot use a scalar function, no matter system or user, as an argument for a table-valued function, again no matter which one. It is the case at least on 2005 platform, and it is really frustrating.
As to your trick, there are some other compatibility limitations which fall prey to it, as well. For example, (UN)PIVOT and APPLY could be fooled exactly the same way as described. But what is more interesting, APPLY is usable directly in 80 compat DB context – but only if used against a subquery.
So this is not an entirely DMV-specific subject, as you can see.