We're Going To Peeka!

Today we decided to get up early and drive out to beautiful Topeka, Kansas. I say "Beautiful Topeka, Kansas" not as a way of clarifying which Topeka, Kansas I mean...as in, not that homely, disheveled Topeka, Kansas that has just given up on itself...but more as a sort of polite form of address. I wouldn't want to say "Generally Unexciting and Agrarian Topeka, Kansas" because, well, it never hurts to be civil.

First up, the Combat Air Museum at Forbes Field.


The hangar was unheated and it was just under the freezing point, but the children were quite taken with the old-fashioned (probably very old) biplane toys. They don't make them like this any more. Or if they do, they are wooden "artisanal" toys that are sold online for unseemly sums.


Engine checks out. Little drafty underneath...


Gretchen exploring the interior of a Sikorsky Sea Stallion.


She also really enjoyed climbing the stairs and looking in each cockpit...this is a MiG21 Fishbed.


An F11 Blue Angels veteran:


And a F9 Panther.


Braving the cold to see the Lockheed EC-121, basically an early sort of AWACS plane built on a Super Constellation platform. Another MiG in the background.


Stay behind the chains, kid! Inside the 121.


An F14 Tomcat in retirement.


Trainer version of the A4 Skyhawk.


Thence to the Kansas National Guard Museum, a surprisingly good little museum for being somewhat obscure and charging no admission fee. The collection was pretty substantial.

Looks like a Spencer carbine or something similar. The boy approves.


Lahti 20mm AT rifle...immense gun, served the Finns in their various wars with the Russkies in the 40s.


Beautiful Mauser broomhandle. Assumedly a P.38 to the right.


A Maxim gun, recoilless rifle, and howitzer. Don't leave home without them.


LET ME INTRODUCE YOU TO 'MA DEUCE'!


Then "Ma Neufeld" gave us both a talking to for him touching the exhibit, so we went over here and I think he would have enjoyed playing with the flamethrower, but we refrained.


Second from top, son. En-Field. Say it with me.


Although he would have settled for the sniper variants of the Springfield or Garand.


These look like war trophies brought back by veterans...P.08 Luger and an ornate dagger with "Alles fuer Deutschland" inscribed.


Back in to the cold...the M60 Patton.


Self propelled artillery...M110 8-inch gun. Could lob a 200lb projectile 15 miles.


M109 howitzer, 155mm gun.


They also had an early M1 Abrams, and this is the M42 Duster, with twin 40mm anti-aircraft guns.


Then we got some lunch and headed north to the Topeka Zoo. First stop was with the orangutan exhibit which was highly entertaining. Given the weather, many of the animals chose understandably to stay in their indoor heated facilities. The orangutans were clever and amusing, a better exhibit of them than at the KC Zoo, I'd say.


Lions doing what lions in zoos do...not very much at all.


Gretchen and I called this chap over to the fence with our amateur deer call techniques.


Inside the main building, the hippos and four giraffes were keeping warm and stuffing their faces.


A beautiful, if somewhat niffy (and thus probably quite authentic) rainforest building had plenty of beautiful birds. And I do not mean that in the British slang sense I should say *ahem*.


The way this fellow was striding I could almost hear Barry Gibb, "well you can tell by the way I use my walk..."


A pair of black bears, one under the rock and the other in the hollow log, sleeping through the winter.


Smile!


This intensely Nordic child is so white he seems to have affected the color balance of the camera compared to the one with Gretchen...


And then, walking from the bear exhibit...oh be still my beating heart...IT'S THE BEAST!


Yes, a fox squirrel, that most prized trophy, the big game for squirrel hunters, a beautiful specimen to be sure, and here I was, lacking a season, a permit, and a rifle. Our eyes met in tense civility. He went his way, I went mine. We will have our peace, for now.

All in all a lovely little zoo and an interesting couple of museums, well worth the hour or so of a drive from Kansas City.

PASS Summit 2013 - Charlotte, NC

So I can't say enough good things about my employer. Well, I could say enough good things, but then I'd come off as an obsequious little toadie and my readers would ask themselves hard questions, like "am I the only person reading this blog?" and "why am I reading this blog?" Far be it from me to thrust my public into such a swirling existential vortex, so suffice to say, my employer was kind enough to shuttle me off to the PASS Summit for a week...Disneyland for DBAs.

After fixing a few problems noticed over the weekend this morning, I headed out to the airport to catch my flight. Once boarded, I settled in by the window, with a couple processed meat product marketing executives gassing away about meat portfolio strategies next to me. I did notice something rather disconcerting about the AirBus we were riding in...one generally doesn't like to see Bondo patching on the wings:


It's been a while since I've flown and unlike the meat-hawking aerial commuters next to me, the process still invokes a sense of wonder...the first kick of thrust from the turbines, the quavering steadiness as we rocket down the runway and the pilot holds it steady, and the joyous weightlessness as the plane breaks ties from terra firma. Halfway through the trip the cloud topography started to interest me. Almost as if we are looking down on white mountains, particularly those off in the distance.


Ironically here we can make out some actual mountains, albeit only what count for mountains in the Southeast.


The effect of watching the low angled sun glint off of this landscape of clouds is a sense of the Antarctic, somewhat. Then we barreled slowly down towards the snowy landscape.


Landed with precision in Charlotte and took the bus into Uptown. After chatting with the family a bit, I grabbed some sushi for dinner...not bad for takeout, but then I'm from Kansas City...


Tomorrow, a pre-con with the Brent Ozar Unlimited group. I shall endeavor to not lose my head and scream like a teenage girl in 1964 when the Beatles get off the airplane.

Next day, up early and over to the convention hall, chatting of this and that with fellow database professionals at breakfast. All day pre-con session in store today with the Brent Ozar folks. I of course remained complete calm and in control of my faculties while settling in before the session startedERMAHGERD LERK ERTS THE ERZERS!!!!!!!!!!!


*ahem* My composure restored, I settled in and it was a surprisingly useful session, filling in gaps of knowledge I already should have had and pushing me a bit further into new areas as well. Proper indexing was really a key point of the session...it delved here and there into other areas but indexing was the leitmotif, if you will, that kept resurfacing, and given that that has been, if not one of my blind spots, then one of my unfortunately somewhat myopic spots, this was excellent training for me and left me somewhat eager to get back to work and start tuning crappy queries and indexes.

And of course, my wife wouldn't let me hear the end of it if I had chickened out and didn't abase myself into queueing up to get the obligatory picture with the SQLebrities! Ermergerd BERNT ERZER!!!


Also thanked Kendra Little for reviewing my resume for one of their resume tuneup podcasts; since it helped land me my current gig, I hold them responsible. Whether that's blame or credit, who can say! Speaking of the SQL glitterati, apologize for the fuzzy picture, but that's PINAL DAVE of SQLAuthority. Basically the Google DBA...you need a script, you Google it, his blog is almost often right there at the top of the list.


So after the day was over I retired to the hotel and picked up some toiletries I had left at home. Soon to return though...


The evening reception was not the "scene" of a writing-focused, quiet chap such as myself. But it was the scene of a parsimonious bastard with a GSA per-diem for meals, so I was very happy to queue for tiny appetizer plates at the reception until I had basically fulfilled the obligations of a light dinner, and thence to the hotel. Was a happening thing, as nerdfests go...tons of people, and I'm sure just ramping up as I left.


Tomorrow, the real conference begins in earnest. Although I could fly home tomorrow and feel like I'd gotten (nearly) my money's worth.

The conference began in earnest Wednesday. I trotted over early to grab a quick continental breakfast in the hall before the keynote. Nice crisp note in the air at that hour.


After a brief, quiet bit of tissue-restoring in which I indulged my predilection to introversion by abstaining from the social mixing aspect, I sauntered over to the ballroom where the keynote was set to take place. Rather large it was, too!


Eventually others joined me and the place filled up. I've not been to a lot of conferences, but this seemed pretty high production value. Even so, I paid more attention to the liveblogging and twittersnark that was going on throughout the presentation. I almost lost it when the wifi seemed to collapse right as they went into the boring demo of new POWERPIVOTPOINTPOWERQUERYBIGDATA or whatever their BI buzzword is these days. Of course the wifi stress test was because the speaker decided to announce that CTP2 of SQL Server 2014 was released, and so everybody en masse decided that conference wifi was the best conduit for two thousand simultaneous downloads of the new CTP bits. The best commentary was on Twitter, and Brent Ozar's live-blog which, in keeping with his commentary from past years, doesn't pull punches when Microsoft blows smoke.

Great morning session on Extended Events from Erin Stellato. Lots of good insights and tools for changing how we do things now that Profiler is fading into the abyss of deprecation. The session was packed.


After lunch the vendors were well under way in the exhibition hall. I chatted with the Violin Memory folks and got to see our all-Flash SAN up close and personal. Violin gets my stamp of approval, I love having all our SQL on blazing fast storage. The Dell booth had the Ozar crew hired on to do a cool presentation, bits of which I caught, I think mostly about interviewing. Strange Doppelgänger Brent in the foreground.


Then on to a great session on indexing by Gail Shaw, who did a great job, but a sense of fatigue had me bailing at the midway point, intent on rewatching the whole thing again when I get the recordings. Had to remote back in to my work servers and play around with newly acquired query tuning techniques in there too, I'm eager to get back and start fixing these problems.

Trotted back to the hotel to do a little work and chat with the family, then eventually back to the convention hall. The hotel has some lovely gardening, several of these bonsai-like trees.


My visit to the evening vendor exhibition was a similar combination of cheapskate food acquisition and harrassment and abuse of our current vendors in light of their failings. Well, perhaps that is an overstatement, I gave lavish praise to the Violin folks, but Dell neé Quest signed up for a masters class in what's wrong with their Spotlight product, and they got it. Good news is, a lot of the issues they are already aware of and working on, so I was appeased. I trotted over to Red Gate on a whim (not our current vendor for anything) and asked them if they had a product to audit access/security on database objects that also considers access via cross-database ownership chaining. Suffice to say I lost the poor chap in a Red Gate shirt at "ownership chaining" but he kept trying, and failing, to understand, and at least wrote down my question to get back to me. I guess they can't all be DBAs working the booth. I hated working the booths in former lives...

Thursday was kilt day, I think in homage of Women in Technology (for which there was later to be a luncheon, I gather), but also just "because". After cursing myself for being so remiss as to neglect packing my latex miniskirt, I opted not to participate, and just headed over to the second keynote arrayed in traditional trousering. Got to chat with Bill Graziano, vainly (in both senses of the words) throwing out the suggestion that hey, maybe Kansas City could host this some time (when certain meteorological conditions prevail in the infernal regions, perhaps), and then talked with Brent Ozar a bit. Once the keynote got underway, Tom LaRock, who I meant to meet and chat with but didn't actually get a chance to, announced next year's Summit, back in Seattle:


Then a great, very cerebral keynote by Dr. David DeWitt on Hekaton.


Beautiful stuff, the twitter snark died down in genuine admiration and interest; the blogger table had Grant Fritchey, Kevin Kline, Erin Stellato, and Brent Ozar among others.


Continuing the trend of mentally challenging topics I walked over and got one of the scarce seats in Kimberly Tripp's session on filtered statistics for skewed data. Wonderful stuff, started out fairly simple and digestible for me, giving me some very distinct takeaways (I finally "get" the DBCC SHOWSTATISTICS histogram). Got very advanced and complicated as it went on, will bear rewatching.


Then another session from Jonathan Kehayias on the system_health extended events session, and I had to call it a day. I skipped the evening party and got to sleep by 7:30 Eastern, I was that tired, mostly mental exhaustion catching up to me.

Next day, I woke feeling rather better and attended Ozar's reveal session for his new stored proc; a nifty little number with a built-in 8-ball called sp_AskBrent. Queries DMVs and wait stats among other things, and gives a good snapshot of why a server may be bottlenecking or slowing down at the moment. Useful tool to add to the DBA belt.


After connecting up and working a bit, then grabbing lunch, I decided to catch the bus and head to the airport early. Seven hours early to be exact, but I was keen to get home and decided to roll the dice in case they were interested in bumping me to an earlier flight. No dice, or that is, I was unwilling to pay the $75 they required for such a purpose, so I just sat and worked. But I did notice this unfortunate name for a TSA Administrator:


Glad to be home with the family, maybe not quite as keenly happy to be back at work, but still, good to get back into routine. I'm eagerly awaiting the session recordings to harvest more value from this conference. Good times overall, and lots of brilliant insight being flung around with abandon, like fecal matter in a primate house.

Solving the Uptime Problem Part Two

My last entry detailed a need we have for a SQL Server specific uptime monitoring solution, both for outage alerts and for uptime reporting to management; I've discussed why we needed it and my earlier attempts to put something together, and today I'll detail the technical components.

Table Structure
The back end of this solution is extremely simple; on an instance we use for DBA monitoring utilities that also happens to house our Central Management Server repository, I have a small database I called DBAUtil, and within there, one table is needed for this solution, named OutageLog.  OutageLog has a identity column primary key, a ServerName varchar column that stores the hostname\instance of the server, a StartTime column and a nullable EndTime column.  StartTime has a default constraint of GETDATE() so when a record is inserted it uses the current date and time, and to facilitate updates the clustered index is on ServerName.

Populating a List of Instances
One of the main problems I detailed in the last entry was of having to hard-code yet another list of monitored servers, which would be assured to fall into unmaintained hell mere seconds after implementing.  What can I say, I don't trust -myself- to keep all these disparate lists maintained, much less any future successor.  In my mind, we have one place that is always kept accurate...our Registered Server list in our Central Management Server.  I kept hunting for a way to use the CMS querying functionality in a batch process, but I came up short.  Ultimately though, I used a combination of CMS and our DTSX package idea...if we could find the back-end of CMS and pull out a list of servers, we'd be on the right track.  Our CMS structure has an "ALL" folder that contains subfolders for each environment (Dev, Test, Model Office, Prod, and a few others) and these contain all currently live SQL Server instances.  So I needed a way to pull in not just the contents of one folder, but all servers in a folder tree structure.  Hearkening back to my days designing Bill of Materials reports for a manufacturing company, I knew the tool I wanted...the recursive CTE.  Here is the query I used.  I include two variables, a root folder name from which to start the recursion, and an "omit" value so that I could easily seperate Prod (root folder Prod, omit folder null) and non Prod (root folder ALL, omit folder Prod).

WITH RecursiveTree AS (
SELECT server_group_id
FROM msdb.dbo.sysmanagement_shared_server_groups_internal
WHERE name = @RootFolderName
UNION ALL
SELECT Child.server_group_id
FROM msdb.dbo.sysmanagement_shared_server_groups_internal AS Child
JOIN RecursiveTree AS Parent ON Child.parent_id = Parent.server_group_id
)
SELECT a.server_name AS Name
FROM msdb.dbo.sysmanagement_shared_registered_servers_internal a
INNER JOIN msdb.dbo.sysmanagement_shared_server_groups_internal b
ON a.server_group_id = b.server_group_id
WHERE a.server_group_id IN (SELECT server_group_id FROM RecursiveTree)
AND b.name != @OmitFolderName

DBAX-OutageWatch_Initialize.dtsx
This package is the parent package of the process:

Basically, after passing in the aforementioned parameters, the msdb database CMS tables are queried using the CTE query above, and the list of servers is passed into a variable.  Then it passes into a Foreach loop, incrementing for each value in the list.  I tried several possible solutions for the inside of this container, first trying the Execute Package task, but being unhappy with the serial execution (even when the out of process option is used) I started trying to use DTEXEC instead.  First the Execute Process task seemed to also operate in a serial fashion with DTEXEC, until I realized I could pass CMD.exe as the executable with DTEXEC (and the whole string of arguments, including the server name to pass to the child package), and everything started running in a quasi-parallel mode at this point.  Having almost 100 instances makes it important for us to parallelize this as much as possible; it is light in workload but the slow timeouts would kill us if operating in serial.

DBAX-OutageWatch_CheckStatus.dtsx
Then on to the child package, which accepts a server name as its input parameter.

 

There is an ADO.NET connection manager that uses the server name variable, via expression, as its connection.  This is how we dynamically build a connection to the target server.  The first step is literally nothing more than running "SELECT 1" against the server.  I allow more than one errors on this step because if it fails, I don't want the entire package to fail.  I have a success and failure constraint from this testing task.  If the logging fails, we go to the "Log Outage If Not Already" task, which goes to DBAUtil.dbo.OutageLog with the following query:

IF NOT EXISTS
(SELECT 1 FROM OutageLog WHERE EndTime IS NULL
AND ServerName = @ServerName)
BEGIN
INSERT INTO OutageLog (ServerName) VALUES (@ServerName)
END

Essentially this looks to see if there is an open outage for this query...if there is not one, then as the discoverer, it must be the first to log this outage and it inserts an outage record.
If however, the server responds to the SELECT 1 query, we go to the Close Active Outages task:

IF EXISTS
(SELECT 1 FROM OutageLog WHERE EndTime IS NULL
AND ServerName = @ServerName)
BEGIN
UPDATE OutageLog SET EndTime = GETDATE()
WHERE EndTime IS NULL AND ServerName = @ServerName
END

This similarly checks for the existence of an open outage; if one exists, now that the server is responding, it must close the outage by updating that record's EndTime value.  Otherwise, it does nothing; this being the most common logical path most of the time hopefully!

Watching the Watcher
As an extra failsafe I have another copy of the DBAUtil database and the DBAX-OutageWatch_CheckStatus package on a similar monitoring server across the wire in our offsite facility.  I have this package running to check for and log outages of a hardcoded specific server, the one that runs the above packages.  This way, we can get alerts on a failure of the monitoring server itself.  It functions the same as the above but instead of having the parent package and querying CMS I just push the a fixed server name straight into the child package.

About those Alerts...
The jobs that launch these packages are seperated into prod and non-prod, as I alluded earlier.  This allows me to set up slightly different logging frequencies, with staggered run times, and customized alerting.  The second job step does precisely that...alerting:

--Give the job some time to ensure the other job has
--completed its updates.
WAITFOR DELAY '00:00:10';
--Variable to place Body text in for notification email
DECLARE @Body VARCHAR(MAX)
--Generate Body text based on servers still down
--(null if no servers are down)
SELECT @Body = COALESCE(@Body, '')
+ 'Server ' + a.ServerName + ' is down for '
+ CAST(DATEDIFF(mi, a.StartTime, GETDATE()) AS VARCHAR(50))
+ ' minutes, since ' + CAST(a.StartTime AS VARCHAR(50))
+ '.' + CHAR(13)
FROM DBAUtil.dbo.OutageLog a
INNER JOIN
msdb.dbo.sysmanagement_shared_registered_servers_internal b
ON a.ServerName = b.name
--Outage is still active
WHERE a.EndTime IS NULL
--Outage has been for 5 minutes at least
AND DATEDIFF(mi, a.StartTime, GETDATE()) >= 5 
--Servers are in the prod folder in CMS
AND b.server_group_id = 6 
--If servers are down send an email with the @Body text
IF (@Body IS NOT NULL)
BEGIN
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'WhatevertheProfileIs',
@recipients = 'whoevertheoncallchumpis@yourcompany.com',
@subject = 'Prod Server Down, Wake UP!',
@body = @Body
END

Note that this is my third iteration of this script after some testing.  I got a few incidents where I received an email with no results, back when I was doing an IF EXISTS, selecting for an active outage, and then having a sp_send_dbmail query that does the same select.  Because our logging process is asynchronous, and because sp_send_dbmail puts its stuff into its own queue and is not guaranteed to operate within the bounds of one transaction, it is possible, and indeed happened in this case that the query decided to send the mail while an outage was logged, and then by the time the mail query was run, the outage was cleared, so it looks like a phantom read type issue.  I resolved this by placing a 10 second delay to allow extra room for outage clearing updates to occur, and generating the body of the email into a variable first, then passing that variable into the email alert, eliminating the chance of differing results (which happened when we had two instances of the same query happening).

So that's my system in a nutshell, in its infancy.  Plenty of work to go hardening it up I'm sure.  Maybe in a future post I'll detail out my Uptime RDL magic I did this week using Reporting Services.  It was fun to get back into SSRS after a long hiatus!  Fit like an old glove.  Or like OJ Simpson's bloody glove...well, whatever the case...

Solving the Uptime Problem Part One

As a DBA, I have to worry about uptime from two angles.  The first is one of raw survival.  I want to be the first to know when an instance decides to shuffle off this mortal coil and join the choir invisible.  I hate being told my servers are down; I absolutely want to know first.  And perhaps less critical but still important is being able to give KPI figures to the management.  So...how can we say what our SQL environment "uptime" is?

Our infrastructure chaps have all sorts of tools...SolarWinds Orion, SCOM, etc. that might be able to give us -server- uptime.  But they can provide that metric to management on its own.  What we are responsible for is a layer deeper: instance uptime.  When SQL Server is online, servicing queries.  If the server is turned on but the SQL service has shot craps, that is a SQL outage.  So we can't just do something simple where we log ping response or anything like that.

I've tried solving this in a handful of ways.  The first attempt was a painful and probably misguided debacle where I tried hacking the back-end of our existing SQL Server monitoring solution, Quest Spotlight.  If anyone out there has monkeyed with Spotlight to any extent they know of which I speak; the back-end databases are all but useless.  There was no possibility of using its history to accurately come up with a picture of uptime.  That said, I did build a fragile little alerting job based on the back end; the outage alerts from Spotlight fire immediately and often are false positives due to network connectivity issues and the like.  I wanted to be able to get an email that I trusted that indicated a hard outage, so I wrote a job that queried the back end for active outage alarms over 15 minutes, and emailed my personal email.  To this day this job is still occasionally saving my bacon.  When no one else is paying attention this job will catch an overlooked outage.

But the uptime calculation problem remained.  I grudgingly started to realize that what I wanted was going to have to be hand built, and so a few abortive attempts to build an SSIS package began.  I started with a fixed list of servers/instances in a table in a DBA utility database with an SSIS package that ran for-each container (for each server name) with a test connection (SELECT 1) to the server, logging in a table whether the instance was up or down.  Lots of strange error handling had to be done to accomplish this.

I had the following issues with this I needed to address:

1. The fixed list of servers was clunky and sure to be left unmaintained at some point.  I'd much rather use a more actively maintained list of current servers in our environment (we have just shy of 100 instances right now).
2. The For-Each container was a painfully iterative process...each one done one at a time, and if any failures were noted, it slowed the process by 10-20 seconds for the timeout to occur.
3. Logging an up or down of every single instance every few minutes is logging tons of data for what should ultimately be assumed; why not just log outages?

In my next blog entry I'll delve into the minutiae of what I actually did to solve this problem.  Til next time...

"You keep writing that query; I do not think it returns what you think it returns..."

So one of our BAs had a 4 hour long "insert into" query running today that completely pegged the CPUs on their box. The simplified version of the SELECT query involved:

SELECT *
FROM theFirstTable
WHERE value1 IN
( SELECT DISTINCT value1
FROM reallyTerrificallyLargeTable
WHERE value1 NOT IN
( SELECT value1 FROM anotherReallyBigTable ) )

So basically, the apparent logic is they want all the records for the first table, when value1 (an ID field) exists in this other spectacularly large table, referenced by subquery, but doesn't exist in another table (referenced with a further nested subquery).

However, the query doesn't work that way. I first got an inkling of this when I realized (while trying to rewrite the script for performance optimization) that there was no value1 column on anotherReallyBigTable (it was something like [Value One], we'll say). But amazingly to me at the time, it doesn't throw a syntax error saying invalid column!

Then we sorted out what was going on. Because the nested subquery is capable of referencing its calling query, the value1 from the last subquery is actually just the value1 from the first subquery. If the query author had named the tables (ie., "anotherReallyBigTable as a" and then referencing a.value1) the logic would make sense and the values returned would be accurate. As it is, it looks suspiciously like getting a set of values with the condition that that set of values does not exist in that same set of values is very likely to give you a result set of GOOSE-EGG. Probably not what the author had in mind, unless they just enjoy letting the CPUs getting a bit of exercise...

Poetry Slam

dev environment
screw it up if you want to
but i won't fix it

stop creating heaps
it isn't that hard to script
a clustered index

solid state storage
it's a nice feeling to have
a new bottleneck

no, developers
we're never gonna let you
query production

failed production jobs
no time to compose haikus
and yet here i am