{"id":718,"date":"2010-04-13T10:40:00","date_gmt":"2010-04-13T10:40:00","guid":{"rendered":"\/blogs\/paul\/post\/A-DBA-myth-a-day-(1330)-you-cannot-run-DMVs-when-in-the-80-compat-mode-(T-SQL-Tuesday-005).aspx"},"modified":"2017-07-03T15:01:58","modified_gmt":"2017-07-03T22:01:58","slug":"a-dba-myth-a-day-1330-you-cannot-run-dmvs-when-in-the-80-compat-mode-t-sql-tuesday-005","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/paul\/a-dba-myth-a-day-1330-you-cannot-run-dmvs-when-in-the-80-compat-mode-t-sql-tuesday-005\/","title":{"rendered":"A DBA myth a day: (13\/30) you cannot run DMVs when in the 80 compat mode (T-SQL Tuesday #005)"},"content":{"rendered":"

(Look in the Misconceptions<\/a> 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<\/a>, plus my online Myths and Misconceptions training course<\/a> on Pluralsight.)<\/p>\n

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<\/a>|blog<\/a>) organizes. This month’s T-SQL Tuesday is being run by Aaron Nelson (twitter<\/a>|blog<\/a>) and is on the subject of reporting – see this blog post<\/a> for details.<\/p>\n

Myth #13:<\/strong> you cannot run DMVs when in the 80 compat mode<\/em>.<\/p>\n

FALSE <\/em><\/u><\/strong><\/p>\n

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).<\/p>\n

In SQL Server 2008 you can use ALTER DATABASE SET COMPATIBILITY_LEVEL<\/em> to change the compatibility level; in prior versions you use sp_dbcmptlevel<\/em>. To see what the compatibility level controls, see the Books Online entry ALTER DATABASE Compatibility Level<\/a><\/em>.<\/p>\n

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<\/a><\/em> for more details, and Msg 602, Level 21, State 50, Line 1<\/a><\/em> for details on the error messages you get when trying to attach\/restore a database to a previous version.<\/p>\n

But I digress, as usual :-)<\/p>\n

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:<\/p>\n

\r\nCREATE DATABASE [DMVTest];\r\nGO\r\nUSE [DMVTest];\r\nGO\r\nCREATE TABLE [t1] ([c1] INT);\r\nCREATE CLUSTERED INDEX [t1c1] on [t1] ([c1]);\r\nINSERT INTO [t1] VALUES (1);\r\nGO\r\n\r\nEXEC sp_dbcmptlevel DMVTest, 80;\r\nGO\r\n\r\nSELECT * FROM sys.dm_db_index_physical_stats (\r\n    DB_ID (N'DMVTest'), -- database ID\r\n    OBJECT_ID (N't1'),  -- object ID\r\n    NULL,               -- index ID\r\n    NULL,               -- partition ID\r\n    'DETAILED');        -- scan mode\r\nGO\r\n<\/pre>\n
And the really useful error I get back is:<\/p>\n
\r\nMsg 102, Level 15, State 1, Line 2\r\nIncorrect syntax near '('.\r\n<\/pre>\n
How incredibly useful is that? It pinpoints the problem exactly – not.<\/p>\n
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<\/em>) 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.)<\/p>\n
Here’s the trick to using the functions as parameters. You change context\u00a0to a database in the 90 or higher compatibility level – and then you can point the DMV at\u00a0the database in the 80 compatibility level.<\/p>\n
Very cool. Check it out:<\/p>\n
\r\nUSE [master];\r\nGO<\/span>\r\n\r\nSELECT * FROM sys.dm_db_index_physical_stats (\r\n    DB_ID (N'DMVTest'),\r\n    OBJECT_ID (N'DMVTest..t1'),\r\n    NULL,\r\n    NULL,\r\n    'DETAILED');\r\nGO\r\n<\/pre>\n
And it works, even though the database\u00a0DMVTest is in the 80 compatibility level.<\/p>\n
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’)<\/em>, it would have tried to find the object ID of the t1<\/em> table in the master<\/em> 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<\/em> database. If by chance there’s a t1<\/em> table in master<\/em>, it’s likely got a different object ID from the t1<\/em> table in DMVTest<\/em>, and so the DMV will fail.<\/p>\n
And sys.dm_db_index_physical_stats<\/em> 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<\/a><\/em> for details of how it works and how expensive it can be.<\/p>\n
So, you’ll need to use the new 3-part naming option\u00a0of OBJECT_ID<\/em> in SQL Server 2005 onward to make sure you’re grabbing the correct object ID when going across database contexts.<\/p>\n
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:<\/p>\n
\r\nDECLARE @databaseID INT;\r\nDECLARE @objectID\u00a0 \u00a0INT;\r\n\r\nSELECT @databaseID = DB_ID (N'DMVTest');\r\nSELECT @objectID\u00a0\u00a0 = OBJECT_ID (N't1');\r\n\r\nSELECT * FROM sys.dm_db_index_physical_stats (\r\n    @dbid,\r\n    @objid,\r\n    NULL,\r\n    NULL,\r\n    'DETAILED');\r\nGO\r\n<\/pre>\n
Bottom line: another myth bites the dust!<\/p>\n","protected":false},"excerpt":{"rendered":"
(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 […]<\/p>\n","protected":false},"author":5,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[31,61,66,95],"tags":[],"class_list":["post-718","post","type-post","status-publish","format-standard","hentry","category-database-maintenance","category-misconceptions","category-performance-tuning","category-tools"],"yoast_head":"\nA DBA myth a day: (13\/30) you cannot run DMVs when in the 80 compat mode (T-SQL Tuesday #005) - Paul S. Randal<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.sqlskills.com\/blogs\/paul\/a-dba-myth-a-day-1330-you-cannot-run-dmvs-when-in-the-80-compat-mode-t-sql-tuesday-005\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"A DBA myth a day: (13\/30) you cannot run DMVs when in the 80 compat mode (T-SQL Tuesday #005) - Paul S. Randal\" \/>\n<meta property=\"og:description\" content=\"(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 […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/paul\/a-dba-myth-a-day-1330-you-cannot-run-dmvs-when-in-the-80-compat-mode-t-sql-tuesday-005\/\" \/>\n<meta property=\"og:site_name\" content=\"Paul S. Randal\" \/>\n<meta property=\"article:published_time\" content=\"2010-04-13T10:40:00+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2017-07-03T22:01:58+00:00\" \/>\n<meta name=\"author\" content=\"Paul Randal\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Paul Randal\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"4 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/a-dba-myth-a-day-1330-you-cannot-run-dmvs-when-in-the-80-compat-mode-t-sql-tuesday-005\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/a-dba-myth-a-day-1330-you-cannot-run-dmvs-when-in-the-80-compat-mode-t-sql-tuesday-005\/\",\"name\":\"A DBA myth a day: (13\/30) you cannot run DMVs when in the 80 compat mode (T-SQL Tuesday #005) - Paul S. Randal\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#website\"},\"datePublished\":\"2010-04-13T10:40:00+00:00\",\"dateModified\":\"2017-07-03T22:01:58+00:00\",\"author\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/a-dba-myth-a-day-1330-you-cannot-run-dmvs-when-in-the-80-compat-mode-t-sql-tuesday-005\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/paul\/a-dba-myth-a-day-1330-you-cannot-run-dmvs-when-in-the-80-compat-mode-t-sql-tuesday-005\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/a-dba-myth-a-day-1330-you-cannot-run-dmvs-when-in-the-80-compat-mode-t-sql-tuesday-005\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"A DBA myth a day: (13\/30) you cannot run DMVs when in the 80 compat mode (T-SQL Tuesday #005)\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#website\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/\",\"name\":\"Paul S. Randal\",\"description\":\"In Recovery...\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce\",\"name\":\"Paul Randal\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/0b6a266bba2f088f2551ef529293001bd73bf026bc1908b9866728c062beeeb6?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/0b6a266bba2f088f2551ef529293001bd73bf026bc1908b9866728c062beeeb6?s=96&d=mm&r=g\",\"caption\":\"Paul Randal\"},\"sameAs\":[\"http:\/\/3.209.169.194\/blogs\/paul\"],\"url\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/author\/paul\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"A DBA myth a day: (13\/30) you cannot run DMVs when in the 80 compat mode (T-SQL Tuesday #005) - Paul S. Randal","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.sqlskills.com\/blogs\/paul\/a-dba-myth-a-day-1330-you-cannot-run-dmvs-when-in-the-80-compat-mode-t-sql-tuesday-005\/","og_locale":"en_US","og_type":"article","og_title":"A DBA myth a day: (13\/30) you cannot run DMVs when in the 80 compat mode (T-SQL Tuesday #005) - Paul S. Randal","og_description":"(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 […]","og_url":"https:\/\/www.sqlskills.com\/blogs\/paul\/a-dba-myth-a-day-1330-you-cannot-run-dmvs-when-in-the-80-compat-mode-t-sql-tuesday-005\/","og_site_name":"Paul S. Randal","article_published_time":"2010-04-13T10:40:00+00:00","article_modified_time":"2017-07-03T22:01:58+00:00","author":"Paul Randal","twitter_misc":{"Written by":"Paul Randal","Est. reading time":"4 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/a-dba-myth-a-day-1330-you-cannot-run-dmvs-when-in-the-80-compat-mode-t-sql-tuesday-005\/","url":"https:\/\/www.sqlskills.com\/blogs\/paul\/a-dba-myth-a-day-1330-you-cannot-run-dmvs-when-in-the-80-compat-mode-t-sql-tuesday-005\/","name":"A DBA myth a day: (13\/30) you cannot run DMVs when in the 80 compat mode (T-SQL Tuesday #005) - Paul S. Randal","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#website"},"datePublished":"2010-04-13T10:40:00+00:00","dateModified":"2017-07-03T22:01:58+00:00","author":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce"},"breadcrumb":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/a-dba-myth-a-day-1330-you-cannot-run-dmvs-when-in-the-80-compat-mode-t-sql-tuesday-005\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/paul\/a-dba-myth-a-day-1330-you-cannot-run-dmvs-when-in-the-80-compat-mode-t-sql-tuesday-005\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/a-dba-myth-a-day-1330-you-cannot-run-dmvs-when-in-the-80-compat-mode-t-sql-tuesday-005\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqlskills.com\/blogs\/paul\/"},{"@type":"ListItem","position":2,"name":"A DBA myth a day: (13\/30) you cannot run DMVs when in the 80 compat mode (T-SQL Tuesday #005)"}]},{"@type":"WebSite","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#website","url":"https:\/\/www.sqlskills.com\/blogs\/paul\/","name":"Paul S. Randal","description":"In Recovery...","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.sqlskills.com\/blogs\/paul\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce","name":"Paul Randal","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/0b6a266bba2f088f2551ef529293001bd73bf026bc1908b9866728c062beeeb6?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/0b6a266bba2f088f2551ef529293001bd73bf026bc1908b9866728c062beeeb6?s=96&d=mm&r=g","caption":"Paul Randal"},"sameAs":["http:\/\/3.209.169.194\/blogs\/paul"],"url":"https:\/\/www.sqlskills.com\/blogs\/paul\/author\/paul\/"}]}},"_links":{"self":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/posts\/718","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/users\/5"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/comments?post=718"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/posts\/718\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/media?parent=718"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/categories?post=718"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/tags?post=718"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}