Search results matching tags 'denali' and 'SQL Server 2011'http://sqlblog.com/search/SearchResults.aspx?o=DateDescending&tag=denali,SQL+Server+2011&orTags=0Search results matching tags 'denali' and 'SQL Server 2011'en-USCommunityServer 2.1 SP2 (Build: 61129.1)Connect Digest : 2011-05-02http://sqlblog.com/blogs/aaron_bertrand/archive/2011/05/02/connect-digest-2011-05-02.aspxMon, 02 May 2011 11:15:00 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:35217AaronBertrand<p>This week, after seeing a lot of the DMV enhancements made in <a href="http://sqlblog.com/blogs/aaron_bertrand/archive/2011/04/25/more-changes-you-might-not-have-noticed-in-the-sql-server-2008-r2-sp1-ctp.aspx" title="http://sqlblog.com/blogs/aaron_bertrand/archive/2011/04/25/more-changes-you-might-not-have-noticed-in-the-sql-server-2008-r2-sp1-ctp.aspx" target="_blank">SQL Server 2008 R2 SP1</a>, I thought I would take a look at some dynamic management view/function requests.</p><p><br><b>Page split personality</b> <br></p><blockquote><p>Michael Zilberstein <a href="http://sqlblog.com/blogs/michael_zilberstein/archive/2011/04/25/35175.aspx" title="http://sqlblog.com/blogs/michael_zilberstein/archive/2011/04/25/35175.aspx" target="_blank">blogged on Monday</a> about how Extended Events and the transaction log differ in the way they report page split information, and that it would be useful to have accurate information about these events available in the DMVs. So he filed this Connect item:<br></p><blockquote><p><a href="http://connect.microsoft.com/SQLServer/feedback/details/664900/please-add-page-splits-data-per-index-index-level-and-page-split-type" title="http://connect.microsoft.com/SQLServer/feedback/details/664900/please-add-page-splits-data-per-index-index-level-and-page-split-type" target="_blank">#664900 : Please add page splits data per index, index level and page split type</a></p></blockquote><p>I asked for a very similar item back in 2008, but thought that it belonged in sys.dm_db_index_physical_stats:</p><blockquote><p><a href="http://connect.microsoft.com/SQLServer/feedback/details/388403/include-page-split-information-in-sys-dm-db-index-physical-stats" title="http://connect.microsoft.com/SQLServer/feedback/details/388403/include-page-split-information-in-sys-dm-db-index-physical-stats" target="_blank">#388403 : Include page split information in sys.dm_db_index_physical_stats</a>&nbsp; </p></blockquote><p>They've already provided additional info via Extended Events (see <a href="http://connect.microsoft.com/SQLServer/feedback/details/388482/sql-server-extended-events-page-split-event-additions" title="http://connect.microsoft.com/SQLServer/feedback/details/388482/sql-server-extended-events-page-split-event-additions" target="_blank">#388482 : SQL Server Extended Events Page/Split Event Additions</a>) so, hopefully, they will take our DMV requests seriously as well.</p></blockquote><p><br><b>How stale is my database?</b></p><blockquote><p>Kimberly Tripp (<a href="http://www.sqlskills.com/blogs/kimberly/" title="http://www.sqlskills.com/blogs/kimberly/" target="_blank">blog</a> | <a href="http://twitter.com/KimberlyLTripp" title="http://twitter.com/KimberlyLTripp" target="_blank">twitter</a>) posted this item, asking for a column in sys.databases to reflect last accessed time (I think it belongs in an existing or new DMV, not in the catalog view). An additional comment on the item suggested a terrific enhancement to this potential feature, where each individual login would be represented with their last access time, making it easy to differentiate when a database is only being accessed by system or background processes.</p><blockquote><p><a href="http://connect.microsoft.com/SQLServer/feedback/details/659846/database-last-accessed-time" title="http://connect.microsoft.com/SQLServer/feedback/details/659846/database-last-accessed-time" target="_blank">#659846 : Database last accessed time</a></p></blockquote><p>They've recently rejected a similar item requesting last accessed time for objects within a database (see <a href="http://connect.microsoft.com/SQLServer/feedback/details/454714/i-should-be-able-to-see-the-last-time-an-object-was-accessed-in-sysobjects" title="http://connect.microsoft.com/SQLServer/feedback/details/454714/i-should-be-able-to-see-the-last-time-an-object-was-accessed-in-sysobjects" target="_blank">#454714 : I should be able to see the last time an object was accessed in sysobjects</a>), though that one had zero votes, so I'm hopeful that Kimberly's request may be deemed both easier to implement and more important to the community. <br></p></blockquote><p><br><b>I'm an E7-8800, what are you?</b><br></p><blockquote>Glenn Alan Berry (<a href="http://sqlserverperformance.wordpress.com/" title="http://sqlserverperformance.wordpress.com/" target="_blank">blog</a> | <a href="http://twitter.com/GlennAlanBerry" title="http://twitter.com/GlennAlanBerry" target="_blank">twitter</a>) asked for an additional column on the sys.dm_os_sys_info DMV that reflects the CPU's description (e.g. Xeon X5505). As you may have noticed on his blog, Glenn spends a lot of effort on analyzing performance of workloads on different CPUs, and there are some pretty substantial differences between CPUs that may look quite similar to the average person - especially when seeing vastly different performance on two servers that are otherwise very similar (same amount of RAM, same SAN, etc).<br><blockquote><p><a href="http://connect.microsoft.com/SQLServer/feedback/details/657435/add-a-processor-description-column-to-sys-dm-os-sys-info-in-sql-server-denali" title="http://connect.microsoft.com/SQLServer/feedback/details/657435/add-a-processor-description-column-to-sys-dm-os-sys-info-in-sql-server-denali" target="_blank">#657435 : Add a processor_description column to sys.dm_os_sys_info in SQL Server Denali</a><br></p></blockquote><p>An additional comment requested that the true observed speed would be shown, as opposed (or in addition) to the manufacturer's stated clock speed - the true speed is a number than can vary greatly if you are using power saving options.<br></p></blockquote><p><br><b>You're a word, but are you reserved?</b></p><blockquote><p>This almost certainly wouldn't be in a DMV, but I still like the idea and it almost fits here. Greg Low (<a href="http://sqlblog.com/blogs/greg_low/default.aspx" title="http://sqlblog.com/blogs/greg_low/default.aspx" target="_blank">blog</a> | <a href="http://twitter.com/greglow" title="http://twitter.com/greglow" target="_blank">twitter</a>) is requesting a system view of some kind that exposes a list of reserved words - my comment here was that relying on IntelliSense alone is a fool's errand.</p><blockquote><p><a href="http://connect.microsoft.com/SQLServer/feedback/details/653455/system-view-that-lists-reserved-words" title="http://connect.microsoft.com/SQLServer/feedback/details/653455/system-view-that-lists-reserved-words" target="_blank">#653455 : System View that lists Reserved Words</a>&nbsp; <br></p></blockquote><p>On a somewhat-but-not-really-related note, Meredith Ryan-Smith (<a href="http://meredithryansmith.wordpress.com/" title="http://meredithryansmith.wordpress.com/" target="_blank">blog</a> | <a href="http://twitter.com/coffegrl" title="http://twitter.com/coffegrl" target="_blank">twitter</a>) recently <a href="http://meredithryansmith.wordpress.com/2011/04/25/of-course-thats-how-you-spell-sp_msacquireheadofqueuelock/" title="http://meredithryansmith.wordpress.com/2011/04/25/of-course-thats-how-you-spell-sp_msacquireheadofqueuelock/" target="_blank">blogged about a workaround</a> she used to export all of the object names from sys.all_objects into a custom dictionary for use in Word, PowerPoint, etc. to avoid the ugly red squiggly lines pointing out that you misspelled sp_server_diagnostics and sp_who2. I think that if the metadata Greg is asking for were made available, then this idea would be easy to extend to reserved words, so that Word also wouldn't try to convince you that perfectly valid "words" like COLUMNSTORE and DATA_COMPRESSION are spelled wrong.</p></blockquote><p><br><b>Do you really like DBCC?</b><br></p><blockquote><p>Greg Low also requested that the information returned by running DBCC SHOW_STATISTICS be made available through a DMV (another user suggested <a href="http://connect.microsoft.com/SQLServer/feedback/details/475270/add-column-to-sys-stats-to-indicate-if-the-optimizer-considers-statistics-out-of-date-invalid" title="http://connect.microsoft.com/SQLServer/feedback/details/475270/add-column-to-sys-stats-to-indicate-if-the-optimizer-considers-statistics-out-of-date-invalid" target="_blank">adding a column like is_stale to sys.stats</a>, but I think a separate DMV makes more sense).<br></p><blockquote><p> <a href="http://connect.microsoft.com/SQLServer/feedback/details/611155/dbcc-show-statistics-info-should-be-available-as-a-dmv" title="http://connect.microsoft.com/SQLServer/feedback/details/611155/dbcc-show-statistics-info-should-be-available-as-a-dmv" target="_blank">#611155 : DBCC SHOW_STATISTICS info should be available as a DMV</a></p></blockquote><p>Exposing this information in a DMV would also, presumably, make it easier to access information like STATS_DATE without having to be in the context of that database (see <a href="http://connect.microsoft.com/SQLServer/feedback/details/654972/stats-date-only-works-as-documented-when-current-db-is-where-the-tables-are" title="http://connect.microsoft.com/SQLServer/feedback/details/654972/stats-date-only-works-as-documented-when-current-db-is-where-the-tables-are" target="_blank">#654972 : STATS_DATE only works as documented when current db is where the tables are</a>).</p><p>&nbsp; <br></p></blockquote>More changes you might not have noticed in SQL Server 2008 R2 SP1 CTPhttp://sqlblog.com/blogs/aaron_bertrand/archive/2011/04/25/more-changes-you-might-not-have-noticed-in-the-sql-server-2008-r2-sp1-ctp.aspxMon, 25 Apr 2011 14:28:00 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:35141AaronBertrand<p>On Friday,&nbsp;after Microsoft released the CTP,&nbsp;I talked a little bit about <a href="http://sqlblog.com/blogs/aaron_bertrand/archive/2011/04/22/sql-server-2008-r2-sp1-ctp-is-now-available.aspx" title="http://sqlblog.com/blogs/aaron_bertrand/archive/2011/04/22/sql-server-2008-r2-sp1-ctp-is-now-available.aspx" target="_blank">the changes you will see in SQL Server 2008 R2 SP1</a>. Namely:</p>
<ul>
<li>new rowcount-related columns in sys.dm_exec_query_stats;</li>
<li>extended functionality of the FORCESEEK query hint;</li>
<li>a new FORCESCAN query hint;</li>
<li>support for 15,000 partitions (to match new functionality added in SQL Server 2008 SP2);</li>
<li>a new DAC framework for in-place upgrades; and,<br></li>
<li>a new disk space control in PowerPivot for SharePoint.<br></li>
</ul>
<p>Since then, I have been digging a little deeper, as the release notes are pretty vague about the changes, and the Books Online updates have not been published yet - at least not that I have seen (and I don't expect them to be published until after the service pack is RTM).</p>
<p>In addition to the new columns in sys.dm_exec_query_stats, there are some other DMVs that have been added or changed in comparison to the RTM release of SQL Server 2008 R2, and some new Extended Events objects as well. I'll also note below each item description whether the change is present in Denali (initially I thought there would be more, but there is only one case where this has happened for DMVs in CTP1).<br></p>
<p><br><b>New system objects:</b></p>
<blockquote>
<p><img src="http://bertrandaaron.files.wordpress.com/2011/04/r2sp1_new_dmvs.png" border="1" height="151" width="402">&nbsp;</p>
</blockquote>
<p><br><b>All columns for new DMVs, and new columns on existing DMVs:</b> </p>
<blockquote>
<p><img src="http://bertrandaaron.files.wordpress.com/2011/04/r2sp1_newdmvcolumns.png" border="1" height="531" width="468">&nbsp;</p>
</blockquote>
<p><b><br><font color="#000000">sys.dm_os_volume_stats</font></b></p>
<blockquote>
<p>This new Dynamic Management Function provides you with information about the disk subsystem where your database files live. It takes the parameters @DatabaseID and @FileID, making it easy to CROSS APPLY against sys.sysalfiles to get all kinds of information about all of the databases across your instance. You can see free vs. available space, whether the drive supports compression / alternate streams / sparse files, and whether the drive is compressed or read only:</p>
<blockquote>
<table bgcolor="#eeeeee" cellpadding="0" cellspacing="0">
<tr>
<td>
<pre style="padding:10px 20px;font-size:12px;font-family:consolas,lucida console,courier new,courier;-moz-background-inline-policy:continuous;"><font color="blue">SELECT </font><br><font color="blue">&nbsp;&nbsp; </font><font color="black">vs.volume_mount_point</font><font color="gray">, </font><font color="green">-- e.g. C:\</font><br><font color="green">&nbsp;&nbsp; </font><font color="black">vs.volume_id</font><font color="gray">,</font><br><font color="gray">&nbsp;&nbsp; </font><font color="black">vs.logical_volume_name</font><font color="gray">,</font><br><font color="gray">&nbsp;&nbsp; </font><font color="black">vs.file_system_type</font><font color="gray">,</font><font color="green"> -- e.g. NTFS</font><br><font color="green">&nbsp;&nbsp; </font><font color="black">db =</font><font color="blue"> </font><font color="magenta">DB_NAME</font><font color="gray">(</font><font color="black">f.dbid</font><font color="gray">), </font><br><font color="gray">&nbsp;&nbsp; </font><font color="black">[file] =</font><font color="blue"> </font><font color="black">f.[name]</font><font color="gray">,</font><br><font color="gray">&nbsp;&nbsp; </font><font color="black">f.[filename]</font><font color="gray">,</font><br><font color="gray">&nbsp;&nbsp; </font><font color="black">file_size_MB =</font><font color="blue"> </font><font color="black">f.[size] </font><font color="gray">/ </font><font color="black">128</font><font color="gray">,</font><br><font color="gray">&nbsp;&nbsp; </font><font color="black">drive_size_MB =</font><font color="blue"> </font><font color="black">vs.total_bytes</font><font color="gray">/</font><font color="black">1024</font><font color="gray">/</font><font color="black">1024</font><font color="gray">, </font><br><font color="gray">&nbsp;&nbsp; </font><font color="black">drive_free_space_MB =</font><font color="blue"> </font><font color="black">vs.available_bytes</font><font color="gray">/</font><font color="black">1024</font><font color="gray">/</font><font color="black">1024</font><font color="gray">, </font><br><font color="gray">&nbsp;&nbsp; </font><font color="black">drive_percent_free =</font><font color="blue"> </font><font color="magenta">CONVERT</font><font color="gray">(</font><font color="blue">DECIMAL</font><font color="gray">(</font><font color="black">5</font><font color="gray">,</font><font color="black">2</font><font color="gray">), </font><font color="black">vs.available_bytes </font><font color="gray">* </font><font color="black">100.0 </font><font color="gray">/ </font><font color="black">vs.total_bytes</font><font color="gray">),</font><br><font color="gray">&nbsp;&nbsp; </font><font color="black">vs.supports_compression</font><font color="gray">, </font><br><font color="gray">&nbsp;&nbsp; </font><font color="black">vs.supports_alternate_streams</font><font color="gray">, </font><br><font color="gray">&nbsp;&nbsp; </font><font color="black">vs.supports_sparse_files</font><font color="gray">, </font><br><font color="gray">&nbsp;&nbsp; </font><font color="black">vs.is_read_only</font><font color="gray">, </font><br><font color="gray">&nbsp;&nbsp; </font><font color="black">vs.is_compressed</font><br><font color="blue">FROM</font><br><font color="blue">&nbsp;&nbsp; </font><font><font color="green">sys</font></font><font color="black">.</font><font><font color="green">sysaltfiles</font></font><font color="black"> </font><font color="blue">AS </font><font color="black">f</font><br><font><font color="blue">CROSS APPLY</font></font><br><font color="black">&nbsp;&nbsp; </font><font><font color="green">sys</font></font><font color="black">.</font><font><font color="green">dm_os_volume_stats</font></font><font color="gray">(</font><font color="black">f.dbid</font><font color="gray">, </font><font color="black">f.fileid</font><font color="gray">) </font><font color="blue">AS </font><font color="black">vs</font><br><font color="blue">WHERE</font><br><font color="blue">&nbsp;&nbsp; </font><font color="black">f.dbid </font><font color="gray">&lt; </font><font color="black">32767</font><br><font color="blue">ORDER BY</font><br><font color="blue">&nbsp;&nbsp; </font><font color="black">drive_percent_free </font><font color="blue">DESC</font><font color="gray">,</font><br><font color="gray">&nbsp;&nbsp; </font><font color="black">db</font><font color="gray">,</font><br><font color="gray">&nbsp;&nbsp; </font><font color="black">[file]</font><font color="gray">;</font></pre></td>
</tr>
</table>
</blockquote>
<p>This is much more powerful than the undocumented and unsupported extended procedure xp_fixeddrives, and I assume - though I haven't tested yet - that it will see things like external hard drives, mount points and other specialty devices. Granted, with or without the usage of sys.sysaltfiles, it is only going to tell you about volumes where you actually have at least one database file.<br></p>
<p><font size="2"><i>* Not yet in Denali. </i></font><br></p>
</blockquote>
<p><br><b>sys.sp_db_increased_partitions</b></p>
<blockquote>
<p>This stored procedure was first introduced in SQL Server 2008 SP2, and enables you to create up to 15,000 partitions on a single table (before this change, the maximum was 1,000 partitions). The reason for the change is that some big customers wanted a partition per day, and the built-in limit of 1,000 meant they couldn't even reach three years of data using that structure. The stored procedure takes two parameters: the database name (@dbname sysname) and whether to turn the setting on or off (@increased_partitions varchar(6)). The latter parameter can take any of the following values: 'on', 'off', 'true' or 'false' - I recommend using lower case to be sure the parameters are not misinterpreted on a binary or case-sensitive collation. As an example, to turn on and then off increased partition support for the AdventureWorks database, you can run the following code:
</p>
<blockquote>
<table bgcolor="#eeeeee" cellpadding="0" cellspacing="0">
<tr>
<td>
<pre style="padding:10px 20px;font-size:12px;font-family:consolas,lucida console,courier new,courier;-moz-background-inline-policy:continuous;"><font color="blue">EXEC </font><font><font color="green">sys</font></font><font color="black">.</font><font color="darkred">sp_db_increased_partitions </font><font color="#434343"><br> @dbname =</font><font color="blue"> </font><font color="red">N'AdventureWorks'</font><font color="gray">, </font><font color="#434343"><br> @increased_partitions =</font><font color="blue"> </font><font color="red">'on'</font><font color="gray">;<br></font><font color="blue"><br>EXEC </font><font><font color="green">sys</font></font><font color="black">.</font><font color="darkred">sp_db_increased_partitions <br> </font><font color="#434343">@dbname =</font><font color="blue"> </font><font color="red">N'AdventureWorks'</font><font color="gray">, </font><font color="#434343"><br> @increased_partitions =</font><font color="blue"> </font><font color="red">'off'</font><font color="gray">;<br></font><font color="green"><br>-- or<br><br></font><font color="blue">EXEC </font><font><font color="green">sys</font></font><font color="black">.</font><font color="darkred">sp_db_increased_partitions </font><font color="#434343"><br> @dbname =</font><font color="blue"> </font><font color="red">N'AdventureWorks'</font><font color="gray">, <br> </font><font color="#434343">@increased_partitions =</font><font color="blue"> </font><font color="red">'true'</font><font color="gray">;<br><br></font><font color="blue">EXEC </font><font><font color="green">sys</font></font><font color="black">.</font><font color="darkred">sp_db_increased_partitions </font><font color="#434343"><br> @dbname =</font><font color="blue"> </font><font color="red">N'AdventureWorks'</font><font color="gray">, </font><font color="#434343"><br> @increased_partitions =</font><font color="blue"> </font><font color="red">'false'</font><font color="gray">;</font></pre></td>
</tr>
</table>
</blockquote>
<p><font size="2"><i>* Not yet in Denali.</i></font><br></p>
</blockquote>
<p><br><b>sys.sp_MScheckIsPubOfSub</b></p>
<blockquote>
<p>I assume this stored procedure is not meant for public consumption, but is probably used by other internal stored procedures involving replication. From the comments in the stored procedure: <br></p>
<blockquote>
<table bgcolor="#eeeeee" cellpadding="0" cellspacing="0">
<tr>
<td>
<pre style="padding:10px 20px;font-size:12px;font-family:consolas,lucida console,courier new,courier;-moz-background-inline-policy:continuous;color:green;">/*<br>&nbsp; *&nbsp; This proc will discover if the specified subid is a subscriber to the specified pubid.<br>&nbsp; *&nbsp; Currently, this is a one level check, it does not support n-level re-publishing.<br>*/</pre></td>
</tr>
</table>
</blockquote>
<p>The procedure takes two uniqueidentifer input parameters (@pubid and @subid), and returns a single output parameter (@pubOfSub bit).</p>
<p><font size="2"><i>* Not yet in Denali. </i></font><br></p>
</blockquote>
<p><br><b>sys.dm_os_windows_info</b></p>
<blockquote>
<p>This new DMV complements sys.dm_os_sys_info with a bit of information about the underlying Windows operating system - version and service pack information, sku and language.
</p>
<blockquote>
<table bgcolor="#eeeeee" cellpadding="0" cellspacing="0">
<tr>
<td>
<pre style="padding:10px 20px;font-size:12px;font-family:consolas,lucida console,courier new,courier;-moz-background-inline-policy:continuous;"><font color="blue">SELECT<br>&nbsp;&nbsp; </font><font color="black">windows_release</font><font color="gray">,<br>&nbsp;&nbsp; </font><font color="black">windows_service_pack_level</font><font color="gray">,<br>&nbsp;&nbsp; </font><font color="black">windows_sku</font><font color="gray">,<br>&nbsp;&nbsp; </font><font color="black">os_language_version<br></font><font color="blue">FROM<br>&nbsp;&nbsp; </font><font><font color="green">sys</font></font><font color="black">.<font color="green">d</font></font><font><font color="green">m_os_windows_info</font></font><font color="gray">;</font></pre></td>
</tr>
</table>
</blockquote>
On my system (US English Windows 7 Ultimate SP1), the following results were returned (the only other windows_sku value I know of for this DMV so far is 28, for Windows 7 Ultimate N):
<blockquote>
<p><img src="http://bertrandaaron.files.wordpress.com/2011/04/r2sp1_wininfo.png" border="1" height="37" width="483">&nbsp;</p>
</blockquote>
<p><font size="2"><i>* Not yet in Denali.</i></font><br> <br></p>
</blockquote>
<p><b>sys.dm_server_memory_dumps</b></p>
<blockquote>
<p>Thankfully I have only had to look into memory dumps a couple of times, but this new DMV will give you a row for each dump that has been generated, including the name of the file, when it was created, and how big it is:</p>
</blockquote>
<blockquote>
<blockquote>
<table bgcolor="#eeeeee" cellpadding="0" cellspacing="0">
<tr>
<td>
<pre style="padding:10px 20px;font-size:12px;font-family:consolas,lucida console,courier new,courier;-moz-background-inline-policy:continuous;"><font color="blue">SELECT<br>&nbsp;&nbsp; </font><font color="black">[filename]</font><font color="gray">,<br>&nbsp;&nbsp; </font><font color="black">creation_time</font><font color="gray">,<br>&nbsp;&nbsp; </font><font color="black">size_in_bytes<br></font><font color="blue">FROM<br>&nbsp;&nbsp; </font><font color="green">sys</font><font><font color="black">.</font></font><font color="green">dm_server_memory_dumps;</font></pre></td>
</tr>
</table>
</blockquote>
<p>And also thankfully, this query returns 0 rows on all of the systems I have updated to the CTP of Service Pack 1. Note that, unlike almost every other date/time-related column in every DMV in the system, creation_time is returned as datetimeoffset, not datetime. Just something to keep in mind if you are accessing this data programmatically. </p>
<p><font size="2"><i>* Not yet in Denali.</i></font></p>
</blockquote>
<p><br><b>sys.dm_server_registry</b></p>
<blockquote>
<p>One of the things I've always hated is having to resort to the undocumented and unsupported extended procedure xp_regread. This DMV eliminates the need to use that xp, provided you are after information that is explicitly relevant to the current instance of SQL Server. On this particular instance, I get information about the engine and SQL Server Agent services (including service account names, startup parameters and agent history properties), as well as plenty of network-related information that is very tedious to obtain in current versions (for example, the port(s) in use and whether they are dynamic):</p>
<blockquote>
<table bgcolor="#eeeeee" cellpadding="0" cellspacing="0">
<tr>
<td>
<pre style="padding:10px 20px;font-size:12px;font-family:consolas,lucida console,courier new,courier;-moz-background-inline-policy:continuous;"><font color="blue">SELECT <br>&nbsp;&nbsp; </font><font color="black">registry_key</font><font color="gray">,<br>&nbsp;&nbsp; </font><font color="black">value_name</font><font color="gray">,<br>&nbsp;&nbsp; </font><font color="black">value_data<br></font><font color="blue">FROM<br>&nbsp;&nbsp; </font><font color="green">sys.dm_server_registry;</font></pre></td>
</tr>
</table>
</blockquote>
<p>I'm not going to show all of the data for this specific instance, but here are the distinct registry keys that are returned for a named instance "SQL2008R2" - this should give you a good idea of the information you can get to without having to trawl through the SQL Server logs, Agent property dialogs, or dig into the registry manually:</p>
<blockquote>
<p><img src="http://bertrandaaron.files.wordpress.com/2011/04/r2sp1_reg.png" border="1" height="360" width="704"></p>
</blockquote>
<p><font size="2"><i>* Not yet in Denali.</i></font></p>
<blockquote>
</blockquote>
</blockquote>
<p><br><b>sys.dm_server_services</b></p>
<blockquote>
<p>In SQL Server 2008, sys.dm_os_sys_info was enhanced with the column sqlserver_start_time which shows when the SQL Server service was started. This new DMV has a row for each server service, and includes the startup time as well the current status, the startup type, the current process_id (making it easy to determine the instance you see in perfmon or task manager on a machine with multiple instances), the account the service is running under, and whether the service is clustered (which is returned as an nvarchar(1), for some strange reason). Like the new registry DMV, you can now get to this information programmatically and a whole lot easier than you could do in previous versions. Here is an example from my system, which is only currently running SQL Server and SQL Server Agent (I truncated some column names to get the image to fit into this post):</p>
<blockquote>
<table bgcolor="#eeeeee" cellpadding="0" cellspacing="0">
<tr>
<td>
<pre style="padding:10px 20px;font-size:12px;font-family:consolas,lucida console,courier new,courier;-moz-background-inline-policy:continuous;"><font color="blue">SELECT<br>&nbsp;&nbsp; </font><font color="black">servicename</font><font color="gray">,<br>&nbsp;&nbsp; </font><font color="black">[startup] =</font><font color="blue"> </font><font color="black">startup_type_desc</font><font color="gray">,<br>&nbsp;&nbsp; </font><font color="black">[status] =</font><font color="blue"> </font><font color="black">status_desc</font><font color="gray">,<br>&nbsp;&nbsp; </font><font color="black">pid =</font><font color="blue"> </font><font color="black">process_id</font><font color="gray">,<br>&nbsp;&nbsp; </font><font color="black">last_startup_time</font><font color="gray">,<br>&nbsp;&nbsp; </font><font color="black">service_account</font><font color="gray">,<br>&nbsp;&nbsp; </font><font color="black">[clus] = is_clustered<br></font><font color="blue">FROM<br>&nbsp;&nbsp; </font><font color="green">sys.dm_server_services;<br></font></pre></td>
</tr>
</table>
</blockquote>
<p>Results:</p>
<blockquote><img src="http://bertrandaaron.files.wordpress.com/2011/04/r2sp1_sysa.png" border="1" height="57" width="721"><br></blockquote>
</blockquote>
<blockquote>
<p>There are two things to note about last_startup_time for the core SQL Server service: (1) it is exposed as a datetimeoffset data type, so it includes offset information from UTC, and (2) it may be a second or two off from the sqlserver_start_time value in sys.dm_os_sys_info. I don't have an explanation for the latter, but I did complain about both the missing Agent startup time and the odd data type choices in this DMV (this is not the only questionable data type choice in this DMV's output):</p>
<blockquote>
<p><a href="http://connect.microsoft.com/SQLServer/feedback/details/664557/sys-dm-server-services-last-startup-time-is-always-null-for-sql-agent" title="http://connect.microsoft.com/SQLServer/feedback/details/664557/sys-dm-server-services-last-startup-time-is-always-null-for-sql-agent" target="_blank">#664557 : sys.dm_server_services.last_startup_time is always NULL for SQL Agent</a> <br></p>
</blockquote>
<blockquote>
<p><a href="http://connect.microsoft.com/SQLServer/feedback/details/664559/odd-data-type-choices-in-sys-dm-server-services" title="http://connect.microsoft.com/SQLServer/feedback/details/664559/odd-data-type-choices-in-sys-dm-server-services" target="_blank">#664559 : Odd data type choices in sys.dm_server_services</a> <br></p>
</blockquote>
<p><font size="2"><i>* Not yet in Denali.</i></font></p>
</blockquote>
<p><br><b>sys.dm_exec_query_stats</b></p>
<blockquote>
<p>I talked about this DMV in <a href="http://sqlblog.com/blogs/aaron_bertrand/archive/2011/04/22/sql-server-2008-r2-sp1-ctp-is-now-available.aspx" title="http://sqlblog.com/blogs/aaron_bertrand/archive/2011/04/22/sql-server-2008-r2-sp1-ctp-is-now-available.aspx" target="_blank">my previous post</a>; it is not a new DMV, but it gets some shiny new columns. Essentially, there are four new columns showing row count statistics for each query exposed by the view: total_rows, last_rows, min_rows and max_rows. This enhances the information you have about each query if you are want to know if it has returned a vastly different number of rows over time.</p>
<p><font size="2"><i>* Not yet in Denali.</i></font> <br></p>
</blockquote>
<p><br><b>sys.dm_os_sys_info</b></p>
<blockquote>
<p>This DMV is also not new, but it has a couple of new columns dedicated to virtualization, so you can tell programmatically, for example, if the SQL Server instance is on a virtualized or physical host. The new columns are virtual_machine_type and virtual_machine_type_desc. I don't have a full mapping of the possible values yet; since I only have 2008 R2 running on virtual machines, I can only show you the values in that situation:</p>
<blockquote>
<table bgcolor="#eeeeee" cellpadding="0" cellspacing="0">
<tr>
<td>
<pre style="padding:10px 20px;font-size:12px;font-family:consolas,lucida console,courier new,courier;-moz-background-inline-policy:continuous;"><font color="blue">SELECT <br>&nbsp;&nbsp; </font><font color="black">virtual_machine_type</font><font color="gray">,<br>&nbsp;&nbsp; </font><font color="black">virtual_machine_type_desc<br></font><font color="blue">FROM<br>&nbsp;&nbsp; </font><font color="green">sys.dm_os_sys_info;<br></font></pre></td>
</tr>
</table>
</blockquote>
<p>Results:</p>
<blockquote><img src="http://bertrandaaron.files.wordpress.com/2011/04/r2sp1_sys.png" border="1" height="37" width="299"><br></blockquote>
</blockquote>
<blockquote>
<p>Note that the machines where I ran these queries are running on Windows 7 virtual machines under VMWare Fusion, so don't always believe that "HYPERVISOR" means Hyper-V explicitly.</p>
<p>Also note: the current Denali BOL documentation for "<a href="http://64.4.11.252/en-us/library/ms143179%28SQL.110%29.aspx" title="http://64.4.11.252/en-us/library/ms143179%28SQL.110%29.aspx" target="_blank">Breaking Changes to Database Engine Features</a>" incorrectly states that these columns were removed.</p>
<p><font size="2"><i>* This DMV is the only one on this page that has already been added/changed in current builds of Denali (CTP1).</i></font><br></p>
</blockquote>
<p><br><b>Extended Events</b></p>
<blockquote><p>Some new xevents were added in SP1 as well. I'm sure Jonathan Kehayias (<a href="http://www.sqlskills.com/blogs/jonathan/" title="http://www.sqlskills.com/blogs/jonathan/" target="_blank">blog</a> | <a href="http://twitter.com/SQLPoolBoy" title="http://twitter.com/SQLPoolBoy" target="_blank">twitter</a>) will talk more about them soon enough, but I can at least identify them - by joining a couple of XE views on the local, SP1 server to a linked server running 2008 R2 RTM: </p>
<blockquote>
<table bgcolor="#eeeeee" cellpadding="0" cellspacing="0">
<tr>
<td>
<pre style="padding:10px 20px;font-size:12px;font-family:consolas,lucida console,courier new,courier;-moz-background-inline-policy:continuous;"><font color="blue">SELECT<br>&nbsp;&nbsp; </font><font color="black">l.name</font><font color="gray">,<br>&nbsp;&nbsp; </font><font color="black">l.map_value<br></font><font color="blue">FROM<br>&nbsp;&nbsp; </font><font color="green">sys</font><font><font color="black">.</font></font><font color="green">dm_xe_map_values </font><font color="blue">AS </font><font color="black">l<br></font><font color="magenta">LEFT </font><font color="gray">OUTER </font><font color="blue">JOIN<br>&nbsp;&nbsp; </font><font color="black">[GREENLANTERN\SQL2008R2RTM].[master]</font><font color="black">.</font><font color="green">sys</font><font><font color="black">.</font></font><font color="green">dm_xe_map_values </font><font color="blue">AS </font><font color="black">r<br>&nbsp;&nbsp; </font><font color="blue">ON </font><font color="black">l.name =</font><font color="blue"> </font><font color="black">r.name<br>&nbsp;&nbsp; </font><font color="gray">AND </font><font color="black">l.map_key =</font><font color="blue"> </font><font color="black">r.map_key<br></font><font color="blue">WHERE<br>&nbsp;&nbsp; </font><font color="black">r.name </font><font color="blue">IS </font><font color="gray">NULL;<br><br></font><font color="blue">SELECT<br>&nbsp;&nbsp; </font><font color="black">l.name</font><font color="gray">,<br>&nbsp;&nbsp; </font><font color="black">l.description<br></font><font color="blue">FROM<br>&nbsp;&nbsp; </font><font color="green">sys</font><font><font color="black">.</font></font><font color="green">dm_xe_objects </font><font color="blue">AS </font><font color="black">l<br></font><font color="magenta">LEFT </font><font color="gray">OUTER </font><font color="blue">JOIN<br>&nbsp;&nbsp; </font><font color="black">[GREENLANTERN\SQL2008R2RTM].[master]</font><font color="black">.</font><font color="green">sys</font><font><font color="black">.</font></font><font color="green">dm_xe_objects </font><font color="blue">AS </font><font color="black">r<br>&nbsp;&nbsp; </font><font color="blue">ON </font><font color="black">l.name =</font><font color="blue"> </font><font color="black">r.name<br></font><font color="blue">WHERE<br>&nbsp;&nbsp; </font><font color="black">r.name </font><font color="blue">IS </font><font color="gray">NULL;<br></font></pre></td>
</tr>
</table>
</blockquote><p>Results:</p><blockquote><p><img src="http://bertrandaaron.files.wordpress.com/2011/04/screen-shot-2011-04-25-at-9-55-45-am.png" border="1" height="218" width="657">&nbsp;</p></blockquote><p><font size="2"><i>* These are partially in Denali CTP1. Well, the first two rows in the first resultset, at least.</i></font><br></p></blockquote><blockquote>
</blockquote>
<p><br><b>Reference queries</b><br></p>
<p>For reference, you can see the queries I used here to perform this Sherlock Holmes-esque work against the DMVs:</p>
<ul>
<li>I talked about how to <a href="http://sqlblog.com/blogs/aaron_bertrand/archive/2010/11/29/sql-server-v-next-denali-new-changed-and-removed-system-objects.aspx" title="http://sqlblog.com/blogs/aaron_bertrand/archive/2010/11/29/sql-server-v-next-denali-new-changed-and-removed-system-objects.aspx" target="_blank">find new DMVs and other system objects</a> in November 2010.</li>
<li>I talked about how to <a href="http://sqlblog.com/blogs/aaron_bertrand/archive/2009/08/11/new-columns-in-sql-server-2008-r2-dmvs.aspx" title="http://sqlblog.com/blogs/aaron_bertrand/archive/2009/08/11/new-columns-in-sql-server-2008-r2-dmvs.aspx" target="_blank">find new and changed DMV columns</a> in August 2009.</li>
</ul>
<p><b><br>Download</b> <br></p>
<p>SP1 CTP for "normal" SKUs (and also a stand-alone download for upgrading client tools only):<br></p>
<blockquote>
<p><a href="http://www.microsoft.com/downloads/en/details.aspx?FamilyID=bd200f8e-ba8a-45e3-af59-e28a9e2d17df" title="http://www.microsoft.com/downloads/en/details.aspx?FamilyID=bd200f8e-ba8a-45e3-af59-e28a9e2d17df" target="_blank">http://www.microsoft.com/downloads/en/details.aspx?FamilyID=bd200f8e-ba8a-45e3-af59-e28a9e2d17df</a></p>
</blockquote>
<p>SP1 CTP for the various Express Editions (Express, Express with Tools, Express with Advanced Services):<br></p>
<blockquote>
<p><a href="http://www.microsoft.com/downloads/en/details.aspx?FamilyID=e70a4b51-53be-48d3-8030-80dc9e755be3" title="http://www.microsoft.com/downloads/en/details.aspx?FamilyID=e70a4b51-53be-48d3-8030-80dc9e755be3" target="_blank">http://www.microsoft.com/downloads/en/details.aspx?FamilyID=e70a4b51-53be-48d3-8030-80dc9e755be3</a> <br></p>
</blockquote>
<p>SP1 CTP Feature Packs (please don't ask me what all of these files are, but <a href="http://koprowskit.eu/geek/2011/04/en-sql-server-2008-r2-sp1-ctp-14-feature-packs-are-available/" title="http://koprowskit.eu/geek/2011/04/en-sql-server-2008-r2-sp1-ctp-14-feature-packs-are-available/" target="_blank">someone else did a pretty good job</a>):<br></p>
<blockquote>
<p><a href="http://www.microsoft.com/downloads/en/details.aspx?FamilyID=15dcfec2-abb8-409d-91ff-c7c8e18c8409" title="http://www.microsoft.com/downloads/en/details.aspx?FamilyID=15dcfec2-abb8-409d-91ff-c7c8e18c8409" target="_blank">http://www.microsoft.com/downloads/en/details.aspx?FamilyID=15dcfec2-abb8-409d-91ff-c7c8e18c8409</a> <br></p>
</blockquote>
<p><br><b>Summary</b> <br></p>
<p>I'm still torn about features being added in service packs, but if you're on this platform, clearly there are some changes that you might be able to take advantage of once the Service Pack 1 is officially RTM. And I have every reason to believe that these changes will be ported to Denali, probably by the time you see the next CTP.</p>
<p>Please remember, though, that this service pack is not going to include fixes from <a href="http://support.microsoft.com/kb/2507770" title="http://support.microsoft.com/kb/2507770" target="_blank">cumulative update #7 for SQL Server 2008 R2</a>, so if you're relying on those fixes, don't install this service pack - it will undo those changes. Also don't install the service pack CTP if you think you might be interested in cumulative update #8, since it will be problematic to downgrade to that CU once the service pack is installed (even the CTP). And who knows when the service pack will be officially released (never mind the subsequent CU that will get it "caught up" with the current branch of RTM fixes).</p><p>Also, please remember that DMV data does not persist through a service
restart. So, while some of these queries will yield relatively static
results (such as those that talk about the operating system or that dig
information out of the registry), things like the rowcount values added
to sys.dm_exec_query_stats are only valid since the time the service was
started. <br></p>
<p>And finally, please don't try, and please don't ask; you cannot install this service pack on SQL Server 2008. This is ONLY for the SQL Server 2008 R2 release. <br></p>
<p>&nbsp; <br></p>SQL Bits 8: &quot;What's New in SQL Server Denali?&quot;http://sqlblog.com/blogs/aaron_bertrand/archive/2011/04/09/sql-bits-8-what-s-new-in-sql-server-denali.aspxSat, 09 Apr 2011 23:25:00 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:34394AaronBertrand<p>At SQL Bits I presented my "What's New in Denali?" deck in two different sessions - once on Friday to about 120 people, and once on Saturday to about 80 people. You can download the deck and samples below.</p><p>&nbsp; <br></p>SQL Saturday #67, Chicago: &quot;What's New in SQL Server Denali&quot;http://sqlblog.com/blogs/aaron_bertrand/archive/2011/03/26/sql-saturday-67-chicago-what-s-new-in-sql-server-denali.aspxSat, 26 Mar 2011 15:01:00 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:34390AaronBertrand<p>Today I was in Chicago for <a href="http://www.sqlsaturday.com/67/eventhome.aspx" title="http://www.sqlsaturday.com/67/eventhome.aspx" target="_blank">SQL Saturday #67</a> to present my "What's New in SQL Server Denali" deck. You can download the deck and samples below.</p><p>I mentioned during the talk that THROW is another command added to the list that require the previous statement to be terminated with a semi-colon. Here is the blog post I referenced that talked more about <a href="http://sqlblog.com/blogs/aaron_bertrand/archive/2009/09/03/ladies-and-gentlemen-start-your-semi-colons.aspx" title="http://sqlblog.com/blogs/aaron_bertrand/archive/2009/09/03/ladies-and-gentlemen-start-your-semi-colons.aspx" target="_blank">why you should start using semi-colons today</a>. </p><p>I liked the feedback mechanism at this event. Everyone was handed an evaluation, and the drawings took place after each session at the chapter table downstairs. Much more compelling to get your evals in right away, and even better, I get them back right after that. Which means I can tally my scores and see what went well, and more importantly, what didn't go well (and wasn't obvious). The evals were much simpler than some other events. Two basic questions and room for comments (not everyone left comments, and not everyone answered both questions):<br></p><p><b>Expectation (circle one): Did Not Meet / Met / Exceeded</b></p><blockquote><p>Met: 23 / 34<br>Exceeded: 11 / 34<br></p></blockquote><p><b>Overall quality (5 = great):</b></p><blockquote><p>3: 4 / 32<br>4: 11 / 32<br>5: 17 / 32<br>Average: <font color="#006600"><b>4.41</b></font> &nbsp; <br></p></blockquote><p>Not top scores, no, but I still feel pretty good about this. What I liked most? Seven comments included one of my brand words, "knowledgeable." Well, a couple used the variation "knowledge" - but that counts, right? <br></p><p>The comment that surprised me most was a complaint that I didn't cover any BI. Even though I stated multiple times at the beginning that I wouldn't be covering BI - and even dedicated my very first "real" slide to this fact. Still, I've taken the feedback. For future events I'm going to change the title of the session to "SQL Server Denali : What's New in Engine, Setup and Tools." </p><p>I also gave a quick demo of SQL Sentry Plan Explorer in our vendor session at lunch (we were a "You Rock!" sponsor of SQL Saturday #67, after all). I didn't use a deck and it was all demos. Plan Explorer is free; if you haven't already tried it, please go to <a href="http://sqlsentry.net/plan-explorer/sql-server-query-view.asp" title="http://sqlsentry.net/plan-explorer/sql-server-query-view.asp" target="_blank">the download page</a> and read my <a href="http://sqlblog.com/search/SearchResults.aspx?q=%22plan+explorer%22&amp;s=18" title="http://sqlblog.com/search/SearchResults.aspx?q=%22plan+explorer%22&amp;s=18" target="_blank">previous blog posts</a> about the tool.<br></p><p>&nbsp; <br></p>Presenting &quot;What's New in SQL Server Denali&quot; for WVPASShttp://sqlblog.com/blogs/aaron_bertrand/archive/2011/03/17/presenting-what-s-new-in-sql-server-denali-for-wvpass.aspxThu, 17 Mar 2011 21:00:00 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:34231AaronBertrand<p>Recently I was asked by John Sterrett (<a href="http://johnsterrett.com/" title="http://johnsterrett.com/" target="_blank">blog</a> | <a href="http://twitter.com/johnsterrett" title="http://twitter.com/johnsterrett" target="_blank">twitter</a>) to present to his SQL Server user group (<a href="http://wvpass.sqlpass.org/" title="http://wvpass.sqlpass.org/" target="_blank">WVPASS</a>) in Wheeling, West Virginia. Well, today is the day, and I will be presenting, "What's new in SQL Server Denali" at tonight's meeting, sponsored by <a href="http://sqlsentry.net" title="http://sqlsentry.net" target="_blank">SQL Sentry</a>. The slide deck is attached below; the samples are available <a href="http://sqlblog.com/files/folders/34238/download.aspx" title="http://sqlblog.com/files/folders/34238/download.aspx" target="_blank">here</a>. </p><p>If you attended this presentation, please feel free to <a href="http://speakerrate.com/talks/6880-what-s-new-in-sql-server-denali" title="http://speakerrate.com/talks/6880-what-s-new-in-sql-server-denali" target="_blank">provide feedback at SpeakerRate</a>. <br></p><p>&nbsp;<br></p>SQL Server v.Next (&quot;Denali&quot;) : How a columnstore index is not like a normal indexhttp://sqlblog.com/blogs/aaron_bertrand/archive/2011/02/28/sql-server-v-next-denali-how-a-columnstore-index-is-not-like-a-normal-index.aspxMon, 28 Feb 2011 17:34:00 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:33810AaronBertrand<p>At the end of my Denali presentation at <a href="http://sqlsaturday.com/65/eventhome.aspx" title="http://sqlsaturday.com/65/eventhome.aspx" target="_blank">SQL Saturday #65</a> in Vancouver, a member of the audience asked, "What makes a columnstore index different from a regular nonclustered index?" At the end of a busy day, I was at a loss for an answer, and I'll explain why.</p><p>First, I'll briefly explain the basic, core, high-level functionality of a columnstore index (you can read a lot more details in <a href="http://bit.ly/AB_Denali_ColumnWP" title="http://bit.ly/AB_Denali_ColumnWP" target="_blank">this white paper</a>). Basically, instead of storing index data together on a page, it divvies up the data from each column into its own set of pages. If you are after the data from only one column, this doesn't provide a great advantage over a traditional index, since you're not going to be able to store that many more rows on a page.<br></p><p>The reason I was having a hard time articulating the benefit is because I was thinking about a simple non-clustered index with one column, and comparing it to a columnstore index with only one column defined. The benefit of the columnstore index is that you can basically declare it against most or all of the columns in a table, effectively similar to creating an index on each column - and this is where column store indexes provide the largest benefit. The differences between a column store index and a handful of indexes for each column are (1) size due to the structure of the index and the fact that compression will likely work much better when you can fit more like values on a page, and (2) you only pay for the index maintenance on a columnstore index once, because of what I'll describe next.<br></p><p>The columnstore index has limitations that definitely make you feel like the grass is greener on the other side. Most importantly, in Denali, due to the cost of maintenance, a columnstore index will be read only... essentially, you will need to rebuild the index when data changes. Some other limitations: only one columnstore index per table, the index must be partition-aligned, can't be filtered, and the base object must be a table (so, no indexed views).<br></p><p>This feature is definitely geared to data warehouse scenarios, or where you are free to rebuild indexes nightly or on some other interval. I can't give you a good idea about the cost of maintaining this index manually compared to multiple traditional indexes, since the feature did not make the cut for the <a href="http://bit.ly/AB_Denali_Download" title="http://bit.ly/AB_Denali_Download" target="_blank">publicly available CTP1</a>. But I hope to publish some blog posts with real numbers on both the read and write sides when the next public CTP is made available. I am fairly certain I will be able to demonstrate cases where this type of index will drastically increase read performance (without losing substantial write performance), provided that you are in a scenario where you can update the data on an infrequent schedule, such as nightly.</p><p>If you want some detailed background on this technology, please check out Dr. David DeWitt's <a href="http://bit.ly/eYzuYD" title="http://bit.ly/eYzuYD" target="_blank">PASS Summit 2009 keynote slide deck</a>. And if you’re a PASS member, you can access the video at <a href="http://www.sqlpass.org/LearningCenter/SessionRecordings/Summit2009/Day3KeynoteDavidDeWitt.aspx" title="http://www.sqlpass.org/LearningCenter/SessionRecordings/Summit2009/Day3KeynoteDavidDeWitt.aspx" target="_blank">sqlpass.org</a> (login required). <br></p><p>[I am currently at the MVP Summit, and I just want to clarify (in case anyone was wondering), none of the material I discuss above is in violation of NDA.] <br></p><p>&nbsp;</p>Slides and samples from SQL Saturday #65 - Vancouverhttp://sqlblog.com/blogs/aaron_bertrand/archive/2011/02/26/slides-and-samples-from-sql-saturday-65-vancouver.aspxSun, 27 Feb 2011 00:53:00 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:33795AaronBertrand<p>Today I presented my "What's New in SQL Server 'Denali'" deck to about 40 attendees at <a href="http://sqlsaturday.com/65/eventhome.aspx" title="http://sqlsaturday.com/65/eventhome.aspx" target="_blank">SQL Saturday #65</a> in Vancouver, British Columbia. Attached below (and <a href="http://sqlblog.com/blogs/aaron_bertrand/attachment/33795.ashx" title="http://sqlblog.com/blogs/aaron_bertrand/attachment/33795.ashx">here</a>) are the slightly updated slides and samples.</p>Slide decks and samples from SQL Saturday #60 - Clevelandhttp://sqlblog.com/blogs/aaron_bertrand/archive/2011/02/05/slide-decks-and-samples-from-sql-saturday-60-cleveland.aspxSat, 05 Feb 2011 22:34:00 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:33178AaronBertrand<p>Attached are the slide decks and demos from my two presentations at <a href="http://www.sqlsaturday.com/60/eventhome.aspx" title="http://www.sqlsaturday.com/60/eventhome.aspx" target="_blank">SQL Saturday #60</a> in Cleveland:</p><ul><li><a href="http://sqlblog.com/files/folders/33222/download.aspx" title="Denali.zip" target="_blank">What's New in SQL Server "Denali"</a></li><li><a href="http://sqlblog.com/files/folders/33223/download.aspx" title="BadHabitsToKick.zip" target="_blank">T-SQL : Bad Habits to Kick</a><br></li></ul><p>&nbsp;</p>SQL Server v.Next (Denali) : Changes to performance countershttp://sqlblog.com/blogs/aaron_bertrand/archive/2011/01/08/sql-server-v-next-denali-changes-to-performance-counters.aspxSat, 08 Jan 2011 17:44:00 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:32435AaronBertrand<p>In a previous post about <a href="http://sqlblog.com/blogs/aaron_bertrand/archive/2010/11/29/sql-server-v-next-denali-new-changed-and-removed-system-objects.aspx" title="http://sqlblog.com/blogs/aaron_bertrand/archive/2010/11/29/sql-server-v-next-denali-new-changed-and-removed-system-objects.aspx" target="_blank">changed system objects in Denali</a>, I talked about the changes to memory-related DMVs due to underlying changes in the memory manager.&nbsp; The SQLOS team has posted a <a href="http://sqlblog.com/blogs/sqlos_team/archive/2011/01/04/sql-server-memory-manager-changes-in-denali.aspx" title="http://sqlblog.com/blogs/sqlos_team/archive/2011/01/04/sql-server-memory-manager-changes-in-denali.aspx" target="_blank">great introduction to these changes</a>, and they plan to post more details in future posts.&nbsp; In the meantime, and due to a question yesterday from Tom LaRock (<a href="http://thomaslarock.com" title="http://thomaslarock.com" target="_blank">blog</a> | <a href="http://twitter.com/SQLRockstar" title="http://twitter.com/SQLRockstar" target="_blank">twitter</a>) ...</p>
<blockquote>
<p>&nbsp;<img src="http://sqlblog.com/files/folders/32434/download.aspx" width="406" height="124"></p>
</blockquote>
<p> ... I thought I would tell you about some performance counters that have changed between SQL Server 2008 R2 and Denali - most of which involve these memory manager changes.&nbsp; In response to Tom, initially I just performed a count and compared, but this isn't very accurate; since many counters are database-specific, the counts depend greatly on how many databases you have installed (as well as edition and features - more on that below).&nbsp; So instead I wanted to query and see the actual differences ... on a 2008 R2 instance, I created a linked server pointing to a Denali instance, then I ran this query:</p>
<blockquote>
<table bgcolor="#eeeeee" cellpadding="0" cellspacing="0">
<tr>
<td>
<pre style="padding:10px 20px;font-size:12px;font-family:consolas,lucida console,courier new,courier;-moz-background-inline-policy:continuous;"><font><font color="gray">;</font></font><font color="blue">WITH </font><font color="black">loc </font><font color="blue">AS<br></font><font color="gray">(<br>&nbsp;&nbsp; </font><font color="blue">SELECT </font><font color="black">obj</font><font color="gray">, </font><font color="black">cn<br>&nbsp;&nbsp; </font><font color="blue">FROM <br>&nbsp;&nbsp; </font><font color="gray">(<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </font><font color="blue">SELECT<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </font><font color="black">obj</font><font><font color="gray"> =</font></font><font color="blue"> </font><font color="magenta">SUBSTRING</font><font color="gray">(</font><font color="black">[object_name]</font><font color="gray">, </font><font><font color="magenta">CHARINDEX</font></font><font color="blue"></font><font color="gray">(</font><font color="red">':'</font><font color="gray">, </font><font color="black">[object_name]</font><font color="gray">) + </font><font color="black">1</font><font color="gray">, </font><font color="black">256</font><font color="gray">), <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </font><font color="black">cn</font><font><font color="gray"> =</font></font><font color="blue"> </font><font color="black">counter_name<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </font><font color="blue">FROM </font><font color="black"><font color="green">sys</font>.<font color="green">dm_os_performance_counters</font><br>&nbsp;&nbsp; </font><font color="gray">) </font><font color="blue">AS </font><font color="black">x<br>&nbsp;&nbsp; </font><font color="blue">GROUP BY </font><font color="black">obj</font><font color="gray">, </font><font color="black">cn<br></font><font color="gray">),<br></font><font color="black">rem </font><font color="blue">AS <br></font><font color="gray">(<br>&nbsp;&nbsp; </font><font color="blue">SELECT </font><font color="black">obj</font><font color="gray">, </font><font color="black">cn<br>&nbsp;&nbsp; </font><font color="blue">FROM <br>&nbsp;&nbsp; </font><font color="gray">(<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </font><font color="blue">SELECT<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </font><font color="black">obj</font><font><font color="gray"> =</font></font><font color="blue"> </font><font color="magenta">SUBSTRING</font><font color="gray">(</font><font color="black">[object_name]</font><font color="gray">, </font><font><font color="magenta">CHARINDEX</font></font><font color="gray">(</font><font color="red">':'</font><font color="gray">, </font><font color="black">[object_name]</font><font color="gray">) + </font><font color="black">1</font><font color="gray">, </font><font color="black">256</font><font color="gray">), <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </font><font color="black">cn</font><font><font color="gray"> =</font></font><font color="blue"> </font><font color="black">counter_name<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </font><font color="blue">FROM </font><font color="black">[Denali_Linked_Server].[master].</font><font color="black"><font color="green">sys</font>.<font color="green">dm_os_performance_counters</font></font><font color="black"><br>&nbsp;&nbsp; </font><font color="gray">) </font><font color="blue">AS </font><font color="black">x<br>&nbsp;&nbsp; </font><font color="blue">GROUP BY </font><font color="black">obj</font><font color="gray">, </font><font color="black">cn<br></font><font color="gray">)<br></font><font color="blue">SELECT <br>&nbsp;&nbsp; </font><font color="black">[2008R2_obj]</font><font><font color="gray"> =</font></font><font color="blue"> </font><font color="black">loc.obj</font><font color="gray">, <br>&nbsp;&nbsp; </font><font color="black">[2008R2_ctr]</font><font><font color="gray"> =</font></font><font color="blue"> </font><font color="black">loc.cn</font><font color="gray">,<br>&nbsp;&nbsp; </font><font color="black">[Denali_obj]</font><font><font color="gray"> =</font></font><font color="blue"> </font><font color="black">rem.obj</font><font color="gray">,<br>&nbsp;&nbsp; </font><font color="black">[Denali_ctr]</font><font><font color="gray"> =</font></font><font color="blue"> </font><font color="black">rem.cn<br></font><font color="blue">FROM<br>&nbsp;&nbsp; </font><font color="black">loc <br></font><font color="gray">FULL OUTER </font><font color="blue">JOIN<br>&nbsp;&nbsp; </font><font color="black">rem <br>&nbsp;&nbsp; </font><font color="blue">ON </font><font color="black">loc.obj</font><font><font color="gray"> =</font></font><font color="blue"> </font><font color="blue"></font><font color="black">rem.obj<br>&nbsp;&nbsp; </font><font color="gray">AND </font><font color="black">loc.cn</font><font><font color="gray"> =</font></font><font color="blue"> </font><font color="blue"></font><font color="black">rem.cn<br></font><font color="blue">WHERE <br>&nbsp;&nbsp; </font><font color="black">loc.obj </font><font color="blue">IS </font><font color="gray">NULL<br>&nbsp;&nbsp; OR </font><font color="black">rem.obj </font><font color="blue">IS </font><font color="gray">NULL<br></font><font color="blue">ORDER BY<br></font><font color="gray">&nbsp;&nbsp; </font><font color="black">[Denali_obj]</font><font color="gray">,<br>&nbsp;&nbsp; </font><font color="black">[Denali_ctr]</font><font><font color="gray">,<br></font></font><font color="blue">&nbsp;&nbsp; </font><font color="black">[2008R2_obj]</font><font color="gray">, <br>&nbsp;&nbsp; </font><font color="black">[2008R2_ctr]</font><font color="black"></font><font color="gray">;</font></pre>
</td>
</tr>
</table>
</blockquote>
<p>The results:<br></p>
<blockquote>
<table style="border-collapse:collapse;" cellpadding="5" cellspacing="0">
<tr>
<th style="border:1px solid black;">2008 R2 Object</th>
<th style="border:1px solid black;">2008 R2 Counter</th>
<th style="border:1px solid black;">Denali Object</th>
<th style="border:1px solid black;">Denali Counter</th>
</tr>
<tr>
<td style="border:1px solid black;">Buffer Manager</td>
<td style="border:1px solid black;">AWE lookup maps/sec</td>
<td style="border:1px solid black;" colspan="2" align="center">&lt;removed in Denali&gt;</td>
</tr>
<tr>
<td style="border:1px solid black;">Buffer Manager</td>
<td style="border:1px solid black;">AWE stolen maps/sec</td>
<td style="border:1px solid black;" colspan="2" align="center">&lt;removed in Denali&gt;</td>
</tr>
<tr>
<td style="border:1px solid black;">Buffer Manager</td>
<td style="border:1px solid black;">AWE unmap calls/sec</td>
<td style="border:1px solid black;" colspan="2" align="center">&lt;removed in Denali&gt;</td>
</tr>
<tr>
<td style="border:1px solid black;">Buffer Manager</td>
<td style="border:1px solid black;">AWE unmap pages/sec</td>
<td style="border:1px solid black;" colspan="2" align="center">&lt;removed in Denali&gt;</td>
</tr>
<tr>
<td style="border:1px solid black;">Buffer Manager</td>
<td style="border:1px solid black;">AWE write maps/sec</td>
<td style="border:1px solid black;" colspan="2" align="center">&lt;removed in Denali&gt;</td>
</tr>
<tr>
<td style="border:1px solid black;">Buffer Manager</td>
<td style="border:1px solid black;">Free pages</td>
<td style="border:1px solid black;" colspan="2" align="center">&lt;removed in Denali&gt;</td>
</tr>
<tr>
<td style="border:1px solid black;">Buffer Manager</td>
<td style="border:1px solid black;">Reserved pages</td>
<td style="border:1px solid black;" colspan="2" align="center">&lt;removed in Denali&gt;</td>
</tr>
<tr>
<td style="border:1px solid black;">Buffer Manager</td>
<td style="border:1px solid black;">Stolen pages</td>
<td style="border:1px solid black;" colspan="2" align="center">&lt;removed in Denali&gt;</td>
</tr>
<tr>
<td style="border:1px solid black;">Buffer Manager</td>
<td style="border:1px solid black;">Target pages</td>
<td style="border:1px solid black;" colspan="2" align="center">&lt;removed in Denali&gt;</td>
</tr>
<tr>
<td style="border:1px solid black;">Buffer Manager</td>
<td style="border:1px solid black;">Total pages</td>
<td style="border:1px solid black;" colspan="2" align="center">&lt;removed in Denali&gt;</td>
</tr>
<tr>
<td style="border:1px solid black;">Buffer Node</td>
<td style="border:1px solid black;">Foreign pages</td>
<td style="border:1px solid black;" colspan="2" align="center">&lt;removed in Denali&gt;</td>
</tr>
<tr>
<td style="border:1px solid black;">Buffer Node</td>
<td style="border:1px solid black;">Free pages</td>
<td style="border:1px solid black;" colspan="2" align="center">&lt;removed in Denali&gt;</td>
</tr>
<tr>
<td style="border:1px solid black;">Buffer Node</td>
<td style="border:1px solid black;">Stolen pages</td>
<td style="border:1px solid black;" colspan="2" align="center">&lt;removed in Denali&gt;</td>
</tr>
<tr>
<td style="border:1px solid black;">Buffer Node</td>
<td style="border:1px solid black;">Target pages</td>
<td style="border:1px solid black;" colspan="2" align="center">&lt;removed in Denali&gt;</td>
</tr>
<tr>
<td style="border:1px solid black;">Buffer Node</td>
<td style="border:1px solid black;">Total pages</td>
<td style="border:1px solid black;" colspan="2" align="center">&lt;removed in Denali&gt;</td>
</tr>
<tr>
<td style="border:1px solid black;">Buffer Partition</td>
<td style="border:1px solid black;">Free list empty/sec</td>
<td style="border:1px solid black;" colspan="2" align="center">&lt;removed in Denali&gt;</td>
</tr>
<tr>
<td style="border:1px solid black;">Buffer Partition</td>
<td style="border:1px solid black;">Free list requests/sec</td>
<td style="border:1px solid black;" colspan="2" align="center">&lt;removed in Denali&gt;</td>
</tr>
<tr>
<td style="border:1px solid black;">Buffer Partition</td>
<td style="border:1px solid black;">Free pages</td>
<td style="border:1px solid black;" colspan="2" align="center">&lt;removed in Denali&gt;</td>
</tr>
<tr>
<td style="border:1px solid black;" colspan="2" align="center">&lt;new in Denali&gt;</td>
<td style="border:1px solid black;">Access Methods</td>
<td style="border:1px solid black;">InSysXact waits/sec</td>
</tr>
<tr>
<td style="border:1px solid black;" colspan="2" align="center">&lt;new in Denali&gt;</td>
<td style="border:1px solid black;">Availability Replica</td>
<td style="border:1px solid black;">Bytes Received from Replica/sec</td>
</tr>
<tr>
<td style="border:1px solid black;" colspan="2" align="center">&lt;new in Denali&gt;</td>
<td style="border:1px solid black;">Availability Replica</td>
<td style="border:1px solid black;">Bytes Sent to Replica/sec</td>
</tr>
<tr>
<td style="border:1px solid black;" colspan="2" align="center">&lt;new in Denali&gt;</td>
<td style="border:1px solid black;">Availability Replica</td>
<td style="border:1px solid black;">Bytes Sent to Transport/sec</td>
</tr>
<tr>
<td style="border:1px solid black;" colspan="2" align="center">&lt;new in Denali&gt;</td>
<td style="border:1px solid black;">Availability Replica</td>
<td style="border:1px solid black;">Flow Control Time (ms/sec)</td>
</tr>
<tr>
<td style="border:1px solid black;" colspan="2" align="center">&lt;new in Denali&gt;</td>
<td style="border:1px solid black;">Availability Replica</td>
<td style="border:1px solid black;">Flow Control/sec</td>
</tr>
<tr>
<td style="border:1px solid black;" colspan="2" align="center">&lt;new in Denali&gt;</td>
<td style="border:1px solid black;">Availability Replica</td>
<td style="border:1px solid black;">Receives from Replica/sec</td>
</tr>
<tr>
<td style="border:1px solid black;" colspan="2" align="center">&lt;new in Denali&gt;</td>
<td style="border:1px solid black;">Availability Replica</td>
<td style="border:1px solid black;">Resent Messages/sec</td>
</tr>
<tr>
<td style="border:1px solid black;" colspan="2" align="center">&lt;new in Denali&gt;</td>
<td style="border:1px solid black;">Availability Replica</td>
<td style="border:1px solid black;">Sends to Replica/sec</td>
</tr>
<tr>
<td style="border:1px solid black;" colspan="2" align="center">&lt;new in Denali&gt;</td>
<td style="border:1px solid black;">Availability Replica</td>
<td style="border:1px solid black;">Sends to Transport/sec</td>
</tr>
<tr>
<td style="border:1px solid black;" colspan="2" align="center">&lt;new in Denali&gt;</td>
<td style="border:1px solid black;">Database Replica</td>
<td style="border:1px solid black;">File Bytes Received/sec</td>
</tr>
<tr>
<td style="border:1px solid black;" colspan="2" align="center">&lt;new in Denali&gt;</td>
<td style="border:1px solid black;">Database Replica</td>
<td style="border:1px solid black;">Log Bytes Received/sec</td>
</tr>
<tr>
<td style="border:1px solid black;" colspan="2" align="center">&lt;new in Denali&gt;</td>
<td style="border:1px solid black;">Database Replica</td>
<td style="border:1px solid black;">Log Send Queue</td>
</tr>
<tr>
<td style="border:1px solid black;" colspan="2" align="center">&lt;new in Denali&gt;</td>
<td style="border:1px solid black;">Database Replica</td>
<td style="border:1px solid black;">Recovery Queue</td>
</tr>
<tr>
<td style="border:1px solid black;" colspan="2" align="center">&lt;new in Denali&gt;</td>
<td style="border:1px solid black;">Database Replica</td>
<td style="border:1px solid black;">Redone Bytes/sec</td>
</tr>
<tr>
<td style="border:1px solid black;" colspan="2" align="center">&lt;new in Denali&gt;</td>
<td style="border:1px solid black;">Databases</td>
<td style="border:1px solid black;">Log Flush Write Time (ms)</td>
</tr>
<tr>
<td style="border:1px solid black;" colspan="2" align="center">&lt;new in Denali&gt;</td>
<td style="border:1px solid black;">Memory Manager</td>
<td style="border:1px solid black;">Database Cache Memory (KB)</td>
</tr>
<tr>
<td style="border:1px solid black;" colspan="2" align="center">&lt;new in Denali&gt;</td>
<td style="border:1px solid black;">Memory Manager</td>
<td style="border:1px solid black;">Free Memory (KB)</td>
</tr>
<tr>
<td style="border:1px solid black;" colspan="2" align="center">&lt;new in Denali&gt;</td>
<td style="border:1px solid black;">Memory Manager</td>
<td style="border:1px solid black;">Log Pool Memory (KB)</td>
</tr>
<tr>
<td style="border:1px solid black;" colspan="2" align="center">&lt;new in Denali&gt;</td>
<td style="border:1px solid black;">Memory Manager</td>
<td style="border:1px solid black;">Reserved Server Memory (KB)</td>
</tr>
<tr>
<td style="border:1px solid black;" colspan="2" align="center">&lt;new in Denali&gt;</td>
<td style="border:1px solid black;">Memory Manager</td>
<td style="border:1px solid black;">Stolen Server Memory (KB)</td>
</tr>
<tr>
<td style="border:1px solid black;" colspan="2" align="center">&lt;new in Denali&gt;</td>
<td style="border:1px solid black;">Memory Node</td>
<td style="border:1px solid black;">Database Node Memory (KB)</td>
</tr>
<tr>
<td style="border:1px solid black;" colspan="2" align="center">&lt;new in Denali&gt;</td>
<td style="border:1px solid black;">Memory Node</td>
<td style="border:1px solid black;">Foreign Node Memory (KB)</td>
</tr>
<tr>
<td style="border:1px solid black;" colspan="2" align="center">&lt;new in Denali&gt;</td>
<td style="border:1px solid black;">Memory Node</td>
<td style="border:1px solid black;">Free Node Memory (KB)</td>
</tr>
<tr>
<td style="border:1px solid black;" colspan="2" align="center">&lt;new in Denali&gt;</td>
<td style="border:1px solid black;">Memory Node</td>
<td style="border:1px solid black;">Stolen Node Memory (KB)</td>
</tr>
<tr>
<td style="border:1px solid black;" colspan="2" align="center">&lt;new in Denali&gt;</td>
<td style="border:1px solid black;">Memory Node</td>
<td style="border:1px solid black;">Target Node Memory (KB)</td>
</tr>
<tr>
<td style="border:1px solid black;" colspan="2" align="center">&lt;new in Denali&gt;</td>
<td style="border:1px solid black;">Memory Node</td>
<td style="border:1px solid black;">Total Node Memory (KB)</td>
</tr>
</table>
</blockquote>
<p>You can see that several AWE-related counters have been removed, since AWE is no longer supported in Denali (this means if you are on 32-bit hardware and have &gt; 4GB of RAM, you may as well start shopping for new hardware if you plan to be an early adopter).&nbsp; You'll also notice several Buffer Manager/Node/Partition objects are gone in Denali, replaced by Memory Manager/Node objects (and now measured in KB as opposed to pages).&nbsp; Some of these are described <a href="http://technet.microsoft.com/en-us/library/ff929156%28SQL.110%29.aspx" title="http://technet.microsoft.com/en-us/library/ff929156%28SQL.110%29.aspx" target="_blank">here</a>.&nbsp; There are also some new replica counters based on the new availability and HA/DR features in Denali (described <a href="http://msdn.microsoft.com/en-us/library/ff878356%28SQL.110%29.aspx" title="http://msdn.microsoft.com/en-us/library/ff878356%28SQL.110%29.aspx" target="_blank">here</a> and <a href="http://msdn.microsoft.com/en-us/library/ff878472%28SQL.110%29.aspx" title="http://msdn.microsoft.com/en-us/library/ff878472%28SQL.110%29.aspx" target="_blank">here</a>).&nbsp; The new Log Flush Write Time (ms) counter is described in this BOL topic: <a href="http://msdn.microsoft.com/en-us/library/ms189883%28SQL.110%29.aspx" title="http://msdn.microsoft.com/en-us/library/ms189883%28SQL.110%29.aspx" target="_blank">SQL Server, Databases Object</a>.&nbsp; I can't find any information about the InSysXact waits/sec counter; hopefully the documentation will catch up by RTM (or, ideally, by the next CTP). <br></p>
<p>So, if you're currently monitoring any of these performance counters in 2008 R2 or previous versions, take note that not only are they changing in Denali, but also that their values can mean something different. <br></p>
<p>Now keep in mind that this does not address changes to performance counters that are only present when certain features are enabled, such as database mirroring and replication.&nbsp; I will try to re-run these tests when I have a suitable environment with as many features enabled as possible.<br></p>
<p>&nbsp;</p>
<p>&nbsp;<br></p>SQL Server v.Next (Denali) : Metadata enhancementshttp://sqlblog.com/blogs/aaron_bertrand/archive/2010/12/20/sql-server-v-next-denali-metadata-discovery.aspxMon, 20 Dec 2010 15:09:00 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:31328AaronBertrand
<p>In my previous job, we had several cases where schema changes or incorrect developer assumptions in the middle tier or application logic would lead to type mismatches. &nbsp;We would have a stored procedure that returns a BIT column, but then change the procedure to have something like CASE WHEN &lt;condition&gt; THEN 1 ELSE 0 END. &nbsp;In this case SQL Server would return an INT as a catch-all, and if .NET was expecting a boolean, BOOM. &nbsp;Wouldn't it be nice if the application could check the result set of the stored procedure, and construct its data types using that information? &nbsp;Another case would be where the schema has changed, but the applications can't all be updated at once. &nbsp;Wouldn't it be great to be able to tell SQL Server what the column name should be, what data type you want, or in what collation, as part of the stored procedure call? &nbsp;How often have you cursed the bizarre behavior of SET FMTONLY ON? &nbsp;And finally, how many times have you been frustrated by the output of sp_who2, which returns SPID twice - and worse yet, as a CHAR(5) column instead of an INT?&nbsp;</p>
<p>You can deal with each of these scenarios with some new procedures and dynamic management functions introduced&nbsp;in Denali. &nbsp;We will have new functionality to either discover or control the metadata of queries, without actually having to run them, store temporary results elsewhere, or use SET FMTONLY ON.<br><br></p>
<p><font size="4">Some examples</font></p>
<blockquote>
<p><b>Inspect output of queries without piecing together catalog views</b></p>
<p>Picture a very simple table like this: <br></p>
<blockquote>
<table bgcolor="#eeeeee" cellpadding="0" cellspacing="0">
<tr>
<td>
<pre style="padding:10px 20px;font-size:12px;font-family:consolas,lucida console,courier new,courier;-moz-background-inline-policy:continuous;"><font color="blue">CREATE TABLE </font><font color="black">dbo.x<br></font><font color="gray">(<br>&nbsp;&nbsp;&nbsp;&nbsp;</font><font color="black">foo&nbsp;&nbsp;&nbsp;&nbsp; </font><font color="blue">INT </font><font color="gray">NOT NULL,<br>&nbsp;&nbsp;&nbsp;&nbsp;</font><font color="black">bar&nbsp;&nbsp;&nbsp;&nbsp; </font><font color="blue">DECIMAL</font><font color="gray">(</font><font color="black">12</font><font color="gray">, </font><font color="black">2</font><font color="gray">),<br>&nbsp;&nbsp;&nbsp;&nbsp;</font><font color="black">blat&nbsp;&nbsp;&nbsp;&nbsp;</font><font color="blue">VARCHAR</font><font color="gray">(</font><font color="black">20</font><font color="gray">),<br>&nbsp;&nbsp;&nbsp;&nbsp;</font><font color="black">mort&nbsp;&nbsp;&nbsp;&nbsp;</font><font color="blue">NVARCHAR</font><font color="gray">(</font><font color="black">32</font><font color="gray">),<br>&nbsp;&nbsp;&nbsp;&nbsp;</font><font color="black">splunge </font><font color="blue">NVARCHAR</font><font color="gray">(</font><font color="magenta">MAX</font><font color="gray">),<br>&nbsp;&nbsp;&nbsp;&nbsp;</font><font color="black">whence&nbsp; </font><font><font color="blue">DATETIME2</font></font><font color="gray">(</font><font color="black">4</font><font color="gray">) NOT NULL<br>);</font></pre></td>
</tr>
</table>
</blockquote>
<p>And then a query like this:</p>
<blockquote>
<table bgcolor="#eeeeee" cellpadding="0" cellspacing="0">
<tr>
<td>
<pre style="padding:10px 20px;font-size:12px;font-family:consolas,lucida console,courier new,courier;-moz-background-inline-policy:continuous;"><font color="blue">SELECT </font><font color="gray">* </font><font color="blue">FROM </font><font color="black">dbo.x</font><font color="gray">;</font></pre></td>
</tr>
</table>
</blockquote>
<p>Today, if I want the application to understand the data types that are coming back from this query, I would have to create queries against the catalog views sys.columns and sys.types, for example:<br></p>
<blockquote>
<table bgcolor="#eeeeee" cellpadding="0" cellspacing="0">
<tr>
<td>
<pre style="padding:10px 20px;font-size:12px;font-family:consolas,lucida console,courier new,courier;-moz-background-inline-policy:continuous;"><font color="blue">SELECT <br>&nbsp;&nbsp;&nbsp;&nbsp;</font><font color="black">c.name</font><font color="gray">, <br>&nbsp;&nbsp;&nbsp;&nbsp;</font><font color="black">t.name</font><font color="gray">, <br>&nbsp;&nbsp;&nbsp;&nbsp;</font><font color="black">c.max_length</font><font color="gray">, <br>&nbsp;&nbsp;&nbsp;&nbsp;</font><font color="black">c.[precision]</font><font color="gray">, <br>&nbsp;&nbsp;&nbsp;&nbsp;</font><font color="black">c.scale</font><font color="gray">, <br>&nbsp;&nbsp;&nbsp;&nbsp;</font><font color="black">c.is_nullable<br></font><font color="blue">FROM <br>&nbsp;&nbsp;&nbsp; </font><font><font color="green">sys</font></font><font color="black">.</font><font><font color="green">columns</font></font><font color="black"> </font><font color="blue">AS </font><font color="black">c<br></font><font color="blue">INNER JOIN <br>&nbsp;&nbsp;&nbsp;&nbsp;</font><font><font color="green">sys</font></font><font color="black">.</font><font><font color="green">types</font></font><font color="black"> </font><font color="blue">AS </font><font color="black">t<br>&nbsp;&nbsp;&nbsp;&nbsp;</font><font color="blue">ON </font><font color="black">c.system_type_id = </font><font color="blue"></font><font color="black">t.system_type_id<br>&nbsp;&nbsp;&nbsp;&nbsp;</font><font color="gray">AND </font><font color="black">c.user_type_id = </font><font color="blue"></font><font color="black">t.user_type_id<br></font><font color="blue">WHERE <br>&nbsp;&nbsp;&nbsp;&nbsp;</font><font color="black">c.[object_id] =</font><font color="blue"> </font><font color="magenta">OBJECT_ID</font><font color="gray">(</font><font color="red">'dbo.x'</font><font color="gray">)<br></font><font color="blue">ORDER BY<br>&nbsp;&nbsp;&nbsp;&nbsp;</font><font color="black">c.column_id</font><font color="gray">;</font></pre></td>
</tr>
</table>
</blockquote>
<p>Results:</p>
<blockquote>
<p>&nbsp;<img src="http://sqlblog.com/files/folders/31746/download.aspx" width="561" border="1" height="159"></p>
</blockquote>
<p>This leaves some serious gaps, since I have to perform all kinds of manipulations to put these outputs into true data type definitions.&nbsp; For example, I have to check max_length for -1 and switch it to MAX, I have to check for nchar/nvarchar and cut max_length in half, and I have to piece together the precision and scale for *some* numeric- and date-based types.</p>
<p>There is a new dynamic management function in Denali, sys.dm_exec_describe_first_result_set, which will take a query and produce a much more concise description of the data types coming back in the query.&nbsp; While the function returns other columns, I'll focus on the ones that are most useful for describing resultsets and creating new tables based on them.</p>
<blockquote>
<table bgcolor="#eeeeee" cellpadding="0" cellspacing="0">
<tr>
<td>
<pre style="padding:10px 20px;font-size:12px;font-family:consolas,lucida console,courier new,courier;-moz-background-inline-policy:continuous;"><font color="blue">SELECT <br>&nbsp;&nbsp;&nbsp;&nbsp;</font><font color="black">name</font><font color="gray">, <br>&nbsp;&nbsp;&nbsp;&nbsp;</font><font color="black">system_type_name</font><font color="gray">, <br>&nbsp;&nbsp;&nbsp;&nbsp;</font><font color="black">is_nullable<br></font><font color="blue">FROM <br>&nbsp;&nbsp;&nbsp;&nbsp;</font><font><font color="green">sys</font></font><font color="black">.</font><font><font color="green">dm_exec_describe_first_result_set</font></font><font color="black"><br>&nbsp;&nbsp;&nbsp;&nbsp;</font><font color="gray">(<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</font><font color="red">N'SELECT * FROM dbo.x;'</font><font color="gray">, NULL, </font><font color="black">0<br>&nbsp;&nbsp;&nbsp;&nbsp;</font><font color="gray">) </font><font color="blue">AS </font><font color="black">f<br></font><font color="blue">ORDER BY<br> </font><font color="black">column_ordinal</font><font color="gray">;</font></pre></td>
</tr>
</table>
</blockquote>
<p>Results:</p>
<blockquote>
<p><img src="http://sqlblog.com/files/folders/31747/download.aspx" width="350" border="1" height="160">&nbsp;</p>
</blockquote>
<p>Clearly this is a lot less work and manipulation to get the data types consistent - all of the "compound" data types come back exactly as you need to define them.&nbsp; So let's imagine you want to generate CREATE TABLE scripts to store the data from a bunch of DMVs or your own views or other queries; again, you can do this without writing very complex queries against the catalog views or with piecing together the data types manually.&nbsp; A very simple example that generates a CREATE TABLE statement to store the data from sys.dm_exec_sessions in your own repository table:</p>
<blockquote>
<table bgcolor="#eeeeee" cellpadding="0" cellspacing="0">
<tr>
<td>
<pre style="padding:10px 20px;font-size:12px;font-family:consolas,lucida console,courier new,courier;-moz-background-inline-policy:continuous;"><font color="blue">SET NOCOUNT ON</font><font color="gray">;<br><br></font><font color="blue">DECLARE </font><font color="#434343">@sql </font><font color="blue">NVARCHAR</font><font color="gray">(</font><font color="magenta">MAX</font><font color="gray">);<br></font><font color="blue">SET </font><font color="#434343">@sql = </font><font color="blue"></font><font color="red">N'SELECT * FROM sys.dm_exec_sessions;'</font><font color="gray">;<br><br></font><font color="blue">SELECT </font><font color="red">'CREATE TABLE dbo.SessionsDMV('</font><font color="gray">;<br><br></font><font color="blue">SELECT <br>&nbsp;&nbsp;&nbsp;&nbsp;</font><font color="magenta">CASE </font><font color="black">column_ordinal <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</font><font color="blue">WHEN </font><font color="black">1 </font><font color="blue">THEN </font><font color="red">'' </font><font color="blue">ELSE </font><font color="red">',' </font><font color="blue">END <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</font><font color="gray">+ </font><font color="black">name </font><font color="gray">+ </font><font color="red">' ' </font><font color="gray">+ </font><font color="black">system_type_name </font><font color="gray">+ </font><font color="magenta">CASE </font><font color="black">is_nullable <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</font><font color="blue">WHEN </font><font color="black">0 </font><font color="blue">THEN </font><font color="red">' not null' </font><font color="blue">ELSE </font><font color="red">'' </font><font color="blue">END<br>FROM <br>&nbsp;&nbsp;&nbsp; </font><font><font color="green">sys</font></font><font color="black">.</font><font><font color="green">dm_exec_describe_first_result_set</font></font><br><font color="black">&nbsp;&nbsp;&nbsp; </font><font color="gray">(<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</font><font color="#434343">@sql</font><font color="gray">, NULL, </font><font color="black">0<br>&nbsp;&nbsp;&nbsp;&nbsp;</font><font color="gray">) </font><font color="blue">AS </font><font color="black">f<br></font><font color="blue">ORDER BY<br> </font><font color="black">column_ordinal</font><font color="gray">;<br><br></font><font color="blue">SELECT </font><font color="red">');'</font><font color="gray">;</font></pre></td>
</tr>
</table>
</blockquote>
<p>The results (not exactly how I would want them formatted, but you can play with the query above so the code conforms better to your standards):</p>
<p>&nbsp;</p>
<blockquote>
<table bgcolor="#eeeeee" cellpadding="0" cellspacing="0">
<tr>
<td>
<pre style="padding:10px 20px;font-size:12px;font-family:consolas,lucida console,courier new,courier;-moz-background-inline-policy:continuous;">-----------------------------<br>CREATE TABLE dbo.SessionsDMV(<br><br>------------------------------------------------------------------<br>session_id smallint not null<br>,login_time datetime not null<br>,host_name nvarchar(128)<br>,program_name nvarchar(128)<br>,host_process_id int<br>,client_version int<br>,client_interface_name nvarchar(32)<br>,security_id varbinary(85) not null<br>,login_name nvarchar(128) not null<br>,nt_domain nvarchar(128)<br>,nt_user_name nvarchar(128)<br>,status nvarchar(30) not null<br>,context_info varbinary(128)<br>,cpu_time int not null<br>,memory_usage int not null<br>,total_scheduled_time int not null<br>,total_elapsed_time int not null<br>,endpoint_id int not null<br>,last_request_start_time datetime not null<br>,last_request_end_time datetime<br>,reads bigint not null<br>,writes bigint not null<br>,logical_reads bigint not null<br>,is_user_process bit not null<br>,text_size int not null<br>,language nvarchar(128)<br>,date_format nvarchar(3)<br>,date_first smallint not null<br>,quoted_identifier bit not null<br>,arithabort bit not null<br>,ansi_null_dflt_on bit not null<br>,ansi_defaults bit not null<br>,ansi_warnings bit not null<br>,ansi_padding bit not null<br>,ansi_nulls bit not null<br>,concat_null_yields_null bit not null<br>,transaction_isolation_level smallint not null<br>,lock_timeout int not null<br>,deadlock_priority int not null<br>,row_count bigint not null<br>,prev_error int not null<br>,original_security_id varbinary(85) not null<br>,original_login_name nvarchar(128) not null<br>,last_successful_logon datetime<br>,last_unsuccessful_logon datetime<br>,unsuccessful_logons bigint<br>,group_id int not null<br>,authenticating_database_id int<br><br>----<br>);</pre></td>
</tr>
</table>
</blockquote>
<p>Now, you can copy the entire result from the messages pane, and run it to create a table that will accept an INSERT statement like "INSERT dbo.SessionsDMV SELECT * FROM sys.dm_exec_sessions;"</p>
<p>I'll leave it as an exercise to the reader to try this out against different values of @sql.</p>
<p><br><b>Best guess at undeclared parameters</b>&nbsp; </p>
<p>There is also a new stored procedure that will allow you to inspect a T-SQL batch and determine the data types of any undeclared parameters.&nbsp; As an example, what are the data types of the variables that will ultimately be passed into this query text?&nbsp; (This is often something an application developer will have to determine in order to program around these queries.)</p>
<blockquote>
<table bgcolor="#eeeeee" cellpadding="0" cellspacing="0">
<tr>
<td>
<pre style="padding:10px 20px;font-size:12px;font-family:consolas,lucida console,courier new,courier;-moz-background-inline-policy:continuous;"><font color="blue">SELECT </font><font color="gray">* </font><font color="blue">FROM </font><font><font color="green">sys</font></font><font color="black">.</font><font><font color="green">objects</font></font><font color="black"> <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</font><font color="blue">WHERE </font><font color="black">[object_id] =</font><font color="blue"> </font><font color="#434343">@ObjectID<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</font><font color="gray">OR (</font><font color="black">name </font><font color="gray">LIKE </font><font color="#434343">@ObjectName</font><font color="gray">);</font></pre></td>
</tr>
</table>
</blockquote>
<p>On first glance, most of us will know that the first parameter, @ObjectID, should be declared as an INT, and the second parameter is likely going to be an NVARCHAR(4000).&nbsp; However there is now way for the system to tell us these exact answers with a lot less uncertainty:</p>
<blockquote>
<table bgcolor="#eeeeee" cellpadding="0" cellspacing="0">
<tr>
<td>
<pre style="padding:10px 20px;font-size:12px;font-family:consolas,lucida console,courier new,courier;-moz-background-inline-policy:continuous;"><font color="blue">EXEC </font><font><font color="green">sys</font></font><font color="black">.</font><font color="darkred">sp_describe_undeclared_parameters<br>&nbsp;&nbsp;&nbsp;&nbsp;</font><font color="#434343">@tsql =</font><font color="blue"> </font><font color="red">N'SELECT * FROM sys.objects <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;WHERE [object_id] = @ObjectID<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;OR (name LIKE @ObjectName);'</font><font color="gray">;</font></pre></td>
</tr>
</table>
</blockquote>
<p>Partial results:</p>
<blockquote>
<p><img src="http://sqlblog.com/files/folders/31748/download.aspx" width="552" border="1" height="68">&nbsp;</p>
</blockquote>
<p>This returns a lot of other columns as well, but even the above should show how the stored procedure will be able to examine large and complicated T-SQL batches and describe all of the parameters that are used.&nbsp; Now, I say "best guess" because the parameter inspection is not perfect - but for most cases it is pretty spot on.<br></p>
<p><br><b>Convert data types explicitly</b></p>
<p>In this example I am showing a simple stored procedure.&nbsp; If we have two applications that call this stored procedure, we can call the procedure with two different WITH RESULT SETS options in order to return the data differently for the applications, allowing us to slowly change schema across our applications instead of changing them all at once.&nbsp; Imagine the following batch where the first column name is changing from 'Email' to 'Username' (the system no longer requires an e-mail address as the user identifier) and the second column, 'Status,' is changing from an INT to a BIT.&nbsp; The current procedure looks like this:</p>
<blockquote>
<table bgcolor="#eeeeee" cellpadding="0" cellspacing="0">
<tr>
<td>
<pre style="padding:10px 20px;font-size:12px;font-family:consolas,lucida console,courier new,courier;-moz-background-inline-policy:continuous;"><font color="blue">CREATE PROCEDURE </font><font color="black">dbo.Users_GetActiveList<br></font><font color="blue">AS<br>BEGIN<br>&nbsp;&nbsp;&nbsp;&nbsp;SET NOCOUNT ON</font><font color="gray">;<br><br>&nbsp;&nbsp;&nbsp;&nbsp;</font><font color="blue">SELECT<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</font><font color="black">Username</font><font color="gray">,<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</font><font color="black">[Status] =</font><font color="blue"> </font><font color="magenta">CASE </font><font color="blue">WHEN </font><font color="black">IsActive = </font><font color="blue"></font><font color="black">1 </font><font color="blue">THEN </font><font color="black">1 </font><font color="blue">ELSE </font><font color="black">0 </font><font color="blue">END<br>&nbsp;&nbsp;&nbsp;&nbsp;FROM </font><font color="black">dbo.Users<br>&nbsp;&nbsp;&nbsp;&nbsp;</font><font color="blue">ORDER BY </font><font color="black">Username</font><font color="gray">;<br></font><font color="blue">END<br>GO</font><font color="black"></font></pre></td>
</tr>
</table>
</blockquote>
<p>As we prepare for the schema changes, we could change the code above to just say:</p>
<blockquote>
<table bgcolor="#eeeeee" cellpadding="0" cellspacing="0">
<tr>
<td>
<pre style="padding:10px 20px;font-size:12px;font-family:consolas,lucida console,courier new,courier;-moz-background-inline-policy:continuous;"><font color="blue">CREATE PROCEDURE </font><font color="black">dbo.Users_GetActiveList<br></font><font color="blue">AS<br>BEGIN<br>&nbsp;&nbsp;&nbsp;&nbsp;SET NOCOUNT ON</font><font color="gray">;<br><br>&nbsp;&nbsp;&nbsp;&nbsp;</font><font color="blue">SELECT<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</font><font color="black">Email =</font><font color="blue"> </font><font color="black">Username</font><font color="gray">,<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</font><font color="black">[Status] = </font><font color="blue"></font><font color="magenta">CONVERT</font><font color="gray">(</font><font><font color="blue">BIT</font></font><font color="gray">, </font><font color="magenta">CASE<br></font><font color="blue"> WHEN </font><font color="black">IsActive =</font><font color="blue"> </font><font color="black">1 </font><font color="gray">&lt;</font><font color="black">and other conditions</font><font color="gray">&gt; </font><font color="blue">THEN </font><font color="black">1<br></font><font color="blue"> ELSE </font><font color="black">0 </font><font color="blue">END</font><font color="gray">)<br>&nbsp;&nbsp;&nbsp;&nbsp;</font><font color="blue">FROM </font><font color="black">dbo.Users<br>&nbsp;&nbsp;&nbsp;&nbsp;</font><font color="blue">ORDER BY </font><font color="black">Email</font><font color="gray">;<br></font><font color="blue">END<br>GO<br></font></pre></td>
</tr>
</table>
</blockquote>
<p>But of course unless we update all of our apps at the same time, this change will break at least one of them.&nbsp; Instead, we can use WITH RESULT SETS to change the shape of the resultset conditionally.&nbsp; From the application where we can change the app immediately to use the new column names, we can say:</p>
<blockquote>
<table bgcolor="#eeeeee" cellpadding="0" cellspacing="0">
<tr>
<td>
<pre style="padding:10px 20px;font-size:12px;font-family:consolas,lucida console,courier new,courier;-moz-background-inline-policy:continuous;"><font color="blue">EXECUTE </font><font color="black">dbo.Users_GetActiveList<br></font><font color="blue">WITH RESULT SETS</font><font color="black"><br></font><font color="gray">(<br>&nbsp;&nbsp;&nbsp;&nbsp;(<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</font><font color="black">Email </font><font color="blue">VARCHAR</font><font color="gray">(</font><font color="black">320</font><font color="gray">),<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</font><font color="black">[Status] </font><font><font color="blue">BIT</font></font><font color="black"><br>&nbsp;&nbsp;&nbsp;&nbsp;</font><font color="gray">)<br>);</font></pre></td>
</tr>
</table>
</blockquote>
<p>In the results, you can't really tell from the screen shot whether Status is a BIT or an INT, but you can certainly see that the Username column has been changed to Email without changing the stored procedure and without having to change all of the consuming apps at once:</p>
<blockquote>
<p><img src="http://sqlblog.com/files/folders/31745/download.aspx" width="289" border="1" height="316">&nbsp;</p>
</blockquote>
The old application can remain unchanged, and continue calling the stored procedure in the default manner - it will still see the Username column, and it will still get Status back as an Int32 instead of a Boolean (we are talking .Net data types here, not SQL).&nbsp; If there really are only two applications, then the WITH RESULT SETS code can be removed from the first application when the schema has changed and the second application has been updated.&nbsp; Otherwise, each app can make use of WITH RESULT SETS until they are all synchronized.&nbsp; At my previous job, I could have used this technique in many scenarios.</blockquote>
<blockquote><br><b>Fix output of system stored procedures</b></blockquote>
<blockquote>Every time I run sp_who2, I cringe. There are two SPID columns for some reason, and both are CHAR(5).&nbsp; Arguably, these could be made SMALLINT, but I think INT plays better with the .NET layer in most cases.&nbsp; There is also a column called BlkBy, which - unlike the rest of the columns - has a name which is unnecessarily truncated.&nbsp; I'll leave this as a CHAR(5) because I know the "." result is appreciated by a lot of people (it lets the true numeric values stand out better than 0 or NULL would).&nbsp; That all said, here is an example of using WITH RESULT SETS to make the output more consumable:
<blockquote>
<table bgcolor="#eeeeee" cellpadding="0" cellspacing="0">
<tr>
<td>
<pre style="padding:10px 20px;font-size:12px;font-family:consolas,lucida console,courier new,courier;-moz-background-inline-policy:continuous;"><font color="blue">EXECUTE </font><font color="black">[master]..</font><font color="darkred">sp_who</font><font color="black">2<br></font><font color="blue">WITH RESULT SETS</font><font color="black"><br></font><font color="gray">(<br>&nbsp;&nbsp;&nbsp;&nbsp;(<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</font><font color="black">[SPID]&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </font><font color="blue">INT</font><font color="gray">,&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</font><font color="green">-- &lt;-- fixed the data type<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</font><font color="black">[Status]&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </font><font color="blue">NVARCHAR</font><font color="gray">(</font><font color="black">32</font><font color="gray">),<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</font><font color="black">[Login]&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</font><font color="blue">SYSNAME</font><font color="gray">,<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</font><font color="black">HostName&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </font><font color="blue">SYSNAME</font><font color="gray">,<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</font><font color="black">Blocker&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</font><font color="blue">CHAR</font><font color="gray">(</font><font color="black">5</font><font color="gray">),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</font><font color="green">-- &lt;-- renamed this column<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</font><font color="black">[Database]&nbsp;&nbsp;&nbsp;&nbsp; </font><font color="blue">SYSNAME</font><font color="gray">,<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</font><font color="black">Command&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</font><font color="blue">NVARCHAR</font><font color="gray">(</font><font color="black">32</font><font color="gray">),<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</font><font color="black">CPUTime&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</font><font color="blue">VARCHAR</font><font color="gray">(</font><font color="black">30</font><font color="gray">),<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</font><font color="black">DiskIO&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </font><font color="blue">VARCHAR</font><font color="gray">(</font><font color="black">30</font><font color="gray">),<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</font><font color="black">LastBatch&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</font><font color="blue">VARCHAR</font><font color="gray">(</font><font color="black">48</font><font color="gray">),<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</font><font color="black">ProgramName&nbsp;&nbsp;&nbsp;&nbsp;</font><font color="blue">NVARCHAR</font><font color="gray">(</font><font color="black">255</font><font color="gray">),<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</font><font color="black">Redundant_SPID </font><font color="blue">INT</font><font color="gray">,&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</font><font color="green">-- &lt;-- renamed this column<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</font><font color="black">RequestID&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</font><font color="blue">INT<br>&nbsp;&nbsp;&nbsp;&nbsp;</font><font color="gray">)<br>);</font></pre></td>
</tr>
</table>
</blockquote>
<p><b><br>Make more usable output in Management Studio</b></p>
<p>In Denali there is a new system stored procedure called <a href="http://msdn.microsoft.com/en-us/library/ff878233%28SQL.110%29.aspx" title="http://msdn.microsoft.com/en-us/library/ff878233(SQL.110).aspx" target="_blank">sys.sp_server_diagnostics</a>, which returns some core system health metrics in XML format.&nbsp; Unfortunately, the implementation returns this data as string rather than XML data, which means the output is pretty useless in Management Studio - you either have to expand the result column and scroll horizontally forever in results to grid mode, or do all kinds of juggling to figure out where each row ends in results to text mode.&nbsp; (I complained about this in <a href="http://connect.microsoft.com/SQLServer/feedback/details/625262/denali-engine-sys-sp-server-diagnostics-should-return-xml-data-as-xml" title="http://connect.microsoft.com/SQLServer/feedback/details/625262/denali-engine-sys-sp-server-diagnostics-should-return-xml-data-as-xml" target="_blank">Connect #625262</a>, but they didn't seem to agree with my reasoning - though I'm curious what other data type they think the output may change to in the future.)&nbsp; In order to make the output more useful, we can do the following to convert the last column to XML, making it clickable within SSMS grid results:</p>
<blockquote>
<table bgcolor="#eeeeee" cellpadding="0" cellspacing="0">
<tr>
<td>
<pre style="padding:10px 20px;font-size:12px;font-family:consolas,lucida console,courier new,courier;-moz-background-inline-policy:continuous;"><font color="blue">EXECUTE </font><font><font color="green">sys</font></font><font color="black">.</font><font color="black"></font><font color="darkred">sp_server_diagnostics<br></font><font color="blue">WITH RESULT SETS</font><font color="black"><br></font><font color="gray">(<br>&nbsp;&nbsp;&nbsp;&nbsp;(<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</font><font color="black">create_time&nbsp;&nbsp;&nbsp;&nbsp;</font><font color="blue">DATETIME</font><font color="gray">,<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</font><font color="black">component_name </font><font color="blue">VARCHAR</font><font color="gray">(</font><font color="black">20</font><font color="gray">)</font><font color="gray">,<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</font><font color="black">[state]&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</font><font color="blue">INT</font><font color="gray">,<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</font><font color="black">state_desc&nbsp;&nbsp;&nbsp;&nbsp; </font><font color="blue">VARCHAR</font><font color="gray">(</font><font color="black">20</font><font color="gray">),<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</font><font color="black">data&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </font><font color="blue">XML<br>&nbsp;&nbsp;&nbsp;&nbsp;</font><font color="gray">)<br>);</font></pre></td>
</tr>
</table>
</blockquote>
<p>In the results, we can now click on the data column in any row, and it will open a new XML document that is much easier to read and parse: </p>
<blockquote><img src="http://sqlblog.com/files/folders/31744/download.aspx" width="687" border="1" height="158"><br> <br></blockquote>
<p><b>Changing the output collation</b></p>
<p>This one is late to the party but since publishing I felt this nagging feeling that the article was incomplete.&nbsp; I can't think of an obvious and practical use for this off the top of my head, as thankfully I have not been exposed to too many collation issues over the years.&nbsp; But if a query is outputting a column in a certain collation, you can override that collation using WITH RESULT SETS.&nbsp; Here is an example that changes Albanian_BIN to Albanian_100_BIN, and I pass this batch to the DMV just to show that the output collation is obeyed:<br></p>
<blockquote>
<table bgcolor="#eeeeee" cellpadding="0" cellspacing="0">
<tr>
<td>
<pre style="padding:10px 20px;font-size:12px;font-family:consolas,lucida console,courier new,courier;-moz-background-inline-policy:continuous;"><font color="blue">DECLARE </font><font color="#434343">@sql </font><font color="blue">NVARCHAR</font><font color="gray">(</font><font color="magenta">MAX</font><font color="gray">);<br><br></font><font color="blue">SET </font><font color="#434343">@sql = </font><font color="blue"></font><font color="red">N'EXEC(''SELECT N''''foo'''' COLLATE Albanian_BIN'')<br>&nbsp;&nbsp;WITH RESULT SETS<br> (<br> (<br> foo NVARCHAR(32) COLLATE Albanian_100_BIN<br> )<br> );'</font><font color="gray">;<br><br></font><font color="blue">SELECT </font><font color="black">name</font><font color="gray">, </font><font color="black">collation_name<br>&nbsp;&nbsp;&nbsp;&nbsp;</font><font color="blue">FROM </font><font><font color="green">sys</font></font><font color="black">.</font><font><font color="green">dm_exec_describe_first_result_set<br> </font></font><font color="gray">(<br> </font><font color="#434343">@sql</font><font color="gray">, NULL, </font><font color="black">0<br> </font><font color="gray">);</font></pre></td>
</tr>
</table>
</blockquote>
<p>Results:</p>
<blockquote><img src="http://sqlblog.com/files/folders/31852/download.aspx"><br></blockquote>
<p>&nbsp;</p>
<p><b>Easier translation in SSIS</b></p>
<p>Finally, here is a blog post from fellow MVP James Rowland-Jones, where he demonstrates how EXECUTE ... WITH RESULT SETS can be very handy in SSIS: <br></p>
<blockquote>
<p><a href="http://consultingblogs.emc.com/jamesrowlandjones/archive/2010/11/16/denali-using-execute-with-result-sets-example-using-ssis-source-adaptors.aspx" title="http://consultingblogs.emc.com/jamesrowlandjones/archive/2010/11/16/denali-using-execute-with-result-sets-example-using-ssis-source-adaptors.aspx" target="_blank">Denali - Using EXECUTE WITH RESULT SETS (example using SSIS Source Adaptors)</a> <br></p>
</blockquote>
</blockquote>
<p><br><font size="4">Caveats</font> <br></p>
<p>The DMVs and stored procedures for describing result sets are currently written to only inspect the *first* result set.&nbsp; So for something like sp_help, it will only assist in determining the very first set of output.&nbsp; I suspect they will correct this in the future, and am thankful they named the objects in an unambiguous way.</p>
<p>In addition, there are certain types of batches that will simply return an error when you try to examine them.&nbsp; For example, while a batch with a table variable can be passed to sys.sp_describe_first_result_set, a batch with a temp table cannot: <br></p>
<blockquote>
<table bgcolor="#eeeeee" cellpadding="0" cellspacing="0">
<tr>
<td>
<pre style="padding:10px 20px;font-size:12px;font-family:consolas,lucida console,courier new,courier;-moz-background-inline-policy:continuous;"><font color="blue">EXEC </font><font><font color="green">sys</font></font><font color="black">.</font><font color="darkred">sp_describe_first_result_set <br>&nbsp;&nbsp;&nbsp;&nbsp;</font><font color="#434343">@tsql =</font><font color="blue"> </font><font color="red">N'CREATE TABLE #y (j INT);SELECT j FROM #y;'</font><font color="gray">;</font></pre></td>
</tr>
</table>
</blockquote>
<p>Results:</p>
<blockquote>
<table bgcolor="#eeeeee" cellpadding="0" cellspacing="0">
<tr>
<td><div style="padding:10px 20px;font-size:12px;font-family:consolas,lucida console,courier new,courier;-moz-background-inline-policy:continuous;color:red;">Msg 11525, Level 16, State 1, Procedure sp_describe_first_result_set, Line 1<br>The metadata could not be determined because statement 'SELECT j FROM #y;' uses a temp table.</div></td>
</tr>
</table>
</blockquote>
<p>WITH RESULT SETS requires a double-nesting of parentheses.&nbsp; If you try the following:</p>
<blockquote>
<table bgcolor="#eeeeee" cellpadding="0" cellspacing="0">
<tr>
<td>
<pre style="padding:10px 20px;font-size:12px;font-family:consolas,lucida console,courier new,courier;-moz-background-inline-policy:continuous;"><font color="blue">EXECUTE </font><font color="gray">(</font><font color="red">'SELECT foo = N''bar'';'</font><font color="gray">)<br></font><font color="blue">WITH RESULT SETS</font><font color="black"><br></font><font color="gray">(<br></font><font color="green">-- (<br>&nbsp;&nbsp;&nbsp;&nbsp;</font><font color="black">foo </font><font color="blue">NVARCHAR</font><font color="gray">(</font><font color="black">3</font><font color="gray">)<br></font><font color="green">-- )<br></font><font color="gray">);</font></pre></td>
</tr>
</table>
</blockquote>
<p>Results:</p>
<blockquote>
<table bgcolor="#eeeeee" cellpadding="0" cellspacing="0">
<tr>
<td><div style="padding:10px 20px;font-size:12px;font-family:consolas,lucida console,courier new,courier;-moz-background-inline-policy:continuous;color:red;">Msg 102, Level 15, State 1, Line 4<br>Incorrect syntax near 'foo'.</div></td>
</tr>
</table>
</blockquote>
<p>To get around this, you will need to un-comment the commented parentheses.&nbsp; The reason is that WITH RESULT SETS is plural; you can dictate the shape of multiple result sets, as demonstrated above.<br></p>
<p>Also note that you can't use WITH RESULTS to add or remove columns from the output; you can only mess with the column names, data types, nullability and collation.&nbsp; So for example, if you try to eliminate columns from the result set as follows:</p>
<blockquote>
<table bgcolor="#eeeeee" cellpadding="0" cellspacing="0">
<tr>
<td>
<pre style="padding:10px 20px;font-size:12px;font-family:consolas,lucida console,courier new,courier;-moz-background-inline-policy:continuous;"><font color="blue">EXECUTE </font><font color="gray">(</font><font color="red">'SELECT foo = N''bar'', x = 1;'</font><font color="gray">)<br></font><font color="blue">WITH RESULT SETS</font><font color="black"><br></font><font color="gray">(<br>&nbsp;&nbsp;&nbsp;&nbsp;(</font><font color="black">foo </font><font color="blue">NVARCHAR</font><font color="gray">(</font><font color="black">3</font><font color="gray">))<br>);</font></pre></td>
</tr>
</table>
</blockquote>
<p>Results: </p>
<blockquote>
<table bgcolor="#eeeeee" cellpadding="0" cellspacing="0">
<tr>
<td><div style="padding:10px 20px;font-size:12px;font-family:consolas,lucida console,courier new,courier;-moz-background-inline-policy:continuous;color:red;">Msg 11537, Level 16, State 1, Line 1<br>EXECUTE statement failed because its WITH RESULT SETS clause specified 1 column(s) for result set number 1, but the statement sent 2 column(s) at run time.</div></td>
</tr>
</table>
</blockquote>
<p>And if you try to convert to an incompatible data type, it will fail at runtime.&nbsp; For example, the following code will compile, but if you run it:</p>
<blockquote>
<table bgcolor="#eeeeee" cellpadding="0" cellspacing="0">
<tr>
<td>
<pre style="padding:10px 20px;font-size:12px;font-family:consolas,lucida console,courier new,courier;-moz-background-inline-policy:continuous;"><font color="blue">EXECUTE </font><font color="gray">(</font><font color="red">'SELECT foo = N''bar'';'</font><font color="gray">)<br></font><font color="blue">WITH RESULT SETS</font><font color="black"><br></font><font color="gray">(<br>&nbsp;&nbsp;&nbsp;&nbsp;(</font><font color="black">foo </font><font color="blue">INT</font><font color="gray">)<br>);</font></pre></td>
</tr>
</table>
</blockquote>
<p>Results:</p>
<blockquote>
<table bgcolor="#eeeeee" cellpadding="0" cellspacing="0">
<tr>
<td><div style="padding:10px 20px;font-size:12px;font-family:consolas,lucida console,courier new,courier;-moz-background-inline-policy:continuous;color:red;">Msg 8114, Level 16, State 2, Line 1<br>Error converting data type varchar to int.</div></td>
</tr>
</table>
</blockquote>
There are many other limitations and caveats to using these metadata enhancements; if you can see usefulness in some of these capabilities, I strongly recommend reviewing the official documentation and playing with the features for yourself.<br>
<p><br><font size="4">Official links</font></p>
<p>Here are links to the official documentation about these new features: <br></p>
<blockquote>
<p>sys.sp_describe_first_result_set<br><a href="http://msdn.microsoft.com/en-us/library/ff878602%28SQL.110%29.aspx" title="http://msdn.microsoft.com/en-us/library/ff878602(SQL.110).aspx" target="_blank">http://msdn.microsoft.com/en-us/library/ff878602(SQL.110).aspx</a></p>
<p>sys.sp_describe_undeclared_parameters<br><a href="http://msdn.microsoft.com/en-us/library/ff878260%28SQL.110%29.aspx" title="http://msdn.microsoft.com/en-us/library/ff878260(SQL.110).aspx" target="_blank">http://msdn.microsoft.com/en-us/library/ff878260(SQL.110).aspx</a></p>
<p>sys.dm_exec_describe_first_result_set_for_object<br><a href="http://msdn.microsoft.com/en-us/library/ff878236%28SQL.110%29.aspx" title="http://msdn.microsoft.com/en-us/library/ff878236(SQL.110).aspx" target="_blank">http://msdn.microsoft.com/en-us/library/ff878236(SQL.110).aspx</a><br><br>sys.dm_exec_describe_first_result_set<br><a href="http://msdn.microsoft.com/en-us/library/ff878258%28SQL.110%29.aspx" title="http://msdn.microsoft.com/en-us/library/ff878258(SQL.110).aspx" target="_blank">http://msdn.microsoft.com/en-us/library/ff878258(SQL.110).aspx</a><br></p>
<p>EXECUTE<br><a href="http://msdn.microsoft.com/en-us/library/ms188332%28SQL.110%29.aspx" title="http://msdn.microsoft.com/en-us/library/ms188332(SQL.110).aspx" target="_blank">http://msdn.microsoft.com/en-us/library/ms188332(SQL.110).aspx</a></p>
</blockquote>
<br>