Michael J. Swart

A necessary evil, but still evil. Why? Because it it won’t share with Sch-S and Sch-S is taken and held for absolutely everything (Yes, even your NOLOCK queries). And that can lead to some interesting concurrency problems. Let me explain.

Sch-M

Sch-M is an abbreviation for Schema Modification. It refers to a kind of lock that is taken on a table/index or other object whenever you want to modify that object. SQL Server allows only one Sch-M lock on an object at a time. So if you want to modify a table, your process waits to take a Sch-M lock on that table. Once that lock is granted, the modification is completed and then the lock is released.

Sch-S

Sch-S is an abbreviation for Schema Stability. It is a kind of lock that is taken on an object when a process doesn’t want that object to change its definition. It makes sense. If I’m reading a set of rows through a table, I don’t want a column to disappear on me half way through. SQL Server allows many Sch-S locks on a table.

Sch-S vs. Sch-M

But Sch-S locks are incompatible with Sch-M locks. This means that when you want to modify a table, you’re not granted a Sch-M lock immediately. You have to wait for everyone using that table to finish using it. You’re essentially put on hold until the existing queries complete and their existing Sch-S locks are released. This also means that while you’re waiting, every query who wants to begin using that table is waiting in line behind you too. Basically “Everybody outta the pool while the lifeguards change shifts.” But that’s usually acceptable right? Database schema modifications are a big enough change to require a maintenance window.

Index Rebuilds Are Table Modifications

It’s true, if you have the luxury of maintenance windows for your DB changes, you’ll be alright. But you also have to consider your database maintenance plans (automated or otherwise). Those plans can launch index rebuilds while the database is online. And all index rebuilds also count as table modifications and take Sch-M locks. An index rebuild has syntax like this:

ALTER INDEX [PK_MyTable] ON [MyTable] REBUILD WITH (ONLINE=ON)

Hopefully you’ve remembered that ONLINE=ON part. When you use that part, the index is rebuilt in the background and at the end of that processing time, a Sch-M lock is taken and released very quickly.

But maybe you’re not so lucky. Maybe you’re not running 2012 yet and have an index that includes blobs. Or maybe you’re running on Standard Edition. In those cases you won’t be able to use the ONLINE=ON feature. In that case, the Sch-M lock is taken by the rebuild process and it’s held the entire time that index is rebuilt. During the rebuild, that index is now truly offline. No access for you.

You Can Get Stuck

Just like I did. A while ago, I was asked to help with this exact situation. An index rebuild had been running for hours it was offline and the Sch-M lock that was held was preventing anybody from using or even looking at that table. I was stuck between a rock and a hard place. I had to choose between letting the index rebuild complete (which could take hours) or cancelling the job (whose rollback could take hours). There was nothing I could do to avoid additional hours of downtime. There was another bit of irony in my situation. We didn’t care about the existing data in that table. A truncate table or drop/recreate table would have suited us just fine.

… Like Really Stuck

It occurred to me to try something out. What if I created an identical empty table with a different name. We didn’t need any data in the locked table. So using a new table could work. And because the locked table is only accessed by stored procedures, I can modify those procedures to use the new table instead of the locked one.

Nope!

For some reason, the ALTER PROCEDURE requires a Sch-S lock on the old table, the table it no longer refers to. The sprocs can’t operate or be modified without a Sch-S lock on that locked table. This sketch illustrates my situation.

Make sure your index plans never attempt to rebuild big indexes offline.

Extras

Some relevant DBAReactions gifs:

• After I tried changing the clustered index on our biggest table
• When I alter a live table without blocking users

Say you want to change the type of a text column using the ALTER TABLE … ALTER COLUMN syntax. It is valuable to know how much work SQL Server will have to do to fulfill your request. When your tables are large, it can mean the difference between a maintenance window that lasts five minutes, or one that lasts five hours or more.

I give a list of exactly when you’ll feel that pain and when you won’t.

(BTW, CHAR(MAX) columns are impossible, you’ll get the gag if you figure out why)

When is the Whole Table Processed?

Here are conditions which require processing the entire table:

• switching from unicode to non-unicode or vice versa.
• changing a column from nullable to not nullable.
• going from fixed length field to variable length field or vice versa.
• decreasing the maximum length of a field.
• increasing the maximum length of a fixed length field.
• converting from limited length columns to unlimited or vice versa. (e.g. varchar(10) to varchar(max)).
• collation modifications on non-unicode columns that change character set or code page. (See Collation Hell Part 3 by Dan Guzman)

On large tables, any of the above alterations will be a heavy hitter and will take time and fill transaction log (except that a shrink of fixed length fields seems to only require a scan).

What Changes are Metadata Only Changes?

That’s a lot of conditions! What’s alterations are left?

Not much:

• Increasing the maximum length of a variable length column.
• Changing the type from text to varchar(max).
• Changing the type from ntext to nvarchar(max).
• Any of the above while making a non-nullable field nullable.
• Any of the above with a change in collation (with some big caveats, see Collation Hell Part 3 by Dan Guzman).

These changes are metadata only changes which means SQL Server doesn’t have to touch any of the actual data. So the size of the table will not impact the time it takes SQL Server to process the ALTER TABLE command. But see some notes about concurrency below.

Some Notes

Some notes about the above:

• I ignored and make no claims about migrations where text or ntext is the target column type because the exceptions are strange and that scenario seems to fall under “Why would you want to do that?”
• The above applies to only versions I’ve tested. Specifically 2008, and 2012.
• The metadata-only changes I described above is not entirely on-line. There are still concurrency concerns to watch out for. These ALTER statements still request Schema modification (Sch-M) locks on the table, and once granted, only hold them briefly. But if you try to alter a column on a live environment and some long running query blocks your ALTER TABLE statement, then other queries that need access to the table will be blocked as well. 
• Terms I used
  • fixed length: char(x), nchar(x)
  • variable length: varchar(x), nvarchar(x)
  • unlimited length: varchar(max), nvarchar(max), text, ntext
  • unicode: nchar(x), nvarchar(x), nvarchar(max), ntext
  • non-unicode: char(x), varchar(x), varchar(max), text

I’ve discovered that non-intuitive lessons stick in the brain the best. I share three examples that I either learned recently (or at least re-learned recently).

Uniqueifiers on Indexes That I Thought Shouldn’t Need Them

Do you remember what a Uniquifier is? SQL Server uses these hidden values on rows to keep non-clustered indexes in sync with their non-unique clustered indexes. Think of this questionable schema:

create table Actors
(
	ActorId int identity not null,
	LastName nvarchar(100) not null,
	FirstName nvarchar(100) not null,
	AgentId int null,
	primary key nonclustered (ActorId)
)
 
create clustered index IX_Actors
	on Actors(LastName, FirstName)

Then add some data:

insert Actors (LastName, FirstName, AgentId)
values ('Douglas', 'Michael', 3),
       ('Douglas', 'Michael', 4),
       ('Keaton', 'Michael', 5),
       ('Douglas', 'Kirk', 4)

I can demonstrate the uniquifier value here because of the non-unique clustered index:

select sys.fn_PhysLocFormatter (%%physloc%%) as [physical rid], *
from Actors -- in my case, this shows page 231
 
DBCC TRACEON (3604);
DECLARE @dbid int = DB_ID(); 
DBCC PAGE(@dbid, 1, 231, 3); 
 
/* showing (among other things):
...
Slot 1 Offset 0x60 Length 51
UNIQUIFIER = 0                       
LastName = Douglas                   
FirstName = Michael                  
ActorId = 1                          
AgentId = 3                          
KeyHashValue = (16035ff378a3)        
 
Slot 2 Offset 0x93 Length 55
UNIQUIFIER = 1                       
LastName = Douglas                   
FirstName = Michael                  
ActorId = 2                          
AgentId = 4                          
KeyHashValue = (16035e22af06)        
...
*/

Cool! Just like real life (See Michael Douglas (I – XXVI) at IMDB). This uniquifier value behaves very much like the roman numerals you see at IMDB.

Here’s the part that wasn’t obvious… what if I chose a clustered index whose columns include the primary key…

drop index IX_Actors ON Actors;
create clustered INDEX IX_Actors
	ON Actors(LastName, FirstName, ActorId)

That should be unique right? The index includes the primary key column (ActorId) that guarantees it. So SQL Server shouldn’t need the uniquifier right?

But it does! 

SQL Server doesn’t count on the index being unique unless you say so. I actually discovered the above while using Kendra Little’s sp_BlitzIndex, a tool so underexposed it’s pasty.

UPDATE Modifies Column Values Only Once

What does the following code produce?

-- set up test table 
declare @test TABLE (id int, value int);
declare @source TABLE (id int, increment int);
 
insert @test (id, value)
values (1, 0), (2, 0)
 
insert @source (id, increment)
values (1, 10),(1, 20),(1, 30),(2, 100),(2, 100),(2, 100)
 
-- "sum": add each value in the source to the existing value
update @test 
set value = value + increment
from @test t
join @source s
    on s.id = t.id
 
-- check the results
select id, value
from @test
/* The results:
id          value
----------- -----------
1           10
2           100
*/

Surprised? I think this example (or one very much like it) surprises those of us who started our careers as programmers. Many of us follow up this lesson with the related “UPDATE statements don’t have GROUP BY clauses” and then the lesson “How do I use CTEs?”

Indexed Views Don’t Support Max/Min Aggregates

Indexed views support a couple aggregate functions like COUNT_BIG() and SUM(). And with some trickery you can calculate AVG() and STDEV(). But SQL Server restricts the use of MAX() and MIN() in indexed views despite how useful they’d be.

It might help to understand why. SQL Server maintains indexed views as physical database objects and it can maintain aggregate values like COUNT() and SUM() by processing only the changing rows in the base table while safely ignoring the rest of the table. But I can’t say the same for MAX() or MIN(). If SQL Server supported MAX() and MIN() in indexed views, then when you delete the row in the base table that represents the MAX() value, SQL Server would have to search the rest of the base table to find the new MAX() value.

Check out the microsoft.connect feature suggestion Expand aggregate support in indexed views (MIN/MAX). Aaron Bertrand created this Connect suggestion and I like it because it shows how effective constructive feedback can be. I like it because of its description, comments and the useful workaround. The Microsoft team even gave some insight into how they almost included this feature in SQL 2008. This connect item only seems to be lacking an E.T.A. so go and cast your vote!

Bonus Content

I didn’t draw any illustrations this week so I’m including some bonus content (admittedly written by others):

Non-obvious Things From Twitter Friends

Trivia

• Did you know Michael Keaton (born Michael Douglas) changed his name to avoid confusion with that other guy? Pretty wise.
• Jes Schultz Borland reminded me recently that good writers use more active verbs. I took that advice to heart and turned the writing of this article into an exercise. I avoided using words like is, was or are here and I think it turned out pretty well.