Search results matching tags 'SQL Server 2008 R2' and 'SQL 11'

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.

Downgrading a database : "You can't get there from here!"

AaronBertrand — Fri, 21 Jan 2011 23:45:00 GMT

I see this question a lot: "I have a SQL Server 2008 R2 database. Can I attach it to a SQL Server 2008 SP2 instance?"

Part of this stems from the confusion over exactly what 2008 R2 is in the first place. It is not a service pack to SQL Server 2008; it is its own, full-fledged version. A lot of people don't fully realize this because of various reasons, including:

they're on software assurance, so the upgrade to 2008 R2 was covered by their 2008 licenses, and they therefore didn't really realize it was a full version;
they're not running 2008 R2 in production, but they do have it in development, and didn't notice the @@VERSION change; or,
they're confused by the horrible naming choice.

But to answer the question: No, you cannot backup/restore or detach/attach a SQL Server 2008 R2 database to any previous version, nor can you log ship or mirror in that direction. That is also true in going from Denali to 2008 R2/2008/2005, or from 2008 to 2005, or anything from the last 6 years to 2000 - you just can't do it. A lot of people expect to be able to just set the compatibility level lower, however that is not the answer. It is the database version and the internal file/page structures that matter here; compatibility level just tells the parser and engine how to handle certain features.

What About Same Version, Lower Service Pack?

Well, I should have known this question would come up too. Let's say you are building your database in SQL Server 2008 SP2 and you are ready to deploy it to production, but production is still at SP1. You want to get the code to production but the cycle to introduce SP2 there is far too long. (Now, of course, and with the understanding that hardware equivalency is tougher, there should be a QA environment that matches production exactly in terms of software / configuration / version. So it shouldn't be going from dev to production in this manner, but I understand this is the reality for many people.)

That aside, can you do it? Yes. I set out to prove this. In a VM I laid down three instances of SQL Server 2008: one at RTM, one at SP1, and one at SP2. I created two databases in SP2: SP2DB, and SP2DB_15K. I created one database in SP1: SP1DB. I then took backups of SP1DB and SP2DB, and tried restoring them on the RTM instance. This worked fine and I was able to access the databases.

On the SP2 instance, I ran the following command, to enable 15K partitions for the SP2DB_15K database:

EXEC dbo.sp_db_increased_partitions 'SP2DB_15K', 'ON';
GO

Then I backed up the database and tried to restore it on the RTM instance. I received the following error:

Msg 3169, Level 16, State 1, Line 1
The database was backed up on a server running version 10.00.4000. That version is incompatible with this server, which is running version 10.00.1600. Either restore the database on a server that supports the backup, or use a backup that is compatible with this server.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

If I tried to restore it on the SP1 instance, I received an almost identical error (predictably, the only thing that changes is the version string):

Msg 3169, Level 16, State 1, Line 2
The database was backed up on a server running version 10.00.4000. That version is incompatible with this server, which is running version 10.00.2531. Either restore the database on a server that supports the backup, or use a backup that is compatible with this server.
Msg 3013, Level 16, State 1, Line 2
RESTORE DATABASE is terminating abnormally.

So, this exception aside, the answer is, yes, at least in SQL Server 2008, you can take a backup or detached database and restore it on an earlier service pack or RTM. Will this always be true? It's tough to say. Denali SP1 may introduce some change to the internal structure or database version, optional or otherwise, that will prevent it from being restored on RTM, just like our exception above.

Going Forward Always Works, Right?

As for the other direction, it is usually fine to backup/restore, detach/attach, log ship or mirror from an older version to a newer version; however, there are exceptions. For example, Denali does not support the 80 compatibility level, so restoring or attaching a SQL Server 2000 database on a Denali instance is out of the question (I blogged about this here in November). Another example is with SQL Server 2008 SP2 - if you have enabled support for 15,000 partitions, the internal database version is upgraded to 662, and you cannot then migrate to SQL Server 2008 R2 because R2 does not understand that version (it is expecting 661) - in this case you will have to wait until 2008 R2 SP1, when support for 15K partitions is added and the database versions will once again have an upgrade path. Here is what happens when I try to restore the SP2DB_15K database on an R2 instance:

Msg 3169, Level 16, State 1, Line 1
The database was backed up on a server running version 10.00.4000. That version is incompatible with this server, which is running version 10.50.1746. Either restore the database on a server that supports the backup, or use a backup that is compatible with this server.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

(Note that this doesn't happen with the SP2DB database, which did not have 15K partitions enabled.)

It does work on Denali, however; here is what happens when I restore the SP2DB_15K database on a Denali CTP1 instance:

Processed 160 pages for database 'SP2DB_15K', file 'SP2DB_15K' on file 1.
Processed 2 pages for database 'SP2DB_15K', file 'SP2DB_15K_log' on file 1.
Converting database 'SP2DB_15K' from version 662 to the current version 684.
Database 'SP2DB_15K' running the upgrade step from version 662 to version 668.
Database 'SP2DB_15K' running the upgrade step from version 668 to version 669.
Database 'SP2DB_15K' running the upgrade step from version 669 to version 670.
Database 'SP2DB_15K' running the upgrade step from version 670 to version 671.
Database 'SP2DB_15K' running the upgrade step from version 671 to version 672.
Database 'SP2DB_15K' running the upgrade step from version 672 to version 673.
Database 'SP2DB_15K' running the upgrade step from version 673 to version 674.
Database 'SP2DB_15K' running the upgrade step from version 674 to version 675.
Database 'SP2DB_15K' running the upgrade step from version 675 to version 676.
Database 'SP2DB_15K' running the upgrade step from version 676 to version 677.
Database 'SP2DB_15K' running the upgrade step from version 677 to version 678.
Database 'SP2DB_15K' running the upgrade step from version 678 to version 679.
Database 'SP2DB_15K' running the upgrade step from version 679 to version 680.
Database 'SP2DB_15K' running the upgrade step from version 680 to version 681.
Database 'SP2DB_15K' running the upgrade step from version 681 to version 682.
Database 'SP2DB_15K' running the upgrade step from version 682 to version 683.
Database 'SP2DB_15K' running the upgrade step from version 683 to version 684.
RESTORE DATABASE successfully processed 162 pages in 0.021 seconds (60.174 MB/sec).

And the messaging is similar for the other databases from the 2008 instance, except their version starts at 661 instead of 662.

But I'm Still Stuck!

So, you have a database that has been upgraded to 2008 R2 and you need to get it back onto 2008 or 2005? There is no straight path for this scenario; it's kind of like driving in Boston. Assuming you aren't using any features that are exclusive to the newer version (and that you can suspend activity against the newer database temporarily), you can use a schema comparison tool like Red Gate's SQL Compare, to build the modules and schema in an empty database on the older version, then use Integration Services to migrate the data.

Blogging from the PASS Keynote : 2009-11-05

AaronBertrand — Thu, 05 Nov 2009 17:24:00 GMT

Bill Graziano took the stage and promised us the shortest keynote yet. He started by giving thanks to outgoing board members Greg Low, Pat Wright and Kevin Kline. Wayne Snyder took over and gave an emotional homage to Kevin, who gave 10 solid years to PASS. Very touching. Kevin left the stage to a standing ovation.

Introduced Brian Moran, Jeremiah Peschka and Thomas LaRock as the new Directors-at-Large, and Rushabh Mehta as the new President. Reaffirmed commitment to the community, and announced the PASS European Conference in Neuss, Germany, April 21-23, 2010. The North American summit is already scheduled for November 8-11, 2010, again in Seattle. (And if you register early enough, you can get in for $995.) A lot of people who live in different time zones have expressed the desire to move the conference around, but I agree with Bill; having the conference this close to Microsoft provides enough benefit to offset the impact on travel. That's my opinion, of course.

Patrick Ortiz, Infrastructure Consulting Services, Dell

Patrick came on and talked about the structure of Dell's operations involving Microsoft architecture (Exchange, SQL Server, SharePoint, etc.). Then he jumped into how they approach the combination of consolidation, disaster recovery, and configuration management. Apologies to Patrick, but there wasn't really anything exciting about his presentation; it seemed more that it should have been an elective session as opposed to a keynote for everyone in attendance. But I guess you get this privilege when you are such a big supporting vendor, and I do hope we collectively appreciate that. Okay, about halfway through, he did have one funny line about typical disaster recovery behavior, which seemed to wake up about 10% of the audience. But this didn't redeem the segment; sorry.

David DeWitt, Data and Storage Platform Division, Microsoft

David came on and made some funny comments about past incidents on stage, including the 192-core server that seemed like it was going to catch on fire when the fans kicked in. David runs the Jim Gray Systems Lab in Madison, WI. He is working on SQL Server Parallel Data Warehouse (or, as David would like to name it, SQL*). He promises to overwhelm us with technical details as opposed to making a marketing-ish presentation.

He compared how things have changed since 1980, including a 1,000X improvement in CPU cache, memory capacity, and CPU performance, and 10,000X increase in storage capacity. Whereas transfer times have only improved 65X, and seek times have only improved 10X. Seems funny that we are worried about getting 32-, 64-, 192-core machines when the disk performance simply can't scale to keep those CPUs busy. In fact when he measures transfer bandwidth per byte of storage, drives are actually 150X slower today compared to 1980, in relative terms. In 1980, the ratio of perf from Sequential : Random is 5 : 1. Today, it is 33 : 1. Meaning we have to focus on sequential reads and move the disk heads as little as possible. He also explained that as much as 50% of the time, the CPUs is sitting there, waiting for the memory to deliver something into its L2 caches.

David's idea about improving the storage bottleneck problem is to use column-wise storage instead of row-wise. Essentially, imagine storing all the values for each column, instead of each row, on common pages. The example showed how you could store ~2,000 values for a BalanceDue column (INT) on a single page, as opposed to the page being crowded by the other columns, and therefore being able to store far fewer rows on each page. (You still have to worry about the I/O for the other column values you want to retrieve; however a subset of columns will be faster in this model. SELECT * will never be faster, of course. But we usually don't want SELECT *, right?) This is a really interesting concept, and at its core it is quite simple, but implementation in existing architectures is far from trivial.

Since disk capacities have gotten 10,000X better, you can store redundant copies using different sort orders. Especially because with columnar storage, you can compress very well, leading to great reductions in storage requirements - leaving plenty of free space that will otherwise go to waste. By using run length encoding compression - in a certain sort order, you only need to store the offsets of contiguous rows that . Bit-vector encoding and dictionary encoding can be combined with run length encoding to achieve really fantastic compression rates; David's research yields improvements from 3X to 10X over row store.

Compression makes a lot of sense in this case because (remember) CPU is 1,000X faster than it used to be, and disk is only 65X. So any time we can trade CPU cycles in exchange for less I/O, we should do it. Basically we are striving to move the majority of the work to thecomponent(s) of the system that have improved the most over time (and continue to do so).

He explained the difference between early materialization and late materialization (where materialization is the process of turning the columns into rows) - queries with joins should use early materialization because they need to process against the whole table; queries without joins can use later materialization which again pushes the materialization work to CPU.

Updates are the big problem here... you have these very tightly packed columns, so you store deltas (which the queries must observe) and occasionally rebuild. Not suitable for OLTP or cases where reads occur against more than half of the columns of a table.

Microsoft is shipping VertiPaq, an in-memory column store, in SQL Server 2008 R2. So the hint is that there is definitely some work in this area underway for SQL11.

My mind is starting to hurt, but there are some very cool ideas here.

Note that throughout David's time on stage, Twitter was still abuzz with complaining about the Dell portion of the keynote. My favorite was from Steve Jones: "@BrentO Somebody tell the Dell guy PASS is in Orlando next year."

Wayne came on stage and announced that the keynote will be available on the DVDs. Just one more reason the $125 will be worth every penny.