Friday, March 30, 2012

On getting to the root of the problem

So, I read an article posted on Slashdot this morning that I found very interesting. It deals with a bad bit of culture at NASA and how it led to both the Challenger disaster and the bad mirror on the Hubble telescope. You can read it here. My takeaway from it is that sometimes the real cause of a failure isn't the obvious one. My grandpa had a saying: "Once is coincidence. Twice is happenstance. Three times is enemy action.". The way I understand that is that if something is happening over and over again, it's time to start looking for why it's happening and deal with it. The battle cry is usually that the grunts don't have time to because they're too busy fighting fires. My opinion is that at that point, it's time for the manager to step up and dedicate resources to eliminating that problem. Because if your nominal state is one of treating the same symptoms over and over, you won't have time to fix new problems. And there will always be new problems.

Tuesday, March 27, 2012

An SMO bug with respect to primary key extended properties

I saw this post by Jamie Thomson and I couldn't help but play "name that tune". Specifically, I figured that it wouldn't be too difficult to do the same in powershell without having to worry about collations, double quoting issues, and the like. It turns out that I was wrong. I discovered a bug in SMO where if you try to retrieve the extended properties for an index that is a primary key, SMO fails to do so. I've logged a Connect item on it.

Thursday, March 22, 2012

Scripting out statistics

I recently read an excellent post by Kevin Kline regarding scripting out histograms from one database and applying them to another.  What would have made it better is if there was a way to automate it.  And so I pulled out powershell and got to work.  Here's what I came up with:


$opts = new-object Microsoft.SqlServer.Management.SMO.ScriptingOptions;
$opts.OptimizerData = $true;

$server = new-object Microsoft.SqlServer.Management.SMO.Server ".";
$database = $server.Databases["AdventureWorks2008R2"];

foreach ($table in $database.Tables) {
    foreach ($stat in $table.Statistics) {
        $stat.Script($opts);
    }
}
 
 
You'll probably want to direct that to a file (either by setting the scripting options appropriately or redirecting the output from the command line), but there you go. If you're feeling frisky, you schedule that to happen every so often and ship it to your dev environment where it gets applied.  Also, if you want to do it for all the databases in your production environment, change the line that sets $database to get all the databases into a variable and add one more foreach loop around the ones that are already there to loop over them.

First Post!

Okay... so I hate "first post" messages in forum discussions, but it seems appropriate here.  Let's just call this a re-branding.  I used to post over at my other blog, but the branding wasn't awesome.  So, I'm going to start throwing SQL content up over here.  Hope I can show you a trick or two to make your life easier.