Friday, October 26, 2012

Changing the operator on a job

So I was in the position today to change the operator on a bunch of SQL Agent jobs here. Since the server was very remote, I didn't want to clickety-clack through it. Not that I'd do it that way anyways, but either way, I used Powershell.

Friday, October 19, 2012

Changing job ownership across all servers in a CMS

Central management servers (CMSes) are awesome. Powershell is awesome. By their powers combined, Captain Planet great things can be done. So, I decided it'd be a good idea to remove myself as job owner on all of the SQL Agent jobs that I've set up at my current client. Rather than click through each server and figure that all out, I wrote a small Powershell script for it. 

Tuesday, October 16, 2012

Cardinality estimates over a linked server

So, I was at SQL Saturday #149 listening to Joe Sack (blog|twitter) talk about how many different ways cardinality misestimation can really wreck the query optimizer's day. One part in particular stuck out for me, though. Specifically, if you run a query over a linked server and whatever principal you end up with on the "remote" side doesn't have either sysadmin, db_owner, or db_ddladmin, table statistics aren't returned to the "local" query optimizer and so it starts guessing (and poorly). Joe and I talked a little bit about it and he pointed me to blog posts by Linchi Shea and Benjamin Nevarez. I thought that we should be able to reduce the required permissions by doing some module signing. So I tried. I'm happy to report that it's possible, but it does involve modifying the resource database (ever so slightly). So if that's the sort of thing that makes you squeemish, stop reading now.

Tuesday, October 9, 2012

New CREATE CERTIFICATE syntax in 2012

While setting up a demo for an upcoming post, I stumbled across a new bit of hotness in CREATE CERTIFICATE. Specifically, you can specify the bits of the certificate right in the T-SQL (much like you can with assemblies). Why is this awesome? There are a couple of scenarios where you need to create the same certificate in multiple places. Off the top of my head:
  • Signed modules that need a principal both at the server and database level
  • Service broker
  • Encryption by certificate in multiple places

Thursday, August 16, 2012

The evil of ISNULL()

So, this has happened to me a couple of times now, so I figured I'd share. The scenario is this: ETL developer comes to me and says "my ETL is running slow". When I look at what they're doing, they have something like this:

select *
from dbo.myTable a
inner join dbo.myOtherTable b
   on a.key = b.key
where isnull(a.col, '|') != isnull(b.col, '|')
Pardoning the "select *" (that's just me being lazy in typing the example) they're trying to find where the data has changed in a nullable column. Unforutnately, ISNULL isn't magic, so this leads to a SARGability problem. Said another way, it has to table scan both tables to figure out how to satisfy the query! While it's a little more verbose to write, the query optimizer is better able to deal with this:

select *
from dbo.myTable a
inner join dbo.myOtherTable b
   on a.key = b.key
where ( (a.col != b.col) or 
        (a.col is null and b.col is not null) or
        (a.col is not null and b.col is null) )
Now we've given the optimizer a better chance. The lastest time I've pulled this trick out of my bag, I was able to take a process that took ~40 seconds to one that took about 2. Twenty-fold increases in speed aren't anything to sneeze at. So remember this trick when you're trying to compare nullable columns and it will serve you well.

Wednesday, August 15, 2012

I'm speaking at SQL Saturday #149!

I got confirmation this morning that I'll be speaking at SQL Saturday #149 at the University of Minnesota (my alma mater). Hope to see you there!

Thursday, July 26, 2012

Neat SSMS trick

One of the developers asked me this morning if there was a way to do a replacement in Management Studio where you look for a constant string at the beginning of the line, followed by arbitrary stuff, and essentially append something else to the end of it. The use case was he had a stored procedure that was called in a bunch of places with different parameter orders that he needed to add a couple of more parameters to. Both he and I knew that SSMS has support for regular expressions, but didn't immediately know how to pull this off. Surprisingly, it's pretty easy. In the "Find what:" box, you put in something like "exec sp_myProc{.*}" and in the "Replace with:" box you put in "exec sp_myProc\1 @newParm1='blue', @newParm2='fragrant'". The "\1" refers to the first capture group within the regular expression. I found the regex reference for Visual Studio to be most instructive. You can find that here. Have fun!

Wednesday, July 18, 2012

Anti-pattern: identity insert

Something that I often see when a new table that holds reference data is added to a database is something like:
CREATE TABLE [myApp].[Status] (
    [StatusName] NVARCHAR(20) NOT NULL
INSERT INTO [myApp].[Status] ([StatusId], [StatusName])
    (1, 'Submitted'),
    (2, 'Accepted'),
    (3, 'Denied')
The typical justification that I hear is that the ID values need to be the same across environments (i.e. development, QA, production). I say that if that's the case, don't define the table with an identity column. Identity columns are for when you don't care what value that column takes for a given row. So, if every insert to a given table looks like the above, consider dropping the identity property from the column and save yourself the hassle of dealing with IDENTITY_INSERT.

Monday, May 14, 2012

On taking backups and checking them

So I just watched a story about how the movie Toy Story 2 was almost lost due to user error. Check it out here. Moral of the story: backing up isn't enough. You gotta test 'em, yo. They got lucky and didn't lose 30 man-years of work. The old adage goes "better lucky than good". If you have a choice, be both. Most of us don't, so choose to be good.

Friday, May 11, 2012

So crazy, it just might work

Ever since I got into computers professionally, I've been about extending the tools that were available in order to make complex behavior easy. Back when I did perl (still a guilty pleasure and go to for quick text parsing), I wrote a couple of libraries for internal use that treated a non-newline delimited file format as though it were newline delimited. I suppose that bears a little more explanation. The format in question began every line with a two byte header that said how long the line was. The idea being that since the computer didn't have to seek to find the end of the line. Premature optimization perhaps, but it's what we had. So as a support guy, I'd typically have to "fix" files that got bogus data in them by whatever mechanism. Since most (if not all) tools at my disposal treated the notion of a line as a newline delimited one, I had to come up with something different. The details are a bit fuzzy now, but perl allows you to create code that overrides the typical semantics of any data type in the language. Since filehandles were included in that, I wrote code to treat lines how they were supposed to be treated. Which allowed me to get on with my life. Fast forward to today. I was recently doing some one-off reporting for the business and thought "man... a treemap would be a really great way to present this data". But alas, I didn't find an easy way to do this in SSRS or otherwise. But I've been doing some thinking and CLR is kind of the glue that allows one to extend SQL Server. So I got to thinking that perhaps CLR aggregate function could be written that leverages the geometry data type to do what needs doing. I'm no C# wizard by any means, but I think I'm going to start tinkering and see what I can do.

Thursday, May 10, 2012

Changing the database of all synonyms - redux

In a previous post, I posted some code that should change the synonyms that point to one database and point them to another. I tried to use that code today and it didn't go so well. Specifically, powershell gave me some guff about changing the collection in the foreach loop. Here's the workaround that I came up with:
$server_name = '.';
$db = 'AdventureWorks';
$old_syn_db = 'old_db';
$new_syn_db = 'new_db';

$server = new-object Microsoft.SqlServer.Management.Smo.Server $server_name;
$db = $server.Databases[$db];

$old_synonyms = @();
$new_synonyms = @();

foreach ($syn in $db.Synonyms) {
    if ($syn.BaseDatabase -eq $old_syn_db) {
        $s = new-object Microsoft.SqlServer.Management.Smo.Synonym
        $s.Schema = $syn.Schema
        $s.Name = $syn.Name
        $s.Parent = $db
        $s.BaseObject = $syn.BaseObject
        $s.BaseSchema = $syn.BaseSchema
        $s.BaseDatabase = $new_syn_db
        $old_synonyms += $syn
        $new_synonyms += $s;

foreach ($syn in $old_synonyms) {

foreach ($syn in $new_synonyms) {

Hope that helps.

Monday, April 16, 2012

Changing the database of all synonyms in a database

So, I came across a problem today at the client where a database had been restored from production into the QA environment, but there were synonyms in the database that pointed to an external database. So, the restored database's synonyms were pointing to the wrong place. I figured this would be easy enough. And it was... I just wish it had been easier. Specifically, I with the following had worked:

$server = new-object Microsoft.SqlServer.Management.Smo.Server '.';
$db = $server.Databases['AdventureWorks2008R2'];

foreach ($syn in $db.Synonyms) {
    if ($syn.BaseDatabase -eq 'OldOtherDB') {
        $syn.BaseDatabase = 'OtherDb';
But it doesn't. In theory, SMO could make it happen by dropping and re-creating the synonym, but it doesn't. I don't know why. Oh well. I bend the computer to my will, not vice versa. So here's what I came up with:

$server = new-object Microsoft.SqlServer.Management.Smo.Server '.';
$db = $server.Databases['AdventureWorks2008R2'];

foreach ($syn in $db.Synonyms) {
    if ($syn.BaseDatabase -eq 'OldOtherDB') {
        $s = new-object Microsoft.SqlServer.Management.Smo.Synonym
        $s.Schema = $syn.Schema
        $s.Name = $syn.Name
        $s.Parent = $db
        $s.BaseObject = $syn.BaseObject
        $s.BaseSchema = $syn.BaseSchema
        $s.BaseDatabase = "OtherDB"
Even with all of this, I love synonyms. They're implemented at my current client on my suggestion. Before, they'd hardcoded the external references into stored procedures. Which makes cross-environment restores "fun". After I ran the above script, everything was back to normal. In a lot less time than if I'd had to have updated all of the stored procedures by hand.

Update: A newer version can be found here.

Friday, April 6, 2012

Automate everything... within reason

I have a confession: I'm lazy. But I'm also a big fan of Larry Wall (creator of perl) who is often quoted as saying "laziness is a virtue". So when I read an article on SQL Server Central today, I thought it would either affirm my world view or show me a new trick. To my surprise, I didn't agree with it. For me, automation comes down to return on investment (ROI). For instance, why would I automate a task that done the "manual way" happens once a year, takes me 30 seconds to do, anyone in my field would know how and when to do it, and would take me 10 hours to automate and test? Aside from exercising skills that I might not otherwise use (which, by the way, I think is important), the ROI for automating that process is longer than my career. The math is simple: there are 1200 30-second intervals in 10 hours. So, the payoff (in terms of time) only happens on the 1201st and subsequent executions. Of course, that discounts any changes to the process and system(s) involved. My point is, figure out what you're getting out of your automation efforts. Otherwise, you run the risk of committing what Larry calls "false laziness".

Sunday, April 1, 2012

New date/time functions in SQL 2012

So I was just doing a little reading on some new features in SQL 2012 and the new temporal functions sparked my imagination. I can't count how many times I've wanted an easy way to be able to do something like find the first of the month given only the year and month number. Usually, you jump through some hoops to do that in any version up to and including SQL 2008R2. But now, it seems that they've added a family of functions that let you pass in each date part as an argument and it spits out the appropriate data type. Going back to the first of the month example, one can now write "select datefromparts(year, month, 1) from table" and it'll spit out result set with the given firsts of the month. Sometimes, it's the little things.

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) {
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.