Do you need to update statistics after an upgrade?

This post originally went live on May 11, 2018, but modifications were made on May 14, 2018 after some additional internal discussions with Microsoft.  Changes made on May 14, 2018 are in blue. 

There are a variety of methods we use for helping customers upgrade to a new SQL Server version, and one question we often get asked is whether or not statistics need to be updated as part of the upgrade process.

tl;dr

Yes.  Update statistics after an upgrade. Further, if you’re upgrading to 2012 or higher from an earlier version, you should rebuild your indexes (which will update index statistics, so then you just need to update column statistics).

History

Some of you may remember that the stats blob changed between SQL Server 2000 and SQL Server 2005, and Microsoft specifically recommended updating statistics after upgrading from SQL Server 2000.  Official Microsoft documentation about the stats blog change in SQL Server 2005 is difficult to find, but this article includes the following paragraph:

After you upgrade from SQL Server 2000, update statistics on all databases. Use the sp_updatestats stored procedure to update statistics in user-defined tables in SQL Server databases. This step is not necessary for upgrading from SQL Server 2005.

Current Microsoft documentation related to upgrading does not state anything specific about updating statistics, but people continue to ask and if you peruse forums, blog posts, and other social media options, you’ll see recommendations to update statistics. Further, the documentation that Microsoft provides about when to update statistics does not mention anything about upgrades.

Side bar: I don’t recommend using sp_updatestats, and here’s why: Understanding What sp_updatestats Really Updates.

Today

The statistics blob has not changed since SQL Server 2000 to my knowledge, but I thought I would ask someone from Microsoft for an official recommendation to share publicly.  Here you go:

Microsoft suggests that customers test the need for a full update of statistics after a major version change and/or a database compatibility level change.

Further items to note:

  1. If Microsoft updates the format of statistics (e.g. the stats blog), customers will be expected to update statistics after an upgrade.
    1. Microsoft does not always upgrade the statistics format as part of a major version upgrade.
  2. There are occasions where Microsoft does not change the format of statistics, but they do change the algorithm for creating statistics as part of a major version upgrade or database compatibility level change.

In addition, there was a change in the nonclustered leaf level internals in SQL Server 2012, so if you are upgrading to 2012 or higher from an earlier version (e.g. 2008, 2008R2), rebuild your nonclustered indexes.  And remember, rebuilding indexes updates the statistics for those indexes with a fullscan, so you do not need to update them again.

Conclusion

As part of your upgrade methodology, it is recommended (by me, based on experience with a lot of customer upgrades) to build in time to update statistics.  I’ve gotten some pushback from customers who don’t want to update statistics after upgrade because it takes too long.  Some kind reminders:

  • Updating statistics is an online operation, therefore, the database and related applications are accessible and usable. A statistics update does take a schema modification lock so you’re not allowed to make any changes to a table while its stats are updating.  Therefore, if you decide to change your schema after upgrading your SQL Server version (not something I would typically recommend), do that before you update stats.
  • You need to update statistics regularly to provide the optimizer with current information about your data, so at some point it needs to be done. Immediately after an upgrade is a really good time, considering the aforementioned items.

If you’re not comfortable upgrading to a newer version of SQL Server, we can help!  I’m in the process of helping a customer migrate from SQL Server 2012 to SQL Server 2017, and I’m so excited to get them up to the latest version so they can start using some new features…like Query Store 😉

DBCC CLONEDATABASE Cannot insert duplicate key Error

If you’ve been using DBCC CLONEDATABASE at all, you might have run into a cannot insert duplicate key error (or something similar) when trying to clone a database:

Database cloning for ‘YourDatabase’ has started with target as ‘COPY_YourDatabase’.
Msg 2601, Level 14, State 1, Line 1
Cannot insert duplicate key row in object ‘sys.sysschobjs’ with unique index ‘clst’. The duplicate key value is (1977058079).

If you do some searching, you’ll probably end up at this Connect item: DBCC DATABASECLONE fails on sys.sysowners.

The Connect item states that the problem exists because of user objects in model.  That’s not the case here.

I’m working with a database created in SQL Server 2000…now running on SQL Server 2016.  It turns out that when you create new user tables in a SQL Server 2000 database, the first objects have the IDs 1977058079, 2009058193, and 2041058307.  (Yes, we actually tested this…I had a copy of SQL 2000 in a VM but Jonathan installed one and dug into it to figure out the IDs.)  There were a lot of changes between SQL Server 2000 and SQL Server 2005, and that included changes to model.  In the model database in SQL Server 2005 and higher, the aforementioned IDs are used for QueryNotificationErrorsQueue, EventNotificationErrorsQueue and ServiceBrokerQueue respectively…Service Broker objects.  As a result, my user database – created in SQL Server 2000 – has user objects with IDs that are the same as system objects in model (and of course when the system objects like QueryNotificationErrorsQueue, EventNotificationErrorsQueue and ServiceBrokerQueue got created in my user database, they got completely different IDs).

Edit 2017-06-15: This issue is fixed in CU3 for SQL Server 2016 SP1 and CU5 for SQL Server 2014 SP2! See KB 4016238, FIX: DBCC CLONEDATABASE is unsuccesful if the source database has an object originally created in SQL Server 2000.

To determine if this is an issue for any of your user databases you can use the following queries (change user_database to the name of your DB):

/*
lists tables in the user database that have an ID that matches an object in model
*/
SELECT m.id, m.name, c.name, c.id, m.type
FROM model.sys.sysobjects m
FULL OUTER JOIN user_database.sys.sysobjects c
ON m.id = c.id
JOIN user_database.sys.objects o
ON c.id = o.object_id
WHERE o.is_ms_shipped != 1
AND m.id IS NOT NULL;

/*
lists system objects in the user database that do *not* match the id of the same object in model
(FYI only)
*/
SELECT m.id, m.name, c.name, c.id, m.type
FROM model.sys.sysobjects m
FULL OUTER JOIN user_database.sys.sysobjects c
ON m.name = c.name
JOIN user_database.sys.objects o
ON c.id = o.object_id
WHERE m.id != c.id

To workaround this, you need different IDs for the affected objects in the user database.  Now, I could recreate my tables in the user database, confirm the ID isn’t used for any system object in model, and then move the data over…  But I have some tables that are larger than 100GB, so it’s not an easy fix.

If you’re interested in reproducing the issue, sample code is below (thanks Jonathan).  You need SQL Server 2000 installed and at least SQL Server 2005 or 2008 or 2008R2.   You cannot restore the SQL 2000 backup to SQL Server 2016 directly, you must do an intermediate upgrade to 2005, 2008, or 2008R2, then go to 2016.  Paul has tested upgrading a SQL Server 2005 database to a newer instance here; but for SQL Server 2000 you have to perform the intermediate upgrade.

Code to create the issue

DROP DATABASE New2000
GO

CREATE DATABASE New2000
GO

USE New2000
GO

DECLARE @loop INT
SET @loop = 0

WHILE @loop  < 3
BEGIN

	DECLARE @table NVARCHAR(4000)

	SET @table = 'table_' + REPLACE(REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR(30), GETDATE(), 121), ':', ''), '-', ''),  ' ', ''), '.', '')

	DECLARE @sql NVARCHAR(4000)

	SET @sql = 'CREATE TABLE ' + @table+ ' (RowID INT)';

	EXEC(@sql)

	SET @Loop = (select COUNT(*) FROM sysobjects WHERE id IN (
		1977058079,
		2009058193,
		2041058307)
		)

	IF @loop = 0
	BEGIN
		PRINT 'Dropping Table'
		SET @sql = 'DROP TABLE '+ @table
		EXEC(@sql)
	END

	WAITFOR DELAY '00:00:00.010'

END

/*
	Backup the database in SQL Server 2000
	Restore in SQL Server 2016
*/

DBCC CLONEDATABASE ('New2000', 'COPY_New2000');
GO

 

Endpoints for Mirroring and AGs in SQL Server 2016

I migrated a customer to SQL Server 2016 last weekend (YAY!) and ran into an interesting issue. The original environment was SQL Server 2012 on server A. The new environment, running SQL Server 2016, is a three-node Availability Group with servers B, C, and D. I had already set up the AG with a test database in the new environment, with B as the primary and C and D as replicas. To upgrade with little downtime, I mirrored from server A to server B, and that’s where I ran into this error:

Alter failed for Database ‘AdminSQLskills’. (Microsoft.SqlServer.Smo)
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
The server network address “TCP://avengers.com:5022” can not be reached or does not next.
Check the network address name and that the ports for the local and remote endpoints are operational. (Microsoft SQL Server, Error: 1418)

[You can see the image of the error in this StackOverflow post, which is also where I found the solution.]

I verified the following:

1. The databases on server B had been restored with NORECOVERY.
2. The accounts I used had the same permissions on both instances.
3. The endpoints existed.
4. Encryption was enabled for both endpoints.

Then I found my issue. The endpoints had different encryption methods.

For SQL Server 2014 and earlier, the endpoints use RC4 encryption by default. Starting in SQL Server 2016, the end points use AES encryption by default (see CREATE ENDPOINT). According to endpoint documentation, the RC4 encryption is deprecated.

The fix was easy, on the 2012 server I changed the encryption to AES:

ALTER ENDPOINT [Mirroring]
    FOR DATA_MIRRORING ( ENCRYPTION  = REQUIRED ALGORITHM AES);
GO

Note that if I had changed the encryption on the 2016 instance to use RC4 encryption, the Availability Group would no longer work.

Once I made this change, mirroring was up and running. All my prep work paid off, as the upgrade last weekend took minutes once we confirmed all services were shut down and users were out of the system. We had minimal post-upgrade issues to work through, and my next step is to enable Query Store 🙂 Hooray for 2016!