Monthly DBA Challenge

The monthly challenge has officially been retired.  It was a fun run, and I appreciate those of you who made an effort to do it.  What follows below is for legacy.

If you miss your fix, SQLStudies has an excellent recurring post called SQL Homework that I highly recommend you check out.  You can find it here:

https://sqlstudies.com/sql-homework/

____________________________________________

Each month I issue our DBA team a challenge.  If everyone completes the challenge we do a team lunch on me.  Often the task is what I consider a low frequency / high liability event – something the typical DBA might only do once or twice in their career, but if called upon would be highly critical to the business.  Sometimes the challenge might just be to learn a new skill or purely for fun; something odd.

If you’d like to play along at home below is each challenge.  Sorry, only members of the studio audience get lunch on me.  But you’re welcome to join us at Wayne’s Smoke Shack in Superior or G-QUE in Broomfield.  Best BBQ around.  Seriously, if you’re ever in the Denver/Boulder area you gotta check it out.

CHALLENGES:

April – May – June – 2016  SQL Launchpad

So, I have been terrible for the past couple months posting a challenge.  I could claim that I was busy, but who isn’t?  I can’t remember a time in my life when I wasn’t busy.  But to catch up, I have a good one for you.  SQL Server 2016 just came out.  If you install it with all the bells and whistles (April) you will find a new item in your SQL Server Configuration Manager.  It’s called SQL Server Launchpad.  It’s right there next to SQL Server Browser and SQL Server.  Go figure out what it’s there for (May), what it enables, and then do something with that (June).  See, it’s a nested challenge – good for covering a couple missed months.

---

March 2016 – HammerDB

HammerDB is an excellent tool for generating a realistic OLTP type workload to benchmark a new or test lab server.  It’s an open source project and works against SQL Server, Oracle, DB2, and a few others.  Able to mimic the load of many virtual users placing orders, making changes, and pulling data, it’s way faster than creating your own load scripts, and cheaper than subscribing to a cloud based load generator.  It’s not a point and shoot tool however, there is a bit of a learning curve.  Your challenge this month is to read the documentation and learn how to use HammerDB.  My own baptism by fire came while trying to generate load for my upcoming Pluralsight course on In-Memory OLTP.

February 2016 – TSQL FizzBuzz

I spaced this month’s challenge so in a rush I asked for input from the team.  JVL (@the_sql_troll) came to the rescue with how about TSQL FizzBuzz.  The challenge is two fold.  A FizzBuzz problem is a common programming interview challenge that asks a coder to print the numbers from 1 to 100.  Every time a number is divisible by 3, print fizz instead, when it’s divisible by 5, print buzz, and when it’s divisible by both, print fizzbuzz.  The real challenge however is to do it in as few lines of code as possible and in our case… TSQL… it should also be set-based – (temp tables are ok).  Don’t cheat and google.  Solve it yourself.  GO!  Then you’ll be ready for your next database developer interview.

January 2016 – TSQL Things You Probably Don’t Use

The challenge this month stems from a conversation with my team where we started talking about the SQL clauses some, any, all, and only_kinda_like.  Ok, one of those is made up.  But the rest are legit.  Challenge this month is pretty simple.  Write up some queries making use of some, any (which do the same thing), and all.  One of my 2016 goals is to up my knowledge in the area of data science and I’m finding these are more relevant there.

December 2015 – Database Categories

The theme for the challenge this month is inspired by Steve Jones post on NoSQL complaints.  My own comments were posted earlier today.  You challenge is to learn the difference between a document DB, a graph database, a key value store, and at least one other type such as object, tuple, multi-value etc.  Something other than what you already know – which is likely relational.  You’ll know you’ve learned it when you can justify a use case other than “joins are hard to conceptualize”.

November 2015 – Resource Governor

Your challenge this month is to setup a Resource Pool in Resource Governor and a related Workload Group that will limit resource consumption of a specific type of query under resource contention.  As an example of usage in our environment we found that certain queries against snapshots were starting to cause resource contention with synchronous mirrors, so one of the folks on the team built a group for all ad-hoc queries against that server.  Hint: the biggest problem is the UDF that identifies which pool work should go against.

October 2015 – Script Library

At the end of this month I’m speaking at PASS.  One of the items that plays an important role in my presentation is how important is manageable trustworthy script library is.  Scripts are the tools of your trade .  Your challenge this month is to assess or build your personal library.  Figure out how to manage it, maintain it, keep it current – know where each one fits, and find out if you trust each when the chips are down.  You need something you can rely on when queries are blocking and databases are corrupting.  Whether you manage it with TFS, or GIT, or a simple file system, – do what works for you, but get it put together.

September 2015 – Fragging VLFs

Your challenge this month is a short one.  Go find a big database and check and see how many VLFs its log file is broken up into and then determine if you feel the log file is fragmented.  If you don’t know what VLFs are, that’s a good place to start.  If you don’t know why fragmentation of the log due to said VLFs could impact performance, move there next.  Psssst, VLF stands for “virtual log file”.

August 2015 – Take a Vacation

So far I’ve had a great month.  I spent a week hiking the Colorado Maroon Bells – one of the most beautiful places on the planet – with my son.  I did some practice sessions for a couple 3 gun competitions that I have later this month, and finally, I spent a week with my brother in law riding my BMW Sertao adventure bike across forest roads and mountain trails from Steamboat Springs down to Telluride.  Taking a couple weeks vacation like that really rejuvenates the mind.  How does a 24-7 on call DBA get to take vacation?  For one, you gotta have a lot of automation in place – two you gotta have a great team.  I can’t help you with the team, but your challenge this month is to automate something you keep putting off, complete with monitoring, alerting, error reporting/handling, and the ability to run completely unattended.  Use SSIS, or PowerShell and windows task scheduler, or get creative in another way.  Need an idea, establish index maintenance, backups, log shipping – just make sure it can run un-attended.  Stop doing monotonous tasks.  Anything worth doing, is worth automating.

July 2015 – Theft, Cheating, and Corruption

If you haven’t already heard of it.  My friend and colleague Steve Stedman is doing a really fun challenge over on his blog.  He calls it the Database Corruption Challenge.  For the month of July I am going to “cheat” by “stealing” his challenge and sending you over there to complete one of his.  Pick one of the past challenges, or join in on the fun of one of the remaining one’s in the series.  Each is themed around database corruption – I’ll see you over there.  Good luck!

June 2015 – SQL 2016

Your challenge this month is to learn something about the upcoming release of SQL 2016 – that’s right, 2014 is already old news.  I met this challenge myself via the TechNet virtual labs where you can play with SQL 2016 right this minute.  I explored live query statistics.  There are also CTP releases available today where you can download and set up your own environment.  GO!

May 2015 – TDE Encryption and DR

This month’s challenge was inspired by real life events.  The challenge is two-fold.  First, wire up a database with transparent data encryption (TDE).  Than, pretend that something horrible happened to that database and bring it’s backup online on a totally different instance.  You’re going to need to do some key management.

April 2015 – Implicit Conversions

Do you know what happens when you combine a date formatted like a string with a plus sign against an int in a select statement?

select '02/01/2015' + 1 as tomorrow

You get an error! (were you expecting 02/02/2015?)  Granted however, sometimes code like that is unpredictable.  Your challenge this month is to become familiar with the precedence of implicit conversions.  What converts to what?  Prove it with some code samples (that work).  Before you go scouring the net, check books online.

March 2015 – CLR

The challenge this month is CLR.  Write some code, compile it into a DLL, load as an assembly, use it from within a TSQL batch.  The actual CLR can be as simple or as complex as you want.  The point is to get familiar with the process of compiling, loading, supporting and troubleshooting an assembly for realsies.

February 2015 – Security Puzzler

Your mission, if you choose to accept it.  Create a stored proc on a database that runs a select query against a different database (same server).  The challenge?  Create a security setup that allows a specific user to have the rights necessary to execute the stored proc and obtain results without giving that user any rights whatsoever on the second database.  In other words – the user shouldn’t be able to connect or query the table on the second database directly – only through the stored proc on the first database.

January 2015 – Offset and Fetch

Have you ever used offset and fetch?  This is a very handy addition to SQL Server that users of MySQL and other SQL Languages have been asking for, for a long time.  Your challenge this month is to write a query that uses offset and fetch against a dataset that is at least 1000 records long.  Specifically, you’ll have met this challenge when you can produce the third set of 50 in order of something from the table.  Hint, try it against one of the AdventureWorks tables.

December 2014 – MIA Temp DB

This challenge is inspired by the newest DBA on our team, who only a couple weeks on the job had to address a server whose Temp DB disks suddenly disappeared.  How do you get a database back online when the entire drive it uses for TempDB is gone??  Figure it out.

November 2014 – Network

This month is an easy one (for some).  The challenge is to reach out and interact with a DBA you’ve never met before.  Exchange ideas and some form of contact info.  You can complete this challenge on stack exchange, linked-in, twitter, or (heaven forbid) in person at your local PASS Chapter meeting.  If you attended PASS, chances are you’re done for the month.

October 2014 – Physical Joins

Write three queries.  One that would benefit most from a merge join, one that would benefit most from a hash join, and one that would benefit most from a loop join.  Prove with an execution plan that the proper join type was used.  Purpose of this challenge is to understand how SQL physically joins record sets and what circumstances dictate when each approach is most beneficial.

September 2014 – system_health event session

The challenge this month is to take one of your important boxes and find the most worrisome thing that happened in the past 24 hours (or longer if you need more history) in the system_health trace (event_file).  The point here is to be familiar with what is in there so that if something was worrisome – you’d recognize it.

August 2014 – Choose Your Own Adventure

Each of the members of our team was so completely consumed in the month of August that we decided it would be a choose your own adventure month.

July 2014 – Write a query that uses over and partition

This is a pretty straightforward challenge.  The point is to make partitioning a comfortable part of your existing t-sql vocabulary.  This can solve a lot of problems, especially with regards to re-factoring old code that was written prior to SQL 2005.

June 2014 – Hekaton (part 2)

Our team failed to complete much on this so we just extended it to a second month.  May was kind of busy for us.

May 2014 – Hekaton

It’s official.  SQL 2014 is out.  The challenge this month is to create an in-memory table.  Create an efficient way to load data into it.  Run some queries against it.  Figure out what DMVs are related to it.  In short – get familiar with by means of a hands on project.

April 2014 – Profile Something; with Extended Events

Chances are you have used profiler or trace to do some tasks as a DBA.  The challenge this month is to take something your comfortable doing with profiler and/or trace and do it using extended events instead.

March 2014 – PowerShell Something

An open ended challenge this month.  Use PowerShell to do something involving SQL Server.  The script must include at least one variable, perform a task “on the pipeline”, be able to run unattended (i.e. agent or task scheduler).

February 2014 – KILL with the DAC

The challenge this month is to use the dedicated admin connection and sqlcmd to log in to a server, identify a blocking session, and kill that session.  I’m looking for actual command line sqlcmd – not SSMS in sqlcmd mode.

January 2014 – Restore the Master

Restore the master database on a server with at least one user database.  Bring the server back online with all settings, user databases, logins, rights, and behaviors intact.