Measuring Efficiency of DBCC CheckDB with Computed Columns

by Scott Ellis and Erik Darling

Published on: Nov 28, 2014 @ 11:16

VERSION of Microsoft SQL : Microsoft SQL Server 2008 R2 (SP3) – 10.50.6000.34

Background

Recently we [a software vendor] added some persisted, computed columns to our software database, which is MS SQL 2008 and 2012.  We (my friend Erik and I – this is not an officially sanctioned test) wanted to know how much longer DBCC checkDB tests would take, and if we could put a percentage on it.  See the last paragraph for the answer if you want to skip to the good part and avoid reading all these accursed words.

Here are the basics of the computed columns.  An index is subsequently created on each computed column, each index contains 3 keys. The computed column is the leading key in all three of them.

Here is the computation in the table definition on each computed column:

 [redactedComputedColumnName]  AS (CONVERT([binary](20),hashbytes('SHA1',case when isnull([hashedField],'')='' THEN CONVERT([nvarchar](30),[ID],0) ELSE upper('S'+rtrim([hashedField])) end),0)) PERSISTED NOT NULL

The Rationale

Why did we want to do this test: This test seeks to identify the percent difference in duration of a DBCC check with and without computed columns.  We sought to determine if we could create a percentage difference based on the size of the single, very large table that houses the computed columns, and then apply that as a multiplier across thousands of databases.

Previously, these columns were populated by a trigger, so adding computed columns has increased the duration of DBCC.  We did the right thing and removed the trigger because it was a never-ending source of trauma when inserting massive amounts of data, which is a common occurrence in this software.

The Experiment

To begin, we restored a 5 GB database* that is representative of the types of databases about which many people will be curious. We ran this test against a database attached to SQL on an 8 core, 50 GB RAM SQL Server 2008 R2.  The table we examined is 3.398 GB.  The database server runs on an Intel Xeon E5 2.6 GHz virtual machine. Here is the restore code we used, if you are into this sort of thing:


 RESTORE DATABASE [DBCC_TEST5GB]
 FROM DISK = '\\Backups_SQL\RELO001\ERIK_COMP_TEST\FULL\v.NoComputedCols.bak'
 WITH BUFFERCOUNT = 500, MAXTRANSFERSIZE = 4194304, STATS
 
Restore Time: 670553 pages in 115.857 seconds (45.216 MB/sec)
Size of BAK file: 1.32GB (compressed backup)

We used BUFFERCOUNT of 500 and a MAXTRANSFERSIZE of 4 MB because we wanted to beat the shit out of our IO. Use this code (really, anything you find here) at your own peril. The word “STATS” allows you to see how far long the restore has progressed.

The we set:

STATISTICS TIME ON
We wanted to know how long it would take, both real world and CPU time.
For monitoring, we opened a new window to run sp_whoisactive to see what’s happening.  We even piped the output of Adam Machanic’s sp_whoIsActive script to a table for further evaluation and verification as needed.
Then we started the DBCC CheckDB. The following Traceflags were active:
--DBCC TRACEON (2549, -1)
--DBCC TRACEON (2562, -1)
--DBCC TRACEON (1118, -1)

You can look these up at:
http://blogs.msdn.com/b/saponsqlserver/archive/2011/12/22/faster-dbcc-checkdb-released-in-sql-2008-r2-sp1-traceflag-2562-amp-2549.aspx  and  http://sqlperformance.com/2012/11/io-subsystem/minimize-impact-of-checkdb

Baseline 1: DBCC against a 5 GB, no Computed Columns Database

We need to know how long a DBCC check will take on a “normal” database. To get to a version of the software that has computed columns, we upgraded from an earlier version.  First we did the command against a previous version of the database:

 /* DBCC command for V.noComputedCols database */
 /* DBCC command for 7.5 database */
 DBCC CHECKDB ([DBCC_TEST5GB]) WITH NO_INFOMSGS, ALL_ERRORMSGS
/* Run DBCC check without indexes */
 Duration: 103985 ms.
 CPU time: 71663 ms.

Baseline 2: DBCC against a 5 GB,  database with computed columns disabled.

The previous database was upgraded to a version with computed columns. To establish foundation, we took a baseline with Computed columns disabled.  This was to look for possible differences introduced by the upgrade.

/* Disable indexes on computed columns */
ALTER INDEX [IX_1003669_RI] ON [EDDSDBO].[Document] DISABLE
ALTER INDEX [IX_1003671_RI] ON [EDDSDBO].[Document] DISABLE
ALTER INDEX [IX_1035456_RI] ON [EDDSDBO].[Document] DISABLE


Ran a DBCC command:

 /* DBCC command for disabled Computed Columns database */
 DBCC CHECKDB ([DBCC_TEST5GB_CC]) WITH NO_INFOMSGS, ALL_ERRORMSGS
 /* Run DBCC check without indexes */

Results:

Duration: 33003 ms.
CPU time: 70056 ms.

Experiment 1: DBCC with Computed columns on against a 5 GB database

To get the database back to where it needs to be, we rebuild the computed column indexes.

/* Rebuild indexes */
ALTER INDEX [IX_redacted_1] ON [EDDSDBO].[Document] REBUILD
ALTER INDEX [IX_redacted_2] ON [EDDSDBO].[Document] REBUILD
ALTER INDEX [IX_redacted_3] ON [EDDSDBO].[Document] REBUILD

Then run the DBCC

 /* DBCC command for enabled Computed Columns database */
 DBCC CHECKDB ([DBCC_TEST5GB_CC]) WITH NO_INFOMSGS, ALL_ERRORMSGS/* Run DBCC check without indexes */
Duration: 147264 ms.
CPU time: 81358 ms.

Baseline 3 : 430 GB database with disabled computed column indexes.

Next, to establish our multiplier, we ran against a 428.48 GB Database.  Having already established that the CheckDB time ran with negligible differences, we ran it again with the same three computed columns, but with them disabled.

The table with the computed columns sported a hefty 269.418 gigabytes.

/* Disable indexes on computed columns */
ALTER INDEX [IX_redacted_1] ON [redacted].[EDDSDBO].[Document] DISABLE
ALTER INDEX [IX_redacted_2] ON [redacted].[EDDSDBO].[Document] DISABLE
ALTER INDEX [IX_redacted_3] ON [redacted].[EDDSDBO].[Document] DISABLE
/* Run DBCC */
DBCC CHECKDB ([DBCC_TEST430GB]) WITH NO_INFOMSGS, ALL_ERRORMSGS
/* Indexes Disabled

SQL Server Execution Times:

Duration: 9705874 ms.
CPU Time: 4391135 ms.
This (9705874 ms) is 2.69 hours (161.7 minutes)

 /* Rebuild indexes */
ALTER INDEX [IX_redacted_1] ON [EDDSDBO].[Document] REBUILD WITH (ONLINE = ON)
ALTER INDEX [IX_redacted_2] ON [EDDSDBO].[Document] REBUILD WITH (ONLINE = ON)
ALTER INDEX [IX_redacted_3] ON [EDDSDBO].[Document] REBUILD WITH (ONLINE = ON)
Index Rebuild time: 00:01:59.003  (about 2 minutes)

Experiment 2: Run CheckDB on large, production-grade data set with enabled computed column indexes

/* Run DBCC */
/* DBCC command for 8.2 database */
DBCC CHECKDB ([DBCC_TEST430GB]) WITH NO_INFOMSGS, ALL_ERRORMSGS

SQL Server Execution Times:

Duration: 10846102 ms.
CPU time: 4689156 ms.

Conclusions

To fully understand what is happening during a DBCC CheckDB of database computed columns, check out this blog post from Paul Randall.

Paul’s post does indicate a much greater performance hit than what we saw. This could be due to some confluence of the calculation involved, the number of them, the size of the data, the index, trace flags in use, and the underlying hardware.  Paul suggests that dropping and re-enabling the indexes is not a “palatable solution.” I submitted an inquiry to him about this, asking why it is unpalatable (which quite literally means not very tasty), and he responded “Because someone might have indexes that take a long time to rebuild, might not have online, might be enforcing a constraint…”

This got me to thinking that, since with this particular database software, there is no constraint, that many people running this product have Enterprise, and many administrators have offloaded the DBCC process to an extraneous SQL server, disabling the indexes may be an option.  Certainly, in an offloaded situation, disabling and leaving them disabled may leave only a mildly unpalatable aftertaste.

For those of you who are doing consistency checking in production, the only real issue that you can experience is if users actually attempt to access and use the database while the check is in progress and the indexes are disabled. If your column has great potential to be used a lot, then we don’t recommend a production disabling of this index.

Also realize that if this index is corrupted in your production system, you run the risk of a user running across it and then you have to rebuild it during production hours. The risk is low, but it is there.

Many infrastructure admins that run corruption checks on very large** databases run them in an offloaded way.  They restore the databases to cheaper hardware and let the checks take a day or two to run.  For those people, disabling these indexes is a perfectly viable solution.  If even the remote capacity exists that someone may hit those indexes WHILE they are dropped and WHILE you are doing a DBCC check DON’T DO IT. This not a viable option for a production system and if you are caught doing it by this software infrastructure architect, I will scold you 🙂

Ultimately, whether you have Enterprise or not is irrelevant – you do far more damage to query capabilities for the one or two hours that the indexes are offline than you do for the two minutes it takes to rebuild them.   Moral of the story :

If you are offloading DBCC and want it to go faster, automate a computed column index disable.  If not, if you are performing DBCC in prod, A) Stop doing that B) leave these indexes enabled.

The percent performance improvement, for these particular databases,  seems to be in the neighborhood of XX%- YY%.

 

*No, you can not have, ever, no matter how often you ask me for it

** By very large I mean any size database that causes you stress!

 

 

Missing African Grey Parrot – Evergreen Park Illinois

parrotHey – as a Chicago Bird Collision Monitor volunteer, I received this message today – please RT and post to FB too!!! It is cold out, and this bird will be scared and alone !

Missing African Grey Parrot
I’m forwarding this kind of heart-breaking message because CBCM might hear something about this bird that escaped.  The person who had it as a pet is a U of C grad student.  The fact he has put up a huge reward suggests it really meant something to him.
–Dave

———- Forwarded message

Hi all,

A good friend of Ben and mine lost his African Grey parrot (see below). If you get a chance could you pass on the information to any local birding societies, collision monitors, etc. that you have contact information for? The parrot was like a child to him and his partner and the loss of the parrot has been utterly tragic for them.
Many thanks,
Aaron
Begin forwarded message:
I apologize for sending this to the entire listserve, but I really could use your help.  Yesterday I returned from SVP in Berlin to the news that my 3-year old, African Grey parrot, named Earl, had escaped from the woman who was boarding him on November 1st (eleven days ago).  She had been lying to me the entire time that he was fine, “so that it wouldn’t ruin my trip.”  I am distraught, and I am desperate, because he could be anywhere on the south-side of Chicago by now (he is fully flighted).  He was last seen in Evergreen Park, IL.  My only hope is that he flew to somebody for help, before a hawk or the cold got him.
Please, if there is anyone you know that is a bird-watcher or who lives in the vicinity of Evergreen Park, please ask them to keep an eye open for Earl.  If you know anybody in any ornithological societies as well, please let them know.  Any sightings of a strange grey bird, with a bright red tail, a little bit larger in size than a pigeon, is a good sighting.  I can’t do this alone and it all feels so hopeless right now.
My sincerest gratitude,
Justin

How to Catch a Chicken and Leverage its Snark Containment Properties

Recently I had a conversation with a friend about her snark. I keep my snarks in a pantry, in my backyard, underneath the tree where I hang my cheapshots. She was considering getting rid of her snark, and finally we arrived upon the decision that I would send her five, fresh and crinkly snarks.  Snarks, being quite snarky, depend upon chickens and thrive best in an environment that is replete with chickens.  So, of course, i can’t send anyone five new snarks unless they have a chicken for me to put them in.

My usual barter for a small chicken filled with fresh, newly minted, and incredibly jagged snarks, then, is exactly one self-addressed, stamped chicken.  iizeeuna, an apparent foreigner to the ways of snarks and chickens, perhaps mired down in his (or her? I really don’t know, it was twitter for frabjulous’ sake

That was a stupid sentence, so I stopped writing it.  No.  You didn’t misread it.  It ended without a completed thought.

I suppose that therein lay the secret to catching chickens. One must be nimble of mind, quick to abandon poor choices, and loathe to take the bother to cleanup after oneself – if one wishes to do battle with chickens.

Perhaps, let us first begin with the definition:  A chicken is a dreadful, dangerous beast and is defined as the only proper container in which a snark may be transported.  Mind you, snarks are not CREATED in chickens, they are only merely stored inside of them (they strike the chicken on the outside, and sink in slowly over time).  It is the existence of the chickens that instantiates the snarks.  For it is so dense in matter and form, that the bright shapely gestaltedness of a snark can only be preserved forever deeply within it.

Further more, neither the USPS nor the FedEx will agree to transport snarks in any other container.  They are far too toxic if left to float and meander about and be left to their own devices.

The etymology of the word “chicken” has its roots in ancient egyption.  This was their word for the country “Turkey” and is the sourse of much confusion in children regarding the separate species.

Further to that, the word combines many words to make the one. It is a portmanteau of the word “Chickadee” and “Monster that hath twice the fury of a woman scorned of which hell hath no fury + a 17 witch coven” the latter being a very long word that is broken up by spaces in order to make it more legible and is also, coincidentally, a transliteration from ancient swahili for “Bad ass dude with a big trident”.

The source of this transliteration in the ancient texts is apparent : one must have a big, big giant, three pronged TRIDENT (lord help you if you get the 3.2 pronged kind!).  You will use the trident to catch the mighty chicken, for it is a cowardly, yet formidable beast and a worthy foe of anyone in a loin cloth.  In fact,it is only permitted to hunt chickens while wearing a loin cloth.  Further outerwear will render your efforts ineffective.  If one is female, then one must pursue the chicken topless (I apologize if this offends, I do not make these rules).

To attract a chicken. one must first have incredibly crinkly and jagged new snarks.  If you don’t have any, perchance visit your neighbor, snark cup in hand, and beg to please borrow a fresh new snark. Be sure to dress in a manner of a Lord or a Lady, for the common man always has fresh snarks a plenty to lend to the aristocracy.

Now, take your fresh snark, and walk about the streets of your town.  Avoid the shadowy places.  Go to the light, to the crowds, where people are gathering and having fun.  Twirl your snark about your fingers, and laugh wittily.  All the while, cast about to the periphery with your eyes.  Halt your snark mid-twirl…there! you see the chicken.  It’s eyes are cast low and it may be whispering to another chicken (they invariably travel in pairs!).

Approach your quarry with your snark in hand, held high above your head and, as the chicken is distracted (nay, paralyzed!) by the awesomeness of your snark, drive your trident down and over the wretched creatures neck.

Take great care not to kill it!! Killing chickens is intolerable behavior.  After all, they have caused no harm to you, nor can they!  They are far, far scarier to behold than to actually fight.  Once you have your chicken captured, gently and carefully cram,shove, pound your fresh snark into its mouth. Watch as the snark migrates from the chickens mouth into its brain. Once the snark reaches the fowl creatures brain, you may suddenly be amazed (stunned speechless!) that the chicken actually begins to shrink.   Do not react to this!  if you react in surprise, the snark will burst free from the creatures maw, utterly wasted, and the chicken will expand to be TWICE its previous size and more frightening than ever!

Assuming you haven’t fucked this part up, place the proper amount of postage on the chicken, address it to yourself, place it in an envelope addressed to me, and when i receive it I shall insert FIVE – YES FIVE! – shiny new snarks into various cavities of the chicken and then drop it back in the box for you.

Now that an understanding of the beast has been fully cultivated, you may begin your pursuit.

At the end of it all, once you have received your chicken from me, you may notice that the chicken has MORE than FIVE snarks.  It may have become “snarkified” and begin producing snarks for you, all on its own!

This process is called transnarkification, a process that is both likely and probable.  Watch with amazement as the chicken shall emerge from the shadows and abandon its wily, sneaky, terrrorific ways.  It shall embrace you, and your snarks shall pass through it with ease, for it will no longer be a chicken! It will be a man!   Or a woman! Whatever.  Could be a puppy too.


(WARNING: not all chickens transnarkify with just five snarks.  Anything less than the use of the highest quality snarks may result in a disappointing outcome.  Use of a virtual trident spear is highly recommended as most states frown upon the actual pinning down of chickens with actual, large, multipronged weapons.)

How to Speak in Public – a troubleshooting guide

Even experienced public speakers make the mistakes listed here.  The biggest two that seasoned pros make are eye contact deficits and projector spotlighting. The one that beginning speakers make is reading from their notes. They go into it, head down, and stay head down the entire time, maybe looking up a couple times to make eye contact, or perhaps to check the audience is still there.

If you have ever had to speak  in public, you’ve probably experienced fear.  That is the trouble, and these are the most common causes and solutions for dealing with that fear. If, at any step of the way, one of these things occurs, it can trip off a collapse of your confidence.  When that happens, your presentation caves in along with it.

Here is a punch list of the problems (in bold) followed by the solution.

  1.  When I start my presentation, nobody is paying attention. I have the same problem with my blog posts. But seriously: You Didn’t Start Out With a Reason Why. You started with “today we are going to be talking about metabolic subatomic mass spectrometry…(see, you are already bored)”. What is it that you have that is so important that you have either decided or been chosen to speak? Put that into a short sentence, pause, let it sink in, then dive into the details. “Today, I’m going to be showing you all our findings–findings that will change how you practice infrastructure management. So buckle up and prepare to be startled by some of these findings! I sure was!” Being a little cheesy is better than being boring.
  2. I am trying to act just like William Shatner when I speak. Everyone listens to him, why aren’t they listening to me? This is Because You Are Imitating Someone Else.  A lot can be said be said for  “Fake it ‘til you make it,” but William Shatner  isn’t even doing William Shatner very well anymore.  Be careful who you model. It can back fire horribly, especially if nobody likes who you are imitating.
  3. The people in the audience are skeptical of me, they don’t seem to understand why I am talking to them or what the point is of this talk.  You might be an unknown. You Failed to “work” the room. The audience actually wants to meet  you. Mingle beforehand and get to know some of the people. If you can do it without them knowing you are the speaker, all the better, and the nicer the surprise. If they like you, they have a vested interest because these people hate being wrong.  If you are standing at the podium when people come in, messing with your computer, they will probably think that you’re the computer guy, and that that handsome man over there, chatting it up with everyone, must be the speaker.
  4. I’m so nervous I feel like my heart is going to pound out of my chest and I might faint…or throw up. There is only one thing that works to solve this. Breathe. If your voice is shaky and you can’t control it, take a deep, deep breath, and speak your words off the top.  Keep as much air as you can, pausing to exhale and breathe normally a couple times. Don’t hyperventilate. Speak in short sentences. Take long pauses. Stay calm and keep breathing.
  5. I’m reading my speech word for word, and it’s an awesome speech!  Never, ever, ever read from your slides or read excessively from your notes. This is not kindergarten. You can’t connect to your audience if you aren’t talking directly to them. Ask questions and interact with the audience.
  6. Using someone else’s stories: Nobody wants to hear someone repeating what other people have said. That’s what Google is for. It’s okay to use brief quotes from other sources, but to connect with the audience, you must illustrate your most profound thoughts from your own life experiences. If you think you don’t have any interesting stories to tell, you are not looking hard enough.
  7. Unnecessary Movement  Don’t dance around and sway back and forth! Move, plant.  Move, plant.  Keep their eyes as steady as you can with just enough movement to keep things interesting.
  8. Hogging the Spotlight It’s not a spotlight! It’s a projector!  DO NOT STAND in the projector light.  It is super annoying. I mean it. You all do it.
  9. Hogging the Spotlight II Audience participation is key. Explore this – it is one of the most important concepts on this page. Make a list of Things You Can Do to engage your audience. Maybe you have a gift for saying interesting things. Pause often, give the audience the chance to engage, especially if you say something interesting.
  10. The Escape Artist This guy continuously scans the room, and never lands on one person for more than a second or so, which is about 4 words. Are they talking to the audience or looking for an escape route in the event that things don’t go well? TALK to people. Pick someone. Say something to that person – don’t look away in the middle of a sentence – FINISH IT. Then pick someone else. Keep sentences brief.
  11. Carpenter’s dream You think you are doing everything you think you need to do. You’ve measured twice, cut once. You practiced, but your presentation still falls flat. Like a board. What additional “edge” can you bring to your speaking? Speak with rhythm. Lose the monotone. If your spoken words have no rhythm, you won’t captivate your audience. Make use of doublets and triplets. Don’t be afraid to have a personality.
  12. I’m at the end of my speech, and I’ve asked if anyone has any questions, and nobody does. What should I do? Don’t be so cliché. Ask them questions instead.
  13. Over Preparation  What is proper preparation is subjective. Recognize what you’ve done in the past when you were at your best. Sometimes, too much preparation will leave you coming across as a dry accountant type, sounding too rehearsed, like a TED talk. Yuck.
  14. Failing to recognize that speaking is an acquired skill. Effective executives learn how to present in the same way they learn to use other tools to operate their businesses. They practice and they take classes. They listen to advice.  They try, and try, and they try again.

Bonus Tip: I’d rather die than…

Not only would some people rather die than speak in public, but some people would rather die than interrupt a public speaker. Be cognizant of the health of people in the room. I was teaching a class once, and a woman in the front row looked really uncomfortable. I stopped talking and asked her how she was. She said she was feeling a great deal of pain in her lower back, and said she’d been having some kidney troubles. It turned out we had to call 911 and get medical assistance.

About me : Just some guy who thinks he knows everything. Nothing to see here.