Instead of working, I blog.

Hey everybody... it's been a while since I've blogged, but that's what starting a new gig will do to you. I've had so many new things to rant about here and I'm sure I'll be getting to those in the future, but right now I want to share a funny story with you.

We had a group of devs who were trying to run a fairly simple insert and it kept filling up the disk. And when I say it was filling up the disk, i mean like 50K rows were filling up like 200GB. So they came to me to see if I could fix it. This is where it gets fun.

It really didn't take me too long in my investigation to find that they had the data file set to autogrow by 131,000%. That's right, I said it! 131,000 percent. So now that I found the problem I was able to set it to something more reasonable, shrink the file and let them get on with their insert.

So it started me thinking about what other DBs in the place had similar issues because I've been hearing about disk space problems here and there. So I wrote a powershell to go out and check the file growth rates for every SQL box and I found a lot of the same type of stuff. There were a lot of them set for several thousand percent growth, several of them set for 1MB growth, and everything in between. In general, as a good generic setting to start from, I like to grow my files 1GB at a time. It's a good round number that works in a lot of cases and then you can snipe the ones that need something else. And then I altered the powershell to go out and change the growth rates of all the DB files out there to 1GB. Life is good again and we have the beginnings of a happy shiny environment.

Oh y, and in the same discovery I also found 110 DBs set to autoshrink and I also took care of those.

The other day I blogged on a cool way to alter database file properties.  And I promised I would film the solution for you.  Well, I got home that night and started filming but when I pulled up sqlps I was stopped dead in my tracks by something I couldn’t explain.  The methods to get and set were missing.  I checked my other box and they were there, so what happened?  The only difference was that the box I wrote the blog from was XP and the box I was filming from was Win7.

So I pinged a couple really smart guys as MS (and I cc’d Buck).  So after a couple of days we have an answer.  There’s a new feature in powershell 2.0 that hides the getter and setter methods from you by default in get-member.  They’re officially calling it an enhancement.

There are 2 new parameters that get-member supports that allow you to control what you see.  You can now use –view and –force.

-view takes the parameters Extended, Adapted, Base, All

-force doesn’t take any additional parameters.

However if you’re looking specifically for getter and setter methods, you’ll have to use –force because –view doesn’t display them.  Here’s what that’ll look like:

dir | gm –force

Now you can see everything.  It’s interesting though is that by defalt, PS loads all the methods, it just doesn’t show them to you.

Here’s the MSDN blog that explains this and so much more.

And I’d like to give a special thanks to Robert Hutchinson at MS for chasing this down for me while Buck and I sat on the sidelines helpless.

Man, I still just get so jazzed when I work with powershell. Every time I need to do something long and hard(that was for you Buck), I look in powershell and it lets me just lay back and enjoy the long and hard.

So this time we've got like 85 DB files for our main DB. I know that's a lot but it's been spread across so many LUNs for so long and it's just grown and grown as it's filled up the LUNs. Now we finally got a couple really large LUNs and we're trying to consolidate the files. And now that we've moved most of them to a single LUN, we want to stop the autogrowth on most of them so they don't grow anymore before we empty them and then drop them. Because what's the point really in putting them all on a single LUN if you're not going to get rid of some of them. So we definitely don't want them to grow anymore before we can empty them.

The task at hand was to set all of these files (most of them really) to not autogrow. The choices were do it in the GUI, or write a T-SQL cursor to go through all the files and create an alter database command in dynamic SQL for each one. Neither of those is very attractive considering that there are so many files. Of the 2 of them though, the cursor is the more attractive solution. Then I thought of powershell. I hadn't specifically done anything like this in powershell before, but I figured I'd be able to get some love. I'm not going to teach you powershell here, or explain the script too much. I'm just gonna give you the code and if you have any questions you're free to write, but it's pretty straightforward. I'll be making a video on this tonight for those of you who like to see things in action.

PS SQLSERVER:\SQL\MYSQLBox\DEFAULT\Databases\MyDB\filegroups\data\files> gci | where{$_.Name -ne "MyDB_Data"} | %{$_.set_GrowthType("None"); Alter();}

OK, so a quick glance at the important parts.

1. Make sure you're in the right node. Notice I'm in data\files.
2. Here I'm filtering out the one file I don't want to be effected. So the main data file I want to still be able to grow. I'll talk about that more in a min.
3. Don't forget to use the Alter() at the end. Usually whenever an object has an alter method it wants you to use it. If you don't it'll change it in cache in your powershell session but you won't see the change persisted to the DB. So you're not really making the change to the object in SQL, just in your powershell pipeline. You can tell if something has an alter method by doing this at the cmdline 'gci | gm'. If there's an alter method there, then use it.

OK, you don't have to filter. If you find it too much trouble or if your files are named in such a way that it would be too hard to snipe a single file for some reason, then you can change them all and then just go back to the GUI to change the last one back to what it was.

So there you go. We have a nice 1-line script to alter as many files as you need. And you can clearly tack as many file changes on there as you need. And the best part about it is that the code doesn't really change if you need to do another file operation. All you do is change the method call. Ahhh, the power of powershell.

While talking to a group of devs late last week the topic of deleting prod data came up.  Since I’m the big MVP of the group they wanted to know what the official word from on-high is.  The question is simple, and the answer is even simpler and frankly it’s one of those that was solved so long ago it just doesn’t hold any interest for me anymore.  I’ll hash it out here again though because I know there are plenty of you out there who haven’t solved this problem yet. 

The question is, what’s the best way to delete gazillions of rows out of a prod table.  The issue is with both concurrency  and rollback.  The problem is that if you do a huge delete in a single transaction you don’t leave any room for anyone else to access the table during that time.  So your delete can bring a DB to a complete halt if you don’t do it right.  As well, you put your system at risk should something go wrong and the transaction has to rollback.  So why would the transaction have to rollback?  Well, there are a couple reasons really.  The first, and most likely, is that it could fill up the log and with no more log the transaction can’t continue.  The other possibility is that the server or the service gets restarted because someone didn’t know you were doing a big operation.  And while I’m at it, there is one more logical possibility.  Someone could get impatient with the operation and stop it without realizing that it has to rollback.  This is actually a scenario that I’ve seen played out many times and the result is always the same.  The system has to wait for at least as long as it was going.  And what’s even better is when the DB doesn’t come back online right away, that same user will bounce the service again and again. 

And of course this counts for updates as well… in SQL2K this was accomplished like this:

SET RowCount 1000

While (1=1)

Begin

Begin Transaction

--Delete stmt here--

if @@RowCount = 0

Begin

   Commit Transaction

   Break

End

    Commit Transaction

End

However, this doesn’t work in SQL2K5 and above where you have to do something more like this:

set nocount on

while 1=1

begin

delete top(1000)

from table1

where conditions…

if @@rowcount = 0

end

 

So ok, that’s how you batch delete/update in prod.  Only under very controlled and specific circumstances should you ever do these types of ops in a single batch.

You know I was thinking just this morning about the last round of interviewing I did to find a new DBA at work.  And that of course got me thinking about some of the interviews I’ve done in the past.  There are a few that really stick out.  The ones that are sticking out right away are the ones who didn’t know anything and claimed that they had so much experience and were so good that they didn’t have to be bothered with memorizing every little thing anymore.

This astounds me because all the experts I know are really sharp and on top of their game.  So what these guys are telling me is that they’re so good they don’t have to demonstrate even the most basic knowledge of SQL because they’ve transcended above that?  If that’s the case then my mother’s 100x the DBA any of us will ever be because she doesn’t know the first thing about it.

I remember this one guy especially.  He claimed both on his resume and in person to be an expert in query tuning.  He said, I’ve never found anyone who’s my equal at tuning queries.  So armed with that bit of knowledge I set about quizzing him with the basics.  I mean after all, you have to just get the basics out of the way, right?  I asked him if he had ever worked with exec plans.  He said of course, you don’t tune queries without them.  I said, that’s what I think, but i just wanted to make sure we were on the same page.  And I then asked him how expert his knowledge was of exec plans.  He said he was a very deep expert and didn’t know anyone with his knowledge.  Wow, now I’m getting a little nervous, right?

So I started with the basics.  What’s the difference between an index scan and an index seek?  Well, I’m not sure the exact difference, but I know you want to get rid of one of them.  OK, which one?  I can’t remember.  Um, ok.

So what’s a bookmark lookup (this was back when SQL2K was stull ubiquitous)?  I’ve seen it before, but I’m not sure what it does.

We went back and forth like that a couple more times and I finally broke down and told him that there was no evidence that he had ever tuned a query because he didn’t even have basic knowledge of exec plans.  I asked him what he was basing his claim of being an expert on.  That’s when he let me have it.  Look, I’m an enterprise DBA and I don’t have to know every piddling definition you dig up out of BOL.  Maybe someday when you’re at the level I am you’ll understand.

Um… ok, I’d say we’re done, huh? 

So like I said, I was thinking about that this morning and while I can’t keep up with everything, and nobody can, I like to think that I’ve got a lot of the basics covered.  And the real experts certainly know their stuff.  Go ahead and see how many people would follow her if you asked Kalen how big a SQL page is and she couldn’t answer.  And how many people do you think would follow Paul Tripp if he couldn’t tell you what DBCC CheckDB() was for? 

It just doesn’t hold water.  So for those of you out there thinking you’re all the Pooh, go test yourself and see how much knowledge you really have.  You may find out you’re not as hot as you thought.

A friend of mine was in a meeting yesterday and he called me right after because he was upset. Apparently he and someone else had conflicting opinions about how to do something that neither one of them had ever done before... so they were both essentially guessing.

He was mostly upset because he said the other guy's opinion was just as valid as his and so far it's brought the group to a standstill while they figure this out.

I told him that there's no way that both opinions are equally valid. In some instances that may be the case, but in this one, I happen to know who the other guy is and I know that when it comes to SQL brains he definitely got the short end of the stick. So I'm saying it out-right: not all opinions are equally valid. My mother-in-law likes to put her 2-cents in when we're sitting around talking computers. She doesn't know the 1st thing about it, but she likes to give her opinion anyway. So when I ask Jen's brother something, his answer is worth more to me than her mother's. The same holds true here right? Because someone is in a group and was invited to a meeting that doesn't mean that they have equal say in things.

Here's another really good example... let's say that there's a weird recovery scenario or even corruption scenario that you come across. And you come up to me and Paul Tripp at a conference and ask us what our opinions are. I'd say that Paul's opinion in this case is worth far more than mine. Sure, I'm a qualified DBA and I've been around the block more than a few times, but Paul WROTE CheckDB so I think he carries a little more weight than I do. Even if it's something neither of us has heard of before, I'd still take his guess over mine.

So no, I'm not one of those who believes that everyone's say is as equally important as everyone else's. Hell, I don't even believe that everyone should even have a say. Some people are just not qualified to give opinions on a topic. In a restaurant you don't see the dishwasher getting a say in what specials go on the board, and in a dojo you don't see white belts chiming in on black belt promotions. So why is it that everyone in IT thinks they deserve an equal say just because they were invited to the meeting?

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:

http://twitter.com/MidnightDBA

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.

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:

http://twitter.com/MidnightDBA

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.

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:

http://twitter.com/MidnightDBA

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

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

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

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

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

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

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

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

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.