Old and Busted: DBCC Commands in 2016

I hate DBCC Commands

Not what they do, just that the syntax isn’t consistent (do I need quotes around this string or not?), the results are a distraction to get into a usable table, and you need to write absurd loops to perform object-at-a-time data gathering. I’m not talking about running DBCC CHECKDB (necessarily), or turning on Trace Flags, or any cache-clearing commands — you know, things that perform actions — I mean things that spit tabular results at you.

Stuff like this puts the BC in DBCC. It’s a dinosaur.

In SQL Server 2016

DBCC INPUTBUFFER got its own DMV after, like, a million decades. Commands like DBCC SQLPERF, DBCC DBINFO, and DBCC LOGINFO should probably get their own. Pinal Dave has a whole list of DBCC Commands that you can break your server with here.

But truly, the most annoying one to me is DBCC SHOW_STATISTICS. It’s insane that there’s no DMV or function to expose histogram information. That’s why I filed this Connect item.

UPDATE: It looks like Greg Low beat me to it by about 6 years. Too bad searching Connect items is so horrible. I urge you to vote for Greg’s item, instead.

Statistics are the intel SQL Server uses to make query plan choices.

Making this information easier to retrieve, aggregate, join to other information, and analyze would put a powerful performance tuning tool into the hands of SQL Server users, and it would help take some of the mystery surrounding statistics away.

Please consider voting for my Greg’s Connect item.

Thanks for reading!

How to Run DBCC CHECKDB for In-Memory OLTP (Hekaton) Tables

tl;dr – run a copy-only full backup of the Hekaton filegroup to nul. If the backup fails, you have corruption, and you need to immediately make plans to either export all your data, or do a restore from your last good full backup, plus all your transaction log backups since.

Yeah, that one’s gonna need a little more explanation, I can tell. Here’s the background.

DBCC CHECKDB skips In-Memory OLTP tables.

Books Online explains that even in SQL Server 2016, DBCC CHECKDB simply skips Hekaton tables outright, and you can’t force it to look at them:

Hey, that's the same way I handle merge replication - I just look away

Hey, that’s the same way I handle merge replication – I just look away.

However, that’s not to say these tables can’t get corrupted – they have checksums, and SQL Server checks those whenever the pages are read off disk. That happens in two scenarios:

Scenario 1: when SQL Server is started up, it reads the pages from disk into memory. If it finds corruption at this point, the entire database won’t start up. Even your corruption-free, non-Hekaton tables will just not be available. Your options at this point are to restore the database, or to fail over to a different server, or start altering the database to remove Hekaton. Your application is down.

Scenario 2: when we run a full (not log) backup, SQL Server reads Hekaton’s data from disk and writes to the backup file. If corruption is found, the backup fails. Period. You can still run log backups, but not full backups. When your full backup fails due to corrupt in-memory OLTP pages, that’s your sign to build a Plan B server or database immediately.

Here’s the details from Books Online:

Beep beep beep

Beep beep beep

The easy fix: run full native backups every day, and freak out when they fail.

Backup failures aren’t normally a big deal, but if you use in-memory OLTP on a standalone server or a failover clustered instance, backup failures are all-out emergencies. You need to immediately find out if the backup just ran out of drive space or lost its network connection, or if you have game-over Hekaton corruption.

Note that you can’t use SAN snapshot backups here. SQL Server won’t read the In-Memory OLTP pages during a snapshot backup, which means they can still be totally corrupt.

This works fine for shops with relatively small databases, say under 500GB.

The harder fix: back up just the In-Memory OLTP data daily.

With SQL Server 2016, the Hekaton limits have been raised to 2TB – and you don’t really want to be backing up a 2TB database the old-school way, every day. You could also have a scenario where a >1TB database has a relatively small amount of Hekaton data – you want to use SAN snapshot backups, but you still have to do conventional backups for the Hekaton data in order to get corruption checks.

Thankfully, Hekaton objects are confined to their own filegroup, so Microsoft PM Jos de Bruijn pointed out to me that we can just run a backup of just that one filegroup, and we can run it to NUL: to avoid writing any data to disk:

Miami Vice Versa

Miami Vice Versa

Oops, did I say we could just back up that filegroup? Not exactly – you also have to back up the primary filegroup at the same time.

If you’re doing great (not just good) database design for very large databases, you’ve:

  1. Created a separate filegroup for your tables
  2. Set it as the default
  3. Moved all the clustered & nonclustered indexes over to it
  4. Kept the primary filegroup empty so you can do piecemeal restores

If not, hey, you’re about to. An empty primary filegroup will then let you do this faster:

Checking for corruption by backing up to NUL:

Checking for corruption by backing up to NUL:

Tah-dah! Now we know we don’t have corruption.

This comes in handy if you’ve got a large database and you’re only doing weekly (or heaven forbid, monthly) full backups, and doing differential and log backups the rest of the time. Now you can back up just your in-memory OLTP objects for corruption.

Note that in these examples, I’m doing a copy_only backup – this lets me continue to do differential backups if that sort of thing is your bag.

For bonus points, if your Hekaton data is copied to other servers using Always On Availability Groups, you’ll want to do this trick on every replica where you might fail over to or run full backups on. (Automatic page repair doesn’t appear to be available for In-Memory OLTP objects.)

If you’d like CHECKDB to actually, uh, CHECK the DB, give my Connect item an upvote here.

[Video] Free Training of the Week: Dynamic Quorum and Dynamic Witness

In this week’s free video training sample, dig into the details of dynamic quorum and dynamic witness: what do they do? What’s strange about them at small node counts in a cluster? You’ll also learn how to configure quorum in three common scenarios in this 23-minute video.


(If you don’t see the video above, you’re reading this somewhere that doesn’t support video embedding. You’ll need to read the post on our blog.)

This video is part of our DBA’s Guide to High Availability and Disaster Recovery class that covers clustering, Always On Availability Groups, mirroring, log shipping, and more.

And it’s on sale this month! Use coupon code HighFive for half off our training videos and bundles.

Breaking News, Literally: SQL CLR Support Removed from Azure SQL DB

In the on-premises, boxed-product version of SQL Server, you can build your own CLR assemblies and call them from your T-SQL code.

For years, this feature was missing from Azure SQL DB – Microsoft’s platform-as-a-service database offering – and users voted that they wanted it.

In December 2014, Microsoft brought SQL CLR code to Azure SQL DB.

Today, Microsoft PM Julie Koesmarno tweeted (update Saturday 9AM – the tweet has been deleted, so here’s a screenshot):

Announcement tweet (since deleted)

Announcement tweet (since deleted)

Details are still coming in, but in the Reddit AMA for the Azure database teams (going on as we speak), it’s one of the users reports that they got an email that SQL CLR will be shut off in one week due to a security issue.

The cloud: at the end of the day, it’s just someone else’s server, and they can – and will – take tough actions to protect their product, their users, their security, and their profits.

Earlier this week, I was telling my SQLSaturday Israel pre-con class about HA/DR in the cloud, and I call the cloud a giant hamster wheel. Once you commit to getting on that wheel, you can never get off. You constantly have to watch what features the cloud vendor adds/changes/removes, and you have to be willing to jump into development and change your application right freakin’ now in order to compensate for their changes.

Have fun on the hamster wheel today, folks. Time to start coding your workarounds.

[Video] Office Hours 2016/04/06

This week, Brent, Erik, Jessica, and Richie discuss the sudden increase in AWS cloud clients, whether you should use cardinality estimator hints, how to do indexing on a former SQL Server 2000 database, and Brent’s hilarious Jerusalem tour guide. (Brent disappears about 90 seconds in, but then shows back up ten minutes later. Love that hotel Internet.)

Here’s the video on YouTube:

You can register to attend next week’s Office Hours, or subscribe to our podcast to listen on the go.

Another Hidden Parallelism Killer: Scalar UDFs In Check Constraints

Every single time

Really. Every single time. It started off kind of funny. Scalar functions in queries: no parallelism. Scalar functions in computed columns: no parallelism, even if you’re not selecting the computed column. Every time I think of a place where someone could stick a scalar function into some SQL, it ends up killing parallelism. Now it’s just sad.

This is (hopefully. HOPEFULLY.) a less common scenario, since uh… I know most of you aren’t actually using any constraints. So there’s that! Developer laziness might be a saving grace here. But if you read the title, you know what’s coming. Here’s a quick example.

USE [tempdb]

    DATEADD(MINUTE, [m].[message_id], SYSDATETIME()) [SomeDate]
    [sys].[messages] AS [m] ,
    [sys].[messages] AS [m2];

CREATE FUNCTION [dbo].[DateCheck] ( @d DATETIME2(7) )
        DECLARE @Out BIT;
            @Out = CASE WHEN @d < DATEADD(DAY, 30, SYSDATETIME()) THEN 1
                        ELSE 0
        RETURN @Out;

ALTER TABLE [dbo].[constraint_test]  ADD CONSTRAINT [ck_cc_dt] CHECK([dbo].[DateCheck](SomeDate) = 1)

FROM [dbo].[constraint_test] 

Parallelism appears to be rejected for maintenance operations as well as queries, just like with computed columns.

Interestingly, if we look in the plan XML (the execution plan itself just confirms that the query didn’t go parallel) we can see SQL tried to get a parallel plan, but couldn’t.



There’s a short list of possible reasons for plans not going parallel here from a while back. A quick search didn’t turn up a newer or more complete list.

Check yourself, etc. and so forth

How do you know if this is happening to you? Here’s a simple query to look at constraint definitions and search them for function names. This query is dumb and ugly, but my wife is staring at me because it’s 5:30 on a Saturday and I’m supposed to be getting ready. If you have a better idea, feel free to share in the comments.

WITH    [c1] AS ( 
  SELECT [name] , [definition]
  FROM [sys].[check_constraints]
  SELECT [name] , [definition]
  FROM [sys].[default_constraints] 
FROM [c1], [sys].[objects] AS [o] 
WHERE [o].[type] IN ('FN', 'TF') 
AND [c1].[definition] LIKE '%' + o.[name] + '%'

Thanks for reading!

I’m on the RunAsRadio Podcast

Pixelated for your pleasure

Pixelated for your pleasure

It’s always fun to talk to Richard Campbell about what’s going on, and this time around, it’s SQL Server 2016:

Listen to me chat with Richard about what’s coming in vNext.

We’re now in the second quarter of the year, and we still don’t have pricing, licensing, or edition/feature lists. Normally, when you release software, you need to educate users on how to choose the right box, but to do that, you need to train the trainers first. There simply hasn’t been any community guidance available yet on the questions end users – and managers – ask the most: how is this thing gonna be licensed?

That means one of two things: either it’s gonna be exactly the same (which doesn’t seem likely, given the crazy number of new features that are going into the boxed product this time around), or it’s gonna be wildly different, and passionate discussions might still be going on.

I think this is the best release since 2005. Granted, there’s still a couple of massive problems – for example, CHECKDB simply skips In-Memory OLTP and stretch tables, so I think you’d have to be an outright idiot to deploy either of those features on data you care about. Don’t think you care about the data? Remember, if there’s any corruption in your Hekaton tables, your entire database doesn’t start up. None of your other tables are accessible, and you have to restore from your last full backup plus your transaction logs. (You were taking those, right?)

But aside from that, SQL Server 2016 is fantastic, and in the podcast with Richard, we talk about Query Store, AlwaysEncrypted, the run to the cloud, and more. Enjoy!

For Technical Interviews, Don’t Ask Questions. Show Screenshots.

When you’re hiring a DBA, sysadmin, or developer, you’re not paying them to answer Trivial Pursuit questions all day long.

You’re hiring them to look at a screen, connect a few dots, and interpret what’s happening.

So for a position, I build a PowerPoint deck with a bunch of screenshots from the actual environment they’ll be working in. I start the deck by explaining that there’s no right or wrong answers – I just want to hear them think out loud about what they’re seeing.

For example, I’ll show a database administrator or developer this:

Tell me what you see in this ink blot.

Tell me what you see in this ink blot.

Typical reactions include things like:

  • “Oh, that’s a fact table.” – What does that mean? Where have you seen a fact table before? How do you handle them differently than other kinds of tables?
  • “It seems really wide.” – What does that mean? Are there strengths or challenges with wide tables? How many columns should a table have?
  • “The fields are mostly nullable.” – Is that the default? How do fields end up that way? What would you recommend changing? How might it break things, and how would you check to see if they were going to break? Would end users notice the impact of your changes?
  • “It isn’t normalized – it has both IDs and names.” – What kinds of tables might have that design pattern? What would be the performance impact of this design?

Whenever they say anything out loud, follow the thought. Don’t assume that they believe the same thing you do – ask open-ended questions to get them to explain what they know.

Let them talk until they’re silent for several seconds, and that’s their normal knowledge base. Resist the temptation to make suggestions like, “Did you notice how there doesn’t seem to be a clustered index?” If they don’t notice it, they don’t notice it – that’s your sign.

Another example – I’ll say, “Someone brought this query to you and complained that it’s slow. Where might you look for improvements?”


The query is abridged, but even just in that first screenshot, I want to hear their thought process around where they look, how they might run the query to check its effects, how to measure their work.

In your own interviewing, try to use real screenshots from your own environment. Show them the kinds of screens that they’re going to have to look at independently in their day-to-day work, and just let them brain dump about what they’re seeing.

Hearing their thought process is way more valuable than playing Trivial Pursuit.


This isn’t about performance

If you’re interested in performance tests, you can get in the way back machine and read a couple posts by Adam Machanic here and here. I’m also not talking about the difference between them. There are a million articles about that. They’re obviously different, but how does SQL handle that internally?

We’re going to run the four queries below separately, and look at the XML of the execution plan for each.

    COALESCE([u].[Id], [u].[Age]) 
    [dbo].[Users] AS [u];

    COALESCE([u].[Age], [u].[Id]) 
    [dbo].[Users] AS [u];

    ISNULL([u].[Id], [u].[Age])
    [dbo].[Users] AS [u];

    ISNULL([u].[Age], [u].[Id])
    [dbo].[Users] AS [u];

Even though COALESCE is the ANSI standard, I still see most people using ISNULL. Spelling? Laziness? Who knows? Under the covers, it’s just a CASE expression. You could write the same thing yourself.

No, I don't think JSON is a good idea either.

No, I don’t think JSON is a good idea either.

The second query gives you almost the same thing, but the columns are reversed. What’s the point, then?


SQL does something rather smart here. The Id column is NOT NULL, so when ISNULL is applied to it, it doesn’t bother evaluating anything.

That seems reasonable.

That seems reasonable.

Reversed, ISNULL, and whatever magical behind-the-scenes code Microsoft has thrown in there, runs against the Age column, which is NULLable.



Pros and Cons


  • Easy to spell
  • Shortcuts if you do something dumb
  • Not the name of a horrible band


  • Only two inputs
  • Not ANSI standard
  • Maybe doesn’t shower regularly

Coalesce Pros

  • ANSI Standard
  • Multiple inputs
  • Drinks coffee black

Coalesce Cons

  • Spelling
  • Just a case expression
  • Is the name of a horrible band

Thanks for reading!

SQL Server 2005 support ends next week. Here’s my tribute to it.

I remember the first time I used SQL Server 2005.

Did you know SQL Server has an expiration date on the bottom of the box? Also, this one is curdled.

Did you know SQL Server has an expiration date on the bottom of the box? Also, this one is curdled.

I was a database administrator working on a new-build data warehouse project in Miami. Both our data warehouse and SQL Server 2005 were looking like they were going to come out at the same time, and I kept hoping I could make the timing work.

SQL Server 2005 looked so seductive. Let’s take just a moment to think back about all the cool new technologies it introduced:

SQL Server Management Studio – for better or worse, this user interface has been with us for over a decade. I still hear some folks pining for the old days of Query Analyzer – not me, man.

Dynamic Management Views – no more obscure DBCC commands. Now you could just query system tables and get all kinds of health information.

SMTP email – which doesn’t sound awesome until you remember that in the SQL Server 2000 days, we usually had to install Outlook on the database server just to send mail.

Database mirroring – well, technically it wasn’t supported at RTM, but you could do it with a trace flag.

VARCHAR(MAX) – goodbye, text and ntext datatypes, and say hello to Max Headroom.

Index improvementstable partitioning looked better on paper than it actually was, but include fields on indexes are a staple of tuning today.

T-SQL crowd pleasers – common table expressions (CTEs), windowing functions, ROW_NUMBER(), cross apply, outer apply, try/catch…

Business intelligence – what an incredible flourish of new stuff. SSIS replaced DTS packages, and SSAS and SSRS were huge. SQL Server Notification Services, uh, not so huge.

Service Broker and CLR – the ability to use the relational database as an app server or an asynchronous messaging queue. Myspace jumped on this. (Or is it off this?)

Looking back, 2005 was an incredible leapfrog past SQL Server 2000. Granted, a lot of that was due to the looooong gestation period – Microsoft will surely never wait 5 years to release a database update again.

But here’s the awesome part: for the last 10+ years, you’ve been able to take your database from the last version up to the new version, as-is. You’ve been able to use the same T-SQL syntax, and just add on a few new tools. You haven’t had to learn a new language (if you don’t want to), or even change any of your tooling.

If you wanted to keep your skills exactly the same from SQL 2005 to SQL 2016, you could. All of the core stuff still works the same.

It’s just faster and more powerful.

Here’s to the foundation laid by SQL Server 2005.

Erik Says: