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

 

PASS Summit Pre-Con on Query Store

PASS has announced the pre-cons for this year’s Summit and I am *thrilled* to have the opportunity to present one centered around Query Store! This is a topic I’ve been presenting on since before SQL Server 2016 was released in June last year.  I’m ridiculously excited about it.  I’m already developing new content and demos – I hope I can fit everything I want to cover into one day!  (I bet you’re surprised that there will be demos :)  LOTS of demos!)

You can find the abstract here, and if you’re not sure if it’s right for you, please email me with your specific questions. There are a lot of great pre-cons available and I want to make sure that if you’re attending mine on Monday, October 30th, it’s the best fit for you. You’ll get the most from this pre-con if you are familiar with query plans, understand what’s in the plan cache, and can write basic T-SQL queries to find information.

I’m really looking forward to being at the Summit this year – and whether you attend any session I present or not, please introduce yourself if we haven’t met in person before!

Query Store Trace Flags

Microsoft maintains a list of supported trace flags and I noticed that there are two new ones related to Query Store: 7745 and 7752.  The descriptions for these Query Store Trace Flags are pretty straight-forward, but for those of you not familiar with Query Store, I thought I’d provide some context and details.

7745

When you enable and configure Query Store, one setting to consider is DATA_FLUSH_INTERVAL_SECONDS.  This setting defaults to 900 (15 minutes), and it determines how frequently Query Store data will be flushed to disk.  As a reminder, some Query Store data is stored in memory as an optimization.  If Query Store had to flush data to disk every time a query executed, Query Store would quickly become your biggest performance problem, therefore data is temporarily stored in memory.  The DATA_FLUSH_INTERVAL_SECONDS setting controls the maximum amount of time SQL Server will wait to flush data from memory to disk, but know that data could be flushed more frequently (also as an optimization).  When I first learned about Query Store, it was my understanding that this setting was a tradeoff between “how much Query Store data are you willing to lose if your server unexpectedly shuts down” and “how much of a performance impact do you want to introduce by having Query Store write data to disk (internal tables in the user database) more frequently”?  Because the Query Store data is not business critical, my advice has been that 15 minutes is a good balance.  However, I now know that when you initiate a shutdown of SQL Server, by default it will wait to write that Query Store data to disk – and I’ll be honest in that I don’t know how long it will wait.  In some cases (e.g. a fail over in a HA/DR scenario), you might not want to wait any longer than absolutely necessary for SQL Server to shut down.  In that case, you can either use SHUTDOWN WITH NOWAIT (not what I would recommend at all) or trace flag 7745 to bypass writing any Query Store data still in memory to disk.  Using 7745 means that you can lose some Query Store data.

7752

When you start SQL Server, it loads some data from the Query Store internal tables into memory (again, this is an optimization to make specific capabilities of Query Store complete quickly).  In some cases this is a small amount of data, in other cases, it’s larger (potentially a few GB), and as such, it can take seconds or minutes to load. As a result, if you execute a query before the Query Store data has finished loading, the query will not execute until the data has been loaded.  To load the Query Story asynchronously, and still allow queries to execute, use trace flag 7752.  With this trace flag enabled, queries can execute while the Query Store data is being loaded asynchronously in the background, however, Query Store is in a read-only state.  No query information will be written to Query Store until after the data has been loaded into memory.  If you don’t know if Query Store is taking a long time to load and thus impacting your queries, set up an Extended Events session that captures the WAIT_INFO event, filtering on the QDS_LOADDB wait_type, and have it run at startup (and also have a job that stop the XE session 10 or 15 minutes after SQL Server starts up – you don’t need to continuously run this session).  After one or more restarts, check the XE output and see if you have any events for the QDS_LOADDB wait type, and if so, add up the total duration for the wait type and compare it against the startup duration for the instance.

Finally, know that the behavior of trace flag 7752 may become the default behavior in a future release – that’s yet to be determined – but if it did, there will ideally be a status change to Query Store to signify that during the asynchronous data load it is in a read-only state.

My thoughts

I always expected that I could potentially lose Query Store data, and so I’m still ok with that.  When I ask SQL Server to shut down, I want it to shut down as quickly and efficiently as it always has, so I’m pretty sure I’m going to be enabling 7745.

I will also enable trace flag 7752.  While I can check to see if I’m experiencing the QDS_LOADDB, if this will become the default behavior in the future, I might as well adopt it now!

Thoughts on public speaking / presenting / teaching

A colleague of mine asked me this on Twitter the other day:

When you started speaking did you know straight away that it was something you loved doing?

My answer: No.

It’s a really good question, and I said I’d go more in depth.  We have to go way back in time.  In asking the question, I believe my colleague was thinking about speaking in the SQL Server community, but for me it started before I found the SQL Server community.

I don’t think there are many people that love public speaking from the get-go.  At the University of Michigan I had to take Communications 101 (a public speaking course) in order to graduate.  I dreaded it.  Most people did.  But I took in in the fall of my sophomore year and got an A.  (Yes, I went and checked my college transcript.)

But the first time I really spoke to a group of peers and professors to explain or teach something was my first year of graduate school.  We had a day to celebrate the accomplishments within the Kinesiology department, and I had been working on a grant that tested the effects of Botox on children with cerebral palsy.  My advisor, Dr. Brown, wanted me to present our initial findings.  I had 10 minutes.  I created 10 slides and had a one minute video to show.  I remember Dr. Brown telling me that she used talk about one slide for 10 minutes, she had no idea how I’d get through all 10.  I was terrified I’d finish in 5 minutes.

I have hazy memory of my talk – I remember what I wore, I remember thinking my voice was shaking, I remember feeling nervous, I remember nodding at Dr. Watkins to start the video…and that’s it.

I can’t remember any feedback, but I do remember thinking I didn’t want to do that again.

Flash forward a couple months to Dr. Brown’s idea that I could teach the motor control section of the Movement Science 110 course.  Teach to freshman and sophomores.  People who were PAYING a lot of money to go to school at Michigan.  Again, I was terrified, despite Dr. Brown’s logic: I’d get paid, I would experience teaching, and it gave me a chance to learn the material even better.  I didn’t even have to create the content – I could just use what she had already been using.  I don’t know if I even tried to argue, I probably knew I wouldn’t win (Dr. Brown was pretty persistent).  So in the fall of 1997, I started teaching.  On the first day I had student argue with me about theories.  THEORIES!  I was teaching science.  I wanted to quit, but I didn’t.  I taught that class for two years, and I probably learned more than my students did.

Fast-forward a couple years to my first job in technology, at a software company, providing technical support.  I was soon asked if I was interested in training customers as well, as there was only one other person who handled training at that time.  I said yes – voluntarily this time.  I learned the software, I learned how to teach other people how to use it, and I got better.

By the time I worked in the Database Services department at Hyland I sought out opportunities to teach.  Every year there was a user conference, and during my first year on the team I asked a senior member of management if I could help with his presentation.  Now, I don’t remember the impetus, but we started co-presenting, until the year that he looked at me and said: “You can do this without me, I’m about to retire.”  I taught that class at multiple conferences over the next few years.  I asked to add database classes to the conferences and I developed and delivered those.  I provided internal training and recorded material to be viewed by partners and users online.  By then, I loved it.

When I discovered the SQL Server community and found out there was a conference every year (the PASS Summit) my initial thought was, “I want to present at that!”  And so I worked my way up.  I presented to my user group in the winter of 2010, and then at the Cleveland SQLSaturday in February 2011.  My first Summit was that same year, with a lot of other SQLSaturday events in between.

I’ve now been “presenting” off and on for about 20 years.  And I put presenting in quotes because I don’t think of it that way; I think I’m always teaching.  I’ve gotten a lot of experience in those years, and as a result I’ve gotten comfortable in front of a crowd and have developed my own style.  And while I’m proud of what I’ve accomplished, I still work to improve.  I tweak every session trying to figure out how to make an explanation even clearer.  I change demos all the time, trying to get them *just right* so they easily demonstrate a concept.  I continually read an audience and make adjustments on the fly when I can.  It doesn’t end, and I’m ok with that.  I do enjoy presenting/teaching now, but I didn’t when I started…because it was uncomfortable, because it was hard, because I didn’t know what I doing.  Because like everything else, it takes practice to become good, even if you have a knack for it from the start.

The greats weren’t great because at birth they could paint
The greats were great cause they paint a lot
~Macklemore and Ryan Lewis

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!

Remove Files From tempdb

I made a mistake with a script today. I created three new tempdb files sized at 10GB each that filled up a hard drive.

Whoops.

Luckily it was in one of my own testing VMs, so it wasn’t awful. Fixing it, however, was a fun one.

**NOTE: All work was done in a test environment. Proceed with caution if you’re running these commands in Production and make sure you understand the ramifications.

In order to remove a file from a database in SQL Server, it has to be empty. For each file I wanted to remove I needed to run:

USE [tempdb];
GO
DBCC SHRINKFILE (logicalname, EMPTYFILE);
GO

However, every time I tried to run this command for any file, I would get a message like this:

DBCC SHRINKFILE: Page 4:130 could not be moved because it is a work table page.
Msg 2555, Level 16, State 1, Line 1
Cannot move all contents of file “logicalname” to other places to complete the emptyfile operation.

This error came up for each file, even if I restarted the instance and did nothing, and even if I restarted it in single-user mode.

Then I found some posts about clearing the procedure cache, and the session cache, so I cleared everything…go big or go home right? Remember, I’m working in a local test environment so this isn’t a big deal.

DBCC DROPCLEANBUFFERS
GO
DBCC FREEPROCCACHE
GO
DBCC FREESESSIONCACHE
GO
DBCC FREESYSTEMCACHE ( 'ALL')
GO

If I tried to empty the file after that, it still failed.

**Note: In talking with Jonathan after the fact, he said he’s seen this before, where every file in tempdb has a workfile in it that you cannot remove. He thinks the behavior started with SQL Server 2012. I haven’t found any documentation from Microsoft about this…yet…

Now I was getting annoyed (mostly with myself for this mistake in the first place). Finally, I tried started SQL Server with minimal configuration, using -f, and connected with sqlcmd. The documentation notes that “tempdb is configured at the smallest possible size.” So small that not all the files were there! I couldn’t run the DBCC SHRINKFILE command because the additional files weren’t available. Perfect, as then I could just remove them:

ALTER DATABASE [tempdb]  REMOVE FILE [logicalname]
GO

I ran the ALTER DATABASE [tempdb] REMOVE FILE for each of the three files I added, shut down the instance, removed -f, and restarted. The files were removed! However, they were still sitting out on the drive, but because they were no longer in use I could delete them. Space reclaimed, time for some chocolate.

SQLskills SQL101: Updating SQL Server Statistics Part II – Scheduled Updates

In last week’s post I discussed the basics of how automatic updates to statistics occur in SQL Server.  This week I want to talk about scheduled (aka manual) updates, because as you might remember, we really want to control when statistics are updated.

In terms of updating statistics you have multiple options, including:

  • Update Statistics Task (Maintenance Plan)
  • sp_updatestats
  • UPDATE STATISTICS

For systems that do not have a full-time DBA, one of the easiest methods for managing statistics is the Update Statistics Task.  This task can be configured for all databases or certain databases, and you can determine what statistics it updates:

Update Statistics Task- deciding which statistics to update

Update Statistics Task – deciding which statistics to update

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

You might think you want to update All existing statistics.  If you just had a plan with just this task, that might be true.  But what I see most often is that someone configures the Rebuild Index task, and then has the Update Statistics task as the next step.  In that case, if you are running SQL Server 2014 and below, you want to update Column statistics only.  When you run the Rebuild Index task in SQL Server 2014, you rebuild all indexes, and when you rebuild an index, its statistic is updated with a fullscan.  Therefore, there is no need to update Index statistics after you rebuild all your indexes, but you do need to update column statistics.

This is a bit more complicated in SQL Server 2016.  The Rebuild Index task has more options in SQL Server 2016, which is nice for that specific task, but it makes providing guidance about statistics updates a bit trickier.  In SQL Server 2016 you can configure the Rebuild Index task so that it only rebuilds an index if a certain level of fragmentation exists.  Therefore, some of your indexes will rebuild (and thus have statistics updated) and some will not (and not have updated statistics).  How do you manage that with the Update Statistics task?  Well, in that case you probably select All existing statistics and update some statistics for a second time, which is really a waste.  Therefore, if you’re on SQL Server 2016, you probably want to look at more intelligent updates.

One method, which I would not say is intelligent, but it is an option, is to use sp_updatestats in a scheduled job that runs on a regular basis.  This command is one you run for a database, not for a specific statistic or index or table.  The sp_updatestats command will only update statistics if data has changed.  That sounds good, but the caveat is that only one (1) row has to have changed.  If I have a table with 2,000,000 rows, and only 5 rows have changed, I really don’t need to update statistics.

The other method is to use UPDATE STATISTICS in a scheduled job.  The UPDATE STATISTICS command can be run for individual statistics or for a table (updating all statistics for a table).  You can develop an intelligent method to use this command, which is what I recommend.  Rather than a blanket update to all statistics, or statistics where one row has changed, I prefer to update statistics that are outdated based on the amount of data that has changed.  Consider the aforementioned table with 2,000,000 rows.  If I let SQL Server update statistics automatically, I would need 400,500 rows to change.  It’s quite possible that with a table of that size I would want to statistics to update sooner – say after 200,000 rows had changed, or 10% of the table.

We can programmatically determine whether we need to update statistics using the sys.dm_db_stats_properties DMF.  This DMF tracks modifications, and also tells us how many rows were in the table when statistics were last updated, and the date statistics were updated. For example, if I update some rows in Sales.SalesOrderDetail, and then look at the output from the DMF, you can see that the modification counter matches the number of rows I changed* for the ProductID index:

USE [AdventureWorks2012];
GO

UPDATE [Sales].[SalesOrderDetail]
SET [ProductID] = [ProductID]
WHERE [ProductID] IN (921,873,712);
GO

SELECT
[so].[name] [TableName],
[ss].[name] [StatisticName],
[ss].[stats_id] [StatisticID],
[sp].[last_updated] [LastUpdated],
[sp].[rows] [RowsInTableWhenUpdated],
[sp].[rows_sampled] [RowsSampled],
[sp].[modification_counter] [NumberOfModifications]
FROM [sys].[stats] [ss]
JOIN [sys].[objects] [so] ON [ss].[object_id] = [so].[object_id]
CROSS APPLY [sys].[dm_db_stats_properties] ([so].[object_id], [ss].stats_id) [sp]
WHERE [so].[name] =  N'SalesOrderDetail';
GO
Output from sys.dm_db_stats_properties

Output from sys.dm_db_stats_properties

 

 

 

 

 

 

*You’re correct, I technically didn’t change ProductID to a new value, but SQL Server doesn’t know that.  Also, there’s a foreign key on that column which is why I can’t easily change it a random number.

Armed with this type of data, we can intelligently decide whether we should update statistics because a percentage of rows (rather than just a fixed number of rows) have changed.  In the example above, only 8% of data changed – probably not enough to require me to update statistics.  It’s quite possible that some statistics need to be updated daily because there is a high rate of change, and other statistics only need to be updated weekly or monthly because data doesn’t change much at all.

Ultimately, when it comes to scheduled updates of statistics, you can go the sledgehammer route (Update Statistics task or sp_updatestats) or the selective update route (UPDATE STATISTICS and sys.dm_db_stats_properties).  Using the Update Statistics task or sp_updatestats is easier if you’re not familiar with SQL Server and if you have the maintenance window and resources for it to run.  To be perfectly clear: if you’re a system administrator and want to update statistics, I’d rather you use this approach than nothing at all.  Presumably, if you don’t have a full-time DBA, you also don’t need the system to be available 24×7, so you can take the performance hit at night or on the weekend while all statistics update.  In that situation I’m ok with the approach.

But, if you are a DBA and you know how to write T-SQL, then you can absolutely write some code that programmatically looks at your statistics and decides what to update and what to skip.  Whatever method you use, just make sure your updates are scheduled to run regularly through an Agent Job, and make sure you have Auto Update Statistics enabled just in case the job doesn’t run and you don’t get notified for some reason (this would be Plan B, because it’s always good for DBAs to have a Plan B!).

Additional Resources

SQLskills SQL101: Updating SQL Server Statistics Part I – Automatic Updates

One of my favorite topics in SQL Server is statistics, and in my next two posts I want to cover how they are updated: either by SQL Server or by you.

We’ll start with updates by SQL Server, and these happen automatically. In order for automatic updates of statistics to occur, the AUTO UPDATE STATISTICS database option must be enabled for the database:

Auto Update Statistics option via SSMS

Auto Update Statistics option via SSMS

 

 

 

 

 

 

 

 

 

 

 

 

This option is enabled by default for every new database you create in SQL Server 2005 and higher, and it is recommended to leave this option enabled. If you’re not sure if this option is enabled, you can check in the UI or you can use the following T-SQL:

SELECT
	[name] [DatabaseName],
	CASE
		WHEN [is_auto_update_stats_on] = 1 THEN 'Enabled'
		ELSE 'Disabled'
	END [AutoUpdateStats]
FROM [sys].[databases]
ORDER BY [name];
GO

If you want to enable the option, you can run:

USE [master];
GO
ALTER DATABASE [<database_name_here] SET AUTO_UPDATE_STATISTICS ON WITH NO_WAIT;
GO

With the option enabled, SQL Server marks statistics as out of date based on internal thresholds.

For SQL Server 2014 and earlier, the threshold was 500 rows plus 20% of the total rows in a table. For example, if I have a table with 10,000 rows in it, when 2500 rows have changed, then SQL Server marks the statistic as out of date. There are exceptions to this (e.g. when a table has less than 500 rows, or if the table is temporary), but in general this threshold is what you need to remember.

A new trace flag, 2371, was introduced in SQL Server 2008R2 SP1 to lower this threshold. This change was designed to target large tables. Imagine a table with 10 million rows; over 2 million rows would need to change before statistics would be marked as out of date. With trace flag 2371, the threshold is lower.

In SQL Server 2016, the threshold introduced by trace flag 2371 is used if you have the compatibility mode for a database set to 130. This means that in SQL Server 2016, you only need to use trace flag 2371 to get that lower threshold if you have the database compatibility mode set to 120 or lower.
If statistics have been marked as out of date, then they will be updated by SQL Server automatically the next time they are used in a query. Understand that they are not updated the moment they are out of date…they are not updated until they are needed. Imagine the following scenarios using the original threshold:

Example 1 – PhysicianData

Date/Time Action
Sunday, March 19, 2017 2:00 AM Statistics updated for table PhysicianData, which has 500,000 rows in it
Monday, March 21, 6:00 AM Processing job runs, and 50,000 new rows are added to the PhysicianData table
Tuesday, March 21, 6:00 AM Processing job runs, and 50,500 new rows are added to the PhysicianData table; statistics for PhysicianData are marked as out of date
Tuesday, March 21, 7:35 AM A user queries PhysicianData for the first time since processing ran at 6:00 AM; statistics for PhysicianData are updated

 

Example 2 – PatientData

Date/Time Action
Sunday, March 19, 2017 2:00 AM Statistics updated for table PatientData, which has 2,000,000 rows in it
Monday, March 20, all day Different processes and user activities access PatientData, adding new rows, changing existing rows.  By the end of day 100,000 rows have changed or been added.
Tuesday, March 21, all day Different processes and user activities access PatientData, adding new rows, changing existing rows.  By the end of day 250,000 rows have changed or been added.
Wednesday, March 22, all day Different processes and user activities access PatientData, adding new rows, changing existing rows.  At 8:15PM, 400,500 rows have changed or been added.
Wednesday, March 22, 8:16 PM A user queries PatientData; statistics for PatientData are updated

 

I’ve given two very contrived example to help you understand that statistics are not always updated the exact moment they are marked as out of date.  They might be – if the table has a lot of activity, but they might not be.

As I stated originally, it is recommended to leave this option enabled for a database.  However, we do not want to rely on SQL Server for our statistics updates.  In fact, think of this option as a safety net for statistics.  We want to control when statistics are updated, not SQL Server.  Consider of the first scenario I described, where statistics updated at 7:35AM.  If that’s a busy time of day and this is a large table, it could affect performance in the system.  It’s preferable to have statistics updated when the system has less activity, so that resource use doesn’t contend with user activity, but we always want to leave Auto Update Statistics enabled for a database…just in case.

Additional Resources:

SQLskills SQL101: The SQL Server ERRORLOG

One of the most useful logs you can review when there’s a problem in SQL Server is the ERRORLOG.  It may not always be the answer to your problem, but it’s a good place to start.

When you initially install SQL Server it only keeps the most recent six (6) ERRORLOG files, in addition to the current, active one.  A new ERRORLOG file is generated when the instance restarts, or when you run sp_cycle_errorlog.  There are drawbacks to this default configuration.  If you do not regularly restart your instance (which is perfectly fine), then one ERRORLOG file could contain months, maybe even a year or more, of information.  That’s a lot of entries to read through if you’re looking for patterns or unusual errors.  In addition, if you happen to run into a scenario where you restart the instance multiple times in succession – three or four times for example – you could potentially lose months of history.

The solution is to recycle the ERRORLOG on a regular basis (I like to do this weekly), and increase the number of files retained.  To recycle the ERRORLOG every week, set up an Agent job that calls sp_cycle_errorlog.  I’ve included code at the end of this post to create the Agent job and weekly schedule.

Next, increase the number of ERRORLOG files you keep.  You can do this through Management Studio.  Expand the instance, then Management, right-click on SQL Server Logs and select Configure.  Enable the option Limit the number of error log files before they are recycled and then enter a number for Maximum number of error log files:  I like to keep 30 around.  That usually equates to about six months of time, including a few unplanned restarts.

Configure SQL Server to keep 30 ERRORLOG files

Configure SQL Server to keep 30 ERRORLOG files

 

 

 

 

 

 

 

 

 

You can also make this change with T-SQL:

USE [master];
GO
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'NumErrorLogs', REG_DWORD, 30;
GO

Checking the ERRORLOG configuration is always something we do as part of a health audit, and I’m always happy when I find systems with at least a few months’ worth of files that are less than a few MB in size (I think the largest ERRORLOG I’ve seen is 4GB…that one took a long time to open).  If this isn’t something you’ve configured on your SQL Server instances yet, take a few minutes and knock it out.  You won’t regret having this information when a problem comes up, or when you’re looking to see if a problem occurred a few months ago but maybe no one realized it.

If you’re interested in other posts in our SQLskills SQL101 series, check out SQLskills.com/help/SQL101.

Additional reading:

 

Code to create a SQL Agent job to run sp_cycle_errorlog weekly (Sundays at 12:01 AM):

USE [msdb];
GO
/****** Object:  Job [SQLskills Cycle ERRORLOG Weekly] ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object:  JobCategory [Database Maintenance] ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'Database Maintenance' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'Database Maintenance'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
END
DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'SQLskills Cycle ERRORLOG Weekly',
@enabled=1,
@notify_level_eventlog=0,
@notify_level_email=0,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N'Cycle the ERRORLOG once a week.',
@category_name=N'Database Maintenance',
@owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [Cycle ERRORLOG] PM ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Cycle ERRORLOG',
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=1,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'EXEC sp_cycle_errorlog;
GO',
@database_name=N'msdb',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Weekly cycle of ERRORLOG',
@enabled=1,
@freq_type=8,
@freq_interval=1,
@freq_subday_type=1,
@freq_subday_interval=0,
@freq_relative_interval=0,
@freq_recurrence_factor=1,
@active_start_date=20170301,
@active_end_date=99991231,
@active_start_time=100,
@active_end_time=235959,
@schedule_uid=N'23a32e3e-c803-451f-b85a-b77d5b97ab3a'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
GO

 

 

SQLskills SQL101: Trace Flags

“You should always use trace flag X for a SQL Server install.”

“Have you tried trace flag Y?”

“We fixed the problem using an undocumented trace flag.”

If you’re new to SQL Server, you might have heard or read some of the above statements.  If you’ve never used a trace flag, you might wonder why you might need one, and how you would know if you did need it.  SQL Server trace flags are used to change the behavior of the engine in some way.  A trace flag is ideally used for improvement, but there can be situations where a trace flag doesn’t provide the intended benefit.  In some cases, it can adversely affect the problem you’re trying to fix, or create a different issue.  As such, trace flags in SQL Server are something to use with caution.  The number one recommendation I always make when someone asks about using a trace flag is to test it, ideally in an identical or comparable situation.  This isn’t always possible, which is why there’s always a slight risk with trace flags.  There are only three (3) trace flags that we at SQLskills recommend, by default, for a SQL Server installation:

  • 1118 (for versions prior to SQL Server 2016)
  • 3023 (for versions prior to SQL Server 2014)
  • 3226

Trace flag 1118 addresses contention that can exist on a particular type of page in a database, the SGAM page.  This trace flag typically provides benefit for customers that make heavy use of the tempdb system database.  In SQL Server 2016, you change this behavior using the MIXED_PAGE_ALLOCATION database option, and there is no need for TF 1118.

Trace flag 3023 is used to enable the CHECKSUM option, by default, for all backups taken on an instance.  With this option enabled, page checksums are validated during a backup, and a checksum for the entire backup is generated.  Starting in SQL Server 2014, this option can be set instance-wide through sp_configure (‘backup checksum default’).

The last trace flag, 3226, prevents the writing of successful backup messages to the SQL Server ERRORLOG.  Information about successful backups is still written to msdb and can be queried using T-SQL.  For servers with multiple databases and regular transaction log backups, enabling this option means the ERRORLOG is no longer bloated with BACKUP DATABASE and Database backed up messages.  As a DBA, this is a good thing because when I look in my ERRORLOG, I really only want to see errors, I don’t want to scroll through hundreds or thousands of entries about successful backups.

You can find a list of supported trace flags on MSDN, and as I alluded to initially, there are undocumented trace flags.  An undocumented trace flag is one that is not supported by Microsoft.  If you ever use an undocumented trace flag and you have a problem, Microsoft will not provide support for that problem; if you decide to use an undocumented trace flag, tread carefully, particularly in production.

How will you know if you should use a trace flag?  Online you’ll typically come across a forum post, blog post, or article that describes a scenario that you might be having, with the recommendation that you fix it with a trace flag.  You could also attend a user group meeting, a SQLSaturday or conference session, and hear the same thing.  You may have it recommended to you by a consultant, or another DBA or developer.  In all cases, it’s important to first confirm that what you’re seeing in your environment matches the behavior described by the trace flag.  If you believe you should enable a trace flag, enable it in a test or development environment first where you can recreate the problem, and then test it thoroughly.  Finally, after it’s gone through rigorous testing, you can try it in production.  Notice I say “try” because even with all your testing, if may not be the right solution for your environment.

If you find you do want to give a trace flag try, there are two ways to enable/disable them:

Enabling a trace flag with DBCC TRACEON is done using T-SQL, and you have the option to set the trace flag at the session or global level.  Typically you want the trace flag to be used by the entire instance, so you enable it globally.  For testing purposes, you may just enable it at the session level.  To enable trace flag 3226 globally you would run:

DBCC TRACEON (3226, -1);
GO

The use of -1 turns on the flag for the entire instance.  To disable the trace flag you run:

DBCC TRACEOFF (3226, -1);
GO

The advantage of using DBCC TRACEON and DBCC TRACEOFF is that you don’t have to restart the instance to use the trace flag.  The drawback is that it can be disabled by anyone who has sysadmin membership and runs DBCC TRACEOFF, and that it will not persist through a restart.  I recommend using this option when testing a trace flag.

For cases where you’ve tested the trace flag and you know that you want it enabled, then you want to add it to the SQL Server service as a startup parameter.  This requires using SQL Server Configuration Manager.  Once you have Configuration Manager open, select Services on the left side and then you’ll see all the services listed on the right.  Highlight the SQL Server service, right-click and select Properties, then select the Startup Parameters tab.  To add a startup parameter use the syntax –T followed by the trace flag, as shown below:

Adding TF 3226 as a startup parameter for the SQL Server service

Adding TF 3226 as a startup parameter for the SQL Server service

Note: There should be no space between the –T and the trace flag (but if you try and put one there, SQL Server removes it for you).

Then select Add so it appears in the Existing parameters: window, and then OK, and you will be notified that the change will not take effect until you restart the instance.  If you are not able to restart the instance immediately, you can apply it using DBCC TRACEON, just be aware that someone could remove it.

Lastly, to check what trace flags, if any, are enabled for your instance, you can use DBCC TRACESTATUS.  In our case, the output shows that we have 3226 enabled globally:

DBCC TRACESTATUS;
GO
DBCC TRACESTATUS output showing TF 3226 enabled

DBCC TRACESTATUS output showing TF 3226 enabled

 

 

 

 

As you can see, using trace flags is pretty straight-forward.  However, deciding whether a trace flag is needed and then testing to ensure it provides benefit and not detriment is what requires real work.  Use trace flags wisely, and always test first!  And remember, if you want to find all of our SQLskills SQL101 blog posts visit SQLskills.com/help/SQL101.

Additional reading: