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.

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. 

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.

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

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.

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!

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!