SQL Server v.Next (Denali) : How has sys.databases changed?
Earlier, I documented the system objects that have changed in Denali. One that caught my eye was a slew of new columns made available through sys.databases, mostly to support a feature I also blogged about recently, contained databases. Consider the following simple query:
SELECT * FROM sys.databases;
Yes, this comes from a DMV, but where does the DMV really get its data? First I wanted to view the execution plans in 2008 R2 and Denali to compare them: were there any significant differences? Opening them in Management Studio proved to be relatively useless… to see everything on the screen, I can tell that they're a bit different, but zooming in and scrolling all over the place is for the birds:
<img src="https://sqlblog.org/wp-content/uploads/2018/01/31087_Screen20shot202010-11-2820at201.41.3320PM.png" width="619" border="1" height="230"> <i>2008 R2 graphical plan in SSMS</i> <a href="https://sqlblog.org/wp-content/uploads/2018/01/31087_Screen20shot202010-11-2820at201.40.5120PM.png" title="http://sqlblog.com/files/folders/31085/download.aspx" target="_blank" rel="noopener noreferrer"><img src="https://sqlblog.org/wp-content/uploads/2018/01/31087_Screen20shot202010-11-2820at201.40.5120PM-1.png" width="619" border="1" height="220"></a> <i>Denali graphical plan in SSMS</i>
So I opened my trusty SQL Sentry Plan Explorer (which I've blogged about before) and got a much better quick view of these plans, without having to perform any zooming whatsoever:
<a href="https://sqlblog.org/wp-content/uploads/2018/01/31087_Screen20shot202010-11-2820at201.38.3220PM.png" title="http://sqlblog.com/files/folders/31086/download.aspx" target="_blank" rel="noopener noreferrer"><img src="https://sqlblog.org/wp-content/uploads/2018/01/31087_Screen20shot202010-11-2820at201.38.3220PM-1.png" width="619" border="1" height="79"></a> <i>2008 R2 graphical plan in Plan Explorer</i> <a href="https://sqlblog.org/wp-content/uploads/2018/01/31087_Screen20shot202010-11-2820at201.38.4820PM.png" title="http://sqlblog.com/files/folders/31085/download.aspx" target="_blank" rel="noopener noreferrer"><img src="https://sqlblog.org/wp-content/uploads/2018/01/31087_Screen20shot202010-11-2820at201.38.4820PM-1.png" width="619" border="1" height="80"></a> <i>Denali graphical plan in Plan Explorer</i>
So this looks interesting so far; the execution plan for Denali is much busier, and has some references to internal objects I haven't noticed before: table-valued functions SYSLANG and SYSFULLTEXTLANGUAGES, and a system table called sysguidrefs. You can see the actual definition of the view pretty simply, by issuing:
SELECT OBJECT_DEFINITION(OBJECT_ID('sys.databases'));
In SQL Server 2008 R2, you get this (about 4,200 characters):
CREATE VIEW sys.databases AS SELECT d.name, d.id AS database_id, r.indepid AS source_database_id, d.sid AS owner_sid, d.crdate AS create_date, d.cmptlevel AS compatibility_level, CONVERT(SYSNAME, CollationPropertyFromID(p.cid, 'name')) AS collation_name, p.user_access, ua.name AS user_access_desc, sysconv(bit, d.status & 0x400) AS is_read_only, -- DBR_RDONLY sysconv(bit, d.status & 1) AS is_auto_close_on, -- DBR_CLOSE_ON_EXIT sysconv(bit, d.status & 0x400000) AS is_auto_shrink_on, -- DBR_AUTOSHRINK p.state, st.name AS state_desc, sysconv(bit, d.status & 0x200000) AS is_in_standby, -- DBR_STANDBY sysconv(bit, d.status & 0x40000000) AS is_cleanly_shutdown, -- DBR_CLEANLY_SHUTDOWN sysconv(bit, d.status & 0x80000000) AS is_supplemental_logging_enabled, -- DBR_SUPPLEMENT_LOG p.snapshot_isolation_state, si.name AS snapshot_isolation_state_desc, sysconv(bit, d.status & 0x800000) AS is_read_committed_snapshot_on, -- DBR_READCOMMITTED_SNAPSHOT p.recovery_model, ro.name AS recovery_model_desc, p.page_verify_option, pv.name AS page_verify_option_desc, sysconv(bit, d.status2 & 0x1000000) AS is_auto_create_stats_on, -- DBR_AUTOCRTSTATS sysconv(bit, d.status2 & 0x40000000) AS is_auto_update_stats_on, -- DBR_AUTOUPDSTATS sysconv(bit, d.status2 & 0x80000000) AS is_auto_update_stats_async_on, -- DBR_AUTOUPDSTATSASYNC sysconv(bit, d.status2 & 0x4000) AS is_ansi_null_default_on, -- DBR_ANSINULLDFLT sysconv(bit, d.status2 & 0x4000000) AS is_ansi_nulls_on, -- DBR_ANSINULLS sysconv(bit, d.status2 & 0x2000) AS is_ansi_padding_on, -- DBR_ANSIPADDING sysconv(bit, d.status2 & 0x10000000) AS is_ansi_warnings_on, -- DBR_ANSIWARNINGS sysconv(bit, d.status2 & 0x1000) AS is_arithabort_on, -- DBR_ARITHABORT sysconv(bit, d.status2 & 0x10000) AS is_concat_null_yields_null_on, -- DBR_CATNULL sysconv(bit, d.status2 & 0x800) AS is_numeric_roundabort_on, -- DBR_NUMEABORT sysconv(bit, d.status2 & 0x800000) AS is_quoted_identifier_on, -- DBR_QUOTEDIDENT sysconv(bit, d.status2 & 0x20000) AS is_recursive_triggers_on, -- DBR_RECURTRIG sysconv(bit, d.status2 & 0x2000000) AS is_cursor_close_on_commit_on, -- DBR_CURSCLOSEONCOM sysconv(bit, d.status2 & 0x100000) AS is_local_cursor_default, -- DBR_DEFLOCALCURS sysconv(bit, d.status2 & 0x20000000) AS is_fulltext_enabled, -- DBR_FTENABLED sysconv(bit, d.status2 & 0x200) AS is_trustworthy_on, -- DBR_TRUSTWORTHY sysconv(bit, d.status2 & 0x400) AS is_db_chaining_on, -- DBR_DBCHAINING sysconv(bit, d.status2 & 0x08000000) AS is_parameterization_forced, -- DBR_UNIVERSALAUTOPARAM sysconv(bit, d.status2 & 64) AS is_master_key_encrypted_by_server, -- DBR_MASTKEY sysconv(bit, d.category & 1) AS is_published, sysconv(bit, d.category & 2) AS is_subscribed, sysconv(bit, d.category & 4) AS is_merge_published, sysconv(bit, d.category & 16) AS is_distributor, sysconv(bit, d.category & 32) AS is_sync_with_backup, d.svcbrkrguid AS service_broker_guid, sysconv(bit, CASE WHEN d.scope = 0 THEN 1 ELSE 0 END) AS is_broker_enabled, p.log_reuse_wait, lr.name AS log_reuse_wait_desc, sysconv(bit, d.status2 & 4) AS is_date_correlation_on, -- DBR_DATECORRELATIONOPT sysconv(bit, d.category & 64) AS is_cdc_enabled, sysconv(bit, d.status2 & 0x100) AS is_encrypted, -- DBR_ENCRYPTION sysconv(bit, d.status2 & 0x8) AS is_honor_broker_priority_on -- DBR_HONORBRKPRI FROM master.sys.sysdbreg d OUTER APPLY OPENROWSET(TABLE DBPROP, d.id) p LEFT JOIN sys.syssingleobjrefs r ON r.depid = d.id AND r.class = 96 AND r.depsubid = 0 -- SRC_VIEWPOINTDB LEFT JOIN sys.syspalvalues st ON st.class = 'DBST' AND st.value = p.state LEFT JOIN sys.syspalvalues ua ON ua.class = 'DBUA' AND ua.value = p.user_access LEFT JOIN sys.syspalvalues si ON si.class = 'DBSI' AND si.value = p.snapshot_isolation_state LEFT JOIN sys.syspalvalues ro ON ro.class = 'DBRO' AND ro.value = p.recovery_model LEFT JOIN sys.syspalvalues pv ON pv.class = 'DBPV' AND pv.value = p.page_verify_option LEFT JOIN sys.syspalvalues lr ON lr.class = 'LRWT' AND lr.value = p.log_reuse_wait WHERE d.id < 0x7fff AND has_access('DB', d.id) = 1
And in Denali, it's much more verbose – almost 50% larger (about 6,200 characters):
CREATE VIEW sys.databases AS SELECT d.name, d.id AS database_id, r.indepid AS source_database_id, d.sid AS owner_sid, d.crdate AS create_date, d.cmptlevel AS compatibility_level, CONVERT(SYSNAME, CollationPropertyFromID(p.cid, 'name')) AS collation_name, p.user_access, ua.name AS user_access_desc, sysconv(bit, d.status & 0x400) AS is_read_only, -- DBR_RDONLY sysconv(bit, d.status & 1) AS is_auto_close_on, -- DBR_CLOSE_ON_EXIT sysconv(bit, d.status & 0x400000) AS is_auto_shrink_on, -- DBR_AUTOSHRINK p.state, st.name AS state_desc, sysconv(bit, d.status & 0x200000) AS is_in_standby, -- DBR_STANDBY p.is_cleanly_shutdown, sysconv(bit, d.status & 0x80000000) AS is_supplemental_logging_enabled, -- DBR_SUPPLEMENT_LOG p.snapshot_isolation_state, si.name AS snapshot_isolation_state_desc, sysconv(bit, d.status & 0x800000) AS is_read_committed_snapshot_on, -- DBR_READCOMMITTED_SNAPSHOT p.recovery_model, ro.name AS recovery_model_desc, p.page_verify_option, pv.name AS page_verify_option_desc, sysconv(bit, d.status2 & 0x1000000) AS is_auto_create_stats_on, -- DBR_AUTOCRTSTATS sysconv(bit, d.status2 & 0x40000000) AS is_auto_update_stats_on, -- DBR_AUTOUPDSTATS sysconv(bit, d.status2 & 0x80000000) AS is_auto_update_stats_async_on, -- DBR_AUTOUPDSTATSASYNC sysconv(bit, d.status2 & 0x4000) AS is_ansi_null_default_on, -- DBR_ANSINULLDFLT sysconv(bit, d.status2 & 0x4000000) AS is_ansi_nulls_on, -- DBR_ANSINULLS sysconv(bit, d.status2 & 0x2000) AS is_ansi_padding_on, -- DBR_ANSIPADDING sysconv(bit, d.status2 & 0x10000000) AS is_ansi_warnings_on, -- DBR_ANSIWARNINGS sysconv(bit, d.status2 & 0x1000) AS is_arithabort_on, -- DBR_ARITHABORT sysconv(bit, d.status2 & 0x10000) AS is_concat_null_yields_null_on, -- DBR_CATNULL sysconv(bit, d.status2 & 0x800) AS is_numeric_roundabort_on, -- DBR_NUMEABORT sysconv(bit, d.status2 & 0x800000) AS is_quoted_identifier_on, -- DBR_QUOTEDIDENT sysconv(bit, d.status2 & 0x20000) AS is_recursive_triggers_on, -- DBR_RECURTRIG sysconv(bit, d.status2 & 0x2000000) AS is_cursor_close_on_commit_on, -- DBR_CURSCLOSEONCOM sysconv(bit, d.status2 & 0x100000) AS is_local_cursor_default, -- DBR_DEFLOCALCURS sysconv(bit, d.status2 & 0x20000000) AS is_fulltext_enabled, -- DBR_FTENABLED sysconv(bit, d.status2 & 0x200) AS is_trustworthy_on, -- DBR_TRUSTWORTHY sysconv(bit, d.status2 & 0x400) AS is_db_chaining_on, -- DBR_DBCHAINING sysconv(bit, d.status2 & 0x08000000) AS is_parameterization_forced, -- DBR_UNIVERSALAUTOPARAM sysconv(bit, d.status2 & 64) AS is_master_key_encrypted_by_server, -- DBR_MASTKEY sysconv(bit, d.category & 1) AS is_published, sysconv(bit, d.category & 2) AS is_subscribed, sysconv(bit, d.category & 4) AS is_merge_published, sysconv(bit, d.category & 16) AS is_distributor, sysconv(bit, d.category & 32) AS is_sync_with_backup, d.svcbrkrguid AS service_broker_guid, sysconv(bit, CASE WHEN d.scope = 0 THEN 1 ELSE 0 END) AS is_broker_enabled, p.log_reuse_wait, lr.name AS log_reuse_wait_desc, sysconv(bit, d.status2 & 4) AS is_date_correlation_on, -- DBR_DATECORRELATIONOPT sysconv(bit, d.category & 64) AS is_cdc_enabled, sysconv(bit, d.status2 & 0x100) AS is_encrypted, -- DBR_ENCRYPTION CONVERT(bit, d.status2 & 0x8) AS is_honor_broker_priority_on, -- DBR_HONORBRKPRI sgr.guid AS replica_id, sgr2.guid AS group_database_id, default_language_lcid = CASE WHEN ((d.status2 & 0x80000)=0x80000 AND p.containment = 1) THEN CONVERT(smallint, p.default_language) ELSE NULL END, default_language_name = CASE WHEN ((d.status2 & 0x80000)=0x80000 AND p.containment = 1) THEN CONVERT(SYSNAME, sld.name) ELSE NULL END, default_fulltext_language_lcid = CASE WHEN ((d.status2 & 0x80000)=0x800000 AND p.containment = 1) THEN CONVERT(INT, p.default_fulltext_language) ELSE NULL END, default_fulltext_language_name = CASE WHEN ((d.status2 & 0x80000)=0x80000 AND p.containment = 1) THEN CONVERT(SYSNAME, slft.name) ELSE NULL END, is_nested_triggers_on = CASE WHEN ((d.status2 & 0x80000)=0x80000 AND p.containment = 1) THEN CONVERT(bit, p.allow_nested_triggers) ELSE NULL END, is_transform_noise_words_on = CASE WHEN ((d.status2 & 0x80000)=0x80000 AND p.containment = 1) THEN CONVERT(bit, p.transform_noise_words) ELSE NULL END, two_digit_year_cutoff = CASE WHEN ((d.status2 & 0x80000)=0x80000 AND p.containment = 1) THEN CONVERT(smallint, p.two_digit_year_cutoff) ELSE NULL END, containment = sysconv(tinyint, (d.status2 & 0x80000)/0x80000), -- DBR_IS_CDB containment_desc = CONVERT(NVARCHAR(60), cdb.name) FROM master.sys.sysdbreg d OUTER APPLY OPENROWSET(TABLE DBPROP, d.id) p LEFT JOIN sys.syssingleobjrefs r ON r.depid = d.id AND r.class = 96 AND r.depsubid = 0 -- SRC_VIEWPOINTDB LEFT JOIN sys.syspalvalues st ON st.class = 'DBST' AND st.value = p.state LEFT JOIN sys.syspalvalues ua ON ua.class = 'DBUA' AND ua.value = p.user_access LEFT JOIN sys.syspalvalues si ON si.class = 'DBSI' AND si.value = p.snapshot_isolation_state LEFT JOIN sys.syspalvalues ro ON ro.class = 'DBRO' AND ro.value = p.recovery_model LEFT JOIN sys.syspalvalues pv ON pv.class = 'DBPV' AND pv.value = p.page_verify_option LEFT JOIN sys.syspalvalues lr ON lr.class = 'LRWT' AND lr.value = p.log_reuse_wait LEFT JOIN sys.syssingleobjrefs agdb ON agdb.depid = d.id AND agdb.class = 104 AND agdb.depsubid = 0 -- SRC_AVAILABILITYGROUP LEFT JOIN master.sys.sysclsobjs ag ON ag.id = agdb.indepid AND ag.class = 67 -- SOC_AVAILABILITY_GROUP LEFT JOIN master.sys.sysguidrefs sgr ON sgr.class = 8 AND sgr.id = ag.id AND sgr.subid = 1 -- GRC_AGGUID / AGGUID_REPLICA_ID LEFT JOIN master.sys.sysguidrefs sgr2 ON sgr2.class = 9 AND sgr2.id = ag.id AND sgr2.subid = d.id -- GRC_AGDBGUID LEFT JOIN sys.syspalvalues cdb ON cdb.class = 'DCDB' AND cdb.value = CASE WHEN (d.status2 & 0x80000)=0x80000 THEN 1 ELSE 0 END LEFT JOIN sys.syslanguages sld ON sld.lcid = p.default_language LEFT JOIN sys.fulltext_languages slft ON slft.lcid = p.default_fulltext_language WHERE d.id < 0x7fff AND has_access('DB', d.id) = 1
But of course you can't dig much deeper than this. If you try and see into objects like sys.singleobjectrefs and sys.sysdbreg, you get the SQL Server equivalent to a 404 Not Found error. This is even true if you use an old hack to view the system resource db directly (which I've described before).
So there isn't all that much value in this after all, unless you're an avid explorer. For me, I like to dig in as far as I can to see how things work, but I get pretty aggravated when I see syntax that works in system objects, but that we can't use ourselves (such as "hidden" OPENROWSET() calls) or things that seem custom for no obvious reason, such as the sysconv() function – why don't they just use CONVERT()?
More importantly, I have a guilty pleasure in seeing bad practices in practice (or at least inconsistency). Notice in the above code that the new columns for database containment properties have been added using "alias = expression" syntax, whereas the existing code had exclusively used "expression as alias" notation. They also should be using statement terminators, if for no other reason than to demonstrate that they really mean it when they say they will be required in some future version of SQL Server (see this rant from last year).
There are other things that irk me too – lack of AS for table/view aliases, use of LEFT JOIN instead of LEFT OUTER JOIN, inconsistent spacing, sometimes putting more than one output column on the same line, sloppy indenting and alignment… it surprises me when my own code quality and consistency standards would reject just about every module that ships in SQL Server. It might take 5 extra minutes to polish this view up, and if I were responsible for it, I would simply squeeze it into my deadline. Of course I don't have much insight into the Microsoft development cycle, but I do know that a lot of – let's say – "less than stellar" code comes out of those offices. The stuff above is pretty tame in comparison.
Probably the most important thing *YOU* should be aware of: there are extra columns here, so if you are currently building #temp tables or auditing scripts with the previous schema of sys.databases in mind, you'll need to be prepared for this change so that your existing scripts don't break.
No, that's me, I keep forgetting about the "other" DAC. DUH! <…off to play…>
Hi Aaron
Thanks for the great couple of posts about Denali metadata… I am waiting until I get back home after my 5 months in Boston to install Denali.
I'm wondering if I'm missing something when you mention using the resource db to see sys.sysdbreg, etc. Is this another change in Denali? In previous versions I can just use the DAC to see the contents of the system tables.
~Kalen