More changes you might not have noticed in SQL Server 2008 R2 SP1 CTP

AaronBertrand — Mon, 25 Apr 2011 14:28:00 GMT

On Friday, after Microsoft released the CTP, I talked a little bit about the changes you will see in SQL Server 2008 R2 SP1. Namely:

new rowcount-related columns in sys.dm_exec_query_stats;
extended functionality of the FORCESEEK query hint;
a new FORCESCAN query hint;
support for 15,000 partitions (to match new functionality added in SQL Server 2008 SP2);
a new DAC framework for in-place upgrades; and,
a new disk space control in PowerPivot for SharePoint.

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).

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).

New system objects:

All columns for new DMVs, and new columns on existing DMVs:

sys.dm_os_volume_stats

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:
SELECT 
   vs.volume_mount_point, -- e.g. C:\
   vs.volume_id,
   vs.logical_volume_name,
   vs.file_system_type,  -- e.g. NTFS
   db = DB_NAME(f.dbid), 
   [file] = f.[name],
   f.[filename],
   file_size_MB = f.[size] / 128,
   drive_size_MB = vs.total_bytes/1024/1024, 
   drive_free_space_MB = vs.available_bytes/1024/1024, 
   drive_percent_free = CONVERT(DECIMAL(5,2), vs.available_bytes * 100.0 / vs.total_bytes),
   vs.supports_compression, 
   vs.supports_alternate_streams, 
   vs.supports_sparse_files, 
   vs.is_read_only, 
   vs.is_compressed
FROM
   sys.sysaltfiles AS f
CROSS APPLY
   sys.dm_os_volume_stats(f.dbid, f.fileid) AS vs
WHERE
   f.dbid < 32767
ORDER BY
   drive_percent_free DESC,
   db,
   [file];
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.

* Not yet in Denali.

sys.sp_db_increased_partitions

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:
EXEC sys.sp_db_increased_partitions 
    @dbname = N'AdventureWorks', 
    @increased_partitions = 'on';

EXEC sys.sp_db_increased_partitions 
    @dbname = N'AdventureWorks', 
    @increased_partitions = 'off';

-- or

EXEC sys.sp_db_increased_partitions 
    @dbname = N'AdventureWorks', 
    @increased_partitions = 'true';

EXEC sys.sp_db_increased_partitions 
    @dbname = N'AdventureWorks', 
    @increased_partitions = 'false';
* Not yet in Denali.

sys.sp_MScheckIsPubOfSub

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:
/*
  *  This proc will discover if the specified subid is a subscriber to the specified pubid.
  *  Currently, this is a one level check, it does not support n-level re-publishing.
*/
The procedure takes two uniqueidentifer input parameters (@pubid and @subid), and returns a single output parameter (@pubOfSub bit).

* Not yet in Denali.

sys.dm_os_windows_info

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.
SELECT
   windows_release,
   windows_service_pack_level,
   windows_sku,
   os_language_version
FROM
   sys.dm_os_windows_info;
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):

* Not yet in Denali.

sys.dm_server_memory_dumps

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:

SELECT
   [filename],
   creation_time,
   size_in_bytes
FROM
   sys.dm_server_memory_dumps;
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.

* Not yet in Denali.

sys.dm_server_registry

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):
SELECT 
   registry_key,
   value_name,
   value_data
FROM
   sys.dm_server_registry;
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:

* Not yet in Denali.

sys.dm_server_services

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):
SELECT
   servicename,
   [startup] = startup_type_desc,
   [status] = status_desc,
   pid = process_id,
   last_startup_time,
   service_account,
   [clus] = is_clustered
FROM
   sys.dm_server_services;
Results:

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):

#664557 : sys.dm_server_services.last_startup_time is always NULL for SQL Agent

#664559 : Odd data type choices in sys.dm_server_services

* Not yet in Denali.

sys.dm_exec_query_stats

I talked about this DMV in my previous post; 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.

* Not yet in Denali.

sys.dm_os_sys_info

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:
SELECT 
   virtual_machine_type,
   virtual_machine_type_desc
FROM
   sys.dm_os_sys_info;
Results:

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.

Also note: the current Denali BOL documentation for "Breaking Changes to Database Engine Features" incorrectly states that these columns were removed.

* This DMV is the only one on this page that has already been added/changed in current builds of Denali (CTP1).

Extended Events

Some new xevents were added in SP1 as well. I'm sure Jonathan Kehayias (blog | twitter) 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:
SELECT
   l.name,
   l.map_value
FROM
   sys.dm_xe_map_values AS l
LEFT OUTER JOIN
   [GREENLANTERN\SQL2008R2RTM].[master].sys.dm_xe_map_values AS r
   ON l.name = r.name
   AND l.map_key = r.map_key
WHERE
   r.name IS NULL;

SELECT
   l.name,
   l.description
FROM
   sys.dm_xe_objects AS l
LEFT OUTER JOIN
   [GREENLANTERN\SQL2008R2RTM].[master].sys.dm_xe_objects AS r
   ON l.name = r.name
WHERE
   r.name IS NULL;
Results:

* These are partially in Denali CTP1. Well, the first two rows in the first resultset, at least.

Reference queries

For reference, you can see the queries I used here to perform this Sherlock Holmes-esque work against the DMVs:

I talked about how to find new DMVs and other system objects in November 2010.
I talked about how to find new and changed DMV columns in August 2009.

Download

SP1 CTP for "normal" SKUs (and also a stand-alone download for upgrading client tools only):

http://www.microsoft.com/downloads/en/details.aspx?FamilyID=bd200f8e-ba8a-45e3-af59-e28a9e2d17df

SP1 CTP for the various Express Editions (Express, Express with Tools, Express with Advanced Services):

http://www.microsoft.com/downloads/en/details.aspx?FamilyID=e70a4b51-53be-48d3-8030-80dc9e755be3

SP1 CTP Feature Packs (please don't ask me what all of these files are, but someone else did a pretty good job):

http://www.microsoft.com/downloads/en/details.aspx?FamilyID=15dcfec2-abb8-409d-91ff-c7c8e18c8409

Summary

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.

Please remember, though, that this service pack is not going to include fixes from cumulative update #7 for SQL Server 2008 R2, 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).

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.

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.

Search results matching tags 'SQL 11', 'Service Pack 1', 'SQL Server 2011', and 'SQL 2011'

More changes you might not have noticed in SQL Server 2008 R2 SP1 CTP