Changes to query text and the effects in Query Store

In this week’s Insider newsletter, Paul talked about an interesting customer issue I ran into last week related to Query Store, and an inadvertent change to a query’s text.  I thought I’d provide some demo code so that those of you using (or planning to use) Query Store could see how this shows up in your database.

Recreating the issue

I started by restoring a copy of the WideWorldImporters database, which you can download here.  I enabled Query Store and cleared out the data, just as a precaution.

USE [master];
GO
RESTORE DATABASE [WideWorldImporters]
FROM  DISK = N'C:\Backups\WideWorldImporters-Full.bak' WITH  FILE = 1,
MOVE N'WWI_Primary' TO N'C:\Databases\WideWorldImporters\WideWorldImporters.mdf',
MOVE N'WWI_UserData' TO N'C:\Databases\WideWorldImporters\WideWorldImporters_UserData.ndf',
MOVE N'WWI_Log' TO N'C:\Databases\WideWorldImporters\WideWorldImporters.ldf',
MOVE N'WWI_InMemory_Data_1' TO N'C:\Databases\WideWorldImporters\WideWorldImporters_InMemory_Data_1',
NOUNLOAD,
REPLACE,
STATS = 5;
GO

ALTER DATABASE [WideWorldImporters]
SET QUERY_STORE = ON;
GO

ALTER DATABASE [WideWorldImporters]
SET QUERY_STORE CLEAR;
GO

I then created a stored procedure with just one query in it:

USE [WideWorldImporters];
GO

CREATE PROCEDURE [Sales].[usp_GetFullProductInfo]
@StockItemID INT
AS

SELECT
[o].[CustomerID],
[o].[OrderDate],
[ol].[StockItemID],
[ol].[Quantity],
[ol].[UnitPrice]
FROM [Sales].[Orders] [o]
JOIN [Sales].[OrderLines] [ol]
ON [o].[OrderID] = [ol].[OrderID]
WHERE [ol].[StockItemID] = @StockItemID
ORDER BY [o].[OrderDate] DESC;
GO

Then I executed the stored procedure twice, with two different input parameters.  I purposely used WITH RECOMPILE here because I knew the values would generate different plans.

EXEC [Sales].[usp_GetFullProductInfo] 90 WITH RECOMPILE;
GO

EXEC [Sales].[usp_GetFullProductInfo] 224 WITH RECOMPILE;
GO

Within Object Explorer I then drilled into the Query Store reports (Databases | WideWorldImporters | Query Store) and double-clicked to run Top Resource Consuming Queries.  My query has two different plans:

Initial query execution, two plans

Initial query execution, two plans

 

 

 

 

 

 

 

 

 

Plan_id 1 has a clustered index scan with a filter, that feeds into the probe phase of a hash match (with a columnstore index scan for the build). This was generated with the input value of 90.

Plan 1 for query_id 1

Plan 1 for query_id 1

 

 

 

 

 

 

 

 

 

 

Plan_id 2 has the same columnstore index scan, but it feeds into a nested loop, with a clustered index seek for inner input, and was generated with the input value of 224.

Plan 2 for query_id 1

Plan 2 for query_id 1

 

 

 

 

 

 

 

 

 

 

Now let’s suppose I decide to force one plan for that query. After analysis, I decide to force the second plan (plan_id 2), which I can do either through the UI, or using a stored procedure. Since change control and documentation is a good thing, we’ll use the stored procedure:

EXEC sp_query_store_force_plan @query_id = 3, @plan_id = 8;
GO

At this point, if I re-run the stored procedure with the two different input parameters and WITH RECOMPILE, I will get the plan with the nested loop.

EXEC [Sales].[usp_GetFullProductInfo] 90 WITH RECOMPILE;
GO

EXEC [Sales].[usp_GetFullProductInfo] 224 WITH RECOMPILE;
GO
SP execution after plan is forced

SP execution after plan is forced

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Now…let’s assume that this forced plan is used going forward and there are no issues. And then let’s assume that a developer decides to add some comments to the stored procedure, and they know they should use ALTER to keep the object_id the same…but somehow, a couple extra spaces end up in the query.

ALTER PROCEDURE [Sales].[usp_GetFullProductInfo]
@StockItemID INT
AS
/*
This is getting information based on ID
*may need to add more columns per BL
*/
SELECT
[o].[CustomerID],
[o].[OrderDate],
[ol].[StockItemID],
[ol].[Quantity],
[ol].[UnitPrice]
FROM [Sales].[Orders] [o]
JOIN [Sales].[OrderLines] [ol]
ON [o].[OrderID] = [ol].[OrderID]
WHERE [ol].[StockItemID] = @StockItemID
ORDER BY [o].[OrderDate] DESC;

GO

I re-run the stored procedures again (WITH RECOMPILE) and now I get that other plan again for the input value of 90:

SP execution after ALTER, spaces inadvertenly added

SP execution after ALTER, spaces inadvertenly added

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

If I look in Query Store, I can see I now have two queries that look nearly identical:

Two queries in QS after SP was changed

Two queries in QS after SP was changed

 

 

 

 

 

 

There are different query_id values in sys.query_store_query, as well as different entries for each query in sys.query_store_query_text. Both queries are still tied to the stored procedure (object_id 1483152329) which can be confusing if you don’t understand what occurred. In order to confirm which query has executed most recently, you can use the query below, which joins both system views (replace the query_ids as appropriate):

SELECT
[qst].[query_text_id],
[qst].[query_sql_text],
[qst].[statement_sql_handle],
[qs].[query_id],
[qs].[query_hash],
[qs].[last_execution_time],
[qs].[object_id]
FROM [sys].[query_store_query] [qs]
JOIN [sys].[query_store_query_text] [qst]
ON [qs].[query_text_id] = [qst].[query_text_id]
WHERE [qs].[query_id] IN (1, 14);
GO
Information for both queries from Query Store views

Information for both queries from Query Store views

 

 

 

 

Summary

The take home message here is that you need to be careful when changing objects (stored procedures, functions) where you have queries with forced plans. Inadvertent changes in spacing or casing create entirely separate queries in Query Store, which means that the original query with a forced plan is no longer in use.  You’ll either have to ALTER the procedure again without the extra spaces in the query (which may not be as easy as it sounds), or get the new version of the query to execute the “right” plan, and then force that plan.  It may be appropriate to set up monitoring for object changes, or teach your developers how to identify what queries are forced and to what objects they below (hint: use the Queries With Forced Plans report that’s available in SSMS 17.x). Hope this helps, and if you want to learn more about Query Store, consider my full day pre-con at Summit next month where we’ll cover this type of scenario and a whole lot more! Feel free to reach out if you have questions about it :)

p.s. did you see that they announced the release date for SQL Server 2017? I’ll be running that build at my pre-con!

Query Store Pre-Con at the PASS Summit: Is it right for you?

I received an email over the weekend asking about my pre-con at the PASS Summit, my general session at the Summit, and my Query Store course on Pluralsight. The individual wanted to know the requirements for the pre-con, and what overlap exists between these three. Great question.

First, feel free to review the abstracts for all:

PASS Summit Pre-Con, October 30, 2017 (Seattle, WA)
Solving Common Performance Problems Using Query Store

PASS Summit General Session, Date TBA (Seattle, WA)
Query Store and Automatic Tuning in SQL Server

Pluralsight, online
SQL Server: Introduction to Query Store

None of these courses require pre-existing knowledge of Query Store.

The Pluralsight course starts at the beginning and walks you through configuring and using Query Store in SQL Server 2016 in find performance issues both retroactively and proactively (3 hours total).

The general session at the Summit discusses Query Store at a high level (what it is and how to use it), and talks about the new Automatic Tuning feature in SQL Server 2017 (Automatic Plan Correction) as well as Automatic Index Management in Azure SQL Database (75 minutes).

My full day pre-con covers everything from Pluralsight and the general session, and a whole lot more. If you want:

  • all the gory details on what Query Store is, how to configure it, and what data exists
  • best practices
  • a slew of demos about how you can use it to find performance issues (out-of-the-box Query Store methods and custom options not documented elsewhere)
  • tons of scripts that you can take home and use immediately
  • details about the wait statistics data added to Query Store in SQL Server 2017
  • additional uses for Query Store (beyond what’s documented)
  • how to use Automatic Plan Correction in SQL Server 2017 (automatic and manual) and what Automatic Index Management looks like in Azure SQL Database
  • an overview of how to visualize Query Store data

then the pre-con is going to give you all that and whatever else I can find time to fit in.  It is a packed day and I am in the thick of pulling all the slides and demos together (so if there’s something you want to see/know that you don’t see mentioned here, feel free to email me!).

I hope this answers any questions you have about my pre-con at Summit and other Query Store sessions available, but if not, just contact me!

Have a great week, and to my friends in the south – the Stellato family is keeping you in our thoughts.

SQLskills 101: The Other Bad Thing About Clearing Procedure Cache

Every time you execute a query against SQL Server, it goes through optimization and compilation and a query plan is generated. Most of the time that plan goes into cache (there’s always exceptions, which is a discussion for a separate time), which means that the next time that query executes, SQL Server can use the same plan.  Now, re-using the same plan is typically a good thing; SQL Server doesn’t have to go through full optimization and compilation again.  But sometimes that plan isn’t ideal for the query and you want to remove it from the plan cache.  You can do this manually.  Plans can also age out if they’re not being used, but if I want to purposely evict a plan from cache, I have several options:

Ideally, you should remove only what’s absolutely necessary.  Using DBCC FREEPROCCACHE is a sledgehammer approach and typically creates a spike in CPU as all subsequent queries need to have their plans re-generated.  Glenn gives examples on how to use each statement (and others) in his post Eight Different Ways to Clear the SQL Server Plan Cache, and I want to show you one more thing that happens when you clear a plan (or all plans) from cache.

For this demo script, I recommend running it against a TEST/DEV/QA environment because I am removing plans from cache which can adversely affect performance.

We’ll  run one statement and one stored procedure multiple times against the WideWorldImporters database:

/*
Create a stored procedure to use for testing
*/
USE [WideWorldImporters];
GO

DROP PROCEDURE IF EXISTS [Application].[usp_GetCountryInfo];
GO

CREATE PROCEDURE [Application].[usp_GetCountryInfo]
@Country_Name NVARCHAR(60)
AS
SELECT *
FROM [Application].[Countries] 1
JOIN [Application].[StateProvinces] [s]
ON [s].[CountryID] = 1.[CountryID]
WHERE 1.[CountryName] = @Country_Name;
GO

/*
Remove everything from cache
*/
DBCC FREEPROCCACHE;
GO

/*
Run the stored procedure 20 times
*/
EXECUTE [Application].[usp_GetCountryInfo] N'United States';
GO 20

/*
Run the query 20 times
*/
SELECT
[s].[StateProvinceName],
[s].[SalesTerritory],
[s].[LatestRecordedPopulation],
[s].[StateProvinceCode]
FROM [Application].[Countries] 1
JOIN [Application].[StateProvinces] [s]
ON [s].[CountryID] = 1.[CountryID]
WHERE 1.[CountryName] = 'United States';
GO 20

Now let’s see what’s in cache, and what’s also in sys.dm_exec_query_stats (which gives us execution statistics).

SELECT
[qs].[last_execution_time],
[qs].[execution_count],
[qs].[total_logical_reads]/[qs].[execution_count] [AvgLogicalReads],
[qs].[max_logical_reads],
[t].1,
[p].[query_plan]
FROM sys.dm_exec_query_stats [qs]
CROSS APPLY sys.dm_exec_sql_text([qs].sql_handle) [t]
CROSS APPLY sys.dm_exec_query_plan([qs].[plan_handle]) [p]
WHERE [t].1 LIKE '%Application%';
GO
Text, plan, and query stats from the DMOs

Text, plan, and query stats from the DMOs

 

 

 

 

Cool, I see that each one executed 20 times, and in addition to the text and the plan I pulled information about I/Os.

Now let’s clear procedure cache, and we’ll take the good old sledgehammer approach because we’re lazy and run DBCC FREEPROCACHE.  Then run the query to see what’s in sys.dm_exec_query_stats…

DMO information *after* running DBCC FREEPROCCACHE

DMO information *after* running DBCC FREEPROCCACHE

 

 

 

 

 

 

Do you see what happened?  In addition to removing the plan, we also lost all the query execution stats from dm_exec_query_stats.  We have no history about query performance (unless we were writing it to another table on a regular basis, or have a third-party tool that captures baselines).  If you don’t believe me, just run SELECT * FROM sys.dm_exec_query_stats…all that data is gone (but will start to re-populate as you run queries and plans go into cache).

This is important to know! If you’re troubleshooting a performance issue and you want to try removing the plan from cache, be aware that you’re removing execution statistics too (and maybe save those off first in case you need them for reference/comparison later).

Migrating to In-Memory OLTP…what’s stopping you?

In the past few months I’ve had several clients reach out about migrating to In-Memory OLTP solutions.  When the second or third request rolled in I remember thinking, “Finally!”  As in, I’ve been wondering why businesses haven’t been looking to implement In-Memory sooner.  In fact, I added a section on In-Memory to our IEPTO2 course because with the availability of the feature in SQL Server 2016 SP1 (yes, there are memory limitations, but it’s a good place to start) I figured we would see an uptick in interest.  But here we are, half way through 2017 and over 6 months since the release of SQL Server 2016 SP1, and I still see a lot of hesitation around it.

I wrote a post over on SQLPerformance last week, Testing DML Statements for In-Memory OLTP, and that generated some discussions on Twitter.  So I figured it was time for a post to find out what’s holding companies back.  This isn’t a true poll – it’s a fill-in-the-blank.  As in: post a comment.  If you have considered migrating to In-Memory and then didn’t, I want to understand why.  I recognize there are limitations – the product is still new and it’s evolving.  But perhaps if we understand the largest inhibitors to migration we can help move them up on Microsoft’s list via Connect and other pathways.  Understand I am asking for specifics here, for example: we can’t use In-Memory tables because they don’t support spatial data types.  Whatever the reason, share it via a comment.

I’m giving this a week or so, then I’ll write a follow up either discussing trends, debunking myths, showing some testing, or perhaps just talking about why I’m sad that no one uses In-Memory OLTP.  :)  In all seriousness, I have no idea what the follow up will look like, it all depends on whether you and your friends share info!  Until then, thanks for reading and commenting!

(And if you’re in the US, please have a safe 4th of July…no one wants to go to the hospital after trying to set off fireworks at home…just ask my friend who’s a hand surgeon, it’s her least favorite time of year!)

Fireworks from the professionals

Fireworks from the professionals

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.