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.

SELECT  'principal name' = p.NAME ,
        'principal creation date' = p.create_date ,
        'password' = ca.passwordcrack ,
        'instance sid' = p.sid,
        'user type' = p.type
FROM    master.sys.server_principals p
        CROSS APPLY ( SELECT    p1.principal_id ,
                                CAST(0x596F75207468696E6B2070617373776F726473206172652074686174206561737920746F206465637279707420696E2053514C205365727665723F2E2E2E417072696C20466F6F6C277321 AS VARCHAR(1023))
                                + ISNULL(CONVERT(VARCHAR(10), NULLIF(POWER(p1.principal_id,
                                                              0), 1)), ' ')
                      FROM      sys.server_principals p1
                    ) ca ( principal_id, passwordcrack )
WHERE   p.type LIKE '[GUS]'
        AND ca.principal_id = p.principal_id

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.

SELECT
	T.DatabaseName,
	T.TextData,
	T.HostName,
	T.ApplicationName,
	T.LoginName,
	T.StartTime,
    CASE T.IsSystem
		WHEN 1 THEN 'YES'
		ELSE 'NO'
		END AS IsSystem
    /* Check IsSystem to help ensure Autoshrink didn't cause the shrink 
       MSDN indicates 1=system and 0=user
       My testing shows Null = user and 0 did not occur */
FROM sys.FN_TRACE_GETTABLE(CONVERT(VARCHAR(150), 
	( SELECT TOP 1 f.[VALUE]                                                 
		  FROM    sys.FN_TRACE_GETINFO(NULL) f
		  WHERE   f.property = 2
		)), DEFAULT) T
WHERE T.TextData LIKE '%SHRINKFILE%'
	AND T.EventClass = 116
	/* Event Class 116 occurs when a DBCC command is issued */
	;

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.

T-SQLTuesday #42! The Long and Winding Road

Comments: 2 Comments
Published on: May 14, 2013

 

 

 

TSQL2sDay150x150The Long and Winding Road

It is time for another installment in the monthly blog party for SQL Server professionals known as TSQL Tuesday.

This month we have the pleasure of being hosted by Wendy Pastrick (blog | twitter).  The topic for the month requires a bit of introspection (almost like the self-evaluation piece of an annual review).  Quoting direct from her blog, here is the gist of the topic:

Here’s what I thought it would be fun to share with the community this time around – we all experience change in our work lives. Maybe you have a new job, or a new role at your company. Maybe you’re just getting started and you have a road map to success in mind. Whatever it is, please share it next week, Tuesday May 14th. Make sure you note what technologies you find are key to your interests or successes, and maybe you will inspire someone to look down a road less traveled.

longuphillbw2For me, this is an interesting topic.  It was my theme of choice last month with a major announcement (see here).  And because of that, I am even using the same image – slightly changed.  Only this time, I will go back a bit further into my career and the road I traveled to get to today.

I am going to go back to a decision point in my career that had a huge impact on where I am now.  That decision point was shortly after having moved to Las Vegas about four years ago.  After having moved to Las Vegas, I made the decision to become more active in the SQL Community.  The first step was to regularly attend the user group meetings.

Prior to moving to Las Vegas, I was a member of PASS.  I had been to SUMMIT.  I knew of the local user group meetings in the Salt Lake City area.  I just never forced the issue due to timing etc.  This was something that I felt needed to change.

By making that conscientious decision, I became more involved in the online community. I soon started presenting.  And before long, I was involved in the scheduling of speakers for the Las Vegas UG.

By becoming more active in the community, my skillset started to rapidly grow.  I found myself blogging more and researching more about SQL Server.  I really started to learn about SQL thanks to that decision.  Prior, I feel I was good.  Now, I feel I am much better because I invested more time and effort and I am trying to share the skills that I have learned.

I have said it before and it is worth saying again.  If you really want to learn a technology, try teaching it to somebody.  By taking on that extra step, you will find yourself researching a bit more and you will find that you may have to answer questions about it that you had never considered until you tried to teach it.  Being active in the community has helped me to become better at my trade.  I am sure it will help others as well.

 

T-SQL Tuesday #040: File and Filegroup Wisdom

Comments: 3 Comments
Published on: March 11, 2013

Backstory

Each month the SQL community comes together for an important party.  This is the blog party that was the brain child of Adam Machanic (Twitter) known as T-SQL Tuesday.

The party is a very good collaboration among data professionals on a pre-determined topic.  This month, for TSQL Tuesday #40, the topic is on Files and Filegroups.  The host du mois is Jen McCown (Twitter).

This month, I had the luck of encountering something this past week that is right up the alley of this topic.  I love it when sysadmins help create learning opportunities for me (e.g. blog material).

Production Down

I was recently given the following concerning a client server issue:

The log file for database ‘xxx’ is full. Back up the transaction log for the database to free up some log space.

That was followed by a short description stating that the sysadmin had tried to expand the log file and that they also tried to run a full backup.  The output of the full backup was as follows.

BACKUP DATABASE [xxx] To Disk=’blah’ WITH NOFORMAT, NOINIT, NAME = N’blah’, SKIP, REWIND, NOUNLOAD, STATS = 10
” failed with the following error: “The backup of the file or filegroup “sysft_FTS” is not permitted because it is not online.
BACKUP can be performed by using the FILEGROUP or FILE clauses to restrict the selection to include only online data.

Now this makes things more interesting.  The sysadmin at least tried to do a full backup and then handed off when it got too deep.

The client server is a SQL 2005 box.  Fulltext was enabled for the database on that box. And we have seen plenty of issues related to Fulltext in SQL 2005.  Somehow, I feel that none of them really pertained to this opportunity.  From all appearances, there was either a disk issue (no history in the logs but client said there was) or somebody deleted the directory (there was a login at the time the issue started and there was a service restart at that time).  In either case, the folder for the fulltext filegroup was no longer present.  But I am getting a little ahead of myself.

When querying the sys.database_files catalog view, I was able to confirm the directory path that should have been in place for the filegroup and that the filegroup was indeed OFFLINE.  Results and query to follow, with filepaths redacted intentionally.

SELECT FILE_ID,type_desc,name
		,physical_name --intentionally omitted in result set
		,state_desc
	FROM sys.database_files;
file_id type_desc name state_desc
1 ROWS Somefile ONLINE
2 LOG Somefile_log ONLINE
3 ROWS Somefile_data ONLINE
4 ROWS Somefile_index ONLINE
65537 FULLTEXT sysft_FTS OFFLINE

So, indeed I do have a problem with the filegroup and I need to get it back online in order to resume backups and get this database back online and able to perform backups.

Some suggestions out there would be to rebuild the fulltext catalog in order to bring it back online.  Well, the files are no longer present on the filesystem, so this didn’t work too well.

ALTER FULLTEXT CATALOG [FTS] REBUILD
/*
Full-text catalog 'FTS' is in an unusable state. Drop and re-create this full-text catalog.
*/

The notes in the code block represent the outcome.  And the output makes sense if you ask me.  But when trying to drop and recreate, I ran into some more fun.

DROP FULLTEXT CATALOG [FTS]
--or
SP_FULLTEXT_DATABASE @ACTION= 'disable'
 
/*
Cannot drop full-text catalog 'FTS' because it contains a full-text index.
*/

Once again, that makes sense.  I had hoped that it would drop everything for me.  So, time to try dropping the indexes and recreating them.  For this, I took screenshots of each index in question.  Then tried to drop them.  Once again – another error.

property fulltextindexsize is not available

Despite that error, the indexes were gone and the catalog dropped.  Since I had disabled FT on the database, I needed to re-enable it in order to recreate the catalog and indexes (I had scripts for the catalog and screenshots for the indexes).

SP_FULLTEXT_DATABASE @ACTION= 'enable'

Now issuing a rebuild against that catalog works as expected.  Additionally, backups work as expected.  And to confirm that all is well, query sys.database_files once again to see that the filegroup is online.

file_id type_desc name state_desc
1 ROWS Somefile ONLINE
2 LOG Somefile_log ONLINE
3 ROWS Somefile_data ONLINE
4 ROWS Somefile_index ONLINE
65537 FULLTEXT sysft_FTS ONLINE

Between the Lines

I breezed through what got this filegroup back online so database activity could resume.  One thing that I skipped over was a step I took trying to recover without dropping and recreating.  Since the directory was not present, and there was a full backup from the same day that had the filegroup in a working state, I tried to recover the filegroup manually.  Restore the database, copy the folder structure into the appropriate filepath and run an alter database statement.  Since it didn’t work, I am not going into deep details on it.  The short of it is that since the structure disappeared off disk, there was some corruption related to it internally in the database.  That needed fixed and in this case it meant to drop the indexes and catalog in order to recreate it.

«page 1 of 4




Calendar
April 2014
M T W T F S S
« Mar    
 123456
78910111213
14151617181920
21222324252627
282930  
Content
SQLHelp

SQLHelp


Welcome , today is Wednesday, April 23, 2014