Art of the DBA Rotating Header Image

March, 2014:

#tsql2sday 52: Stop depending on “it depends”

depends-on-who-is-askingIt seems to be a standard practice for data professionals to say “It depends” when asked for suggestions, recommendations, or outright solutions.  This isn’t surprising, because in the “magic” world of technology the actual solution for a problem usually requires some very specific information and that any number of solutions could work.  Add in to this the nature of our world usually puts you at the whip end when things break, so we are natuarally hesitant to draw a line in the sand.

Stop it.

Let’s face it, much of what we do is fairy dust and moonbeams to those outside our industry.  They don’t understand what we do, just that we make it happen.  And that’s what managers, directors, veeps, and c-fillintheblank-os want when they ask us questions: How can we make it happen and/or how to we make the broken thing go away.  The problem with saying “It depends”, especially in situations like that, is that it sounds more like “I don’t know” and that’s not what you’re getting paid for.  You’re the technical expert.  Whether you’re a consultant or a full-time employee, a seasoned veteran or a fresh-faced n00b, you’re being paid to understand the technology and be able to implement solutions.

Many data professionals, however, are afraid to take this sort of stand.  A lot of this stems from the tremendous amount of risk involved, particularly when tied in to the heavy responsibility we tend to bear.  Our job is to protect the company’s data assets and if we screw up it’s not just our head, but it could be seriously damaging to our company.  So we like to hedge our bets.  Unfortunately, a lot of people in our profession will use the phrase “It depends” as a dodge because they’re afraid of completely taking on that responsibility.

Ultimately, they’re afraid of failure.

It’s a common mantra in life coaching that we can’t be afraid of failure.  Failure is what we learn from and how we grow.  Make a mistake, analyze it, and grow from it.  We’re only human, however, and screwing up is scary.  We don’t want to look bad and we don’t want to get in trouble.  That doesn’t help the people who are looking to us for help.  This is when saying “It depends” as a shield turns into a roadblock, hindering both you and your organization from getting anywhere.

So what do we do about it?  Next time someone asks you for a technical opinion and you don’t have enough detail, ask for that detail.  What’s the RTO/RPO? How quickly does this need to perform?  What’s our budget?  Questions like that to refine the answer.  Maybe outline a solution, but caveat it with qualifiers, such as “I’d probably put tempdb on SSDs, but that assumes we can afford that sort of hardware.”  Maybe there’s a better way to do it, maybe you’re just wrong.  But it’s ok to make a mistake, as long as you’re not making the same mistake.

Most of all, I’d suggest to simply remove “It depends” from your vocabulary.  There’s a dozen ways to get the point across that a solution will require some thought and planning, but I found that when I was forced to use something other than this two-word quip, I had to work harder on my response to really explain the dependencies for a solution.  And the non-technical folks around you are ok with that.  Sure, they don’t want you talking above their head, but they also need to know why things need to be done a certain way.

Some folks might call this leadership.  Others, sticking your neck out. Still others might call this being unnecessarily risky.  I call it doing your job.  Like I said, companies pay us to know our field, it’s time we act like it.  Data is our world, very few people live it in and understand it the way we do, so own that knowledge and champion it.  And stop hiding behind that phrase, “It depends”.

(This month’s T-SQL Tuesday, a grand tradition started by Adam Machanic, is being hosted by Michael J. Swart(@MJSwart).  Great topic choice, sir!)

A PoSH way to skin the cat

Today my friend John Morehouse(@SqlrUs) posted a handy T-SQL script to configure your SQL Server memory based on some rules provided by Jonathan Kehayias(@SQLPoolBoy).  This is a great script and helps automate a lot of the work a DBA needs to do when setting up a new instance. Hopefully, you know by now about my passion for automating and standardization.  However, when reading John’s post, I had the following internal conversation:

“Wow, self, John’s got a neat little script here.”

“Yeah, but T-SQL is so passé.  Everyone does it.  Plus, not sure how easily we could add that into a server deployment script if we wanted total hands-off-ness.”

“Self, I know what you mean.  Nothing wrong with it, it just doesn’t fit my own particular….oh…particular….”

Idiom?

“Yes!  Idiom!  Hrmmm…how about a Powershell script?”

“A Powershell script?  Brilliant!”

And so I sat down and cranked out the following script, which essentially does what John’s script does, just using Powershell.

</pre>
<#
.SYNOPSIS
 Configures a SQL Server instance per the Jonathan Kehayias' guidelines.
.DESCRIPTION
 This script will configure your SQL Server instance per the guidelines
 found in Jonathan Kehayias' blog post: http://www.sqlskills.com/blogs/jonathan/how-much-memory-does-my-sql-server-actually-need/
 The rules are:
 - 1GB for initial OS reserve
 - +1GB per 4GB server RAM up to 16GB
 - +1GB per 8GB server RAM above 16
.PARAMETER
 -instance SQL instance name, i.e. localhost\SQL2012, DBASERVER01
 -apply Switch parameter, call if you want to actually apply the changes. Otherwise, a report will be produced.
.EXAMPLE
 Configure-SQLMemory -instance DBASERVER01 -apply
#>

param([parameter(Mandatory=$true)][string] $instance
 , [Switch] $apply
 )

#load SMO
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null

if($instance.Contains("\")){
 $sqlhost = $instance.Split("\") | Select -First 1
 }
else{
 $sqlhost = $instance
 }

#set memory variables
$totalmem = (gwmi Win32_ComputerSystem -computername $sqlhost).TotalPhysicalMemory/1GB
$sqlmem = [math]::floor($totalmem)

#calculate memory
while($totalmem -gt 0){
 if($totalmem -gt 16){
 $sqlmem -= [math]::floor(($totalmem-16)/8)
 $totalmem=16
 }
 elseif($totalmem -gt 4){
 $sqlmem -= [math]::floor(($totalmem)/4)
 $totalmem = 4
 }
 else{
 $sqlmem -= 1
 $totalmem = 0
 }
}

#if not in debug mode, alter config. Otherwise report current and new values.
$srv = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Server $instance
 "Instance:" + $instance
 "Max Memory:" + $srv.Configuration.MaxServerMemory.ConfigValue/1024 + " -> " + $sqlmem
 "Min Memory:" + $srv.Configuration.MinServerMemory.ConfigValue/1024 + " -> " + $sqlmem/2
if($apply){
 $srv.Configuration.MaxServerMemory.ConfigValue = $sqlmem * 1024
 $srv.Configuration.MinServerMemory.ConfigValue = $sqlmem/2 * 1024
 $srv.Configuration.Alter()
 "Configuration Complete!"
 }
<pre>

Now it should be noted that I’m not going to tell you which of these scripts are “better”. Neither is, really, they’re just different ways of approaching the problem. And that’s the fun of it. When working in technology, cats can be skinned in multiple ways, we just have to use something. Whether it’s T-SQL, Powershell, C#, or some other thing, the point is that you want scripts to automate your work.

What was also cool about this was it gave me another exercise to practice Powershell.  After all, practice makes perfect and when learning something, any excuse to make me use it is good.  John and I actually had a conversation shortly after I wrote the script about these sort of opportunities, and we’ll continue to look for ways to challenge each other for learning.

Edit:  Well, that was embarrassing.  Amazing how sometimes you can get the math little screwy.  I’ve updated this script as of 9:44 PM MST to correct me having the math backwards.  If you grabbed/reviewed this script before hand, please make sure up grab the updated version.