Friday, January 29, 2016

[Event Notifications] Simple alert to make sure everything's working.

When I built it, I thought I'd added enough monitors to make sure it didn't break.  Nope!  Someone dropped an endpoint, so things backed up silently for a day and a half.

Add this as a job step on your repository.  For the time, YMMV - 30 is actually too short for me, but 60 should work.

Wednesday, January 6, 2016

[Presenting] SQL Saturday Austin 2016, here I come!

Woohoo!  Come join us on January 30th, 2016, at Wingate Round Rock, for another awesome SQL Saturday!  I presented last year on SQLWatchdog/Event Notifications, and I'm thrilled they chose me again.

This year: WMI! 

Practically?  EVENT LOGS AND MORE.  Your SQL Server runs on a machine.  Be it virtual or physical, there's stuff going on there that you should know about.  Let's collect them.  And then, since we're building this process out of it, what else can we do?  A BUNCH.  Fix your server's Power Plan!  Monitor for file changes! Check logins! SQL Server Changes! More! And it's all stuff I use in production, so set it up Monday!

There looks to be 36 different sessions, and I see some friendly faces (Tim Mitchell, John Sterrett, Amy Harold) some people I've wanted to see speak (Thomas LaRock, Robert Davis), and some sessions that just sound seriously cool (Regexp, "Analyze your query plan like a Microsoft Engineer", "Azure Data Factory"). It'll be a blast!  And it's FREE. 

Thursday, December 10, 2015

[T-SQL] Basic Pivot automation script

There's a guy out there who has a great dynamic pivot CLR.  This is a much simpler version.  I _think_ this is original to me.  I've been using it in various projects on this blog, but I don't see where I posted just this code.

Say you have records from your monitor that happens to Track EventLogs.
We'll call it EventLog_Tracking for argument's sake (hint hint and want to look at trends over time.

First, you'd need a grouped set.

SELECT sourcename, CONVERT(DATE, TimeGenerated) AS the_date, COUNT(*) AS the_count
INTO #temp
FROM EventLog_201512
WHERE sourcename LIKE 'microsoft-windows%'
GROUP BY SourceName, CONVERT(DATE,TimeGenerated)
ORDER BY CONVERT(DATE,TimeGenerated), SourceName

And you want it to look like...

Here's the base pivot code.  For this example, replace down with "the_date" and across with "sourcename"

If you want a name or something instead of the number, then do something like "max(yourname)" instead of "sum(the_count)"

[Hey Kids] @@servername and BCP and ultra-long statements.

Hear me now and listen to me later.

When you take that ultra-complex 6000 character string for BCP and wonder why it's getting truncated to 4000 (which SSMS has problems showing anyhow), remember that @@servername is sysname, which is an alias for Nvarchar(256)... and adding it to a varchar converts the whole string to nvarchar. 

I'll never get those hours of my life back.  Be smarter than me.

Wednesday, December 9, 2015

[System_Health] pulling all the data from the saved-to-disk files

TL;DR - imports the 3-4 system_health files that are automatically saved to disk.

I had a problem this week with a deadlock.  I went looking in the ring buffer for the system_health session, but it had already aged out. 

So, remembering that the system_health session has a file target, went looking for it (Jason Brimhall has code for it - thanks!).  The file went back further but not all the way.  Since the event definition says to keep a couple of older files, I use the current file to find the path, list all the files in that path (xp_dirtree; doesn't require xp_cmdshell), then walk through and import all of them. 

You could simplify it a lot and use fn_xe_file_target_read_file and tell it to read all files like system_health*0.xel in the path, but with my luck those ending numbers will sometimes end in a non-0 value.  I use the 0 in the mask because I make local copies of the files occasionally, so that weeds out " - Copy".

When I get some free time, I'll get my current sp_server_diagnostics/system_health parsers to work with it (see and  I started on that, but it's cartesianing on me and I don't have time to fix it right now. 



Wednesday, October 21, 2015

[Presenting] WMI and your sanity

Here's my current slide deck, the link below is for the SQL Saturday Kansas City which has the code.

Thursday, October 8, 2015

[Tools] The various flavors of SSMS I have on my box right now

First, yes, there are reasons to have the various versions. 2005 can open DTS, for instance.

But this is what I get when I use "choose default program" with .SQL files. Big hugs to Microsoft for never including the version number of SSMS in there.  It makes it lots of fun when I wind up accidentally resetting it.

Putting it in here so hopefully I won't have to do this 10-minute exercise next time.

Top left: 2005
Middle left:  2014
Bottom left (highlighted): 2016 CTP2
Middle right: 2012
Bottom right: 2008 R2

Monday, October 5, 2015

[Presenting] So far, so good...

Wow! I just realized that this past weekend makes two years that I've been presenting at SQL Saturdays!

First, this past weekend.  It was a blast, and reminds me why I love doing this.  The drive to Kansas City was long (longer on the way back, of course), but the BBQ crawl was stellar, the location for everything was, in all seriousness, crazy perfect (the hotel, BBQ crawl meetup, speaker dinner, and after-parties were all within a 5 minute walk of each other, and it would've been trivial to catch a ride to the venue), the venue was spectacular (that's a cool college with some really modern presentation facilities), the speaker selection was great, and the locals were amazing (special shout out to Dave for taking us around on Friday and showing us all SORTS of cool stuff we wouldn't have known about otherwise - I'm bringing my family back to do some of that).

Bill, Megan, & company were fantastic hosts, and since so many speakers came to the BBQ crawl, I was able to talk at length with many of them, which actually meant that I got to talk in depth about a ton of topics, learned some other cool things, and even changed which sessions I was going to.  What's the saying?  "Don't be the smartest guy in the room".  I most assuredly wasn't, and so I hope/think that some of that rubbed off on me.  Plus, not to be a putz or schmuck, but it's nice when you can talk to new people who live & breathe this stuff - it's refreshing and it reinvigorates me.  Finding out that you are not the only person doing X, and now you have cards from others who also do it and can help you.  It's amazing.  (That Service Broker class, too - I'd never seen so many people using it)

After-party - now that I've been to #SQLKaraoke, I see why people like it.  When the person can sing it's really good, and when it doesn't... it's still interesting.  Mostly, it worked. I'd swear there were a couple of ringers in there, except I saw them all at SQLSat.

Lessons learned: if I could recommend two things to any of the other SQL Saturday groups:
  1. Put everything nearby.  I don't know how many cities can do it, but being able to walk out of the hotel and do everything except be at the venue was freaking great.  Yes, ideally you want the hotel next to the venue, but I'd pass on that in order to have all the other associated things (dinner, day-before stuff, afterparty) all near each other.  The Westport neighborhood in Kansas City fit that to a T.  I could easily & safely walk to several really good restaurants, & there was plenty of nightlife nearby.  Runner up goes to Denver for having the after-party at the venue itself - not something you can do most places, but worked really well. (Though I missed the after-after party... and given what happened on Saturday night, I'm _really_ interested in seeing next years'.)
  2. Extended pre-time/cool events.  I'll be honest, the BBQ crawl was a good reason to come.  And, maybe surprisingly, not just because of the food.  It was because I got to sit and talk to Russ at length about his ETL-with-Hekaton.  I got to chat with Brandon about the sucky parts of Table Partitioning.  I got to shoot the bull with Bill about Cleartrace and Event Notifications.  Bugging people about how to get a MVP (hey, if you know someone...).  Hanging out with Andre and talking some of the things he was doing in his Powershell session - and then finding out he'd downloaded my deck for the same reason.  Zipping around town with Dave learning about great coffee places (PTs!), Always Encrypted, the plan cache, and the architecture and history of KC.  You can't get that out of a normal 2-3 hour dinner, much though I wish you could.  In this case, popping between multiple restaurants worked REALLY well, as it meant you weren't sitting next to the same people all afternoon, and so you got exposed to a ton of different conversations and topics.   Just wish I knew how you could do that in more cities.  Plus, as an out-of-towner, it was cool actually seeing more than just the venue itself.  It's easy to go around and find cool stuff during the 3-5 days of PASS.  It's much harder when you get in at 9pm on Thursday after a 9-hour drive, and are leaving early Sunday.

Now on to the presentation (I mean, that's why I went, right?): I think it went really well.  Thanks to Jason & Zane who were watching, I learned some changes I need to make, places to cover in more depth (or add demos), and parts to cover less.  I still massively dig the topic, and want people to do more with it, which means I need to work on adding some more PRACTICAL use cases.  But I find it funny/awesome that there are a couple of "throw-away" bits (powershell unrestricted, power plans, etc) that were probably more useful to some than the main topic.  Which reminds me that I need to read through the reviews.

So, two years in.
Where have I been so far?
  • Dallas 2013
  • Albuquerque 2014
  • Houston 2014
  • OKC 2014
  • KC 2014
  • DC 2014
  • Austin 2015
  • KC 2015
  • DBA Virtual Chapter
  • PASS 24 HOP 2015
  • FWSSUG (3 times thus far, I think)  
  • Denver 2015 (attendee only, but 12 hour drive means I'm at least mentioning it)
What's left/next?
I want to present at PASS on EN, so badly I can taste it.  Doing 24 HOP was one of those "holy crap, I'm there" moments.  (So was going and having dinner with a bunch of the other speakers on Saturday - walking up to a table full of people wearing the speaker shirts, and realizing I both Fit In and was One Of Them, was amazing).  I need to come up with more presentations.  I know a decent number of presenters who write an abstract, and then come up with a topic.  My presentations, for better or worse, are because I have to solve a particular problem - and then I write a post-mortem that becomes my presentation.  Next couple topics I have planned: "SAVE ALL THE THINGS" which will probably encompass my EPR, WMI, EN, and probably CDC or CT.  The one after that is probably going to be cloud-based.  But I think an easy one would just be the simple things that most people don't have set right (and now that I think about it, I saw this type of thing in DC in 2014, by Jason Brimhall).  That one, though, I might convince one of my coworkers to do.

What else?
I'd like to thank all the respective teams for having me, my awesome coworkers for coming with me on these (truly, this would've been impossible without them), my other coworkers for covering while I'm out, my boss for letting me attend, and my wife for not just letting, but actually encouraging me to go.

Thursday, September 24, 2015

[Presenting] 9 days until WMI at #SQLSatKC !

Reminder: I will be presenting on SQL Saturday Kansas City on October 3rd, at Rockhurst University. 

I'm doing a new presentation!  (Which means my coworkers get to sleep through me doing a Lunch & Learn).  Eventlogs, WMI, WQL, and why you as a SQL Server DBA should care.  We'll start with Event Logs and go down the rabbit hole of WMI.  We'll cover Eventing, WQL alerts inside SQL Server, and more.  You'll find new abilities inside Windows & SQL Server you didn't know existed.

It looks like it ought to be another stellar event - the reason I'm driving the 12 hours is because of how well last year's went (plus, hey, BBQ crawl). 

And there's a ton of other awesome speakers, even if you don't see mine.  Kathi Kellenberger talking about window function performance, Bill Graziano talking about modern high-speed OLTP, Jason Horner on Dimensional Design, Bill Fellows on BIML, Wendy Pastrick on indexes... and another 29 sessions!