Search results matching tags 'denali' and 'SQL 2011'http://sqlblog.com/search/SearchResults.aspx?o=DateDescending&tag=denali,SQL+2011&orTags=0Search results matching tags 'denali' and 'SQL 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>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>SSMS Tools Pack now supports Denali CTP1http://sqlblog.com/blogs/aaron_bertrand/archive/2010/12/28/ssms-tools-pack-now-supports-denali-ctp1.aspxTue, 28 Dec 2010 14:24:00 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:32048AaronBertrand<p>Earlier today, Mladen Prajdić (<a href="http://weblogs.sqlteam.com/mladenp/" title="http://weblogs.sqlteam.com/mladenp/" target="_blank">blog</a> | <a href="http://twitter.com/MladenPrajdic" title="http://twitter.com/MladenPrajdic" target="_blank">twitter</a>) released an updated version of his <a href="http://www.ssmstoolspack.com/" title="http://www.ssmstoolspack.com/" target="_blank">SSMS Tools Pack</a> (v.1.9.4), a free add-in for Management Studio that provides a ton of helpful functionality that isn't available with the native tools.&nbsp; I'm really glad this happened, because I've installed Denali on all of my VMs and have been using it for most of my work, and I've been missing some of the little things the tool adds.&nbsp; In addition to adding Denali support, Mladen also fixed a handful of minor bugs - you can <a href="http://www.ssmstoolspack.com/News" title="http://www.ssmstoolspack.com/News" target="_blank">read about them here</a>.</p><p>Happy SSMSing!</p><p>&nbsp; <br></p>