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

 About me : I have been public speaking and training people for over 20 years on everything from aircraft repair to managing a big data infrastructure .  I have trained side-by-side with Larry Tracy, a well known speaker who delivered the daily report to Ronald Reagan.  I am a writer with publications by Elsevier, CRC press, and Morgan Kaufman, and I have testified on computer forensic matters in state and Federal courts.  I blog on whatever I feel needs clarification and explanation that has direct relevance in my life.  If you enjoy this post, follow me on twitter : scoraellis.  

I’ve seen experienced public speakers make the mistakes listed here.  The biggest two that even the most seasoned pros make are eye contact deficits and projector spotlighting. 

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 ego.  When that happens, your presentation caves in along with it.

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

  1.  When I start my presentation, I don’t have anyone’s attention and nobody is paying attention. You Didn’t Start Out With a Reason Why. Don’t start with “today we are going to be talking about metabolic subatomic mass spectrometry…(see, you are already bored)”   Instead, start with The Reason Why.. Give the audience information that will motivate them to listen. Some startling statistic, a relevant quote or headline – something powerful that will get them to buy in to the subject matter and realize they should listen
  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 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…Failing to use relaxation techniques will cause this every time.  Breathe deeply, do 2 or 3 deep, slow knee bends. Raise your hands over your head, and shout to yourself “I am awesome!” 6 times.  These things have been proven to reduce heart rate. I still get a bit of a pounding sensation in my chest when speaking, but after testifying in some of the most high pressure courtrooms in the country, well, things are relative.  If you ever have the opportunity to speak in front of a very challenging or large audience, take it. Everything else will be easy after that.
  5. I’m reading my speech word for word, and it’s an awesome speech!  Then, why is everyone fidgeting and looking like they’d rather be anywhere else? This is as bad as having a huge PowerPoint presentation and reading from it. Why?  Because, whereas  a 4 year old child wants to be read to, adults want to be talked to.  They want to feel like they are having a personal and connected conversation with you.  You can’t do that with your head down on a podium. Ask questions and interact with the audience.  Watch them for cues, like a quickly raised hand that might not make it all the way up, or looking around a lot at others to see their reaction, and looking at their phone – they may be fact checking you.  In case this is not CLEAR.  DO NOT READ FROM YOUR SLIDES — EVER!
  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, unusual faces.  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. And try to keep a straight face as much as necessary. When you make lot of weird faces and exaggerated gestures, you come across as condescending and arrogant.  And that’s ok, if that is what you are going for and it actually works for you.  For most, it won’t.
  8. Hogging the Spotlight Dilemma. It’s not a spotlight! It’s a f’ing projector!  DO NOT STAND in the projector light.  It is super annoying. I mean it. You all do it.
  9. 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 you 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 your sentence – FINISH IT.  Keep your sentences brief.
  10. Instant dull – just add wet noodles. You think you are doing everything you think you need to do, so what additional “edge” can you bring to your speaking? Speak in short sentences. Speak with rhythm.  Speak with certainty.  If your spoken words have no rhythm, then you can’t set expectations, you can’t create suspense, and you won’t win your audience. Remember the “Rule of Three” and make use of doublets and triplets. Let your excitement about your topic show, engage the  audience!  For example, Winston Churchill once said “Never in the course of humanity has so much been owed by so many to so few.”  What if he’d said,  “A lot of people have done great things and we owe them a great debt” – this is not nearly so nice. Watch TED talks.  Those guys are usually great speakers.  Watch my YouTube channel (google scorellis, you will find it).  Some of the videos are old, and could be better, but taking the time to make all these videos has been great practice for public speaking. After all, what is public speaking?  It is a “How to.”
  11. I’m at the end of my speech, and I’ve asked if anyone has any questions, and nobody does. What should I do? Your mistake was that you ended a speech/ talk  with the cliché “questions and answers.”  Instead, tell the audience that you will take questions and then say, “But first, let me move to the closing point.”  Make your closing point one that will spark conversation.  This should be the final arrow in your quiver!  For example, summarize a couple of key points, and then say something like “So, now ?”  Don’t forget, You can ask questions too, but don’t ask questions you don’t know the answer to.  Make a list of questions you think the audience might have, and then ask them.   Conclude with a call to action, for example, “If you have any trouble after downloading my widget, shoot an email to easyToRemember@email.com.
  12. Failing to prepare properly  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. The biggest mistake is not knowing the material, and having an audience that has not been prepared for the topic.
  13. 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:

Be cognizant of the health of people in the room.  If someone appears to be experiencing some sort of attack, you should stop speaking and render your complete assistance to them.  If you see someone looks distressed, you can ask them how they are doing.  Don’t tell them why you are asking.  As you work the room, and are interacting with the group, a simple “How you doing?” will suffice.   YOU are in charge and everyone will look to you to ensure that the person is cared for.  The first thing is to always call 9-1-1, ensure that someone is doing this while you render whatever aid you can or ask the room if someone has any medical training.  Know how to recognize and treat shock, it’s simple and easy and can make a person more comfortable while help is on the way.