Art of the DBA Rotating Header Image

troubleshooting

What’s CHECKDB doing in my database restore?

Recently I was doing some work with a friend around some database restores. It was pretty routine stuff. However, after one restore my friend came across something in the SQL Error Log that caught him by surprise. As part of the restore, there was a CHECKDB message for the restored database:

My friend’s first reaction was “why is SQL Server doing a DBCC CHECKDB as part of the restore?” He was concerned, because CHECKDB is a pretty hefty operation and this could really impact the restore time if he had to wait on a CHECKDB to complete. But the other confusing thing was that the date for the CHECKDB didn’t match up with the restore timing.

A Clean Start

Now I was pretty sure that the SQL restore didn’t actually run a CHECKDB, but I needed an explanation for this message. I figured I’d start with good old AdventureWorks2014 to test through the process. Also, since I needed to get in the guts of the restore, I broke out a few undocumented trace flags:

DBCC TRACEON(3014);
DBCC TRACEON(3604);
DBCC TRACEON(3605);

Trace flag 3014 is the important on, as it provides the details on what actually happens during a database restore. Once I turned these flags on, I ran an initial restore of the AdventureWorks2014 database to the name of AW2014_BASE and reviewed the output from TF 3014. I focused on everything from when the database was brought online to the completion of the restore.

Notice that no where in this output do we see evidence of a CHECKDB command. So far, so good, right?

Checking our CHECKDB

My suspicion at this point is that the CHECKDB message found in a database restore was actually unrelated to the restore itself. The next step was to actually run a DBCC CHECKDB against my test database (and capture the time the DBCC was run).

With this completed, it was time to do another backup and restore with my trace flags enabled to see what I got.

Lo and behold, our CHECKDB message is back. But what’s going on? Why did it appear here but not in the first restore? The key (as you might have already guessed) is to look at the datetime for the CHECKDB in the error log: 2018-03-31 09:17:16. This matches the time we actually ran DBCC prior to our backup. In fact, if we review the message output from the restore (and not the error log messages) for the same restore span, you’ll see there are no actual CHECKDB messages within the restore.

Mystery Solved

The answer here is pretty simple. When you restore a database, SQL Server is trying to help you by telling you the last time the database had a CHECKDB run against it. That’s actually why the message says “his is an informational message only; no user action is required.” However, for many folks not familiar with the process can be confused and jump to the conclusion that SQL Server is doing more than it should be. The key to clearing up these misconceptions is to use our tools to lift up the hood and see what’s going on internally. Once you look at these details, it’s pretty clear to see what’s happening and become better informed on how SQL Server works.

WHERE to JOIN?

I’ve been really enjoying the DBA StackExchange site recently.  Not only can you see what challenges and hurdles people have, the site construction gives people a great way to contribute to an ever expanding library of database solutions.  Questions range from the very simple to the highly esoteric, but in all cases the community comes together to groom both questions and answers in such a way that a comprehensive knowledge base is built for future use.

One of these questions that recently came up was:  Which performs better, creating your joins in the FROM clause or the WHERE clause?  Most people have been using the ANSI-92 syntax, so this question may seem a little odd, but I still see a lot of SQL code out there that uses the prior syntax where the joins are declared in the WHERE portion of your query.  If you want to read more, Mike Walsh(@Mike_Walsh) has a great post on how the syntax has evolved and how its changed in SQL Server 2012.

Back to the question, though.  Does it really make any difference?  Well, I could tell you straight out, but what sort of blog post would that make?  Instead, let’s test it out ourselves.  Using a basic schema, I’ve put together two very basic queries:

SELECT
  s_id
  ,s_desc
  ,b_desc
  ,f_desc
FROM
  snafu s
  ,bar b
  ,foo f
WHERE
  s.b_id = b.b_id
  AND b.f_id = f.f_id;

SELECT
  s_id
  ,s_desc
  ,b_desc
  ,f_desc
FROM
  snafu s
  INNER JOIN bar b ON (s.b_id = b.b_id)
  INNER JOIN foo f ON (b.f_id = f.f_id);

As you can see, the only real difference here is that in the first query we have our joins in the WHERE clause. The second follows ANSI-92 syntax and places the joins in the FROM clause. Now how do we tell if they perform differently? Query plans, of course!

Query 1 (WHERE clause)

WHERE_JOIN

Query 2 (FROM clause)

FROM_JOIN

Notice how both queries have exactly the same plan.  This is because our friend, the Optimizer, understands the two approaches and will build the plan accordingly.  Want to play with it yourself?  You can check out the full example over at SQL Fiddle.

There are three things I’d like you to take with you after this brief exercise:

  • Functionally, it doesn’t matter if you declare your JOINs in your FROM or your WHERE clause, the optimizer will treat both as the same.  However, if you read Mike Walsh’s blog post, you really should be using the ANSI-92 standard.  The “old” syntax only works if you have your database in SQL 2000 compatibility mode (which means it doesn’t work at all in SQL 2012).
  • Query plans will answer most of your performance questions regarding SQL syntax.  If you haven’t been looking at them, I strongly suggest you pick up Grant’s book and start checking those plans out.
  • I’ve only recently discovered SQL Fiddle, but this is a great tool for mocking up and testing concepts for databases.  I haven’t built anything larger than 2-3 tables, but for basic test cases and examples to demonstrate something, it’s really cool(it even lets you look at query plans!).  Check it out.

Standing on the Shoulders of Giants

When I went to SQL Rally last week, I fully expected to be learning from many of the presenters there. The excitement about being immersed in all that awesomesauce is infectious. What I didn’t expect is that I would take my turn at teaching, handing out information like candy to twelve year olds. It was one of those times where I was talking shop with some other folks while we were all taking a session off. The group started chatting about performance tuning(something I geek out about) and I shared out some of the fundamental links, blogs, and tools I use when I get the inevitable “My query is slow!” call.

Now, just to be clear, none of the information I shared was “mine”. The beauty of the SQL Server community is that we have so much great talent and everyone is willing to help educate. What follows here is a list of my core tools and information links, resources that I use on almost a daily basis. I also see them as the start of a journey, because none of this is the last word on a particular subject. If you’re like me you’ll continue to browse the Google ‘verse with what you pick up here.

Brent Ozar:

Full disclosure, I’m a Brent Ozar fanboy. I started reading his blog about a year or so ago and he’s made me laugh and taught me a whole heck of a lot. It’s because of him that I started blogging and getting involved in the community.

Glenn Berry:

Glenn Berry is a local Denver MVP and I see him from time to time at the Denver SQL User’s Group meetings. Not only has he provided these great DMV queries, but he really understands hardware and has some great posts on the nitty-gritty of your machines.

General Community Resources

These two sites are invaluable resources for articles and tutorials on getting stuff done. When I was figuring out how to implement partitioning, I did a lot of reading on both of these sites.

Documents

Both of these docs live on my laptop and are constantly getting referenced. My only complaint about the waits and queues white paper is that it’s a little stale and I hope Microsoft does an updated one for Denali when it’s released.

If you don’t have a cheat sheet with your go to resources, hopefully this list gives you a good start. Or you can add this to your currently existing list. I have found each one of these links invaluable to my day to day work, not only enriching my general knowledge but giving me the tools to troubleshoot many of the performance problems that come my way.

Dumb Questions

“Can I ask a dumb question?”

Anyone who works with me for a short period of time will hear this tumble out of my mouth.  I can’t tell you the number of times someone launches into a 5-10 minute explanation of a really cool concept and giving me that “I have a cunning plan” look when they finish.  However, because I didn’t have that one thing in my head they figured was obvious, I end up having to ask a couple questions to get myself on the same page.  It’s never a big deal to fill in those gaps and the opposite party is usually happy to provide the context, but I do have to ask those questions.

Assumption: The Mother of all Screwups

The problem is with assumptions.  Whenever we communicate with each other, we have to make an assumption about what the other person knows.  Unfortunately, a lot of times those assumptions are wrong.  Remember the ill-fated Mars orbiter where there was a mixup between the imperial and metric measurement systems?  While we all smack our forehead and say “D’OH”, it’s pretty obvious that this is the result of one side making assumptions about the other side’s knowledge.  If someone had questioned the basic assumptions of both sides, they might have saved that $125 million orbiter, but instead, the two teams ran off on different tracks because they didn’t want to appear dumb.  (How how do they look now, right?)

I know we all get frustrated when the Level 1 support tech asks us if our network cable is plugged in or that we haven’t kicked out the power cable, but these are exactly the kind of questions we need to ask when troubleshooting database problems or building requirements for a project.  The reason for this is because asking the dumb questions helps establish a baseline starting point for figuring out the problem.  After all, when someone comes to us saying “the system is slow”, we don’t want to bash our heads with IO stats or memory pressure when all we had to do was deal with one blocking query.  We wouldn’t know about that unless we asked the right questions to start our troubleshooting.

So tell me a little about yourself

The best approach is to dealing with this is to teach yourself to reduce the number of assumptions you make.  Easier said than done, right?  What really helped me was an exercise a former boss/mentor had me do early on in my career.  It’s pretty simple:  In one paragraph (no real limit on sentences or words), describe your work to someone who has no idea what you do.

Seems easy at first, but think about it for a second.  Let’s break down the following sentence that I might use:
“I administer and maintain SQL Server databases.”

Already in this sentence we have several assumptions about the listener.  First, that they know what a database is, and they know what maintenance and administration is for SQL Server.  Chances are most folks don’t, so we would then have to explain those terms to them in a way they would understand.  We don’t need to get overly complex, but provide the listener a basic grasp of what we’re talking about.

The end result is that this exercise puts us in the mindset of someone who may not have the same knowledge or experience as we have, thus getting us to restructure our thinking and presentation in a way that covers the entire context of whatever idea we’re presenting.  It also gets us to start evaluating our assumptions based on our audience.  We’re always going to make assumptions, but what we want to do is make them in the context of our audience.  Speaking to a group of DBAs?  Yeah, leave out that stuff about relational theory.  Talking with IT infrastructure dudes?  Chat with them a bit about database file layouts and managing I/O contention.  Oh, monkeys from Kenya?  Throw ’em a couple of bananas.

Duh!

So whether we’re asking ourselves or someone talking to us, it’s always good to get those assumptions straight.  Sure, we all pride ourselves on figuring things out.   That’s why we’re in this business, for the joy of solving that process that no one else could.  But it never helps us when we get lost on that wild goose chase because we made the wrong assumption.  Save yourself some time and a lot of headache, make sure you’re on the same page with those you work with, and ask some “dumb” questions.  It never hurts.