PolyBase Revealed: Hive Shim Errors

I just recently worked through an error in which predicate pushdown would work for flat files but would fail with a weird error on ORC files.

tl;dr

If you’re hitting Hive 3, make sure you’re using SQL Server 2019 CTP 2.3 (or later).

The Equipment

  • HDP 3.0.1.0-187 running standalone. This includes HDFS 3.1.1, Hive 3.1.0, and YARN 3.1.1.
  • SQL Server 2019 CTP 2.2 on a Windows VM.

The Issue

I have two copies of the same data set, one in CSV format and one in ORC format.  When running PolyBase queries, here’s a table of what I get for outputs:

 No PushdownForce ExternalPushdown
CSVQuery succeedsQuery succeeds
ORCQuery succeedsQuery fails

Three out of four scenarios work just fine, but that last one was a doozy. SQL Server would barf out an error telling me to check out the Hadoop job server. I check the error log and I see this doozy:

2019-02-20 19:33:29,219 INFO [main] com.microsoft.pp.converter.ToPaxBlockConverter: Creating input PPax with inblock size 1 MB, offblock size 3 MB
2019-02-20 19:33:29,236 INFO [main] com.microsoft.pp.converter.ToPaxBlockConverter: Created PPAX with tuple count limit 3811 on a schema with 3 required columns of which 0 are LOBs and 0 are large varlen columns.
2019-02-20 19:33:29,246 INFO [main] org.apache.hadoop.conf.Configuration.deprecation: No unit for dfs.client.datanode-restart.timeout(30) assuming SECONDS
2019-02-20 19:33:29,376 INFO [main] org.apache.hadoop.hive.ql.io.orc.ReaderImpl: Reading ORC rows from hdfs://clusterino:8020/PolyBaseData/NYCParkingTicketsORC/000000_0 with {include: [false, false, false, true, false, false, false, false, false, false, false, false, false, false, false, true, false, false, false, false, false, false, false, false, false, false, false, false, false, false, false, false, false, false, false, false, true, false, false, false, false, false, false, false], offset: 0, length: 439403459}
2019-02-20 19:33:29,395 ERROR [main] org.apache.hadoop.mapred.YarnChild: Error running child : java.lang.ExceptionInInitializerError
	at org.apache.hadoop.hive.ql.io.orc.RecordReaderImpl.<init>(RecordReaderImpl.java:195)
	at org.apache.hadoop.hive.ql.io.orc.ReaderImpl.rowsOptions(ReaderImpl.java:539)
	at com.microsoft.pp.converter.ORCFileToPaxBlockConverter.<init>(ORCFileToPaxBlockConverter.java:95)
	at com.microsoft.polybase.storage.input.ORCFileToPaxBlockInputFormat$OrcFileToPaxBlockRecordReader.initialize(ORCFileToPaxBlockInputFormat.java:64)
	at org.apache.hadoop.mapred.MapTask$NewTrackingRecordReader.initialize(MapTask.java:560)
	at org.apache.hadoop.mapred.MapTask.runNewMapper(MapTask.java:798)
	at org.apache.hadoop.mapred.MapTask.run(MapTask.java:347)
	at org.apache.hadoop.mapred.YarnChild$2.run(YarnChild.java:174)
	at java.security.AccessController.doPrivileged(Native Method)
	at javax.security.auth.Subject.doAs(Subject.java:422)
	at org.apache.hadoop.security.UserGroupInformation.doAs(UserGroupInformation.java:1730)
	at org.apache.hadoop.mapred.YarnChild.main(YarnChild.java:168)
Caused by: java.lang.IllegalArgumentException: Unrecognized Hadoop major version number: 3.1.1.3.0.1.0-187
	at org.apache.hadoop.hive.shims.ShimLoader.getMajorVersion(ShimLoader.java:174)
	at org.apache.hadoop.hive.shims.ShimLoader.loadShims(ShimLoader.java:139)
	at org.apache.hadoop.hive.shims.ShimLoader.getHadoopShims(ShimLoader.java:100)
	at org.apache.hadoop.hive.conf.HiveConf$ConfVars.<clinit>(HiveConf.java:368)
	... 12 more

2019-02-20 19:33:29,497 INFO [main] org.apache.hadoop.metrics2.impl.MetricsSystemImpl: Stopping MapTask metrics system...
2019-02-20 19:33:29,498 INFO [main] org.apache.hadoop.metrics2.impl.MetricsSystemImpl: MapTask metrics system stopped.
2019-02-20 19:33:29,498 INFO [main] org.apache.hadoop.metrics2.impl.MetricsSystemImpl: MapTask metrics system shutdown complete.

The Strategy

My initial plan was to google things. The specific error: java.lang.IllegalArgumentException: Unrecognized Hadoop major version number. That pops up HIVE-15326 and HIVE-15016 but gave me no immediate joy.

After reaching out to James Rowland-Jones (t), we (by which I mean he) eventually figured out the issue.

While he was figuring out the issue, I found a more apropos error and the chat discussion really helped flesh out the problem. In this Stack Overflow case, the root cause was that a user-defined function was configured to use Hive 3.1.1 but the user’s version of Hive was 1.2.1. My speculation was that maybe something similar was happening with PolyBase but I wasn’t going to be able to go any further.

The Solution

The solution here is to upgrade to SQL Server 2019 CTP 2.3 on those test machines. There were some quiet updates to PolyBase and that resolves the issue at hand. For 2017 and 2016, I can’t speculate on what will happen there, but my guess is that most people in production are still using HDP 2.x and haven’t moved to 3 just yet.

Advertisements

Approaching Zero: Constraint Changes

This is part six in a series on near-zero downtime deployments.

In the last post, we looked at making minimally-blocking changes to tables. Today, we’re going to focus on constraints: primary keys, unique keys, foreign keys, default constraints, and check constraints to be precise.

Setup Phase

For this set of tests, I’m going to create two artificial tables, LookupTable and LargeTable. The former will have 10 rows and the latter 2 million.

CREATE TABLE dbo.LookupTable
(
	Id TINYINT NOT NULL,
	CONSTRAINT [PK_LookupTable] PRIMARY KEY CLUSTERED(Id)
);
INSERT INTO dbo.LookupTable
(
	Id
)
VALUES
	(0),(1),(2),
	(3),(4),(5),
	(6),(7),(8),(9);

CREATE TABLE dbo.LargeTable
(
	Id INT IDENTITY(1,1) NOT NULL,
	LookupTableId TINYINT NOT NULL,
	SomeChar CHAR(1) NOT NULL,
	SomeVal DECIMAL(10,2) NOT NULL,
	SomeNullableInt INT NULL
);

INSERT INTO dbo.LargeTable
(
	LookupTableId,
	SomeChar,
	SomeVal,
	SomeNullableInt
)
SELECT TOP(2000000)
	lt.Id,
	LEFT(ac.name, 1),
	150 * RAND(CHECKSUM(NEWID())),
	CASE WHEN RAND(CHECKSUM(NEWID())) > 0.5 THEN 350 * RAND(CHECKSUM(NEWID())) ELSE NULL END
FROM dbo.LookupTable lt
	CROSS JOIN sys.all_columns ac
	CROSS JOIN sys.all_columns ac1
	CROSS JOIN sys.all_columns ac2;

At this point, LargeTable is a heap with no indexes anywhere.

Creating a New Primary Key Constraint

PhaseProcess
Database pre-releaseCreate constraint
Database release
Code release
Database post-release

We’re going to create a combination primary key constraint and clustered index on the Id column.

IF NOT EXISTS
(
	SELECT 1
	FROM sys.key_constraints kc
	WHERE
		kc.name = N'PK_LargeTable'
)
BEGIN
	ALTER TABLE dbo.LargeTable ADD CONSTRAINT [PK_LargeTable]
		PRIMARY KEY CLUSTERED(Id)
		WITH (ONLINE = ON, DATA_COMPRESSION = PAGE);
END

This is fundamentally the same as creating a clustered index (as we did in the last post): you create the constraint during the pre-release step and there is a short amount of blocking after the clustered index / primary key is ready to go. Writers will block the start of this operation like during a clustered index.

If you create a non-clustered primary key constraint on a heap, the process is the same and so is the story on blocking. Avoiding a clustered index won’t save you on that short bout of blocking.

Let’s say that we have a clustered index on SomeVal and want to create our primary key on Id. You’re probably not surprised when I tell you it’s exactly the same behavior. In short, expect a small amount of blocking and try to create this primary key when nobody’s looking (or at least writing).

Create a New Unique Key Constraint

PhaseProcess
Database pre-releaseCreate constraint
Database release
Code release
Database post-release

Again I have a clustered index on SomeVal. I’m going to try to create a unique index on Id this time, after having dropped the primary key constraint.

IF NOT EXISTS
(
	SELECT 1
	FROM sys.key_constraints kc
	WHERE
		kc.name = N'UKC_LargeTable'
)
BEGIN
	ALTER TABLE dbo.LargeTable ADD CONSTRAINT [UKC_LargeTable]
		UNIQUE(Id)
		WITH (ONLINE = ON, DATA_COMPRESSION = PAGE);
END

The locking story is the same as with primary key constraints above. We need to hold a lock once the unique key constraint is ready to go into action.

Creating a New Foreign Key Constraint

New constraint, same process:

IF NOT EXISTS
(
	SELECT 1
	FROM sys.foreign_keys fk
	WHERE
		fk.name = N'FK_LargeTable_LookupTable'
)
BEGIN
	ALTER TABLE dbo.LargeTable WITH CHECK ADD CONSTRAINT [FK_LargeTable_LookupTable]
	FOREIGN KEY (LookupTableId)
	REFERENCES dbo.LookupTable(Id);
END

The locking story is not the same as with the primary and unique key constraints. First, there’s one extra piece: the transition will block access to dbo.LookupTable as well as the table we create the constraint on. That’s to keep us from deleting rows in our lookup table before the key is in place.

Second, the locks begin as soon as we hit F5. Even SELECT statements get blocked requesting a LCK_M_SCH_S lock. Bad news, people.

So what can we do to get around this problem? Two routes: the ineffectual way and the ugly way.

The Ineffectual Way

PhaseProcess
Database pre-releaseCreate index (online)
Create constraint
Database release
Code release
Database post-release

Knowing that there will be blocking, we can reduce the length of blocking by including a non-clustered index on our foreign key constraint like so:

CREATE NONCLUSTERED INDEX [IX_LargeTable_LookupTableId] ON dbo.LargeTable
(
    LookupTableId
) WITH (ONLINE = ON, DATA_COMPRESSION = PAGE);

Doing that can speed up considerably the amount of time it takes for SQL Server to check LookupTableId looking for bad values. This doesn’t get you down to milliseconds, but might turn minutes into seconds and that route may be acceptable.

The Ugly Way

PhaseProcess
Database pre-releaseCreate new table with constraint
Create temporary trigger
Create backfill process
Disable backfill process
Database releaseRename tables
Drop temporary trigger
Code release
Database post-releaseDrop old table
Drop backfill process

This is similar to what we did when making a big column change in the last post. As you can see, there’s a lot more work here and it also requires that we have twice as much disk space available as normally needed for this table. But let’s trudge through this one step by step because sometimes the ineffectual way really won’t work.

Step 1: Create the Table

First, we need to create a new table. Let’s call it dbo.LargeTableTemp:

CREATE TABLE dbo.LargeTableTemp
(
	Id INT IDENTITY(1,1) NOT NULL,
	LookupTableId TINYINT NOT NULL,
	SomeChar CHAR(1) NOT NULL,
	SomeVal DECIMAL(10,2) NOT NULL,
	SomeNullableInt INT NULL,
	CONSTRAINT [PK_LargeTableTemp] PRIMARY KEY CLUSTERED(Id) WITH(DATA_COMPRESSION = PAGE),
	CONSTRAINT [FK_LargeTableTemp_LookupTable]
		FOREIGN KEY(LookupTableId)
		REFERENCES dbo.LookupTable(Id)
);

Make sure to use the new name with these constraints so we don’t have constraint name violations like you would if you tried to create another PK_LargeTable. Also, add the foreign key here. If you have non-clustered indexes, you can add them here or wait until the very end of the pre-release process. Just have them in place before you get to the database release phase.

Step 2: Create the Data Migration Process

Our data migration process will be a two-pronged approach: we create a trigger to handle new records and a backfill process to take care of existing records. Let’s first look at the backfill.

In the backfill, we’ll batch insert records. Ideally, you want to have a BackfillMetadata table like I showed in the last post so that your backfill can look a bit like this:

DECLARE
	@LatestBackfilledID BIGINT,
	@BackfillTable SYSNAME = N'LargeTableTemp',
	@BatchSize INT = 1000,
	@NumberOfBatches INT = 5;

SET NOCOUNT ON;

SELECT
	@LatestBackfilledID = MaximumProcessedValue
FROM dbo.BackfillMetadata bm
WHERE
	bm.BackfillTable = @BackfillTable;

IF (@LatestBackfilledID IS NULL)
BEGIN
	-- If you start your identity columns at MIN_VALUE, this would change.
	SET @LatestBackfilledID = ISNULL(@LatestBackfilledID, 0);

	INSERT INTO dbo.BackfillMetadata
	(
		BackfillTable,
		MaximumProcessedValue
	)
	VALUES
	(
		@BackfillTable, @LatestBackfilledID
	);
END

DECLARE
	@i INT = 0,
	@LoadThroughID BIGINT,
	@RowsAffected INT = 0;

SET IDENTITY_INSERT dbo.LargeTableTemp ON;

WHILE (@i < @NumberOfBatches)
BEGIN;
	WITH records AS
	(
		SELECT TOP (@BatchSize)
			PredictionID
		FROM dbo.PredictionTest pt
		WHERE
			pt.PredictionID > @LatestBackfilledID
		ORDER BY
			pt.PredictionID ASC
	)
	SELECT
		@LoadThroughID = MAX(PredictionID)
	FROM records;

	IF (@LoadThroughID IS NULL)
		-- We have finished processing.
		BREAK;

	INSERT INTO dbo.LargeTableTemp
	(
		Id,
		LookupTableId,
		SomeChar,
		SomeVal,
		SomeNullableInt
	)
	SELECT
		lt.Id,
		lt.LookupTableId,
		lt.SomeChar,
		lt.SomeVal,
		lt.SomeNullableInt
	FROM dbo.LargeTable lt
		LEFT OUTER JOIN dbo.LargeTableTemp ltt
			ON lt.Id = ltt.Id
	WHERE
		lt.Id > @LatestBackfilledID
		AND lt.Id <= @LoadThroughID
		AND ltt.Id IS NULL;

	SET @RowsAffected = @@rowcount;
	RAISERROR ('%i rows updated.', 10, 1, @RowsAffected) WITH NOWAIT;

	SET @i = @i + 1;
	SET @LatestBackfilledID = @LoadThroughID;
END

SET IDENTITY_INSERT dbo.LargeTableTemp OFF;

UPDATE dbo.BackfillMetadata
SET
	MaximumProcessedValue = @LoadThroughID
WHERE
	BackfillTable = @BackfillTable;
GO

This is functionally very similar to the backfill we used for changing a column type and that’s on purpose: the mechanics behind backfills are similar even as the particularities make them unique. Once that process is in place and running to load data into your temp table, we can also create a trigger which inserts new records into dbo.LargeTableTemp. As a side note, the backfill and trigger will need some hefty powers in this case because SET IDENTITY_INSERT ON requires ALTER on the table. We won’t get into security in this series because I have enough rabbit holes to go down as it is, but certificate signing can be your friend here.

Step 3: Disable the Backfill when Finished

Here we twiddle our thumbs for a bit. Remember that “pre-release” is not a defined amount of time, so this can take a while. I’ve worked on backfills which took weeks to complete because the tables were so large and so heavily-used that we didn’t want to move more than a tiny number of rows at a time.

Once the backfill is done, you can disable it. Here is where having one more column on dbo.BackfillMetadata might help: something like MaximumValueToProcess and perform a check when MaximumProcessedValue is at least MaximumValueToProcess. Or you can check out the table once in a while and disable it when you’re done. You probably aren’t doing so many backfills that you lose track of them, after all. Hopefully.

Once the backfill is disabled, you are ready for the database release portion of the show. That’s where we pick up in:

Step 4: Rename Tables and Drop Temp Trigger in a Single Bound

We have the dbo.LargeTableTemp table finished and it is in sync with dbo.LargeTable. We’re ready to make a move. In our release phase, we need to do all of these as one transaction:

  1. Rename dbo.LargeTable to something like dbo.LargeTable_DELETEMEAFTER20190501. We want to keep the table around for a little bit if we have the disk space, just in case there’s a catastrophic problem which requires rollback. If we are running on borrowed space, delete it sooner. But don’t drop it immediately.
  2. Drop the temp trigger.
  3. Rename dbo.LargeTableTemp to dbo.LargeTable.

Do this as one transaction so that people just get blocked rather than errors when trying to run procedures which query dbo.LargeTable. Here’s a sample:

BEGIN TRANSACTION
EXEC sp_rename
	'dbo.LargeTable',
	'LargeTable_DELETEMEAFTER20190501';
DROP TRIGGER [dbo].[LargeTable].[tr_LargeTable_Backfill_IU];
EXEC sp_rename
    'dbo.LargeTableTemp',
    'LargeTable';
COMMIT TRANSACTION

This will cause blocking for a moment but the pain will subside quickly and users will otherwise be unaware of your switcharoo.

After this, we’ve done everything we need to do in the release phase. On to the post-release phase.

Step 5: Drop Obsolete Objects

At some point, it becomes safe to drop the old dbo.LargeTable which we’ve renamed to have a DELETEMEAFTER indicator. We can also drop the backfill process now that we’re done with it.

The moral of this story is, try to get your domain model right the first time, or at least when tables are small and you can absorb a couple seconds of blocking.

Creating a New Check Constraint

PhaseProcess
Database pre-releaseCreate new table with constraint
Create temporary trigger
Create backfill process
Disable backfill process
Database releaseRename tables
Drop temporary trigger
Code release
Database post-releaseDrop old table
Drop backfill process

Here’s an example of a check constraint, causing rejection if SomeVal is not less than 200:

IF NOT EXISTS
(
	SELECT 1
	FROM sys.check_constraints cc
	WHERE
		cc.name = N'CK_LargeTable_SomeVal_LT200'
)
BEGIN
	ALTER TABLE dbo.LargeTable ADD CONSTRAINT [CK_LargeTable_SomeVal_LT200] CHECK
	(
		SomeVal < 200
	);
END

Bad news: check constraints cause blocking during creation, so they follow the same process as foreign key constraints above: the query takes a schema modification lock on the dbo.LargeTable table.

Schema modification locks are a necessary evil for us.

Because we take a schema modification lock, users can’t even query the table because SELECT operations require an incompatible LCK_M_SCH_S schema stability lock: in other words, readers want to know that the world won’t change out from under them as they query. Even though I can reason through this and say that the check constraint ought not change reader behavior at all (because either it succeeds and data & column definitions remain the same, or it fails and data & column definitions remain the same), check constraints take that lock to prevent writes so we pay the price during reads.

That leads us back to foreign keys and the two routes: ugly and ineffectual. My table shows the ugly route rather than the ineffectual route because I’m a pessimist that way.

Creating a New Default Constraint (Non-Nullable Edition)

PhaseProcess
Database pre-releaseCreate constraint
Database release
Code release
Database post-release

Creating a default constraint on a non-nullable column will lead to blocking but just momentarily: we need a schema modification lock to put the constraint in place, but because the column is not allowed to be NULL, there’s no work to do.

Here is an example of creating a default constraint:

IF NOT EXISTS
(
	SELECT 1
	FROM sys.default_constraints dc
	WHERE
		dc.name = N'DF_LargeTable_SomeVal'
)
BEGIN
	ALTER TABLE dbo.LargeTable ADD CONSTRAINT [DF_LargeTable_SomeVal]
		DEFAULT(17) FOR SomeVal;
END

Creating a New Default Constraint (Nullable Edition)

PhaseProcess
Database pre-releaseCreate constraint
Database release
Code release
Database post-release

What if we decide to create a new default constraint on a nullable column? It turns out the behavior is the same—the default constraint does not require writing any values to the table as of SQL Server 2012. Remus Rusanu covers this in detail.

Dropping Constraints (Not Clustered Index Edition)

PhaseProcess
Database pre-release
Database release
Code release
Database post-releaseDrop constraint

Here I’m going to bundle them all together. Drop constraints in the database post-release phase. Dropping foreign key, check, and default constraints is just a metadata operation which requires a schema modification lock, so blocking is limited. That means we don’t need to do anything special here.

Dropping primary or unique key constraints means dropping an index. If the constraint is tied to a non-clustered index, that’s another metadata operation requiring a schema modification lock.

Dropping Constraints (Clustered Index Edition)

PhaseProcess
Database pre-releaseCreate new table without constraint
Create temporary trigger
Create backfill process
Disable backfill process
Database releaseRename tables
Drop temporary trigger
Code release
Database post-releaseDrop old table
Drop backfill process

Uh-oh. Remember this bad boy? Yeah, we’re back to the land of pain (Land of Pain: population, 7 billion).

Dropping a primary key which is also the clustered index means that the database engine needs to rebuild the table before we can move forward, converting from a clustered index to a heap. During that time, SELECT statements won’t run because readers need to take a schema stability lock and our current schema is anything but stable.

Unfortunately, there’s no way to get around this if your clustered index and primary key are the same. That’s not me saying you shouldn’t combine the two, but be aware of this cost.

Conclusion

Unlike indexes, working with constraints can leave you with unacceptable amounts of downtime even on the latest versions of SQL Server Enterprise Edition. Constraints are definitely something you want to get right on the first try. Barring that, if you still have a small enough table, adding a blocking constraint (foreign key or check) or changing a clustered unique/primary key can block for just a few seconds. On large tables, you’re going to want to stage a big backfill operation. Well, dread might be a better word than want here…

Approaching Zero: Table Changes

This is part five in a series on near-zero downtime deployments.

Last time we met to talk about deployments, we covered stored procedure changes. In this post, we will look at a number of basic changes around tables.

Creating a New Table

PhaseProcess
Database pre-release
Database releaseDeploy new table
Deploy new procedures (optional)
Code releaseDeploy calling code (optional)
Database post-release

Creating a table is easy when it comes to deployments. It’s a new table, so there won’t be any code dependencies. We want to deploy the table, then deploy any procedures which access this table, and deploy calling code in the release. As an example, here is a re-runnable script which creates a table called dbo.ProductLink and includes indexes and foreign key constraints.

USE [Database]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF(OBJECT_ID('dbo.ProductLink') IS NULL)
BEGIN
	CREATE TABLE [dbo].[ProductLink]
	(
		[LinkID] BIGINT NOT NULL,
		[LinkTypeID] TINYINT NOT NULL,
		[CustomerID] [int] NOT NULL,
		[ProductID] [bigint] NOT NULL,
		[CreateDateGMT] [datetime2](3) NOT NULL,
		[LastModifiedGMT] [datetime2](3) NOT NULL,
		CONSTRAINT [PK_ProductLink] PRIMARY KEY CLUSTERED
		(
			[LinkID] ASC,
			[CustomerID] ASC,
			[ProductID] ASC
		)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 100, DATA_COMPRESSION = PAGE) ON [Tables]
	) ON [Tables]
	CREATE NONCLUSTERED INDEX [IX_ProductLink_CustomerID_ProductID] ON [dbo].[ProductLink]
	(
		[CustomerID] ASC,
		[ProductID] ASC
	)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = ON, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 100, DATA_COMPRESSION = PAGE) ON [Indexes]
	CREATE NONCLUSTERED INDEX [IX_ProductLink_LinkID_LinkTypeID] ON [dbo].[ProductLink]
	(
		LinkID,
		LinkTypeID
	)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = ON, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 100, DATA_COMPRESSION = PAGE) ON [Indexes]
	ALTER TABLE dbo.ProductLink ADD CONSTRAINT [FK_ProductLink_LinkType]
		FOREIGN KEY (LinkTypeID)
		REFERENCES dbo.LinkType (LinkTypeID);
	ALTER TABLE dbo.ProductLink ADD CONSTRAINT [FK_ProductLink_Link]
		FOREIGN KEY (LinkID)
		REFERENCES dbo.Link (LinkID);
END
GO

This table links to a couple other tables, Link and LinkType and could link to a Product table too, so if these are new tables, they would need to be created before we can execute this code.

After deploying the table, we can deploy procedures to modify or query data in the ProductLink table. Then we can release code which uses this new table.

Adding a Column to an Existing Table

PhaseProcess
Database pre-release
Database releaseAlter table
Deploy altered procedures (optional)
Deploy versioned procedures (optional)
Code releaseDeploy calling code (optional)
Database post-releaseDeprecate old versions of procedures (optional)

Adding a new column to an existing table is easy if you can meet one of the two circumstances:

  • The new column is nullable.
  • The new column is non-nullable but contains a default constraint.

The nice thing about default constraints is that they work as metadata operations: adding a default constraint to an existing table does not require writing the default value to every row on every page. Let’s say I have a table called dbo.PredictionTest. This table has three columns:

CREATE TABLE [dbo].[PredictionTest]
(
	[PredictionID] [bigint] IDENTITY(1,1) NOT NULL,
	[PredictedQuantitySold] [decimal](8, 3) NOT NULL,
	[ActualQuantitySold] [decimal](8, 3) NULL
) ON [Tables]
GO

I want to add a new column called IsCloseEnough, which is a bit field describing whether the predicted quantity was close enough to the actual quantity. If I simply want to make this a nullable column, the re-runnable script could look like:

IF NOT EXISTS
(
	SELECT 1
	FROM sys.columns sc
		INNER JOIN sys.Tables st
			ON sc.object_id = st.object_id
	WHERE
		st.name = N'PredictionTest'
		AND sc.name = N'IsCloseEnough'
)
BEGIN
	ALTER TABLE dbo.PredictionTest ADD IsCloseEnough BIT NULL;
END
GO

This is your quick reminder that “near-zero” still means we need locks. Here’s a quick demo. In session 99, I start a transaction to insert a new row into PredictionTest:

BEGIN TRANSACTION
INSERT INTO dbo.PredictionTest
(
	PredictedQuantitySold,
	ActualQuantitySold
)
VALUES
(
	500,
	495
);

Then, in session 86, I try running the alter statement. Finally, in session 114, I try to insert another row. Here’s what the blocking chain looks like with sp_whoisactive:

Don’t worry, I’m sure that transaction will finish any moment now.

Notice that 86, our script to add the new column, wants a LCK_M_SCH_M lock. This is a schema modification lock. This lock type is incompatible with LCK_M_IX, which the insert operation needs and is why 86 is blocking session 114.

Eventually, I commit transaction 99.

We’ve picked up a new friend. Ignore that.

We still have the blocking chain and I picked up a friend from some automated task which was running during my demo that wanted to read from the table.

Now, in practice, this alter operation won’t take long assuming you commit your transaction and unless it’s on an extremely busy table, people typically won’t notice the milliseconds of locking.

I decided to roll back the table alter statement so that I could continue with adding a column with a default constraint. To do this, we need to include the constraint as part of the ALTER TABLE statement. Otherwise, if you alter a table and set it to NOT NULL without a constraint, you will get a surprisingly helpful error:

Msg 4901, Level 16, State 1, Line 22
ALTER TABLE only allows columns to be added that can contain nulls, or have a DEFAULT definition specified, or the column being added is an identity or timestamp column, or alternatively if none of the previous conditions are satisfied the table must be empty to allow addition of this column. Column ‘IsCloseEnough’ cannot be added to non-empty table ‘PredictionTest’ because it does not satisfy these conditions.

With a constraint, the script now looks more like this:

IF NOT EXISTS
(
	SELECT 1
	FROM sys.columns sc
		INNER JOIN sys.Tables st
			ON sc.object_id = st.object_id
	WHERE
		st.name = N'PredictionTest'
		AND sc.name = N'IsCloseEnough'
)
BEGIN
	ALTER TABLE dbo.PredictionTest ADD IsCloseEnough BIT NOT NULL CONSTRAINT [DF_PredictionTest_IsCloseEnough] DEFAULT(0);
END
GO

Once you alter the table, you can push out new procedures which use the table. This breaks down into procedures you need to version and procedures you do not need to version. For the long version, you can infer from the prior post in this series what the rules are, but here is a quick summary:

  • If you do not intend to expose this new column in a procedure, you probably don’t need to make any changes at all.
  • If you expose this column in a result set, you probably do not need to version the procedure unless you have a particularly brittle data access layer. Hopefully you don’t have that.
  • If you use this new column in filters, joins, or the like without exposing the column in a result set, you can deploy the changed procedure without versioning it or changing code.
  • If you add a new parameter to a stored procedure—a procedure for inserting or updating the data, for example—you probably will need to version the procedure.
  • If you use a table-valued parameter and the table type needs this new field as well, you will need to create a new version of the table type and then version the procedures which use the table type.

Once those changes are in place, deploy the code. Then, clean up by removing any deprecated procedures, table types, or other objects which are no longer in use.

Removing a Column from an Existing Table

PhaseProcess
Database pre-release
Database releaseDeploy new versions of procedures
Code releaseDeploy calling code
Database post-releaseDeploy altered procedures
Drop old versions of procedures
Drop column

Removing a column is trickier than adding a column. There are even debates about whether you ever remove columns because removal is permanent. If you drop the column, you can’t roll that back later when you find out you really need it.

As a dedicated Deletionist (that is, the type of person who really enjoys deleting code), this does not deter me one bit. You have to make sure that the column really isn’t in use any longer but if that’s the case, I don’t see much value in keeping it around.

The flow here is a little different from adding the column. First, for procedures which you need to version (e.g., cases where the input parameter change), create the versioned procedures as part of the normal database release. Then, release code which eliminates all reference to the to-be-removed column and uses the new versions of these procedures. After code is deployed and we are in the database post-release phase, we can deploy procedures which have changed, drop the procedures which were versioned, and finally drop the column.

Before I move on, I should note that you might want to rebuild indexes (including the clustered index) on the table after you drop columns, especially if you modify columns in LOB storage.

Changing Column Details

When I say “column details,” I mean one of a few things:

  • Making a non-nullable column nullable or vice versa.
  • Extending or shortening the maximum length of an (N)(VAR)CHAR column.
  • Changing the column type on a table.

I’ll break these out into less significant and more significant changes.

Less Significant Changes

PhaseProcess
Database pre-release
Database releaseAlter table
Deploy altered procedures (optional)
Deploy versioned procedures (optional)
Code releaseDeploy calling code (optional)
Database post-releaseDeprecate old versions of procedures (optional)

Less significant changes are ones where we can make the change in-place. An example of this is taking a VARCHAR(30) column and extending it to VARCHAR(40) because we find out we need the extra space. This is the type of change you can build in place because it’s effectively a metadata operation. Here are a couple examples of metadata operations:

-- Make a non-nullable column nullable.
BEGIN TRAN
ALTER TABLE dbo.PredictionTest ALTER COLUMN PredictedQuantitySold DECIMAL(8, 3) NULL;
ROLLBACK TRAN

-- Extending a VARCHAR column.  SomeData started off as VARCHAR(30)
BEGIN TRAN
ALTER TABLE dbo.PredictionTest ALTER COLUMN SomeData VARCHAR(40) NULL;
ROLLBACK TRAN

Of note is that changing a DECIMAL(8,3) to DECIMAL(10,3) or a TINYINT to an INT does modify the data and will cause a block as we scan through all of the data. Also, switching a nullable column to non-nullable requires a scan of the data and blocking. If you want to make a change like one of those, you’re in the realm of More Significant Changes.

More Significant Changes

PhaseProcess
Database pre-releaseAdd new column
Create backfill process (and maybe temp index)
Create trigger
Database release
Deploy temporary versioned procedures (optional)
Code releaseDeploy calling code (optional)
Database post-releaseDeploy altered procedures (optional)
Deploy final versioned procedures (optional)
Drop old versions (optional)
Drop temporary index
Drop trigger
Drop backfill process
Drop old column
Rename new column (optional)

The realm of More Significant Changes is not where you often want to be. There’s a lot of scaffolding code you need to write. Basically, suppose you want to make a repair on the 5th story exterior of an 8-story building. You have a couple of options: the YOLO option is to kick everybody out of the building and have people rappel from the top of the building down to the 5th story to make their changes. They need all of the people out of the building because shut up it’s a strained analogy. This approach is kind of inconvenient: people have to stay out of your building until your people are done repairing the exterior. That’s blocking in the database world.

On the other side, you can build a bunch of scaffolding and attach it to the exterior of the building, perform your repairs, and tear down that scaffolding. While the scaffolding is up, people come and go like normal and don’t even think about it. As you tear the scaffolding down, you temporarily block the door for a moment as you’re finishing your work. This is much more convenient for end users and fits the “near-zero downtime” approach we’re taking.

Photo by Samuel Zeller on Unsplash

If you look at that scaffolding and think “that’s a lot of waste material there and a lot of effort that will just go away as soon as the repair is done,” that’s technically correct but misses the broader point: we do extra work to make our changes happen safely and with a minimum of disturbance.

Strained analogy aside, let’s talk about the mechanics of this. Let’s say that we find out our ActualQuantitySold needs to be DECIMAL(10,3) instead of DECIMAL(8,3). I want to keep the name as ActualQuantitySold but we have a lot of rows and can’t afford to block for more than a few milliseconds.

Step 1: Add New Column

The first thing I need to do is add the new column, which I will call ActualQuantitySoldTemp. All procedures are currently pointing to ActualQuantitySold, which is a DECIMAL(8,3). If I’m okay with the final column being nullable, I can run a command like so:

ALTER TABLE dbo.PredictionTest ADD ActualQuantitySoldTemp DECIMAL(10, 3) NULL;

But I want this to be non-nullable so I’m going to have to create a default. Here I’ll have a sentinel value (that is, one that I as a developer know is a false, impossible value to reach and can use it as a logic check):

ALTER TABLE dbo.PredictionTest ADD ActualQuantitySoldTemp DECIMAL(10, 3) NOT NULL CONSTRAINT [DF_PredictionTest_ActualQuantitySoldTemp] DEFAULT(-99);

With that column in play, I’m going to start my backfill.

Step 2: Create Backfill Process

Now that I have a temporary column, I need to create a process to update this table. For a small table, I might do the update as a simple UPDATE statement like:

UPDATE dbo.PredictionSet
SET ActualQuantitySoldTemp = ActualQuantitySold;

If I’m dealing with an exceptionally large table, though, I don’t want to update the entire thing in one go. Even if there’s no blocking because I’m using Read Committed Snapshot Isolation, I can use up a lot of transaction log space with a big enough update. For those big tables, I’m going to want to run a batching process.

There are two main approaches I tend to take when batching, where one requires a bit of extra infrastructure and the other doesn’t. If your table contains a monotonically increasing clustered index (e.g., an identity column or column using a sequence), then you can store the latest ID in a table and run batches. For example, let’s create a clustered primary key on dbo.PredictionTest and create a backfill table called dbo.BackfillMetadata.

ALTER TABLE dbo.PredictionTest ADD CONSTRAINT [PK_PredictionTest] PRIMARY KEY CLUSTERED (PredictionID);

CREATE TABLE dbo.BackfillMetadata
(
	BackfillTable SYSNAME NOT NULL,
	MaximumProcessedValue BIGINT NOT NULL,
	CONSTRAINT [PK_BackfillMetadata] PRIMARY KEY CLUSTERED(BackfillTable)
);

Our batch update process is fairly simple, allowing us to control the number of operations and number of records per batch. You could certainly make this more complex or feature-rich; this is just a quick demonstration of approach.

DECLARE
	@LatestBackfilledID BIGINT,
	@BackfillTable SYSNAME = N'PredictionTest',
	@BatchSize INT = 1000,
	@NumberOfBatches INT = 5;

SET NOCOUNT ON;

SELECT
	@LatestBackfilledID = MaximumProcessedValue
FROM dbo.BackfillMetadata bm
WHERE
	bm.BackfillTable = @BackfillTable;

IF (@LatestBackfilledID IS NULL)
BEGIN
	-- If you start your identity columns at MIN_VALUE, this would change.
	SET @LatestBackfilledID = ISNULL(@LatestBackfilledID, 0);

	INSERT INTO dbo.BackfillMetadata 
	(
		BackfillTable,
		MaximumProcessedValue
	)
	VALUES
	(
		@BackfillTable,
		@LatestBackfilledID
	);
END

DECLARE
	@i INT = 0,
	@LoadThroughID BIGINT,
	@RowsAffected INT = 0;

WHILE (@i < @NumberOfBatches)
BEGIN;
	WITH records AS
	(
		SELECT TOP (@BatchSize)
			PredictionID
		FROM dbo.PredictionTest pt
		WHERE
			pt.PredictionID > @LatestBackfilledID
		ORDER BY
			pt.PredictionID ASC
	)
	SELECT
		@LoadThroughID = MAX(PredictionID)
	FROM records;

	IF (@LoadThroughID IS NULL)
		-- We have finished processing.
		BREAK;

	UPDATE dbo.PredictionTest
	SET
		ActualQuantitySoldTemp = ActualQuantitySold
	WHERE
		PredictionID > @LatestBackfilledID
		AND PredictionID <= @LoadThroughID;

	SET @RowsAffected = @@ROWCOUNT;
	RAISERROR ('%i rows updated.', 10, 1, @RowsAffected) WITH NOWAIT;

	SET @i = @i + 1;
	SET @LatestBackfilledID = @LoadThroughID;
END

UPDATE dbo.BackfillMetadata
SET
	MaximumProcessedValue = @LoadThroughID
WHERE
	BackfillTable = @BackfillTable;
GO

If you do a lot of backfills and typically use identity columns as your clustered indexes, this approach has a lot going for it. It’s going to be efficient and as long as you put in error handling (for example, so that you don’t update the maximum processed value to NULL in the event of an error updating the table), you’ll be fine.

Supposing you don’t have a nice clustered key to use, you can simply keep updating until you handle all missing records (here I went with the IS NULL variant but it could also be my sentinel value of -99 if I go down the non-nullable path):

UPDATE TOP(1000) dbo.PredictionTest
SET
	ActualQuantitySoldTemp = ActualQuantitySold
WHERE
	ActualQuantitySoldTemp IS NULL;

Here’s the problem: that operation gets progressively worse and worse. Here’s what the execution plan looks like:

Not a pretty face.

The first time you run it, it probably performs pretty well. We can see why on the execution plan details:

This plan looks fine. Nothing to see here.

We read 2000 rows and update 1000 rows. We needed to read 2000 rows because I had previously updated the first thousand (as a test; I actually test my blog post code…sometimes…). The next time we run this scan, we’ll read 3000 rows and so on. This is after me running the update statement 13 times:

I’m sure this will fix itself any day now.

On a multi-billion row table, your dev testing probably looks just fine (because dev has 1500 rows total) and it even looks great in production for the first hour or two, depending upon how aggressive you are in updating. But once you start getting into millions of rows, the number of I/O operations will increase steadily until you’re reading past 9,999,000 rows to get to the next thousand.

This is what you might call an anti-scaling pattern. Sometimes, people see this bad performance and say, “Hey, I’ll add an index!” So let’s add an index:

CREATE NONCLUSTERED INDEX [IX_PredictionTest_ActualQuantitySoldTemp] ON dbo.PredictionTest
(
	ActualQuantitySoldTemp,
	ActualQuantitySold
) WITH(DATA_COMPRESSION = PAGE, ONLINE = ON) ON [Indexes];

That solved our problem:

The plan got big enough that I needed to drop to SentryOne Plan Explorer to show it easily.

Oh, wait, that was sarcasm. It’s been hard to tell since I broke my sarcasm detector. This index didn’t help. It made things worse. So let’s drop that stupid index and use a smarter index:

CREATE NONCLUSTERED INDEX [IX_PredictionTest_ActualQuantitySoldTempFiltered] ON dbo.PredictionTest
(
	ActualQuantitySold,
	ActualQuantitySoldTemp
)
WHERE
(
	ActualQuantitySoldTemp IS NULL
) WITH(DATA_COMPRESSION = PAGE, ONLINE = ON) ON [Indexes];

But to use this filtered index, we’re going to need to rewrite our update statement; otherwise, it won’t use the filtered index and will just use the clustered index.

WITH records AS
(
	SELECT TOP(1000)
		pt.PredictionID,
		pt.ActualQuantitySold,
		pt.ActualQuantitySoldTemp
	FROM dbo.PredictionTest pt WITH(INDEX([IX_PredictionTest_ActualQuantitySoldTempFiltered]))
	WHERE
		pt.ActualQuantitySoldTemp IS NULL
)
UPDATE records
SET
	ActualQuantitySoldTemp = ActualQuantitySold
WHERE 1=1;

The good news from introducing all of this added complexity is that at least the query plan is stable. Here’s what it looks like after 15 executions:

Everything is A-OK. Sort of.

Now we read 1000 rows to update 1000 rows twice: once for the clustered index and once for the temporary filtered index. This makes it more expensive up-front than the anti-scaling approach, but as you get into the millions, this will perform much better. Still not as good as the clustered index update, however, so use this approach only if you need it.

Step 3: Create a Trigger (Optional but Recommended)

During our backfill, people are still writing data to the table. Remember: we’re trying to act in a way which disrupts our customers the least. When we flip the switch on this column, we need the new column to have all of the data. The best way to ensure this is to create a trigger which updates ActualQuantitySoldTemp and sets it to ActualQuantitySold if those two values are not the same. That gives you an end cap for your backfill: you need to backfill up to the point when that trigger went out, but nothing past it. If you have a relatively “stupid” backfill like my clustered index version above, it won’t hurt for the backfill to keep running past the point when we added the trigger, though it is technically some wasted processing power. If you want to make a smarter backfill strategy which knows when to stop, go for it.

We need to pause here until the backfill is complete. Once the backfill is complete, we can leave the pre-release phase and move into the database release phase.

Step 4: Release the Hounds

Here, I’m combining the next couple of phases together. You’ll want to deploy out new versions of procedures which take in ActualQuantitySold as DECIMAL(10,3) rather than DECIMAL(8,3). These new versions are temporary versions, though: they will need to write to ActualQuantitySold and ActualQuantitySoldTemp (or you could just write to the former and let the trigger continue to handle the data move). Basically, this version of the procedure exists to enable the next phase: deploying code which expects the new version. Until you’ve completed the type change, your application code should still try to limit itself to the more restrictive of the two data types (that is DECIMAL(8,3) in our case).

Step 5: The Great Switcharoo

Now what we have is a fully-finished ActualQuantitySoldTemp but I want it to be called ActualQuantitySold. We are officially in the database post-release phase and we’ve got a lot of work yet to do. Here are the steps in order:

Step 5a: Deploy Altered Procedures

First, we need to deploy any procedure alterations which did not require versioning the procedure. An example of this is where we load values in dbo.PredictionTest into a temp table and do processing from that table. We can simply update the signature of the temp table and move on with our lives.

Step 5b: Deploy Final Versioned Procedures

In the database deployment phase, we pushed out temporary versions of versioned procedures. Now we can see them in their final form!

What I mean by this is that we can drop all reference to ActualQuantitySoldTemp and reference only ActualQuantitySold. For a few milliseconds, this won’t be quite right but we’ll survive, yes we will.

Step 5c: Drop Temporary Index and Backfill Procedure

If you created a procedure to perform this backfill, you can drop it here. You can also drop any temporary indexes used in the process.

Step 5d: Drop Trigger, Drop Old Column, Rename New Column

That temporary trigger you created during the pre-release phase won’t be needed any longer. In a single transaction, we want to drop the old trigger, drop the old column, and rename the new column (if necessary).

BEGIN TRANSACTION
DROP TRIGGER [dbo].[PredictionTest].[tr_PredictionTest_Backfill_IU];
ALTER TABLE dbo.PredictionTest DROP COLUMN ActualQuantitySold;
EXEC sp_rename
	'dbo.PredictionTest.ActualQuantitySoldTemp',
	'ActualQuantitySold',
	'COLUMN';
COMMIT TRANSACTION

This is a blocking step! During the transaction, you will block anybody trying to read or write. The good news is that dropping triggers, dropping columns, and renaming columns are all fast operations so it shouldn’t block for more than a few milliseconds once your transaction is able to begin (that is, once the writer queue ahead of it disappears).

Step 5e: Rebuild Indexes

If you make big enough changes (like during a VARCHAR(1500) into a VARCHAR(MAX)), you might want to rebuild indexes afterward to ensure that you re-pack any gaps in the indexes. This is something you might want to do in a regular maintenance period and doesn’t necessarily have to happen right after these other steps.

Step 5f: Optionally Rename Default Constraint

Recall that if we wanted the expanded ActualQuantitySold column to be non-nullable, we needed to add a default constraint. Here I would like to change the default constraint’s name to match its new column, and doing this is just another sp_rename call:

EXEC sys.sp_rename
	@objname = N'DF_PredictionTest_ActualQuantitySoldTemp',
	@newname = N'DF_PredictionTest_ActualQuantitySold',
	@objtype = N'OBJECT';

Whew. Told ya’ you didn’t want to go down this path too often.

Renaming a Table

PhaseProcess
Database pre-release
Database releaseRename table
Create temporary view
Deploy altered procedures
Code release
Database post-releaseDrop temporary view

Let’s say you have a table named dbo.tblTableTibble and it’s a really stupid name that you’d like to change to dbo.Customer because it actually stores customer data and tibbling is annoying beyond all get-out.

If you followed the handy-dandy assumptions and have all of your calling code (including application code, SQL Agent jobs, and the like) go through stored procedures, this is a really easy process. Let’s go through an example with dbo.PredictionTest and rename it to dbo.TestPrediction.

First, we call sp_rename to rename the table. In the same transaction, I’m going to create a new view which matches the schema of the old table. But here’s the trick: views must be the first operation in a batch. You cannot run a rename operation and then a CREATE VIEW statement together. Unless we change the context, that is, and execute the view creation statement as dynamic SQL; then it’s in its own batch and we are fine.

BEGIN TRANSACTION
EXEC sp_rename 
	@objname = N'dbo.PredictionTest',
	@newname = 'TestPrediction',
	@objtype = NULL;
EXEC (N'CREATE VIEW dbo.PredictionTest AS
SELECT
	pt.PredictionID,
	pt.PredictedQuantitySold,
	pt.ActualQuantitySold,
	pt.SomeData,
	pt.SomeInt,
	pt.ActualQuantitySoldTemp
FROM dbo.TestPrediction pt;');
COMMIT TRANSACTION

This is a blocking step! It should only block for a very short amount of time but watch out if that table is extremely busy.

Creating the view means that we don’t need to update any procedure calls. Because it’s a trivial view, updates and deletes will pass through.

After we update the view, we can update any existing stored procedures which call dbo.PredictionTest and have them point to dbo.TestPrediction. This should probably be a simple find and replace in your source control system. Alternatively, if you know all of the references are in a single database, you can search sys.sql_modules to find references like so:

SELECT
	OBJECT_SCHEMA_NAME(sm.object_id) AS SchemaName,
	OBJECT_NAME(sm.object_id) AS ObjectName,
	CONCAT(OBJECT_SCHEMA_NAME(sm.object_id), '.', OBJECT_NAME(sm.object_id)) AS FullName,
	CONCAT(OBJECT_SCHEMA_NAME(sm.object_id), '.', OBJECT_NAME(sm.object_id), ',') AS CommaSeparatedName,
	definition
FROM sys.sql_modules sm
WHERE
	sm.definition LIKE '%PredictionTest%'
	--AND OBJECT_SCHEMA_NAME(sm.object_id) = 'Something'
	--AND OBJECT_NAME(sm.object_id) = 'Something'
ORDER BY
	SchemaName,
	ObjectName;

Once you’re sure you have everything, we can skip the code phase (because code doesn’t care about table names; it’s going through stored procedure interfaces) and in the post-release phase, drop the temporary view.

DROP VIEW dbo.PredictionTest;

Dropping a Table

PhaseProcess
Database pre-release
Database releaseRename table
Code release
FUTURE Database post-releaseDrop table

If you have an obsolete table and you are sure there are no code or procedure references to that table, it can be safe to drop. My advice is to rename the table to something like dbo.PredictionTest_DELETEMEAFTER20190501. This tells other database developers that you understand this table should go away, but you’re giving it until May 1st of 2019 before you decide to pull the plug on it. I typically try to give 3-4 weeks of time between renaming and deleting.

The biggest benefit here is that sometimes you don’t realize how a table is used until some process which only runs once a month breaks or some weird application a long-gone intern wrote a decade ago makes direct database calls and depends on that table. It’s a lot easier to rename a table back than it is to restore a table from a backup.

At some point, you’re reasonably confident that this table really doesn’t get used and in a future database post-release, you can write the DROP TABLE script.

Creating or Modifying a Non-Clustered Index

PhaseProcess
Database pre-releaseCreate index
Database release
Code release
Database post-release Drop old index (optional)

When working with indexes, I’m assuming that you have Enterprise Edition. If you are running Standard Edition, you don’t get the benefit of online index creation and that means any index modifications you make will be liable to cause blocking and hence downtime. Also, I’m assuming that this is an existing table with code references. If it’s a brand new table, you can simply drop the index into the database release phase and be done with it.

Creating indexes should happen in the database pre-release phase, especially for larger tables with a lot of rows. You want to keep the database release and code release phases as short as possible to reduce the amount of exposure you have to weird interactions between different versions of code and database objects, so if you can complete all of your index operations before the database release happens, life is better.

If you are replacing an existing index, you have a couple of options: you can create the index with DROP_EXISTING = ON or create an index with a new name and drop the old one during the database post-release phase. My personal preference is that if I’m changing the key columns on the index (or if the index has a bad name like [index] or [dta_##_####_###_##_##_#_###_###], I’ll create an index with a new name.

One warning is that if you are running SQL Server 2017 and have a clustered columnstore index, any non-clustered indexes must be created offline. It’s very important to get columnstore tables right the first time and limit changes on them.

Other than the warning about columnstore, working with nonclustered indexes is pretty easy.

Creating a New Clustered Index

PhaseProcess
Database pre-releaseCreate index
Database release
Code release
Database post-release

If you have a table lacking a clustered index, you can create one online. Let’s walk through an example of this by first dropping the existing primary key and clustered index on dbo.PredictionTest:

ALTER TABLE dbo.PredictionTest DROP CONSTRAINT [PK_PredictionTest];

Now the prediction test table is a heap. If I want to create a new clustered index (not primary key; I’ll cover constraints later in the series), I can create one like so:

CREATE UNIQUE CLUSTERED INDEX [CIX_PredictionTest] ON dbo.PredictionTest
(
	PredictionID
)
WITH (DATA_COMPRESSION = PAGE, ONLINE = ON);

Locking gets just a little tricky here. Other sessions can select from the table while this online operation is occurring without an issue. Once the index is complete, the database engine needs to take a lock. During that phase, readers will be blocked with a schema stability lock (LCK_M_SCH_S) until the clustered index creation transaction commits.

Writers will block the start of clustered index creation with a LCK_M_S wait. Once the database engine begins creating the clustered index online, writers do not block operation, but if there are any writers still working once the clustered index is complete and ready to tag in, the session creating this clustered index will wait with a schema stability lock until all writers have finished their work. Once the clustered index has a chance to sneak in, it only needs a moment to wrap up its work.

Changing an Existing Clustered Index

PhaseProcess
Database pre-releaseDrop index (if necessary)
Create index (preferably drop_existing)
Database release
Code release
Database post-release

Let’s say that you have a clustered index now but want to change it. I’d generally recommend using DROP_EXISTING = ON for clustered index modifications because of the reasons in Microsoft Docs. Namely, DROP_EXISTING doesn’t do as much work as if you need to drop the clustered index and add it again. The key benefit is that if you use DROP_EXISTING, the database engine only rebuilds non-clustered indexes once, whereas if you drop and re-create the clustered index, non-clustered indexes get built twice. Also, suppose that you decide to drop and create a clustered index for some reason. During the time period after the first clustered index gets dropped and before the second clustered index completely replaces it, performance might suffer. Degraded performance isn’t the end of the world, but it is something we can mitigate.

Here is a sample create statement with DROP_EXISTING on:

CREATE UNIQUE CLUSTERED INDEX [CIX_PredictionTest] ON dbo.PredictionTest
(
	PredictionID
)
WITH (DROP_EXISTING = ON, DATA_COMPRESSION = PAGE, ONLINE = ON);

Conclusion

In this post, we spent a lot of time on handling table changes. Some of these are trivial and others require a lot of foresight and planning. The long process of changing a data type is one of the reasons I can’t get entirely behind automated database build tools (like database projects in Visual Studio): I can write a deployment which leaves my end users happy, but those tools will either fail or try to create a new table and perform a fill-and-swap which can itself be trouble if the table is in use during this process.

PolyBase Revealed: Predicate Pushdown Does Not Include Strings

In this week’s edition of PolyBase Revealed, here’s something I found surprising about predicate pushdown.

Why Pushdown Matters

Before I start, let’s talk about predicate pushdown for a moment. The gist of it is that when you have data in two sources, you have two options for combining the data:

  1. Bring the data in its entirety from your remote source to your local target and work as though everything were in the local target to begin with. I’ll call this the streaming approach.
  2. Send as much of your query’s filters, projections, and pre-conditions to the remote source, have the remote source perform some of the work, and then have the remote source send its post-operative data to the local target. Then, the local target once more treats this as though it were simply local data. This is the pushdown approach because you push down those predicates (that is, filters, projections, and pre-conditions).

Option 1 is easy to do but struggles as your data sizes get larger. You spend time streaming that data from the remote source to your target and that can add up. Suppose you have 2 terabytes of data in HDFS. Over a 10 gigabit network, you can get 1.25GB/sec max assuming you swamp your network and nothing else is running. That’s pretty unrealistic so let’s cut it approximately into half and say about 650 MB/sec. It would take a bit over 3200 seconds just to move all of that data over (2 TB * 1024 GB/TB * 1024 MB/GB / 650 MB/sec). That’s before you do any filtering of the data.

Option 2 can be more efficient for giant data sets but tends to be much less efficient with small data sets, especially for a technology like Hadoop’s MapReduce, where there is a fixed cost just to start up a job. My rule of thumb is if you can stream the data and get an answer within 30 seconds, a MapReduce job won’t get an answer to you faster.

Now that we’ve talked about the concepts, we’re going to look at a tiny omission in the documentation which means a lot.

What Can You Push Down?

Check out Microsoft’s documentation on pushdown computations in PolyBase. That documentation is, as far as I can tell, correct. I just want to call out one piece which I’ve highlighted:

SQL Server allows the following basic expressions and operators for predicate pushdown.
* Binary comparison operators ( <, >, =, !=, <>, >=, <= ) for numeric, date, and time values.
* Arithmetic operators ( +, -, *, /, % ).
* Logical operators (AND, OR).
* Unary operators (NOT, IS NULL, IS NOT NULL).

That’s right: there’s no mention of strings. Because you can’t use strings in a pushdown operation.

Here’s an example which I use in the book (sneak preview alert!). I have a copy of New York City parking ticket data for a multi-year period (e.g., FY 2017). I decided to look for violators with Ohio tags and vehicles with model years between 2005 and 2015. As you’d expect, there aren’t that many instances of this: a couple hundred out of 33 million. This is a perfect case for predicate pushdown: I’d much rather have my Hadoop cluster pick up the slack and turn 33 million rows into a dozen (because I’m grouping by year) and take some of the pressure off of SQL Server. But because my most selective predicate is on vehicle state, I can’t push it down. I can push down the model year predicate if and only if I define the model year column as a numeric data type like INT. That means instead of 33 million rows, I push 7.8 million rows over. That’s much better but still less than ideal.

But there may be cases where I don’t want to label the column as a numeric data type. For example, if I know there’s a lot of bad data that I nonetheless wish to keep, I would want to use a VARCHAR or NVARCHAR type. As soon as I do that, however, I lose all ability to perform any kind of predicate pushdown and my MapReduce jobs are essentially project operations. That can still be marginally useful because I’m probably projecting only a small number of the total columns in the data set, but the real gains come in letting SQL Server ignore large numbers of records.

Call to Action

If this sounds interesting to you, vote up my UserVoice entry on supporting string columns for predicate pushdown. If this doesn’t sound interesting to you, vote it up anyhow; what’s the worst that could happen?

Approaching Zero: Stored Procedure Changes

This is part four in a series on near-zero downtime deployments.

In the first three parts of this series, I’ve laid out conceptual groundwork around what helps in reducing database downtime for releases. Now I’m going to start getting into specific details. The next several posts will look at making different types of database changes and walk through a number of examples. In each case, I intend to show a quick table of what steps happen when in the process. This allows you to chain together the individual elements in these posts and create more complicated results, seeing what needs to happen in order.

Today’s topic is stored procedures. Dealing with stored procedures is pretty easy, all things considered. But even with these, we have some nuance. Let’s look at a number of different scenarios and see how we can handle each.

New Stored Procedure

PhaseProcess
Database pre-release
Database releaseDeploy new procedure
Code releaseDeploy calling code (optional)
Database post-release

New stored procedures can go out in the database release step. There’s no need to pre-release them because new code won’t call the procedure yet. We can create the stored procedure and deploy it. If you’re generating your own deployment scripts, the CREATE OR ALTER PROCEDURE syntax is a wonderful addition to SQL Server 2016 SP1 and lets you create a procedure quite easily:

CREATE OR ALTER PROCEDURE dbo.SomeProcedure
(
@Parameter1 INT,
@Parameter2 BIGINT
)
AS
SET NOCOUNT ON;

SELECT
	a.Col1,
	a.Col2
FROM dbo.TblA a
WHERE
	a.Parameter1 = @Parameter1
	AND a.Parameter2 = @Parameter2;
GO

Pretty much any automated deployment process will handle this scenario well. Then, once the procedure is out, new code released in the code deployment phase can reference it. The reason I like CREATE OR ALTER so much here is that it is an idempotent process: every time I execute, I get to exactly the same end state. That means my continuous integration process can deploy that change dozens of times over the course of a { sprint / month / week / day } and nobody will care.

A Note on Ordering

In the scenario above, I assumed that your stored procedure lives on its own and the only caller is some block of code someplace or manual execution. There are a few cases where that won’t be true, like nested stored procedures or SQL Agent jobs (or other scheduled tasks) executing stored procedures. In that case, we have to make sure that we deploy things in the right order. Creating procedures in the wrong order isn’t a catastrophe but it does lead to a warning:

CREATE OR ALTER PROCEDURE dbo.p1
AS
EXEC dbo.p2;
GO
CREATE OR ALTER PROCEDURE dbo.p2
AS
SELECT
	1 AS Val;
GO

The module ‘p1’ depends on the missing object ‘dbo.p2’. The module will still be created; however, it cannot run successfully until the object exists.

But if you create a SQL Agent job expecting a procedure to exist before it actually exists and that Agent job runs, you’ll get errors. Again, this is new code so it won’t break existing stuff but this is easy to get right.

New Result Set Column on an Existing Procedure

PhaseProcess
Database pre-release
Database releaseDeploy procedure
Code releaseDeploy new calling code (optional)
Database post-release

Adding a new column to an existing procedure’s result set is trivial as long as the calling code does not demand an exact signature for procedure outputs. For example, suppose we have procedure p2 above and we want to add a new column to its output set:

CREATE OR ALTER PROCEDURE dbo.p2
AS
SELECT
	1 AS Val,
	2 AS Val2;
GO

ADO.Net and most ORMs which accept stored procedures can handle the addition of a new field, so I’ll work from the assumption that your code can as well. If not, you’d need to version the procedure, which I’ll describe below.

Assuming your code can safely handle adding new fields, you update the code and deploy it. Old code doesn’t care about the new field and new code knows how to reference it.

One note here is that it’s probably safest to add new columns to the bottom of result sets. Some data access layers look at the order of items rather than a keyed name (like the column alias in the result set). I’m not a fan of using order to determine which column is which, but I know these technologies exist. So add new columns to procedures after existing columns unless you know how the calling code knows which column is which.

Removing a Column from a Stored Procedure Result Set

There are two scenarios I could come up with around removing a column from a stored procedure. Let’s hit them in order of difficulty.

Manual Execution or No Code Dependency

PhaseProcess
Database pre-release
Database releaseDeploy altered procedure
Code release
Database post-release

This is the easiest scenario: you have a manually run procedure or a procedure whose result set doesn’t need to have a set shape. Maybe it’s a logging procedure or writing data out to a table or whatever. You can safely change the procedure because it won’t break any existing code.

Code Dependency

PhaseProcess
Database pre-release
Database release
Code releaseDeploy new calling code
Database post-releaseDeploy altered procedure

If the code is currently referencing a column which can go away, we need to change application code first. Only after application code is completely finished will we be able to deploy an altered version of the stored procedure missing that column from its result set.

Changing Input Parameters

PhaseProcess
Database pre-release
Database releaseDeploy new procedure version
Code releaseDeploy new calling code
Database post-releaseDrop old procedure version

Any change to input parameters is liable to break existing code. This can include adding new parameters, removing existing parameters, changing parameter types, and possibly even changing parameter names. Depending upon how flexible your data mapping layer is, you might need to version a procedure. I’ll assume for the sake of simplicity that your data mapping layer is quite inflexible and breaks with any input parameter change, so we’ll go ahead with versioning.

Versioning a procedure is pretty simple: you create a new procedure with alterations you want. Corporate naming standards where I’m at have you add a number to the end of versioned procedures, so if you have dbo.SomeProcedure, the new version would be dbo.SomeProcedure01. Then, the next time you version, you’ll have dbo.SomeProcedure02 and so on. For frequently-changing procedures, you might get up to version 05 or 06, but in practice, you’re probably not making that many changes to a procedure’s signature. For example, looking at a directory with exactly 100 procedures in it, I see 7 with a number at the end. Two of those seven procedures are old versions of procedures I can’t drop quite yet, so that means that there are only five “unique” procedures that we’ve versioned in a code base which is two years old. Looking at a different part of the code with 879 stored procedures, 95 have been versioned at least once in the 15 or so years of that code base’s existence. The real number is a bit higher than that because we’ve renamed procedures over time and renamings tend to start the process over as we might go from dbo.SomeProcedure04 to dbo.SomeNewProcedure when we redesign underlying tables or make other drastic architectural changes.

As far as pushing out new versions of procedures goes, we deploy the new procedure dbo.SomeProcedure01, then deploy code which uses the new input interface. Once all of the old code which used the old stored procedure dbo.SomeProcedure is gone, we can drop the old version of the procedure and now our code base has just one version of the procedure left, dbo.SomeProcedure01.

As a quick side note, you might wonder why we use incrementing numbers. We could probably just get away with two values: dbo.SomeProcedure and dbo.SomeProcedure01. Then, the next change could just go back to dbo.SomeProcedure because that’s available. The reason we don’t do this is because we want to keep old versions in source control. We store old versions of procedures in an Archive folder so that we can see how the procedures morph over time.

Changing Input Parameters With Table Types

PhaseProcess
Database pre-release
Database releaseDeploy new table type version
Deploy new procedure version
Code releaseDeploy new calling code
Database post-releaseDrop old procedure version
Drop old table type version

This is a bonus scenario. Let’s say you use table-valued parameters and need to change the TVP. In the best-case scenario, your TVP allows nulls and your calling code can handle changes with aplomb. Well, too bad—you still need to follow this process because any change to a table type requires that you drop and recreate the type. We don’t want to drop a table type because we’d need to drop all stored procedures referencing the type first, and dropping procedures which the code uses is a no-no.

Creating a new type which is CI-safe is pretty easy. Here’s an example of a product type which takes a ProductID input:

IF NOT EXISTS
(
	SELECT 1
	FROM sys.types t
	WHERE
		T.name = N'ProductType'
)
BEGIN
	CREATE TYPE [dbo].[ProductType] AS TABLE
	(
		ProductID BIGINT NOT NULL
	);
END
GO

We would deploy the new table type and then create a versioned procedure which references this table type as two database deployment-time scripts. Then, in the code deployment phase, we transition from the old procedure-type combo to the new procedure-type combo. After the code release completes, we can run the database post-release process and drop the procedure and then the type in that order. If you try to drop a type which is in use, you’ll get an error like:

Msg 3732, Level 16, State 1, Line 30
Cannot drop type ‘dbo.ProductType’ because it is being referenced by object ‘<Calling procedure name>’. There may be other objects that reference this type.

Therefore, in this case, order really matters.

Refactoring a Procedure

PhaseProcess
Database pre-release
Database releaseDeploy altered procedure
Code release
Database post-release

This scenario is one of my key reasons for preferring stored procedures over ad hoc SQL in the code base: I can refactor stored procedures and they simply go out. In this case, I mean “refactoring” in the truest sense: the inputs and outputs remain the same, but the process by which we translate inputs into outputs can change. In a complicated stored procedure, this might involve creating and working with temp tables, reshaping the T-SQL query, or even adding query hints.

Conclusion

In today’s post, we looked at different procedure modification scenarios and covered how we can make minimally disruptive changes. Using the four-phase model works well here, and we haven’t run into a case where we absolutely need to use pre-release…yet. That will come soon.

Course Reviews: Fundamentals of Bayesian Data Analysis in R

This is a review of Rasmus Baath’s Fundamentals of Bayesian Data Analysis in R DataCamp course.

I really enjoyed this course. Rasmus takes us through an intuitive understanding of Bayesian data analysis without introducing Bayes’s Theorem until the 4th chapter. The best part is, that’s not even a criticism: by the time he introduces the theorem, you already know the parts and the theorem is more a formalization of what you have already done.

If you’re new to Bayesian thought, give this course a try. The examples are clear and interesting, and Rasmus does a good job of mixing tabular results with histograms and other methods for visualizing results.

One of the nicest things I can say about the course is that during the exercise phases, I almost never had to go look things up independently of the course materials. Pretty much every concept was already on the slides or in the instructions and it was a matter of putting the pieces together rather than spending an hour trying to research some function somewhere which might get you through the problem at hand. I did have to read the help files to figure out parameters for a couple of functions, but that’s fine—the problem comes instead when an instructor expects you to know something not mentioned at all anywhere. In setting up these exercises, Rasmus does a great job.

If there’s one thing I would have liked, it was a bit more detail on BEST and other Bayesian estimation tools. Fortunately, there are a couple of courses dedicated to STAN and JAGS, so those should satisfy my curiosity.