Complete Coverage
15:52 | 
Posted by
Sean McCown
I talked to a dev friend of mine recently and he presented me with a very common problem that he ran across at work. With it being the holidays still, a lot of the members of his team are on vacation. Well, a problem came up and he was stuck troubleshooting by himself because he couldn’t get anyone on the phone. And to top it off it’s a very complicated system with no clear troubleshooting paths, so the SMEs are very important in this case.
This is a very common mistake that a lot of shops make. You need to make sure you have full coverage even when everyone’s on vacation. At my gig we’re all vacation this week, but we still have people taking turns being on-call in case something breaks. So if you guys really want to avoid problems like these, take the easy road and make sure that someone is at least on-call during vacations.
It’s just that easy.
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:
Xmas List 2009 Posted
Hey everyone, go check-out our new techie gift guide this year…
http://infoworld.com/d/applications/last-minute-xmas-wish-list-2009-296
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:
The Best DR Plan in Existence
Someone just wrote to tell me that they just had a major outage on their main DW server due to one of the teams upgrading a driver. There was nothing wrong with what they did, so there’s no reason to believe it would have brought the entire system down.
All I can say is that they had the best DR plan in existence and I had to share it with you so you can mimic it in your own shops. All you have to do is not take any system-level backups and don’t save any of your configs or any of your files, or document any of your processes. Then you can just have everyone in the dept wish as hard as they can that nothing bad will ever happen.
It must be an excellent DR plan because it’s the same one that so many companies use. And it costs next to nothing to implement, which is probably why it’s so popular. And the real joy is that you can put it into practice almost as quickly as you can put it on paper. It takes next to no approval from the brass either.
I really don’t see the big deal with DR though. There’s so much time online and in magazines/books dedicated to this topic and I just don’t get it. If so many companies already have this practically foolproof DR plan then what’s left to discuss? I’ve been thinking about this for a while now and I can’t come up with a single situation where this wouldn’t apply.
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:
Labels:
Speaker Call
We’re holding our first SQL Saturday event in Dallas and I’m in charge of speakers. So this is an official call for speakers. If any of you speakers out there are going to be in Dallas (or would like to come) on May 22, then send me an abstract by Feb 15 and we’ll get you going.
Send the abstracts directly to me at KO@KenpoSecrets.com
OK, that’s all I’ve got 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:
New webcast
New BI Site
I recently had a conversation with Brian Knight and he told me about his new site. Actually, I was supposed to have posted this mon, but I’m only now getting around to it.
I could sit here and tell you about Brian’s new site, but it may be best to hear it directly from Brian himself:
The site is focused only on BI for Microsoft developers. We’re launching with more than 150 blog posts day 1 and 30 articles and are going to have an article a day (Monday -Thursday) for 2009 and 2010. It’s pretty much a classic community but has a lot of social networking built into it like adding others as friends, walls, etc.
Now, I’ve already been a couple times and there really is a lot of content there. So go check it out and maybe if we’re all lucky, Brian will let me do a couple articles for him.
Let’s all go check it out: http://www.BIDN.com
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:
Pirate Coder
I find myself multitasking again with my little one.
Today I'm testing some HA scenarios with replication while simultaneously being a pirate.
Enjoy.
Watch my free SQL Server Tutorials at:
http://midnightdba.itbookworm.com/
Read my book reviews at:
http://www.itbookworm.com/
Blog Author of:
Database Underground – http://www.infoworld.com/blogs/sean-mccown
Follow my Twitter:
The Juice Box
I had a very typical conversation with my 2yr old yesterday. It went something like this…
The Juice Box
Benji: More juice daddy.
me: Ok, go throw the box away and bring me another one.
so he goes and throws it away then looks at me.
me: Ok baby, now bring me another box.
about a minute later he shows up with a brown box that some books came in.
me: No baby, bring me a juice box. The small one.
so then he shows up with a smaller shipping box.
me: No baby, a juice box. Bring me a juice box. A juice box.
(Benji looking around all over)
me: it’s in the cabinet right there honey.
(keeps looking around)
me: open the cabinet door and take out a juice box.
(looks at the fridge door.)
me: No baby, the cabinet door. Right there by the broom.
(goes to the other side of the fridge and looks at the wrong broom instead of the one right in front of him)
me: no sweetie, the other broom. Open the door by the other broom.
(opens the fridge door)
me: no sugar, the cabinet door. close the fridge.
(looks all around)
me: the green door right in front of you.
(Looks at fridge again)
me: no no, the green door by the broom.
(goes to the other side of the fridge again and looks at the wrong broom)
me: honey, bring me a juice box.
(looks at ceiling, floor, dog food, etc. everything but the door right in front of him)
me: sweetie, bring me some juice and I’ll open it for you.
(opens right door and brings the juice box.)
Now I ask you, how many of us have had conversations very similar to that with our end users or even our devs? I know some of the devs I’ve worked with have been exactly like that.
So it really got me thinking about the skills a good DBA needs. So as it turns out if you’re looking to make a switch to being a DBA, here’s what you should do. If you really wanna be successful as a DBA, then while you’re studying SQL and learning your job, open up a daycare and run it for about 5yrs. Don’t only open it and run it, but actually get in there and work with the kids. I’d say a good mix of 2 and 4yr olds should do it. I’ve got 3 kids myself and I did it in reverse. I became a DBA first and then had my kids, but I’m convinced that having kids has made me a better DBA because I honestly do have a lot of very similar conversations with my kids and my devs.
And this may piss of some devs, but any DBA out there who’s had to deal with a group of devs who thinks they know what they’re doing when you’re trying to show them how to do something, you know exactly what I’m talking about.
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:
Job Security
Job security is a really big concern for most DBAs because we tend to automate ourselves out of a job. It doesn’t have to be though.
First I’d like to talk about the worst way possible to ensure you keep your job. I’m talking about those guys who write impossible processes that nobody else can support. They think that if they’re the only ones who can support it then the company will keep them around. And unfortunately that’s not the case. A company is gonna do what they’re gonna do no matter how much you hold their processes hostage.
Then there are the guys who do something similar. They just refuse to document anything and they hold all of the tribal knowledge and keep it to themselves. This isn’t exactly the same as the guy above because this guy could just have info that nobody else does and refuses to show anyone. And again, while it seems like holding information hostage in exchange for your job would be effective, it’s not. Companies are gonna do what they’re gonna do no matter what you do to prevent it. So if they’re looking to get rid of someone, you may be safe for a while, but not for long. Your boss may recognize that you’re the keeper of the processes, but if they get rid of him then the next guy in charge of you may not see your brilliance.
So ok, taking hostages isn’t the way to go, so what is? That’s not a really easy question to answer because the specifics change from company to company. However, the overall method goes something like this. Be a knowledge expert. Don’t just be a DBA. DBAs can be automated. What you have to do is be more than that. Be a data expert. Be a process expert. Be a troubleshooting expert. You have to actually prove to everyone that you’re the one who’s solving problems, or preventing them. It’s not enough to just be a DBA anymore. Buck Woody is fond of telling people to not be DBAs. He wants them to be DB professionals. And while he’s right to a degree, he’s splitting hairs. Instead of changing the name of the position, we should work on changing the meaning of the position. Show the world what DBA really means.
And it’s not just about being an expert. It’s about solving problems that nobody even knows are problems.Talk to users and find out what they do every day, or every month. Find out what their pain points are. Not only that, but also look at existing processes and try to find out where you can improve things. And by improve, I mean greatly improve. Don’t just take a single SSIS package and improve its performance by a few mins. What you need to do is something major. Really increase the performance or the reliability of something. Make something easier for your group like building a management portal. Put your mind to it and be useful. But don’t just manage backups and automated processes.
Unfortunately, all this is just fodder. Again, companies are gonna do what they’re gonna do. So there’s really no way to save your job if it’s really in peril. Layoffs are unfortunately a part of being in the workforce. And it always seems that no matter what you do or how good you are, or how useful and incredible you are, the lazy, stupid guy in your group is the one your company keeps instead of you. That’s another fact of being in the workforce. Companies have no concept of who their best people are. I could almost guarantee you that if you poled 50 companies who their best people were, and then had the ability to magically look into the workings themselves and see who’s actually making things happen, the two lists would be completely different. Or better yet, ask the big bosses at any company who their best people are and then ask the employees themselves. Again, the lists will be different. All of this is just a windy way of saying that companies have no idea who their key people are and sometimes no matter what you do you can’t secure your position. Sometimes companies just insist on being blind and there’s nothing you can do about it.
But if you’re going to have any chance at all, then follow my (and Buck’s) advice.
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:
Flatliners
Have you ever heard something so stupid or been asked such a stupid question that it actually made your brain flatline? And I mean truly flatline in the way that you actually can’t form a complete thought well enough to respond. I suppose to a large degree you have to decide whether they’re being serious or not.
One of the things I used to get flatlined on all the time was baselines. In a gig I had a few years ago I was constantly being asked why the CPU was so high on the server, or why there were so many active connections in the DB. Here’s how the scenario typically went… I would get a call from someone saying that the CPU was at 87% and why is it so high. I would then ask what it normally is and to that they would reply, I don’t know. That’s when my brain would just flatline. I couldn’t think of anything to say.
Another one that used to kill me is when someone would call me very concerned because they were in perfmon and noticed that the disk queue was 15%. I literally went blank and couldn’t think of any way to respond. For those of you who aren’t up on perfmon, disk queues aren’t measured in %.
Of course I’ve been flatlined enough over the years that I’m better equipped to handle the extreme stupidity that falls on DBAs sometimes… or so I thought.
It was actually earlier this year when one of my devs came up to me and said that he had a problem with one of his SQL boxes. He then handed me a stack of papers like an inch thick. This is the problem he says. What he had given me was a bunch of printouts he’d taken from various websites that came up in his google search. Now, these weren’t really a fix for a single issue. No no… what these were, were a bunch of different possible issues to the problem he was seeing. So he had gone into google and typed something like “SQL Server running slow”, and then printed out the top 50 results or so. He didn’t read any of them; he just handed them to me as a collective solution to his specific problem.
And I, the experienced DBA and MVP… flatlined.
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:
Common Sense
The longer I stay in IT, the more I realize I just don’t know what’s common sense and what isn’t. I used to think all kinds of things were common sense but I’m having to change my opinion on them. Keeping in mind that common sense changes with each industry, but there are some large overlaps.
For instance, I used to think it was common sense to actually test code before you put it into prod. I no longer think that. Apparently it’s something that has to be taught, drilled, practiced, and re-taught. I’m just awed at how hard it is to get devs to test code, and quite often, how much harder it is to get companies to see the value of testing.
As well, I used to think that it was common sense to test the same scenario that you want to go into prod. This sounds like the same one above but it’s not. Here, the testing is happening, but they’re testing something different than is going to be in prod. I saw this last year actually. A company was testing a repl scenario to send their prod data to their reporting server. They tested 25 tables and latency was excellent so they pushed forward with putting it into prod. And of course the scenario crashed and burned in prod because they published over 200 tables in that DB, and the same number in like 10 other DBs. So evidently it’s not common sense to test exactly what you’re going to put into prod. And there are so many more examples of this one I could fill a book.
I used to also think that it was common sense to troubleshoot the same scenario you’re having trouble with, but again, I’m wrong. I recently saw a scenario where a tech was troubleshooting a data load. The load process was put on a different server than the one it usually runs on and it took about 4x longer than normal. And of course that’s cause for alarm especially since both source and destination servers are in the same data center. At least it would be alarming if he were comparing the same processes. In prod he was running an incremental process and on his test box he was running a full load process. So let me see if I’ve got this straight, you went from an incremental process to a full load and you’re surprised it’s taking longer. I’m actually speechless. Hopefully you at least now know why we have the incremental to begin with.
Again, I could go on forever, but I think you get the point. Common sense isn’t so common anymore. I’m not sure we can say that about anything anymore, much less IT stuff.
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:
More Questionable Code
A couple pieces of code came across my desk yesterday and they’re definitely the kind of thing you read about… if nowhere else, then here for sure. This was code that a dev sent me that is to be deployed next week.
The first one could actually cause a problem so I’ll talk about it first.
I’ll write the pseudo-code because I don’t have the actual code in front of me right now. It goes like this:
1. Check if the Whale schema exists.
2. If it does exist then drop it.
3. Create the Whale schema.
Ok, so that’s exactly what the script does. Here, I’ll go ahead and attempt the code. I didn’t look it up so it may have a slight error in it, but you get the idea.
If exists (select name from sys.schemas where name = ‘Whale’)
BEGIN
drop schema Whale
END;
Create schema Whale with authorization = ‘dbo’;
So what’s wrong with this code you ask? It’s simple, here’s the logic.
First, if it exists and you drop it then why just turn around and recreate it in the next step? What’s the point of that? So if your goal is to create the schema then everything above the ‘create’ line is useless. And I know what you’re thinking… so what? What’s the big deal if a tiny query gets run during a deployment? It’s not like it’s going to drag the system down. Well, you’re right about that, but it could kill the deployment. If that schema did exist and it actually contained objects, then the drop statement would fail until you put those objects somewhere else. So with there being no code to check that objects exist inside, you could be dooming the deployment to an unnecessary error. You could also say that you know that the schema doesn’t exist so there’s nothing to worry about. That’s fine, then take out the check and the drop. If you know it’s not there, then take it out. It’s called having concise code and it’s something that we lack in this industry these days. Let me illustrate this with something completely ridiculous that also doesn’t really effect performance.
Create table #t1 (col1 int)
truncate table #t1
truncate table #t1
truncate table #t1
truncate table #t1
truncate table #t1
Insert #t1 Select 1
Ok, so look at that code above. I created a temp table and then truncated it 5x. That’s not going to effect performance at all because there’s no data in it since it was just created and there are no pages. Then I go ahead and insert a row. I can’t think of anyone who would let this kind of thing go on in an SP, or in deployment code, but we’re expected to let useless checks stay in our scripts.
This code was clearly scripted in the wizard so it’s not like the dev went out of his way to do this by hand, but it’s the mark of a fairly inexperience coder to let the wizard create scripts and not inspect what it’s giving you.
The other piece of code doesn’t really do any damage as much as it’s just annoying. In fact, it’s really 2 different scripts. They’re create view scripts and the first one reads something like this:
create view MyView
as
Select MyFavoriteTable.col1,
MyFavoriteTable.col2,
MyFavoriteTable.col3,
MyFavoriteTable.col4,
MyFavoriteTable.col5,
MyFavoriteTable.col6,
MyFavoriteTable.col7,
MyFavoriteTable.col8,
MyFavoriteTable.col9,
MyFavoriteTable.col10
from MyFavoriteTable as MyFavoriteTable
Ok, so even typing this in the blog pisses me off. And again, it’s just a style thing, but this just drives me crazy. What drives me crazy the most is that this dev doesn’t understand what aliases are for. To alias a table with the same name as the table itself defeats the purpose of having the alias in the first place. Here a much better alias would have been mft or mt or m. Hell, you could even go as far as MyFT or something like that. Here, I’ll play one of those out for you and you tell me which one you’d rather read.
Select mft.col1,
mft.col2,
mft.col3,
mft.col4,
mft.col5,
mft.col6,
mft.col7,
mft.col8,
mft.col9,
mft.col10
from MyFavoriteTable as mft
Forget reading, which one of those would you rather type? It’s just more concise and easier to read. I happen to know the dev who wrote this and his opinion is that the full table name makes it easier to read when you’ve got larger joins. Personally, I’ve never known anyone to complain about short aliases before, and again, I honestly think this boils down to inexperience. When I first started coding well over a decade ago, I used to need things to be presented to me in very specific ways too. It was the only way I could read the code. That kind of thing is much less important to me now that I have been doing it for a long time. Why? Because I know how to code.
So the second thing about this script that bugs me is the fact that he saw the need to alias a query with a single table in it. Now you’re just being obstinate for no reason. I know the argument though. He probably would say that he did it for consistency. The only problem with that is the other create view script he submitted didn’t have the same stupid aliasing structure, so where’s the argument now?
Ok, so this code was clearly brought to us as part of a code review so the next logical question is why don’t we just reject the code if it bothers us so badly? Well, the answer is simple. Like so many places, our code reviews are merely perfunctory process placeholders. Our lead dev has made it very clear that he’s going to do whatever he likes no matter what we say. We’ve rejected code plenty of times for really valid process or performance reasons and he always thumbs his nose up at us and pushes it into prod anyway. I really don’t understand how he’s gotten such a superior attitude towards the DBAs, but it’s completely unwarranted. He acts like we’re in this just to piss on his parade and make him look bad, but we’re really only trying to help. But we don’t even bother anymore. What’s the point? He knows more than the rest of us put together so we don’t even bring stuff up anymore.
So that’s my rant for tonight. Remember, use aliases wisely and be as consistent as you can. And for everyone’s sake listen to what your DBAs are telling you for a change. They’re really only trying to help.
Sick of LiteSpeed
I’m just getting sick of dealing with LIteSpeed problems. My big DW was using a lower version of LS and it was fine for a long time. Then I upgraded to the new version and my backups went from 30mins to like 10hrs. So I went back to the previous version. Then I upgraded my DB from yukon to katmai and i upgraded LS at the same time hoping that the even newer version would work better with katmai on my big DW system. That hasn’t happened. It’s been ridiculous trying to get backups to even complete.
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:
Under the Radar
So I’m sitting here at the blogger table at the 2nd PASS keynote by Tom Casey and he brings up a good point. Now this is something I’ve blogged about in the past, but it’s about that guy in your org who goes off and does his own thing and puts a small DB on his desktop and starts doing data loads, and crunching things on his own. That can be a problem because IT isn’t involved and IT doesn’t even know it exists. That’s why slammer was so devastating because of all those “unauthorized” DBs out there. And everyone should be going through IT for these types of projects… or should they?
Tom actually brings up a good point that every piece of data, or every query doesn’t have to go through IT. Some things aren’t big enough or are only for short-lived times and there’s no reason for it to be spun up as a project. And again this is a fine line because as a DBA I really do have to know where my data’s going and if I let everyone just pull whatever they wanted, then server performance would be crap. So it’s a tough line to walk because while we need to have some kind of decent control over where our server resources are going, we also don’t need to be involved in every little thing that crosses someone’s mind.
IT quite often also isn’t very responsive because we do have to plan things better than the users do and I think they lose sight of that. The trick is for us to make it a real project while at the same time not slowing them down too much.
So I’m gonna go ahead and say that office users are free to keep things under our radar, but know that comes at a price. If you lose the data or the code or it starts performing badly, then don’t come crying to us. These are the things that IT brings to the table. We bring recoverability, stability, accountability, etc. And it’s quite possible that you start something on your own and then get IT involved when it gets too big or too important for you to manage yourself. I really like this scenario because it’s often times easier for us to take over a project than it is to build it from scratch. Sometimes it’s not because you have to re-write it to actually make it stable, but even that’s often times easier because you’ve already got the business logic and the GUI defined for you.
So anyway, I like what I see in the demos on their new excel plugin (PowerPivot), but personally I’m waiting to see if it’ll actually work as well as they expect. Because the data they’re using is highly specialized and I have to wonder how well my data will work with the product. PowerPivot is a new BI plugin for excel 2010 but I really expect that it won’t really take hold for a couple yrs because the requirements to duplicate the scenario they outlined in the keynote are office 2010, sql server R2, and sharepoint 2010, and I just don’t see many people converting all of these that fast.
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:
Perfmon Scale
Bloggers’ Table
I’m sitting here at the bloggers’ table here at PASS listening to the announcements. So far it’s just housecleaning, but Wayne is a good speaker.
There’s been a lot of twitting this year and it really changes the face of the summit because you can find people and events just by pinging the group. I didn’t think I’d like something like that, but it actually is pretty cool.
I’m horrible at this live social blogging thing though so I’ll shut up now.
1st Night at PASS
Ok we arrived in Seattle for PASS only I found out tonight that I’m not supposed to call it PASS. Apparently the PC name for PASS is the PASS Summit. Oh well…
So we started at the Tap House wtih Allen White and his wife and then went to the convention center (I can still call it that, right?) to register. There we met up with tons of other MVPs and a few people that Jen follows on twitter. Would that be her fellow twits?
I was talking to Allen about how things appear to be getting better because there are a lot more MVPs here than there have been the past couple yrs. I think this is going to be a good week.
We also did our first DBAs@Midnight with Allen. It was really fun. We got to talk about Oracle and making mistakes, and powershell, etc. It was a fun talk. I’ll upload it soon so check back and I’ll let you know when it’s up.
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:
PASS Treasure Hunt
Ok, so the MidnightDBAs are off to PASS next week and we’ll be giving away some of our recently designed swag. The way this will work is we’ll be twittering our position from time to time and the 1st one to come up and find us will get a shirt, book, or something.
So if you don’t already follow us in twitter, get yourself setup and have them delivered to your phone and just look for us out and about.
Good luck.
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:
Uniting at Last
The DBAs in my area have finally started using their connections through the user group in a way that will actually do them some good. When they get an offer from a company they put the word out to the user group to get any info on that company that may do them some good in making their decision. They typically ask what the company’s like to work for, what the bosses are like, what kind of vacation they get, if bonuses happen on a regular basis, if they expect you to work lots of overtime, what their work from home policy is like, etc.
Personally, this is an excellent use of resources and I think companies have had it too good too long. They way many of them just dump employees for silly, pathetic reasons because they know that it’s too much effort and cost to get a lawyer and sue so most people will just go get another job and forget about it. Unreasonable demands on time and project guidelines are another favorite trick of companies when dealing with DBAs. And they like to hold your head to the fire and then hold it against you when you get burned. So maybe a little bad reputation will help to straighten them out.
It’s always one of the hardest parts about starting a new job isn’t it… not knowing what you’re really in for? So it’s really nice when you can ask someone who worked there before and get the full scoop before you take the plunge. So I’d like to encourage all of you to keep it up and start working with the members of your user group to make sure that companies who don’t value their DBAs get found out. So they’ll either change their ways or not get any quality DBAs.
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:
Time off
Ok guys, I took a little time off of blogging to catch up on videos and MidnightDBA stuff, but I’m back now and I’ll be blogging more regularly again.
So if some of you haven’t heard yet, we’ve now got T-shirts on MidnightDBA and I’m rather proud of some of them. We’ve got some other stuff too. The main thing to remember with the shirts is to not take yourself too seriously. We’re just having fun here and I hope you really like some of them. We’re using Zazzle and you can find the link to our store here.
We’re not making much $$ off of them but what we do make will be going to fund our swag giveaways.
So go check out our store and find something you like.
I’ll have a more meaningful post tomorrow.
Firehat Coding
You know, sometimes you’ve just gotta do what you’ve just gotta do. And today is another one of those days where I end up multitasking at home because I’m watching my youngest boy (almost 2yrs).
So let no one say that you can’t code and play with your kids at the same time.
Hence, this is what I look like today.
Head in the Clouds
OK, so a while back I wrote a post about how Oracle doensn’t build as much of a community as MS. And not only do I stand by it, but I’ve seen quite a few of the replies around the internet and those Oracle guys amaze me even to this day.
They spend an awful lot of time talking about how much better Oracle is than mssql and how much more stable it is and how much more Oracle users expect from their DBs because they tend to be more important than mssql DBs. Also, Oracle DBs have more users going against them than mssql DBs so more people are affected when they do go down so Oracle DBAs have to be more on the ball because their users expect more uptime. Whereas mssql DBAs’ users expect more downtime so the DBAs don’t have to hurry as much to get the system back up because that downtime is expected.
Man, talk about having your head in the clouds. I can’t believe that in this day and age that people are still so incredibly blind. Do they really think that mssql has taken the market by storm because there are so many people with little insignificant DBs and they just don’t wanna pay for Oracle on these tiny little things. It’s not under dispute that Oracle outshines mssql in some areas. They’ve been around longer and they’ve had more time to bake their product. But that doesn’t make mssql a slouch either. I know you guys know this all too well. Some of the biggest and most important DBs on the planet are on mssql and they require just as much uptime as those super-important Oracle DBs.
To make such statements is not only ludicrous, it’s just childish. It’s like saying that linux apps are more important than windows apps. Grow up guys.
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:
The Clean House
This post was particularly inspired while cleaning my house today. It’s easy, if you think you’ve got a clean house then just stand up on a ladder some time and change your perspective. I think you’ll find there are lots of things about your clean house you don’t know. And while nobody’s officially complaining about the condition of the house (and in fact, everyone thinks it’s in great shape), it clearly is in disarray when you shift to that new bird’s eye view.
The same is true for your DB. I honestly can’t count the number of times I’ve been told that there was no reason to look at DB performance because nobody was complaining about it. Then when I dig into it and look at things from a DBA angle, the system is actually abysmal. So if performance is so bad then why isn’t anyone complaining? Well there can be a couple reasons for that. First of all the DB could just not be very busy comparatively so the users won’t notice the issues. And second, they may have just gotten used to things running slower so again they don’t notice it. I’ve actually seen that several times. Things get slower and slower, or better yet, they just start out slow and nobody ever knows the difference. And what’s even WORSE is that more often than not there’s some cornhole in the background telling them that Oracle would solve all their problems. Listen, if you can’t write a simple process against mssql and make it run right do you really think you’ll be able to hit Oracle ok?
So I’ve gotten off topic a little… back on track. The point of all this is that just because nobody’s complaining about performance that doesn’t mean that your system is performing. I quite often find that companies have no idea what their systems can scale to and they just assume that they’ll be able to handle whatever they throw at them. And it typically takes more forethought than most companies have to look at a system objectively and proactively fix problems.
So the next time you’re considering your system performance, stand on a ladder or crawl down on the floor and see how things look from there.
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:
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:
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:
Diabetic Servers
One question I get a lot is about performance and how systems can run well for years and then suddenly just stop performing well. That’s an understandable question and one that’s both easy and complicated to answer.
The short answer is that there’s a huge margin for error in these types of things. However, the margin is only huge if you’re not doing anything about it. Let me explain.
It’s like being on a diet. When you’re watching what you eat every little bit matters. That extra helping of fries, that bowl of ice cream, and that soda are all death to a strict diet. Even little things can be harmful when you’re on a diet and the more strict the diet the more the little things matter. That’s why professional athletes of all kinds watch their intake like hawks. So in their case that extra ounce of potatoes, or that extra ounce of meat can really make a difference. And that’s not even to mention diabetics and other people on strict medical diets. Think about someone with severely high blood pressure. Their diet is extremely important and the slightest wrong food can have serious blowback on their system.
Now look at someone who’s already grossly overweight. This guy eats whatever he likes up to thousands of extra calories a day. He eats only fried and fatty foods and eats as much of it as he likes. So that extra helping of ice cream or that extra few fries really doesn’t matter much on top of everything else. That’s not to say that it doesn’t have a cumulative effect, just that day to day it doesn’t matter much. Eventually though, it will take its toll as he get heavier and heavier and starts to feel health effects from it. So while those extra fries do eventually catch up with him, they don’t cause any real immediate effect on top of all the other stuff he’s eating.
Well, that’s much the way it is with servers too. If you have a bunch of stuff that runs kinda poorly or just not as well as it could, it’s not really that important on a daily basis because the server itself runs slow and what’s one more mediocre process going to hurt? So a server can run for quite a while like that and nobody will ever really notice the difference. Part of the problem is that so few people bother to investigate better ways to do things so they get used to their DB performing slowly. It’s not necessarily their fault and these things can sneak up on them. Even a fairly good DBA can have wrong decisions go undiagnosed for a long time and the poor performance can sneak up on him and next thing he knows his system is just dragging. And it’s hard to go back and find that one thing that started the whole thing. I find typically that performance problems are systemic. By that I mean that whatever mistake is made, is made throughout the whole system. It’s quite often not an isolated incident unless someone new comes into a shop where things are already running smoothly.
So anyway, a server can put up with a good deal of abuse before it goes belly-up, but it will eventually happen. What you want to try to get to is a point where you can treat your server like it’s got diabetes. You want it on a very strict code diet where you watch every single I/O, every single MB going into RAM, every CPU cycle, etc. On servers like this, one single process that doesn’t behave can have a noticeable effect on many other processes and you’ll know you did something wrong right away. But if you’ve got a system that’s slow to begin with, who’s really going to notice if it’s running a little slower or if the CPU is hitting 81% instead of 76%?
This is why I’m not only stressing performance considerations on servers, but also why I’m always answering this question.
This of course has to play hand in hand with tomorrow’s post on reasonable performance.
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:
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:
Snotting Rights
I recently ran across a perfectly delicious situation I wanted to share with you. Someone actually wrote to tell me about this. He just started a new gig and there was massive corruption in one of the dev DBs. He went to ask the DBA where the backup was and he got a very snotty reply.
Well, as it turns out this was something that the DBA had gone around and around with them about. See, the devs didn’t want the DBA to do anything on their precious box so they refused to give him rights. He tried to explain that they needed backups, etc but they wouldn’t hear of any DBAs pissing all over their dev box.
And now when there’s massive corruption and they need to get their dev code back they call the DBA to ask for help. Y, fat chance guys.
I’m here to tell you that the DBA has full snotting rights here. And it only goes to teach them a lesson I hope. DBAs aren’t here to piss on your parade. We’re here to make sure your parade lasts as long as you want and you can even start your parade over and over again if you need to. Seriously guys all metaphors aside, we’re here to help. So stop acting like we’re on different sides.
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:
Powershell Webcast by O’Reilly
Hey guys... I'm starting a webcast series next week on Beginning Powershell for SQL Server DBAs.
This is an excellent chance for those of you who have always wanted to get started with powershell to do so.
It'll be a ground zero course so if you don't know anything at all about powershell I'll attempt to bring you up.
I hope to see you all there.
Here's a link to the webcast and I hope all of you can join me.
Signup and tell all your friends. If we get enough people signup we'll be able to keep this series alive.
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:
Get Away from the GUI
I had a comment on one of my last posts that suggested that I talk about a few things and this is the first thing that struck my interest because it’s a topic that’s close to my heart. And of course, I’m talking about getting away from the GUI and learning to do things at the command line.
And I know that SQL Server was built around the cmdline and a lot of you cling to it for all of your daily tasks, but you’ve just got to get past it and start doing things in the GUI. No wait, reverse that.
So why do I want you to get off the GUI? Well for starters you never really know a system until you code in it. You can go your entire life not knowing what goes on under the covers if you’re just clicking around and now you’re never learning anything. So when problems come up you have no idea how to fix them because you’ve never bothered learning how anything works.
OK, I could sit here and write everything out in anecdotes but since time’s of the essence and my boys are going crazy this morning, I’ll just put it in a list with comments.
1. GUI is limited – quite often the GUI won’t do everything you need it to do. This comes up quite often working with security, but also things like CDC and data compression. The GUI just doesn’t have all the features the cmdline does. Backup/restore is another good example because you can’t do page-level restores in the GUI.
2. GUI isn’t scalable – You can’t save GUI actions and reproduce them for several boxes. If you code a solution you can code in parameters that allow you to run the same code against different objects. I think user accts are a good example. What if you have to add a user to all 200 DBs on your server, or on 10 servers? What are you gonna do? Click through those perms on all 2000 DBs? It’ll take you days. Meanwhile at the cmdline you can run it against all boxes at once and be done in just a couple mins. Seriously, you can’t beat that. So everything you do at the GUI you have to reproduce every time you need to perform that task.
3. Gui provides no knowledge – Like I said already, if you always use the GUI then you never learn much because you don’t ever have any exposure to the inner workings.
4. GUI is single-task based – Everything you do in the GUI is a single action. You can’t do too multiple tasks as once and you can’t set them up in transactions so they all pass or fail together. What if you needed to add a user to SQL and to Windows? You can do it in powershell at the same time, but in the GUI you have to physically click through and do them yourself. And what if you wanted to make it so that if one of them failed they both did? You can’t do that in the GUI.
5. GUI is typically longer – Even though the GUI is more friendly sometimes, it often takes longer to click through the screens than it does to type the cmds… at least for those of us who can type.
6. GUI is slower – Many times the GUI has been slower to accomplish a task. I’ve had the GUI take quite a bit of time or even freeze up on me while trying to do something when the cmdline finished in just a couple secs. So the GUI isn’t nearly as efficient.
7. GUIs don’t multitask well – While Yukon brought us the ability to have GUI tasks running in the background while we work in SSMS, you can still only do so many things that way. You can multitask much better at the cmdline because you can offload it easier to other boxes or just schedule scripts to kick off. Again, you can’t schedule a GUI operation.
Look… GUIs are fine when you’re a beginner. They ease the burden of learning something new and when a new technology comes out I quite often start with the GUI just to get my feet wet. But then I switch to the cmdline pretty quickly and do it in there the rest of the time.
And there’s not much excuse for not learning the cmdline anymore since Yukon brought us the ability to generate the code for our actions and Katmai has improved on that. Now if you do something in the GUI you’re not sure how to do in the cmdline, you just script it from the GUI and learn something. Now you can just modify it for your purposes. I do that sometimes still.
I was really lucky when I started learning SQL in that the guy teaching me was a huge cmdline buff and insisted that I did everything that way. Also, I emulated the MVPs who all touted the cmdline so I figured if it was good enough for them then I should be doing it too. So in that respect I was lucky to learn that lesson early.
I hope some of you now learn that lesson here and start trying to do your jobs at the cmdline. It’ll be tough at first, but in a yr you’ll be really glad you took the pains.
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:
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:
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:
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:
Decimal(18,2)?!?
I just reviewed a script that had 3 major problems.
1. It created a #table and then immediately truncated it.
2. It passed in a var into the sp and then hardcoded the value a few lines later. The passed-in value will never be read.
3. It did the following operation.
Declare @var decimal(18,2)
Set @var = select count(*) from table
Really? Come on people let’s think about what we’re doing.
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:
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:
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:
Fairy Repellent
One of my big peeves is devs writing code to protect themselves against things that never happen.
You really see it all the time don’t you? And it always sounds so reasonable at the time, but it really isn’t if you put any thought into it at all. Here are a couple examples.
We had a dev a while back to introduced a redesign for a job where he put everything pretty much into the same step. He had a long SP written that combined xp_cmdshell steps and reindexing steps, etc. It was all just one long strip of code. And of course his logic sounded logical on the surface, but not once I started asking the right kinds of questions. See, his reasoning was that since there’s no way in SQL to run a job step by itself and not the steps that come after it, he needed the ability to be able to run any section of that SP he needed. Ok, that sounds alright because he’s right. While you can start a job at any step, you can’t tell it to only run a single step unless you play with the workflow in the steps.
But one simple question killed the whole thing… when’s the last time you needed to run just a single step in this job? I assume that the steps that follow are there for a reason, right? So give me a scenario that this would cover. And of course it’s always the ethereal… I can’t think of anything right now, but I’m trying to cover all the contingencies. I get that dude, but you’re coding for issues you can’t even state.
What about when the job fails? How often does the job fail?
He said, oh it probably fails a couple times a week for one reason or another.
Ok, now how easy is it going to be to troubleshoot the failure if everything’s in just in one huge pile? Shouldn’t you be coding for the case that happens the most? And of those times it fails say at step 2, do you ever need to run step 2 and nothing after it?
He said, No.
, then why are you coding for it?
He says, well there might be a time when I need to run a single piece of the code for some reason say redoing something in the middle of the day or something. It hasn’t happened yet, but it could.
Yeah sure, that could happen. And if it ever does what’s stopping you from copying the code from the job step you need to run and just running it manually in SSMS?
To which he replied… Ummmm….
So ok, this is getting long so I’ll leave this at one example, but you get the point. It’s a lot like buying fairy repellent for your house, and setting fairy traps everywhere. When’s the last time you had a fairy problem?
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
No More Select *
Ok, not ‘no more’, but you guys should seriously limit your usage of select * for everything. I realize that it’s easier to support when you change the data requirement, but it pushes a lot more data than you need and that could really impact network and server performance.
Let’s say you’re running a web app and you need 3 cols on your page. And you pull all 37 because it’s just easier to type a * than each column. That’s fine from your end, but you could seriously impact the server and the network because if one of those cols is really wide, say varchar(200) (or even 400, right…) then you’re taking up that much extra bandwidth and server memory. Sure it probably won’t effect your session that much now, but when you’ve got 500 people on your site at the same time you’ll start to feel the pain then for sure.
So just code for performance and stop being so lazy about having to type a few chars. And if you really don’t like typing that much then get yourself a nice code completer like the one from Red-Gate and you won’t have to type nearly as much. But I’m getting sick of laziness being an excuse for bad coding.
There are some exceptions though. It is ok to use select * for some things but you have to choose those individually and judiciously. Let’s say that you’ve got an SP that pulls 65 cols from a complicated set of logic that you don’t wanna have to re-create or maintain separately. And let’s also say that you only need to query it every now and then, or maybe just 2-3 times a day. In a case like that, it’s probably ok to go ahead and use the SP even though you’re only using a handful of the resultset. However, in that same scenario, if you were using that data several times a minute, or even a second, then you’re really better off from a performance perspective to go ahead and create your own SP that returns less data.
Another excuse that gets used is people often tell me that they used select * to make it easier to make changes to the app. And that is logical to a degree. But people protect themselves all the time from issues that aren’t issues. For instance, I had this just a while back where someone gave me that excuse and when I probed, the app had been up for 2yrs and had only ever had one minor change. So what are you protecting yourself from then? If the app is fairly static, then grow up and do the right thing.
OK, that’s actually stepping on the toes of another post so I’ll stop here.
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
About Me
- Sean McCown
- I am a Contributing Editor for InfoWorld Magazine, and a frequent contributor to SQLServerCentral.com as well as SSWUG.org. I live with my wife and 3 kids, and have practiced and taught Kenpo for 22yrs now.