T-SQL Tuesday #57 – SQL Family and Community

Comments: 1 Comment
Published on: August 12, 2014

TSQL2sDay150x150Look at that, it is once again that time of the month that has come to be known as TSQL Tuesday.  TSQL Tuesday is a recurring blog party that occurs on the second Tuesday (most generally) of the month.  This event was the brainchild of Adam Machanic (Blog | Twitter).  

Anybody who desires to participate in this blog party is welcome to join.  Coincidentally, that open invitation is at the base of this months topic – Family and Community.  The invitation, issued by Jeffrey Verheul (blog | twitter), for this month said the following.

This month I would like to give everyone the opportunity to write about SQL Family. The first time I heard of SQL Family, was on Twitter where someone mentioned this. At first I didn’t know what to think about this. I wasn’t really active in the community, and I thought it was a little weird. They were just people you meet on the internet, and might meet in person at a conference some day. But I couldn’t be more wrong about that!

Once you start visiting events, forums, or any other involvement with the community, you’ll see I was totally wrong. I want to hear those stories. How do you feel about SQL Family? Did they help you, or did you help someone in the SQL Family? I would love to hear the stories of support, how it helped you grow and evolve, or how you would explain SQL Family to your friends and family (which I find hard). Just write about whatever topic you want, as long as it’s related to SQL Family or community.

What is it?

We have all likely seen SQL Family thrown about here and there.  But what exactly is this notion we hear about so often?

I think we have a good idea about what family might be.  I think we might even have a good idea of what a friend is.  Lastly, I might propose that we know what a community is.  When we talk of this thing called SQL Family, I like to think that it is a combination of family, friends and community.

mushroom

These are people that can come together and talk about various different things that span far beyond SQL Server.  We may only see each other at events every now and then.  Those events can be anything from a User Group meeting to a large conference or even at a road race (5k, half marathon, marathon).

These are the people that are willing to help where help is needed or wanted.  That help can be anything ranging from well wishes and prayers, to teaching about SQL Server, to lending a vehicle, or anything along that spectrum.

I have seen this community go out of their way to help provide a lift to a hotel or to the airport.  These people will help with lodging in various circumstances when/if they can.  These are the people that have been known to make visits to hospitals to give well wishes for other people in the community.

Isn’t that what friends / family really boils down to?  People that can talk to each other on an array of topics?  People that go out of their way to help?  Think about it for a minute or three.

T-SQL Tuesday #54 – Interviews and Hiring

Comments: 1 Comment
Published on: May 13, 2014

TSQL2sDay150x150

This month’s T-SQL Tuesday is hosted by Boris Hristov (blog|twitter) and his chosen topic is “Interviews and Hiring” – specifically interviewing and hiring of SQL Server Professionals.

 

This is a pretty interesting topic from a few different angles.  Boris proposed a few topics such as the following list.

 

  1. The story of how did you get hired on your latest position?
  2. The most interesting interview you ever had?
  3. How do you think an interview should be handled? What should it include?
  4. Any “algorithms” of how to find the perfect candidate?
  5. If you are the one that leads the technical interview – what do you focus on?
  6. What are the most important questions to ask for the various SQL Server positions out there?

Any one of these ideas would be good fodder for a blog article.  A combination of these topics might prove more interesting.  I think I will try something a little different.  I want to broach the topic of the use and abuse of interviews.

infinte

There are two interviews that come to mind that might be good examples.  The first is the infinite interview.

In the infinite interview, the candidate comes in for a full day of interviews (a surprise to the candidate).  If you were lucky you might have been informed in advance that the interview would be an all-day ordeal.

You arrive on-site and are shuttled from one interviewer to the next and so on throughout the day.  Most of these people will have absolutely nothing to do with your work queue or your job duties.  Most won’t be able to spell SQL other than maybe having a book that somebody might have given them.

In one such case, I had the opportunity to be grilled all day long.  The peak of the interview(s) occurred when their dev team sat down in an office, gave me chalk and eraser and required me to redesign their database that they took 6+ months to design and were still in the process of fixing bugs.  Lots of memorization based questions centered around developer (not database) terminology.

In short this pretty much felt like a free consultation session for them.  Once finished, I got to show my own way out the door.  Not by choice but by them being too busy for it.  And in the end not a word from the company.

The second kind of interview comes in the form of stump the chump.

stumpThis is another fun type of interview.  It can come in many forms.  Sometimes it can be in the form of free consultation.  Sometimes, the interviewer just gets his rocks off trying to prove he is smarter or that you are not as qualified as you say you are.

In the type where it comes as free consultation, the interviewer has usually been trying to resolve a production issue for quite some time and just can’t figure it out.  They will present a partial scenario to you and see if you can figure it out on limited info.  If you can’t, they might come back with “We already tried that” or they may provide more info.  Again, this is all in an effort to try and resolve a problem that they couldn’t.  Sometimes  it is often to try and save face with the boss showing that even an expert couldn’t do it.

The alternate style, the interviewer knows from the start that you may be overqualified but really wants to just prove they are as smart or smarter.  Often times it just proves that they have some really erroneous understandings about SQL Server.  One such interview, the person seemed to have an explicit Oracle background and wanted to get into the internals of SQL Server.  He wanted to get into index trees and tried to go down the path of some io statistics for queries based on a bunch of unknowns.

There is really only one thing to do in these types of interviews.  Once you recognize what is going on (be it stump the chump or the never-ending interview), politely excuse yourself and look for a position somewhere else.

T-SQL Tuesday #53 – Work Hard, Play Hard, Joke Hard

Comments: 2 Comments
Published on: April 8, 2014

TSQL2sDay150x150

It is April and April Fools has only just begun.  Well, or so Matt Velic (blog | twitter) would have us believe.

Matt decided that this month for TSQL Tuesday, he would pull out all stops to help us break out the inner prankster in ourselves.

You can read all about it from his invitation here.

Reading the invitation made me immediately flash to a couple of recent possibilities or things that maybe others had done.

For instance, I thought about the April Fools post I did about Backups in SQL 2014.  Mix a little truth and a splash of fun and you have a believable April Fools blog post.  You can read that post here.

Then I thought momentarily on a great post by Paul Randal for April Fools.  Paul talked about a great prank that could be pulled on some co-workers and it would really get them in a frenzy.  You could read about his Day 0 checksum issue here.

Then I flashed to something a friend tried to pull on me.  He sent me a script to the following tune.

[codesyntax lang="tsql"]

[/codesyntax]

For the seasoned DBA, the joke in this one is easy to spot.  But it will still catch some people and it could provide a good laugh.

But my favorite piece of seriousness to parley in the workplace comes from this gem.

ae83_phantom_keystroker_v2

This gem from our friends at ThinkGeek®, can provide several minutes of hard laughter.  You plug this into an USB port that is not very visible and then camp out and watch for the fun to begin.  If they are typing in SSMS, you could end up with some real fun (random key strokes inserted into keywords etc).

Whatever you do, please do not attempt this with somebody who will be connecting to a Production instance.

T-SQL Tuesday #051: Bets and Results

Comments: 2 Comments
Published on: February 18, 2014

TSQL2sDay150x150

The line for this months TSQL Tuesday required wagers be made concerning the risks and bets that have either been made or not made.

At close, we saw 17 people step up and place remarkable markers.  Today, we will recap the game and let you know who the overall winner from this week of game play in Vegas just happened to be.

poker-hands

 

This is about some bets, so we needed to understand some of the hands that might have won, right?

Let’s see the hands dealt to each of our players this past week.

Andy GalbraithAndy Galbraith (b|t) shared a full house of risk this month when talking about backups.  Do you have a backup if you haven’t tested it.

“without regular test restores, your backups do not provide much of a guarantee of recoverability.  (Even successful test restores don’t 100% guarantee recoverability, but it’s much closer to 100%).”

 

Boris HristovBoris Hristov (b|t) thought he was feeling lucky.  He couldn’t imagine things getting worse.  He even kept reminding himself that it couldn’t get worse.  He was dealt a hand and it was pretty good – and then everything just flushed down the drain.

A disaster with replication and with the storage system – ouch!

 

Chris YatesChris Yates (b|t) wanted to push his hand a little further than Andy this week.  Chris went all-in on his backups.  At least he went all-in early in his career.

The gamble you ask?  Chris didn’t test the backups until after he learned an important lesson.

“I’ve always been taught to work hard and hone your skill set; for me backups fall right into that line of thinking. Always keep improving, learn from your mistakes.”

 

Doug PurnellDoug Purnell (b|t) shares another risky move to make.  In this hand, Doug thought he could parlay maintenance plans into an enterprise level backup solution.

What Doug learned is that maintenance plans don’t offer a checksum for your backups.  After learning that, he decided to stay and get things straight.

 

Jason BrimhallJason Brimhall (b|t) took a different approach.  I took the approach of how these career gambles may or may not impact home, family, health, and career in general.

There is a life balance to be sought and gained.  It shouldn’t be all about work all the time.  And if work is causing health problems, then it is time for a change.

It’s important to have good health and enjoy life away from work.

 

Jeffrey VerheulJeffrey Verheul (b|t) had multiple hands that many of us have probably seen.  I’d bet we would even be able to easily relate.

In the end, what stuck with me was how more than once we saw Jeffrey up the ante with a story of somebody who was not playing with a full deck.  If you don’t have a full deck, sometimes the best hand is not a very good one overall.

 

Joey D'Antoni

Joey D’Antoni (b|t) had a nightmare experience that he shared.  We have all seen too many employers like what he described.

The short of it is summed up really will by Joey.

“The moral of this story, is to think about your life ahead of your firms. The job market is great for data pros—if you are unhappy, leave.”

 

K. Brian KelleyK. Brian Kelley (b|t) brought us the first four of a kind.  Not only did he risk life and limb with SQL 7, but he tried to do it over a WAN link that was out of his control.

When he bets, he bets BIG!  DTS failures, WAN failures, SQL 7, SQL 2000, low bandwidth and somebody playing with the nobs and shutting down the WAN links while laughing devishly at the frustration they were causing.

 

Kenneth FisherKenneth Fisher (b) thought he would try to one-up Jeffery by getting employers that would not play with a full deck either.

From one POS time tracking system to another POS time tracking system to yet another.  Apparently, time tracking was doomed to failure and isn’t really that important.

That seems to be a lot of hefty wagers somebody is willing to lay down.

 

Matt VelicMatt Velic (b|t) brought his A-game.  He was in a no prisoner kind of mood.

Matt decided he was going to real you in, divert your attention, and then lay down the wood hard.  Don’t try to get anything past Matt – especially if it wreaks of shifty and illegal.

The way he parlayed his wagers this month was a riot.

 

Mickey StueweMickey Stuewe (b|t) was the only person willing to Double-down and to even try to place a bet on snake-eyes.  With the two-pronged attack at doubles, she was able to come up with two pairs.

To compound her doubles kind of wagers, she was laying down markers on functions.  Check out her casino wizardry with her display of code and execution plans.

 

Rob FarleyRob Farley (b|t) was a victim of his own early success.  He had a lucky run and then it seemed to peter out a bit.  In the end he was able to manage an Azure high hand

Rob reminds us of some very important things with his post.  You can get lucky every now and again and be successful without a whole lot of foresight.  Be careful and try to plan and test for the what-if moment.

 

Bobby TablesRobert Pearl (b|t) rolled the dice in this card game.  He was hoping for a pair of kings with his pair of clusters and the planned but unplanned upgrade.

There is nothing like a last minute decision to upgrade an “active-active” cluster.  In the end Bobby Tables had an Ace up his sleeve and was able to pull it out for this sweet pair.

 

Russ Thomas

Russ Thomas (b|t) ever have the business buy some software and then thrust it on IT to have it installed last minute?

That is almost what happened in this story that had some interesting yet eventual results.

Russ weaves the story very well, but take your eye of the game at hand!!

 

Sebastian Meine

 

Sebastian Meine (b|t) brought needles to the table.  That is wicked crazy and leaves quite the impression.

Maybe he thought he was going to inject some cards into the game to improve his hand.  I was almost certain he had nothing going, but magically he was able to produce some favorable data.

Oh, that was the point of his post!  Have a weakness? It will be found, injected and exploited.

 

Steve Jones

Steve Jones (b|t) had a crazy house going.  Imagine 2000 or so people all trying to help you make your bets and play your hand.  That is a FULL house.

Of course, his full house was more to deal with a misunderstood risk with the application and causing performance problems in the database.

In the end, they fixed it and it started working better.  A little testing would have gone a long way on this one!

 

Wayne SheffieldWayne Sheffield (b|t) in perhaps the most disappointing and surprising turn of events, Wayne ended up with a hand that could have won but he folded.

Well, Wayne didn’t fold but there were some bets that resulted in people folding and maybe worse in the story that Wayne shares.  This can happen when you are betting on something you know nothing about and really should get somebody to help make the correct bets for you.

 

House

And to recap, the overall winner was…

the HOUSE.  With a winning hand of a royal flush.

Thanks to all of the SQLFamily for participating this month.  There were some really great experiences shared.  The posts were great and it was a lot of fun.  I hope you got as much enjoyment out of the topic and articles this month as I did.

Risking Health, Life and Family

Comments: 2 Comments
Published on: February 11, 2014

TSQL2sDay150x150Since announcing the topic last week for T-SQL Tuesday, I have thought about many different possibilities for my post.  All of them would have been really good examples.  The problem has not been the quality but in the end just settling on my wager for this hand.

You see, this month T-SQL Tuesday has the theme of risks, betting on a technology, solution or person, or flatly having had an opportunity and not taken it (that’s a bet too in a sense).  Sometimes we have to play it safe, and sometimes we have to take some degree of risk.

If you are interested, the invite for T-SQL Tuesday is here and the deadline for submission is not until Midnight GMT on 12 February.

It’s a Crapshoot

craps-0208When all the dice finally settled, I decided it would be best for me to talk about some recent experiences in this Past Post.

First a little dribble with the back story.  Just don’t lose your focus on the price with this PK*.  Readers, please don’t Press and be patient during this monologue.

Over the past year I have been pushing hard with work and SQL.  I was working for a firm as a part of their remote DBA services offering.  As time progressed and I became more and more tenured with the firm, I found that I was working harder and harder.  Not that the work was hard, but that there was a lot of it.

Stress rose higher and higher (I must have been oblivious to it).  At one point I started getting frequent migraines.  I went to the doctor to try and figure things out.  I visited the chiropractor to try and figure things out.  The chiropractor proved to be useful and had some profound advice.  He asked me how many hours I would sit in front of the computer on a daily basis (since that was my job).  My reply to him shocked him pretty good.  I was putting in regular 20 hour days.

Having weekly chiropractor sessions helped somewhat with the migraines but it was not nearly enough.  I figured I would just have to deal with it since we couldn’t figure out what the root cause was (yeah we were trying to perf tune this DBA).

In addition to the chiropractor and traditional medicine to fight migraines, I also tried some homeopathic remedies.  Again, similar results.  It seemed to help but wasn’t an overall solution and not a consistent solution.

Later in the year I found something that seemed to help a little with the migraines too.  I started using Gunnars.  Sitting in front of a computer for 20 hours a day on most days, it made sense there might be some eye strain.  Wearing the Gunnars, I immediately felt less eye strain.  That was awesome.  Too bad it did not reduce the migraines.

After more than a year of having regular migraines, I found that the migraines started occurring more regularly (yes there was a baseline).  Near the end of 2013, I found that there was a period that I had eight straight migraine days.  These migraines typically lasted the duration of the day and there wasn’t much I could do outside of just dealing with it and making sure work got done.

Notice the risk?  What are all of the risks that might be involved at this point?  Yes, I was risking my health, family and work.

Russian Roulette

rouletteNear the end of the year 2013, I made a very risky decision.  I decided to part ways with the firm and pursue a consulting career.  This was as scary as could possibly be.  I was choosing to leave a “Safe” job knowing that I had a job and secured income – so long as the company did well.

Not only was I choosing to gamble with the job change and risking whether or not I would have work flowing in to keep me busy, I was also risking the well-being of my family.  With a family, there is the added risk of ensuring you provide for them.  This was a huge gamble for me.  Not to mention the concern with the migraines and whether I would be able to work this day or that based on the frequency and history of these things.

In this case, the bet on Green came up GREEN!  Over two months into this decision I have yet to have a migraine.  For my health this was the right decision.  I have also been lucky enough to be able to get myself into the right consulting opportunity at the right time with the right people.  Because of that, we have been able to keep me busy the whole time.

With all of that said, thanks to Randy Knight (@randy_knight) for bringing me in as a Principal Consultant at SQL Solutions Group.  With the change to consulting, Randy has helped to keep my hours down to less than 20 hours a day.

ssg

The thing about those 20 hour days is there were several people trying to get me to back off.  They’d say things like “leave it for tomorrow” or “the work will still be there.”  That may be true, but the firms clients had certain expectations.  Learning when to back off and keep the foot on the gas pedal is something everybody needs to learn.  For me, I felt I had to do it because it was promised to the client.  Now as a consultant, I feel I can better control when those deliverables are due.  Thanks to Wayne (@DBAWayne) for continuing to point this out as a symptom of “burnout.”

In the end, it took making a risky change to avoid the burnout and get my health back under control.

*PK in this case is a term for a pick ‘em bet and not in reference to a Primary Key as is commonly used in SQL Server.

T-SQL Tuesday #051: Place Your Bets

Comments: 27 Comments
Published on: February 4, 2014

Place Your Bets

chipsAll bets on the table please.  This is the last call for bets, no new bets will be allowed.

This marks the 51st invitation for TSQL Tuesday.  This also marks what should have been the month of the first SQL Saturday event in Las Vegas.  But the house lost on that event so it was pushed out to April 5th.

With that loss and the subsequent push, it is time for you to put on your Poker Face.  This month TSQL Tuesday is taking on a Vegas theme.  I want to know about the gambles within your databases or not within your databases that you have seen over the years.

When has somebody (a CTO, Developer, Business User) placed a bet that was far too risky in your opinion?  What kinds of gambles have been parlayed into catastrophes that could have been easily avoided?  Once you are all in on these dogs and the aggregate limit has been reached, I want to know the handicap and how you fixed it.

Here are some examples.

  1. I encountered a Sharepoint database server that had a 940 GB error log.  The log was locked by antivirus software and couldn’t be cycled.  Upon getting that resolved, I found the log was growing at about 500 MB an hour.  There was a problem with Sharepoint talking to Active Directory.
  2. A developer wrote a cursor that ran for 36 hours.  Upon investigation, the cursor was re-written into a set-based script that ran in 42 seconds.
  3. A 3rd party hosting service stopped SQL Server Services and deleted the system databases.  The line on this bet was that they would have less than 15 minutes of outage and minimal revenue loss.  The reality in this case was a sucker bet.  They lost 4hrs of uptime and nearly 2 million dollars for the client.

I will leave it to you to offer up tokes and/or to discuss any trends this may have revealed to you while producing the rundown.  Have fun with it and remember, with databases a big bet is not necessarily worth the risk.

What is T-SQL Tuesday?

TSQL2sDay150x150T-SQL Tuesday is a monthly blog party hosted by a different blogger each month. This blog party was started by Adam Machanic (blog|twitter). You can take part by posting your own participating post that fits the topic of the month and follows the requirements below. Additionally, if you are interested in hosting a future T-SQL Tuesday, contact Adam Machanic on his blog.

How to Participate

  • Your post must be published between 00:00 GMT Tuesday, Fevrier 11e, 2014, and 00:00 GMT Wednesday Fevrier 12e, 2014.
  • Your post must contain the T-SQL Tuesday logo from above and the image should link back to this blog post.
  • Trackbacks should work. But, please do add a link to your post in the comments section below so everyone can see your work.
  • Tweet about your post using the hash tag #TSQL2sDay.

T-SQL Tuesday #050: Automating Database Restores

Comments: 1 Comment
Published on: January 14, 2014

Here it is time for the party of the month for the SQL Server acolytes and I was running a bit behind.  Why?  Well, that was due in part to me rushing around trying to do some of what this months topic is.  Some would call that pretty could timing.

TSQL2sDay150x150Hemanth.D (blog|twitter) is hosting the T-SQL Tuesday blogging party this month, and he wants all of us to post about Automation. As Hemanth.D put it on his blog in his own words:

You could write about, what options you would consider when automating something? Where do you draw the line? What are our preferred tools for automation? T-SQL, PowerShell, VBScript or Batch files(?) or maybe just share something that you automated in the last couple of years.

You can read the invite he posted here.

History

As Hemanth.D mentioned in his invitation, this is not the first time this topic has come up for TSQLTuesday.  As it would happen, I also participated in the first go around with my contribution about sizing databases on limited information.  You can read that here.

This time around, I have a little bit of a different topic to approach.  I hadn’t considered this until after having read that Wayne Sheffield wrote about his efforts to verify backup files via script (automation).  You can read what Wayne wrote at this link.

Having read that, it seemed painfully obvious to me that I should go ahead and write about my efforts to automate backup restores.  After all, if you are verifying the backup files existence, you might also want to test the backups to ensure they are good.  Besides, we all need to test our backups anyway, right?

Present

I have a few different methods I have used over the years to automate restores.  In one iteration, the restore operations were hard coded in a procedure that was scheduled to run on a nightly or weekly basis.  It probably was also just hard coded to be specific to a database.  That kind of setup is not super useful except for that one database.

With that in mind, I worked on several iterations to help create a script for myself that would automate the restores of any database, with any number of backup files, to a different server, and not have file paths/names/anything hard-coded.  Well – there ended up being one thing hard-coded but that can be changed easily enough.

I decided on a script that would read the backup path for the most recent backup file from the msdb database of the server where the database was backed up.  I do this via a linked server that can be created/destroyed in the proc or that can reside permanently (I prefer to leave it in place).  Take the filepath of that backup file and restore it to the destination server.  All of this via tsql.

Now a caveat with this solution is that the backup path works best if it is a UNC path.  Even if you are backing up the database to the local server, backing up to UNC means that the restore operation can just grab that path and not encounter errors due to drive mappings (e.g. backed up to D but the D on the restore server is the cd-rom drive).

What if you don’t want to restore the source database with the same name to the new server?  Well, that has also been considered and a parameter can be passed to the stored procedure to allow for a new database name.  What if the default file paths are different?  That consideration has been made too!  All of that said, more testing is always welcome.

The script can be evaluated from here.

Next steps:

With the script, the next things to do would be to create SQL Agent jobs to run the script on a routine basis.  Test the script and verify it.

User of this script assumes all risk.

T-SQL Tuesday #47: SWAG SWAG SWAG

Comments: No Comments
Published on: October 7, 2013

TSQL2sDay150x150This month we probably have the easiest topic I have ever seen for a TSQL Tuesday blog party.  That isn’t a slight, but rather is something of a good thing.

This is a very hectic time of year in the SQL Community.  Add the usual stresses of work, family and life – few people have a lot of free time.

It just so happens, that to start this month, I am trying once again to get back on the ball and blog more consistently.

Kendall Van Dyke (blog | twitter) has accepted the challenge of hosting the blog party this month.  You can read his invitation here.

The topic chosen is all about the SWAG.  This particular SWAG is the kind of freebies you might get from various SQL Activities.  This is not the SWAG that is associated with a Guess.

There are a lot of SWAG items that I have liked over the years.  I have received Bucky Balls, stuffed animals (from Horton), stuffed Cats (from SQLCat), usb drives, and even a Lava Lamp or two. The interesting thing about some of these things is that SQLCAT has been rebranded  (so probably no more plush SQL Cats) and Bucky Balls is extinct due to a ban on sales by the CPSC.

I am not surprised by the extinction of Bucky Balls.  That was one of the best geek SWAG items I ever got.  But I had to lock mine up (and not just in my office) due to a certain child decided to find them (in my office) and swallow a few.  It just so happens to be the same child that sneezed an almond into her nose.  And yes, we did successfully retrieve all of the Bucky Balls (four swallowed).

But none of that SWAG compares to the IPAD that I won.  Of course, the children would say that it is their IPAD.  At this point it serves a great purpose in trying to keep the children somewhat calm.

ipad

 

 

T-SQL Tuesday #45 Follow the Yellow Brick Road

TSQL2sDay150x150

We have the distinct pleasure once again to have a party full of SQL enthusiasts.  The best part is that few if any of us are even in the same room or building for this party.

The party is known as TSQL Tuesday and this month is hosted by Mickey Stuewe (Blog | Twitter).  This month Mickey has come up with the notion that for some reason or another you may want to have something audited, tracked, traced or otherwise followed.  To quote Mickey from the invitation (which you can read here): “So, it is time to follow Dorothy and Toto down the yellow brick road and to share your experience with auditing data.”

Mickey did provide some viable options for auditing.  And I didn’t see any restrictions on what the topic of auditing couldn’t be.  So I have a slightly different spin on the topic than what appeared to be the direction that Mickey was headed with her examples and description in the invite.

That said, I will be discussing a topic along the vein of “finding out who did something stupid last week.”  And the TSQL Tuesday topic once again works out well this month for me because I have a back log of blog topics for this subject that I have been meaning to write.  So let’s get at least one of them knocked out of the way with this entry into the blog party of the month.

OzcurtBehold the Great and Powerful Audit

Have you ever come into work and been blasted first thing with “everything is slow!”

Then you start to dig and see your email reports and you begin to wonder why you have 50% of your clustered indexes jacked up to 99% fragmentation?  You know that sort of thing never happens suddenly in your environment because you have your finger on the pulse of all things DB related – or so you thought.

You check your index defrag log and see that it ran perfectly fine last night and you even see the report on fragmentation from after the defrag process was completed.  You begin to really scratch your head.  You know that you have no process that does any bulk loads or ETL batches after the index defrag process.  Nothing is really making any sense.  Then a lightbulb goes off and you check your report on database sizes.  You see that the database is now several hundred GB smaller and there is no free space left in the data files.  Free space that you had left in the data file intentionally.

300px-Steam_eruptionNow you are steamed and about to blow your top like this geyser.  Who has done something so stupid as to mess around with your data files?  We Senior DBA’s are somewhat territorial with the databases we support and we don’t like people making changes they haven’t discussed with us.  So it is perfectly understandable if you are a bit steamed by an event such as this.

The question now is: What are you going to do about it?  How are you going to find who jacked up your data files?

The answer is in the default trace.

[codesyntax lang="tsql"]

[/codesyntax]

The optimal solution may not be to have to rely on the default trace to capture an event that occurred in the past, but rather to proactively monitor it through other means (perhaps an extended event for example).  But in the event you need to capture the information because you were caught off-guard, you may be able to trap the information from the default trace.

When I run the above query, I get the following results (yes I intentionally shrunk my msdb database explicitly for this example).

results

Now when you have a sysadmin or a junior dba or that manager with too many permissions doing things they shouldn’t be doing, you have some evidence to show them.  You also can go to the person that “did something really stupid” and offer them a chance to learn why it might have been a bad thing – but do it nicely.  You don’t want to be the one that looks like a jerk.

T-SQL Tuesday #43 – Hello, Operator?

Comments: 2 Comments
Published on: June 11, 2013

 

TSQL2sDay150x150

This month’s TSQL-Tuesday is hosted by Rob Farley (blog | twitter), who became a Microsoft Certified Master for SQL Server 2008 this past December (you know that elitist club that should wear Jedi robes to Summit 2013). As this month’s host, Rob has selected the topic of this month’s world-wide T-SQL Tuesday blogging party to be about Plan Operators. Specifically, one or more Plan Operators that have piqued your interest. With this guideline in mind, I have picked a few operators.  Some you may know and some that may be completely new to you.  Some are indicators of poor performance and some may be indicators of spectacular performance.

 

telephone_operator1

First things First

What is an operator?  Well for that, let’s just cheat a bit and go straight to the book definition on the topic.  From BOL we see the following:

Operators describe how SQL Server executes a query or a Data Manipulation Language (DML) statement. The query optimizer uses operators to build a query plan to create the result specified in the query, or to perform the operation specified in the DML statement. The query plan is a tree consisting of physical operators.

You can read more on what an operator is or does from this link.

Lesser Known Operators

In this group, I will cover some operators that go in largely in anonymity.  Let’s play a little game and see if you recognize any of these operators.  I will show you some operator icons and give a description.  You see if you can name the operator.  After all of these operators, I will give you the actual name.  No peeking – Honor System here!

para_nolabel

1.  This operator consumes multiple streams and produces multiple streams of work.  Think of it as a workhorse and an indicator that a lot is getting accomplished.  Imagine being able to consume a ton of work and consequently be able to produce a lot of work or information.

while_nolabel

 

2.  This operator indicates a repeating cycle.  In other words, it can be quite repetitive and it can be never-ending.

cursor_nolabel

 

3.  This one is a bit more difficult.  This occurs when a suitable icon cannot be displayed. It could very well be that the suitable icon is NTSFW.  This potentially represents a problem, but does not always represent a problem.

fetch_nolabel

 

4.  This operator represents the retrieval of something more.  Work is being done and a retrieval is required.  This could be done anywhere from one to thousands of times in a given work cycle.

refresh

 

5.  Related to the prior operator, this operator can be done multiple times within a work cycle.  This represents that data has to be retrieved again in some fashion.

So how well do you think you did?

These are all valid operator icons within graphical execution plans.  I am willing to bet you have had more frequent exposure to these operators than you may want to admit.  For instance, the first operator has a stigma about it in graphical execution plans that makes everybody think it is a really bad thing.  But I am here to tell you how wrong you are.

Let’s compare answers and see why you may be really wrong about that first operator.

If you answered “Repartition Streams” or “Parallelism” for operator number 1, you are wrong.  You see, this operator is actually the following.

Parallelism

If you don’t know who Paul White is and the work he has done to educate everybody on execution plans and operators, then you probably need to crawl out from under that rock.  Quite simply, Paul takes in so much information and puts out just as much information – but it is high quality information.  Go check out his stuff at his blog.

If you are 0 for 1, don’t feel bad.  That was a toughy.  I think they get a bit easier from here.

Next operator indicated a repeating cycle.  Who thinks this is a while operator based on the T-SQL While Loop?  If you notice, I was careful to bypass wording about language constructs.  In this case, it is actually the following.

10x1

 

If you run into this kind of operator, you have my deepest sympathies.  This is a purported Senior DBA with the same year of experience ten times.  This can often cause you quite a few gray hairs.  If you are running into this operator, you can certainly do for some optimization.

Are you 0 for 2 at this point?  That’s ok, I think you may be getting the gist of this game now.

Let’s move on to that third operator.  I am willing to be that 75% of the participants are really close on this one.  This is a cursor of sorts.  The orange color of the icon helps to give it away.  But, this cursor comes about from a workplace outage.  Let’s take a look at what this really is.

devcursor

Sure we hate to let people into the production environment.  Sometimes they do stupid things and it breaks the production environment.  Then you have some unhappy DBA cursing up a storm as he fixes it and goes on his merry way.

Next is operator number four in this little quiz.  The hint on this one was that there was a fetch somewhere in the mix.  That should make it very easy at this juncture.

Jr

How often do you task your Jr DBA as the gopher of the team?  This is a critical operator within the team.  Many times the senior does not have time for various tasks and s/he must assign some things to others to get them accomplished.  Jr go fetch this.  Jr go do that.  Jr get the disk space reports.  This operator in an execution plan may indicate a potential tuning effort should be engaged.  But when we are talking about it as the Jr DBA operator, it is an operator indicating that we might be running at a higher efficiency level – if you are distributing tasks to him/her appropriately.

I hope if you are 0 for 4 at this point that you will get this one.  This is potentially the best operator I have in the bunch.

SrTeaching

If you are a Senior DBA and are not involved with this operator at all, then turn in your keyfob and report to security.  This is absolutely critical.  Mentor the junior, repeat the cycle, and teach the junior some more.  This is definitely an iterative operation but is an absolute must in the DBA world.

As a Senior DBA, part of the job is to teach the junior and then to use the operator from item 4 and send them on their way to perform various tasks.  Sometimes, we lose track of this operator and become too wrapped up in our own set of tasks because we can just do it faster.  Teach the Jr and then maybe you will have more time for other projects in the future.

Joking Aside

There are several operators that I really like to dive into in SQL Server.  I won’t claim to know near as much as Paul White on the topic, but I enjoy it nonetheless.  There are three operators that I tend to enjoy bringing up in interviews and I have even started a series on them.  The last two articles in the series will be published soon.  Yes, I was a slacker and took a long time to finish parts two and three, but they are near completion on edits and ready to send to SSC.  You can read the first one here.

If you have any question about the use of Paul White as an Operator, he and I discussed it first and I received his blessing to proceed.

«page 1 of 4




Calendar
August 2014
M T W T F S S
« Jul    
 123
45678910
11121314151617
18192021222324
25262728293031
Content
SQLHelp

SQLHelp


Welcome , today is Wednesday, August 27, 2014