Getting the SQL Server Install Path

One of the challenges that comes along with a large number of SQL Servers, that aren’t always built out in a standard fashion, is that you run into problems whereby correct AV exclusions are not set, leading to performance problems (and other issues).

Going through and quickly grabbing the location of the SQL Server executable, or the directory in which it resides can be cumbersome. Doing it once is a pain, doing it 30 times multiplies that pain by a factor of about 400.

A quick query run against your servers (and done against server groups) can quickly return results for your machines and help you get on your way again.

Continue reading “Getting the SQL Server Install Path”

SSMS Results: A Cautionary Tale

As a person who messes with SQL Server there is a pretty strong probability that you are using SQL Server Management Studio (SSMS). It is a very full featured tool, but does have a couple of problems and quirks (like crashing on large result sets due to it reaching the maximum memory allocation for a 32bit process).

The other day I came across another quirk, maybe even a bug, which can happen when returning and working with XML results, which could lead you to copy incorrect data…

Continue reading “SSMS Results: A Cautionary Tale”

ISNULL & Data Length Handling

Some folks are not fans of having NULL values in your data. I am not much of a fan myself, however sometimes (all the time) they are a reality in which we have to deal. When working with NULL there are lots of strange behaviors you have to be sure to manage, for example WHERE x = NULL gets you nothing, whereas WHERE x IS NULL gets you the data you want (which will still depending on your ANSI NULL settings). Not much fun to be had with NULL.

Something else to be careful of is managing queries where you might look at a value in one column, and if it’s a NULL, return the value from another. For example:

Continue reading “ISNULL & Data Length Handling”

Life as a DevOps DBA

A few weeks ago Grant Fritchey (b|t) posted about DevOps, the DBA , and the word “No”. Go ahead and read it, I’ll wait right here.

Welcome back. As you’ve just read, Grant ranted a little on the concept that the DBA does nothing but say no to requests, and that people still live the belief that it’s their default response to anything.

The whole concept of the DBA saying no really comes about because the people that are responsible for getting up in the middle of the night, because somebody did something crazy that broke the site, are not fans of a lack of sleep. Hey, if you knew that there was a not-null probability of losing an evening, or a weekend, wouldn’t you be hesitant to allow¬†changes as well.

I spent many years as that gatekeeper. The one that didn’t want to lose that time. I didn’t want to spend my time saying no, but I knew for darn sure that I wanted to understand what was going in and what the potential impact was going to be. After all, I’d rather spend my time figuring out how to simplify managing my enterprise than trying to figure out what was broken with the latest release.

Continue reading “Life as a DevOps DBA”

SSIS Job Steps Failing Pending Execution

The other day a few SQL jobs were failing when attempting to execute SSIS packages. As usual a cryptic error was surfaced up to the job log “Execution Status: 5”

Great, really helpful. So I went to look at the package logs. Those actually didn’t show any failures, rather they just showed “Pending Execution”. At this point I knew it was going to be a long day. Continue reading “SSIS Job Steps Failing Pending Execution”

FusionIO and the Go Faster Button

Nobody has ever said that FusionIO cards were slow (because they aren’t). Especially if you compare their performance to regular spinning media, or high performance SANs. After all, no SAN will allow you to measure storage write latency in microseconds.

Anyone that has had their database reside on a FusionIO card has had nothing but good things to say about them. The only thing is that a lot of people are probably not making the most out of the cards that they have. FusionIO cards actually have an artificial performance restriction put on them, and can go even faster than they do now.

Continue reading “FusionIO and the Go Faster Button”