You can upgrade from any version 2005+ to any other version

There’s a persistent myth that you cannot upgrade a database to a version more that is more then two versions newer.

It’s not true.

You can backup+restore (safest way) or detach+attach (not a safe way) from SQL Server 2005 (or higher) to any other newer version. You just can’t do an in-place upgrade to the latest version from SQL Server 2005.

I didn’t know this until a few weeks ago, and it’s not common knowledge, hence this short blog post.

Here’s the proof, restoring a SQL Server 2005 database directly to SQL Server 2016, and I tried it successfully on SQL Server 2014 and SQL Server 2012 as well.

SELECT @@VERSION;
GO

RESTORE HEADERONLY FROM DISK = N'C:\SQLskills\SalesDB2005.BAK';
GO
Version
--------------------------------------------------------------
Microsoft SQL Server 2016 (RC3) - 13.0.1400.361 (X64) <snip>

(1 row(s) affected)

<snip> DatabaseName   DatabaseVersion DatabaseCreationDate    <snip>
--------------------------------------------------------------------
       SalesDB        611             2008-08-06 12:47:41.000 
(1 row(s) affected)
RESTORE DATABASE [SalesDB] FROM DISK = N'C:\SQLskills\SalesDB2005.BAK'
WITH MOVE N'SalesDBData' TO N'C:\SQLskills\SalesDBData.mdf',
MOVE N'SalesDBLog' TO N'C:\SQLskills\SalesDBLog.ldf',
REPLACE;
GO
Processed 24480 pages for database 'SalesDB', file 'SalesDBData' on file 1.
Processed 2 pages for database 'SalesDB', file 'SalesDBLog' on file 1.
Converting database 'SalesDB' from version 611 to the current version 852.
Database 'SalesDB' running the upgrade step from version 611 to version 621.
Database 'SalesDB' running the upgrade step from version 621 to version 622.
Database 'SalesDB' running the upgrade step from version 622 to version 625.
Database 'SalesDB' running the upgrade step from version 625 to version 626.
Database 'SalesDB' running the upgrade step from version 626 to version 627.
Database 'SalesDB' running the upgrade step from version 627 to version 628.
Database 'SalesDB' running the upgrade step from version 628 to version 629.

<snip>

Database 'SalesDB' running the upgrade step from version 845 to version 846.
Database 'SalesDB' running the upgrade step from version 846 to version 847.
Database 'SalesDB' running the upgrade step from version 847 to version 848.
Database 'SalesDB' running the upgrade step from version 848 to version 849.
Database 'SalesDB' running the upgrade step from version 849 to version 850.
Database 'SalesDB' running the upgrade step from version 850 to version 851.
Database 'SalesDB' running the upgrade step from version 851 to version 852.
RESTORE DATABASE successfully processed 24482 pages in 0.142 seconds (1346.892 MB/sec).

Very cool! This is going to make upgrading some of our clients a lot easier.

Also, remember that upgrading is a one-way operation. You absolutely cannot take the database and attach or restore it to an older version of SQL Server.

Some Microsoft links around this:

Enjoy!

Announcing the comprehensive SQL Server Wait Types and Latch Classes Library

It’s finally ready!

For the last two years, I’ve been working on-and-off on a new community resource. It was postponed during 2015 while I mentored 50+ people, but this year I’ve had a bunch of time to work on it.

I present to the community a comprehensive library of all wait types and latch classes that have existed since SQL Server 2005 (yes, it includes 2016 waits and latches).

The idea is that over time, this website will have the following information about all wait types and latch classes:

  • What they mean
  • When they were added
  • How they map into Extended Events (complete for all entries already)
  • Troubleshooting information
  • Example call stacks of where they occur inside SQL Server
  • Email link for feedback and questions

As of today, I have complete information for more than 225 common wait types (897 entries in the library) and 26 common latch classes (185 entries in the library), and I’m adding more information constantly.

If there’s one that I haven’t done yet, and you’re really interested in it, click the email link on the page to let me know. Also, if you’ve seen something be a bottleneck that I haven’t, let me know so I can update the relevant page too.

I’m doing this because there is so little information about waits and latches available online and I know that people are constantly searching for information about them. This is very much a labor of love for me as wait statistics are now my favorite area to teach.

Waiting until it’s complete isn’t feasible, so it’s open and available for use now!

Check it out at https://www.SQLskills.com/help/waits and https://www.SQLskills.com/help/latches, or jump to some examples PAGELATCH_EX, SOS_SCHEDULER_YIELD, WRITELOG, CXPACKET.

Enjoy!

Reconciling set-based operations with row-by-row iterative processing

Yesterday in class we had a discussion around the conceptual problem of reconciling the fact that SQL Server does set-based operations, but that it does them in query plans that pass single rows around between operators. In other words, it uses iterative processing to implement set-based operators.

The crux of the discussion is: if SQL Server is passing single rows around, how is that set-based operations?

[Edit 8/21/19 – what about batch mode operations? See the end of the post for details.]

I explained it in two different ways…

SQL Server Example

This explanation compares two ways of doing the following logical operation using SQL Server: update all the rows in the Products table where ProductType = 1 and set the Price field to be 10% higher.

The cursor based way (row-by-agonizing-row, or RBAR) would be something like the following:

DECLARE @ProductID   INT;
DECLARE @Price       FLOAT;

DECLARE [MyUpdate] CURSOR FAST_FORWARD FOR
SELECT [ProductID], [Price]
FROM [Products]
WHERE [ProductType] = 1;

OPEN [MyUpdate];

FETCH NEXT FROM [MyUpdate] INTO @ProductID, @Price;

WHILE @@FETCH_STATUS = 0
BEGIN
    UPDATE [Products]
    SET [Price] = @Price * 1.1
    WHERE [ProductID] = @ProductID;

    FETCH NEXT FROM [MyUpdate] INTO @ProductID, @Price;
END

CLOSE [MyUpdate];
DEALLOCATE [MyUpdate];

This method has to set up a scan over the Products table based on the ProductType, and then runs a separate UPDATE transaction for each row returned from the scan, incurring all the overhead of setting up the UPDATE query, starting the transaction, seeking to the correct row based on the ProductID, updating it, and tearing down the transaction and query framework again each time.

The set-based way of doing it would be:

UPDATE [Products]
SET [Price] = [Price] * 1.1
WHERE [ProductType] = 1;

This will have one scan based on the ProductType, which will update rows matching the ProductType, but the query, transaction, and scan are only set up once, and then all the rows are processed, one-at-a-time inside SQL Server.

The difference is that in the set-based way, all the iteration is done inside SQL Server, in the most efficient way it can, rather than manually iterating outside of SQL Server using the cursor.

Non-Technical Example

This explanation involves a similar problem but not involving SQL Server. Imagine you need to acquire twelve 4′ x 8′ plywood sheets from your local home improvement store.

You could drive to and from the store twelve times, and each time you need to go into the store, purchase the sheet, and wait for a staff member to become available to load the sheet into your pickup truck, then drive home and unload the sheet.

Or you could drive to the store once and purchase all twelve sheets in one go, with maybe four staff members making three trips each out to your pickup, carrying one sheet each time. Or even just one staff member making twelve trips out to your pickup.

Which method is more efficient? Multiple trips to the store or one trip to the store, no matter how many staff members are available to carry the sheets out?

Summary

No-one in their right mind is going to make twelve trips to the home improvement store when one will suffice. Just like no developer should be writing cursor/RBAR code to perform an operation that SQL Server can do in a set-based manner (when possible).

Set-based operations don’t mean that SQL Server processes the whole set at once – that’s clearly not possible as most sets have more rows than your server has processors (so all the rows in the set simply *can’t* be processed at the same time, even if all processors were running the same code at the same time) – but that it can process the set very, very efficiently by only constructing the processing framework (i.e. query plan with operators, scans, etc.) for the operation once and then iterating over the set of rows inside this framework.

PS Check out the technical comment from Conor Cunningham below (Architect on the SQL Server team, and my counterpart on the Query Optimizer when I was a Dev Lead in the Storage Engine for SQL Server 2005)

[Edit 8/21/19 PPS What about batch mode operations? These are done using vector-based CPU instructions working on a vector of column values (multiple rows from a column) instead of a single row, and going vector by vector instead of row by row. So it’s still doing one operation at a time, but it’s a bigger, more efficient operation. Plus there are algorithms used that are optimized for multi-core CPUs and better leverage processor caches than row-based algorithms. Bottom line: batch-mode is much more efficient than row-mode, but still has to go piece-by-piece.]