(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!