Tag Archives: DBA

The Zombie Standard

As I promised yesterday I’m gonna talk about process and coding standards today and I want you to really think about what I’m saying here because it’s what separates the men from the boys.

This topic won’t ramble on quite as much as the past 2 days have though so don’t worry.  Basically what I want to say is that you should definitely be creating coding standards and following them, but don’t rest there and don’t allow any of your standards to rest either.  What I mean by that is just because you come up with a standard, say using ‘insert into’ instead of ‘select into’ during your ETL loads, don’t always blindly follow that standard.  Every process is unique and every process deserves consideration.  You can adversely effect your servers if you do the same thing across the board without ever questioning whether this is the right tool for this particular job or not.  So where a ‘select into’ may be the perfect solution for one really huge snapshot load, an ‘insert into’ may be the right solution for other lesser loads. 

One argument I hear a lot when preaching this bit of DB wisdom is that you want to have a solid way of doing things and you want to standardize.  And frankly, that’s just ridiculous.  That argument doesn’t hold up to even the most basic scrutiny.  Ok, maybe ridiculous isn’t the word… let’s go with asinine instead.  So you’re telling me that for this big snapshot load you should use the exact same process as you do for your smaller ones because standardization is the key?  Then why aren’t all of your loads full snapshots?  Shouldn’t you be standardizing on a single load process?  And what about your indexes?  Do you have the exact same fill factor for every single index in your place?  If so you’re not paying attention to your index stats.  And are you using #table instead of @table absolutely everywhere?  Or are there cases where one is better than the other? 

So you see, with just a few examples I’ve shown you that you don’t do EVERYTHING the same way in your DBs.  So there’s no reason for you to scream standardization with something as important as a data load.  Take each load on an individual basis and decide what the best course of action is for each one.  And yeah, as the data grows or changes in nature you may end up revisiting your process and updating it to reflect the new nature of your objects.  And that’s ok.  So many people think that having to rewrite a process is a huge sin and that you’ve failed somehow.  It’s really not you know.  Not only are these types of things a learning process, but things also change that require you to change tactics. 

I really hope you take what I’m saying here and apply it.  And don’t be one of those idiots who tells me that he runs everything serially because he wrote the process on a single-CPU box and therefore he needs to standardize on that platform instead of taking advantage of a multi-CPU machine.

Watch my free SQL Server Tutorials at:
http://MidnightDBA.ITBookworm.com

Read my book reviews at:
www.ITBookworm.com

Blog Author of:
Database Underground – http://www.infoworld.com/blogs/sean-mccown

Follow my Twitter:

http://twitter.com/MidnightDBA

The Performance Dilemma

OK, so yesterday I was talking about how servers can take a long time to finally be brought to their knees with poor processes, and that you want to get to the point where your server is on a strict diet and you control every morsel that goes into it.

Today I want to talk about how that can play into a real performance strategy because unfortunately you have more to consider than just raw performance.  I know, I know, it sounded yesterday like I was advocating that performance was the most important thing.  And it is… but at the same time it isn’t.  So let me explain a little.

Performance is extremely important and some might even say it’s the most important thing on the server, but you also have to consider management et al.

See, quite often the code that leeches out the absolute best performance on the server isn’t as easy to manage as other solutions.  Something like this isn’t very concrete without an example so I’ll give you one.

Let’s look at what I call moving schema.  The guys in my shop are sick to death of hearing that term but it’s an important one.  Moving schema is where you drop and recreate objects all the time that should be permanent.  A good example of this is doing ‘select into’ instead of ‘insert into’.  Sure, the ‘select into’ performs better than the ‘insert into’, but it’s also harder to manage from a couple different perspectives.  For starters, even during your load you can’t specify a filegroup for the object so you’re stuck with the default.  Now for performance or growth reasons you may find it better in general if you put it on a different set of disks, but you can’t because ‘select into’ doesn’t allow it.  So from a space management aspect ‘select into’ traps you in a corner.  Also, if you have specific permissions granted on the table you have to keep track of them somewhere and reapply them every day when you recreate the table.  And you also have to remember to update your permission repository every time you change the perms on that table during the course of your other admin functions.  As well, most performance stats are gathered by objectID.  And if you’re recreating the object every day then your objectIDs are invalid tomorrow.  So you either have to keep track of what the objectID is every day so you can track performance and other issues (like fragmentation) from day to day, or you have to store the objectName in your repository as well which leads to an inefficient and larger schema to track production issues.  It also makes the timing of other processes more critical because with ‘insert into’ they’ll be blocked until the current load finishes, but with ‘select into’ they’ll fail because the object doesn’t even exist. 

So ok, I’ve shown you a few reasons why something as simple as moving schema can be a problem.  And like I said, it’s really one of my diatribes because of those issues mainly, and a couple out-lying ones.  And the guys at my shop have been hearing this battle cry for about 3yrs now and I’m sure they’re quite tired of it.  But when it comes right down to it, it’s good DB practice. 

There are some instances where the moving schema rule can be broken though.  Let’s look at a really large data load for instance.  If for some reason you can’t do any kind of incremental load and you have to do a full snapshot every night (those instances are becoming fewer and fewer with Katmai) on a HUGE table (several hundred million rows), then you may be better off doing a ‘select into’ because you don’t incur the logging and it can it can decrease your load time significantly.  It of course can also help control how big your log grows.  So this is a decision you have to make at the time, right? 

However, I will say that quite often, if you take the size of the log out of the picture, the ‘select into’ doesn’t out-perform the ‘insert into’ by all that much.  I recently tested the 2 methods against 30mill rows and the ‘insert into’ only took about 40secs longer.  That’s really not enough to worry about in the course of a full ETL load.  And for all the extra benefits you get from having a stable object, that 40secs is more than worth it.  So this would be a case where choosing the absolutely best performing process wouldn’t be the best way to go.  In the case above where I talked about loading several hundred million rows, the ‘insert into’ may increase the load time by as many as 30mins and that’s hard to ignore.  So depending on your requirements you may decide that dropping the object every day is worth it.  But in the instance where the difference is only a handful of minutes, you’d typically choose to keep your schema in tact and gain the other benefits.

So OK, you’ve got a process and you’ve decided to drop the schema every day to do your load.  And your process performs as well as it possibly could because of the lack of logging.  The problem is that people tend to think of performance in terms of the single piece they’re writing and nothing else.  Performance includes more than just the load.  Performance also includes up-time, and number of errors, and manageability, etc.  The load is just one part of the process for any given object.  Examples of different parts of an object’s process would be the load process, the reporting process, the troubleshooting process, the triage process, security process, the space management process, etc.  I’m sure there are others, but you guys get the point.  So all of these are all part of the process of managing this object and if they all don’t play well then the process for that object doesn’t work well.  So be careful in throwing all the other processes away and making them extremely more complicated just to appease a single piece.

So I’ve been in several shops and it’s always different everywhere.  Some shops are really big into management and others are really big into performance.  I’ve been in shops where the management of the objects was just a pain because the systems really were so busy that every last CPU cycle was counted and every last I/O had to be justified.  So they were willing to put up with the management headache of that kind of performance cost because they needed it to stay alive.  And again you have to decide what’s best for your place and your processes.  But whatever you decide, don’t rest on that decision.  Keep making it again and again.  I’ll talk about that tomorrow.

I guess that’s all I’ve got to say for now.

Watch my free SQL Server Tutorials at:
http://MidnightDBA.ITBookworm.com

Read my book reviews at:
www.ITBookworm.com

Blog Author of:
Database Underground – http://www.infoworld.com/blogs/sean-mccown

Follow my Twitter:

http://twitter.com/MidnightDBA

The Silent DBA

Be careful when you’re complaining about how much advice you get from your DBA because one day he might stop giving it.  And that’s not really a scenario anyone wants because it means he’s stopped caring about the systems and you won’t get any real help.  Because I don’t care what you devs out there think, you need your DBAs to keep your code playing nicely with others.

So when your DBA stops putting in his 2-cents your prayers haven’t been answered… in fact, just the opposite.  You’ve taken your DB support staff and turned him into a wallflower.

So now my question to you is what did you do to shut him up?  Because I guarantee you it was your fault.  Are you constantly going out of your way to circumvent procedures he puts in place?  Are you constantly disregarding his advice?  Do you refuse to setup things in a way that will make it easier for everyone involved to troubleshoot?  Do you refuse to follow any coding guidelines he gives you to keep things going smoothly?

OK so I realize that even though your code runs in double the time it could and it always deadlocks, that you’re the smartest dev out there and you don’t need advice from any of those dumb DBAs, but try shutting up and listening sometimes.

Watch my free SQL Server Tutorials at:
http://MidnightDBA.ITBookworm.com

Read my book reviews at:
www.ITBookworm.com

Blog Author of:
Database Underground – http://www.infoworld.com/blogs/sean-mccown

Follow my Twitter:

http://twitter.com/MidnightDBA

Easier to Care

We’ve all had companies that didn’t listen to us.  They hire you with the intention of having you make things better and improving their processes.  Then when you come on and start making suggestions, they don’t want to implement any of them.  The reasons can be political, selfish, or just laziness, but the fact remains you’re not doing what you were told you would be.  And the company doesn’t seem to mind.

So what do you do now?  The company doesn’t seem to mind if things run the way they always have been so why should you?  It’s definitely easier to care about the quality of your job when the company does.  But why would any company choose to do things poorly especially when they’ve got to such great lengths to hire someone to fix them?  The answer is I just don’t know.  I’ve seen it too many times under too many companies and it confounds me every time.  I think a lot of time it’s an issue with a boss having too much faith in a guy who’s telling him you don’t know what you’re doing.  And even when you bring solid numbers to him he still doesn’t see the light.  That’s the one that gets me.  When I come to them with solid benchmark results and they still refuse to change the process because there’s a guy they’ve worked with longer who’s lying to them about how it’ll really behave in prod.

OK, so now what to do… well, you’ve really only got a couple choices.  You can quit or you can make your peace with it.  If you’re just not getting anything you need out of the gig then maybe it’s time to move on.  But if you’re getting something else out of it like a good schedule, or work from home, etc then it might be worth it to you to stick around.

If you do stick around then you need to make your peace with it.  And that’s the hard part.  Basically you have to stop caring about the systems and how they run.  Consider yourself more of an internal consultant.  They ask you questions and you advise them.  If they don’t take your advice then it’s not your problem.  It’s really as simple as that.  Of course, if you’re on the hook for support and this is gonna cause a bunch of extra work for you then that’s another story.  But you’ve gotta weigh that against everything else and make a decision.  If they’re really not gonna listen to anything you say then they really don’t understand DBs now do they?  Part of that is education, right?  You have to educate them about how to work with data.  Remember, this data explosion we’re in the middle of is still relatively new and many companies don’t have any idea how to work with it yet.  They have to be taught.  Now if only you can find a way to open them up to listening.

Just remember… numbers don’t lie.  If you keep showing them numbers eventually they may listen.  Of course, for some of these things you could always just do it yourself and say sorry later.  I’d never personally do anything like that(cough-cough), but I know some guys who have.

Watch my free SQL Server Tutorials at:
http://MidnightDBA.ITBookworm.com

Read my book reviews at:
www.ITBookworm.com

Blog Author of:
Database Underground – http://www.infoworld.com/blogs/sean-mccown

Follow my Twitter:

http://twitter.com/MidnightDBA

10yrs Experience

I’ve interviewed many DBAs and one thing holds true more often than not.  Almost everyone has 10+yrs experience, and almost none of them can show the most basic knowledge of SQL.  So I have to ask myself again and again how it is that such experienced people can manage to not have any knowledge about their field.  I think the answer is that these people don’t really have 10yrs of experience.  What they have is 1yr 10 times.  They never bother learning anything new or pushing their skills so they never get any advanced knowledge.  This is why the big conference speakers can give the same sessions year after year and always pack the big rooms.  Because there are more people out there who need the basics and they don’t even study the session material.  So they’re able to come back year after year and still learn something from the content.  And that’s not to say that the session isn’t fabulous.  It’s just to say that people should be getting tired of it and they’re not.

So you’ve got the bulk of our profession out there doing the bare minimum to survive.  It’s honestly like they’re potty training.  When you’re a kid and you start potty training you have a hard time at first, but once you get it, that’s it.  Once you’re potty trained there are no extra levels.  You either pee in your pants or you don’t.  And that’s how so many DBAs treat their jobs.  They’re learned a very small core of DBs and they think that’s it.  They can stop learning because they’ve potty trained in SQL.  Come on guys… that’s now how it works.  Learning SQL is more like chess.  There’s much more to it than just the basic moves of the pieces.  You eventually have to push yourself and learn to think in different ways. 

So all I wanted to tell all you guys is now that you’ve learned to not pee in your pants, start pushing yourself for more and actually get 10yrs experience, and stop repeating the same basic level of knowledge again and again.

 

Watch my free SQL Server Tutorials at:
http://MidnightDBA.ITBookworm.com

Read my book reviews at:
www.ITBookworm.com

Blog Author of:
Database Underground – http://www.infoworld.com/blogs/sean-mccown

Follow my Twitter:

http://twitter.com/MidnightDBA

Landing that job

You’ve been on a couple interviews and you’re finally getting offers coming in.  But a mistake that gets made quite often is that someone takes the first gig that makes them an offer because they can’t afford to turn it down.  That’s an evil in our society that we have to be forced to something we don’t want just to make a living.  If more companies considered retention in their plans we would be more stable as a workforce and you wouldn’t be forced to make decisions you don’t want to make.  Of course, if companies gave even a single thought to retention a lot of us wouldn’t find ourselves out of a job to begin with.

But leaving that behind, let me just advise you against taking the first job you come across.  If you have a family to support I certainly understand it and you’ve gotta do what you’ve gotta do.  But if you’ve got more than one offer coming in, there’s no reason why the other guys can’t wait a day or 2 for your answer.  Most companies take forever to get you through the process and then expect you to make your decision on the spot.  Try not to fall into that trap if you can help it.  It’s not going to kill them if you take an extra day or 2 to consider all your offers.  Some recruiters like to put pressure on you by getting offended at your audacity for considering a different offer, but that’s just childish and don’t fall for it.  Their only concern is their own paycheck and it has nothing to do with you.  You gotta do what you gotta do.  Take the gig you want not the one the losing recruiter wants you to take. 

Recruiters will play games with you to get you to take gigs too.  I recently witnessed a recruiter telling someone they had to accept the company’s offer right now or it would be rescinded.  Whatever dude.  So if something like that happens to you you have 3 choices.  You can capitulate in which case you get what’s coming to you.  You can also tell them up front that if the deal’s only good right this second that you pass.  That usually changes their tune and fast.  Or you can accept the offer and then entertain other opportunities as they come up.  That may leave you accepting the offer and then rescinding it a few days later, but that’s the cost of doing business.  And if they ask what happened and why you’re backing out, just tell them that you don’t like being blackmailed so you took it to appease them but did your own thing.  Then if at all possible, make sure the company finds out how the recruiters who are representing them are doing business.  You’ll probably find that they knew nothing about the threat and would be pretty upset to hear about it.  I’ve personally ratted out a couple recruiters for similar behavior.  Seriously, don’t let them bully you.

I don’t really like the idea of having to accept a gig and then turn it down a couple days later, but if the recruiter is going to be a child about it then you have to play the game.  My job is to get the best deal for me and my family.  So I’m going to make sure that happens.

So unless you’re about to lose your house, don’t marry the first guy who holds your hand.  There may be better out there.  Personally I don’t like shortterm gigs if I can help it.  I like to get somewhere and stay there.  So when I accept a gig it’s because I think it’s something I want to do for more than 3yrs.  That’s the goal anyway.

Watch my free SQL Server Tutorials at:
http://MidnightDBA.ITBookworm.com

Read my book reviews at:
www.ITBookworm.com

Blog Author of:
Database Underground – http://www.infoworld.com/blogs/sean-mccown

Follow my Twitter:

http://twitter.com/MidnightDBA

Murphy’s Law of Vacation

We’ve got this situation at work where we’ve just run across this very well-known law. 

The law states that that code you just put into production will always break right before you go on vacation.

The law also states that if the only resource for an application goes on vacation, no matter how long the app has been running without issue, it will break as soon as he goes on vacation and you’ll have nobody there with knowledge to fix it.

Help us all.

Is there a connection between Oprah and Michael Jackson that caused his death?

Watch my free SQL Server Tutorials at:
http://MidnightDBA.ITBookworm.com

Read my book reviews at:
www.ITBookworm.com

Blog Author of:
Database Underground – http://www.infoworld.com/blogs/sean-mccown

Follow my Twitter:

http://twitter.com/MidnightDBA

DBA Requests

Here’s just a little advice when sending your DBAs a work request. 

Keep the request as succinct as possible.  If you can get away without giving tons of extra info in paragraph form it’s easier to get exactly what you want.

For instance… if you need a DB restored to a different box the perfect request that will be understood by your DBAs will be this:

I’d like you to restore the latest full backup of Server1.CustomerDB to Server7.CustomerDBtest.  I don’t need the logs restored.

Here’s another good one that will get you something specific that you need as well. 

I need you to take an immediate backup of Server1.CustomerDB and restore it to Server7.CustomerDBtest.  I’m trying to solve a data issue.

That’s it.  You really don’t need any more than that.

Here’s an example of a bad request I received:

Can you please copy some data for me?  I had one of our field agents call and tell me that he accidentally deleted some data and he needs me to investigate it and get it back.  And can you let me know when it’s done because he’s waiting for it.  The problem is that he was trying to delete an order he just entered but he deleted more data than he needed and now someone else’s orders aren’t showing up.  So if you could take care of this for me as soon as possible it would be appreciated.  If you could also give him permissions to the development server so he could inspect it before I make the change that would be fabulous.  I need the data copied from the OrdersPending view on Server17 to Server 58.

Thanks.

 

So, ok… here are the problems with this request.

1.  Way too much extra crap.  I don’t need the story, just the request.

2.  The request doesn’t come until after the big story so you’re wasting my time by making me read all that stuff before I even find out what you want me to do.

3.  As a DBA I’m having a hard time figuring out how copying the current production data to a dev box will recover the lost data.  This request doesn’t even make sense.

4.  As it turns out, it’s not that data that was needed.  He needed the view itself to be placed on the dev box so he could investigate which orders were missing.  Then he was going to request that the data be recovered.

Here’s how this request should have gone…

A field agent has deleted needed orders.  Can you create the Server17.OrdersPending view to Server58.ThisDB so I can investigate which ones are missing?  Also, do you know of a way we could just roll the entire operation back or otherwise get those missing orders?  Thanks and any help you can provide will be appreciated.

It’s not the most succinct, but it allows the DBA to understand the situation and respond accordingly as the view may not even be necessary.  We may be able to get a log reader and just rollback the entire thing.  So this is where the DBA can make a decision.  This request gives just enough info to get the problem across, but not so much that it’s unwieldy. 

The elements that a DBA request should have are:

1.  Brevity

2.  Concision.

3.  Explanation of specialized circumstances (but remember #1 above).

Anyway, this is just to help those of you who may have trouble getting what you want from your DBAs.  Speak a little of their language and you’ll get things faster and more easily.

Watch my free SQL Server Tutorials at:
http://MidnightDBA.ITBookworm.com

Read my book reviews at:
www.ITBookworm.com

Blog Author of:
Database Underground – http://www.infoworld.com/blogs/sean-mccown

Follow my Twitter:

http://twitter.com/MidnightDBA

Celebrity DBA Work

I’ve always had a soft spot for celebrities who have their entire lives spilled out in public. It’s gotta be tough to not only have everyone see how good or bad you do you in your job, but also in your personal life as well. And I’ve often thought for everything I’m not (rich, well-known, etc) at least I don’t have all my failures made public.

At least, that’s the way it is until you have a disaster with one of your DBs. Then everyone wants to come stand over your shoulder and watch you bring it back online. Now the heat is on because you have to remember every last command and parameter in front of the crowd. And knowing that is like trying to stop laughing in church. The pressure is just too great. Some DBAs fold at this time. Others just do their jobs like nothing’s going on. Me, I clear my desk of on-lookers. I had that happen just this morning. I can’t stand to have people just standing there watching me work. I like to be able to follow a train of thought without worrying about how I come off to my audience. So I always tell them… the sooner you leave the sooner I can get to work fixing this.
This morning’s disaster came in the form of a dev sending me an email telling me his system was down. He then followed up with a trip to my desk. So I asked him… did you come over just to stand over my shoulder and watch me work? Thankfully, he took the hint and left. But it’s not always that easy.

I’ve always been very sensitive about that with other people too. Unless invited, I always try to stay on the backside of the monitor… especially when someone is trying to get something done in a hurry or needs to concentrate.

There are those who don’t really mind people watching them. These would be those who present at conferences all the time and are used to it. But I’m not one of them. Anyway though… give your DBAs a break. If you want them to do something for you, don’t stand there and watch them. Just leave and they’ll call you when it’s ready.

Will the Real Idiot Stand-up.

As you know our other DBA just left and he just started his new job. I was IMing him today and asked him how it was going. He said, the last DBA was an idiot. It’s funny, cause I can’t count the number of times I’ve said that. I don’t thing I’ve ever started a job where the guy before me knew what he was doing.

The question is though, am I really all that good or do I just have an inflated ego? I’d probably have to say it’s a bit of both really. I’ve seen a lot of DBAs who just don’t know the simplest things about SQL. I’ve talked about this several times in both my blogs so I’m not going to harp on it too much right now, but it holds more true every year I’m in this industry.

There’s a difference between just doing things differently than the other guy, and his systems actually being neglected. Not performing backups or index maint. is bad DBAing. It’s not just a different way of doing things. I remember talking to a guy who was a very high DBA at a company we all know last year. I was at PASS come to think of it. And he sat there proudly and told me that they NEVER change their sa passwords on any of their systems. I would love to tell you his reasoning, but I just couldn’t get into something like that. But to be proud that you never change your sa password is just assinine. You know what they say dude, if you look around the room and you can’t find the asshole, it’s you. The same goes with idiots.

It’s hard to measure skill though. Everyone has such different experiences. Things I’ve come to know well may be completely foreign to a different DBA who’s far better than I at something else. So does it make him an idiot because he doesn’t know what I know? Yeah, sometimes. The basics should be covered. Every DBA should know what it is to backup a system and do maint. and basic security. And so often it’s these basics that aren’t covered.

So now it comes down to simplicity. What makes a really good DBA? I’ve had several talks with guys all over IT about this same topic, and in almost every session, we’ve pretty much concluded that you only have to try a little bit to be better than the average guy. The average guy does very very little to further his knowledge or to get really good at his job. Most people just skate by. So if you try just a little bit you can rise above the crowd. That’s what I think anyway.