SQL Clarity: February 2017

Welcome to the latest (and long overdue) Q&A session where I'm talking SQL Server I'm talking to Kenneth Fisher (MVP Data Platform). I'm really pleased to have got the opportunity to speak to Kenneth about all things SQL; his thoughts on working in the profession, the SQL community, blogging, books and of course being an MVP!

Huge thanks to Kenneth for contributing, hope you enjoy the read as much as I enjoyed asking the questions. You can find Kenneth over at his blog and on twitter!

I like to open with a quick introduction so who are you and what do you do?

Let's see. I'm Kenneth Fisher. I'm a DBA but what I do is best defined as "I help the developers". I'm part of a big team and we handle security, performance tuning, suggestions for new technology etc. Personally I'm a husband and father of two amazing kids. My hobbies include blogging, building models and I'm starting to do some origami.

[Origami?!

Yep. Origami. Technicaly origami with dollar bills]

How did you start your career in databases/SQL Server?

Interestingly I started in databases because a friend of mine was doing a project (for a sorority no less) and asked me to help. I really enjoyed the work so when he offered me a job working in Foxpro a few years later I jumped at it. After a while I ended up doing some work converting some Foxpro applications to SQL Server and I've been working with SQL ever since. I am most definitely not an "accidental" DBA.

What would you say is the appeal to working with SQL Server in particular, what has kept you working within the same area?

To a large degree I'm a developer at heart. I love writing code. I particularly like working with SQL because I get to concentrate on a single "language". I love the fact that I can go deep into one technology (even though it's an absolutely huge technology). And in the last few years the community has been a big draw.

What was the first version of SQL you used and how has the product evolved over time? Is there anything that has improved immensely?

I started with SQL 2000. I'd say lots of stuff has improved. Just look at the new optimizer. My favorite is SSMS. It took me a little while to get used to it when it was created but we've seen more and more improvements over the years. In fact I love the fact that we are getting monthly updates.

The way that SQL Server is heading, do you think there is even of an emphasis on DBA's knowing development and vice versa and is BI becoming more of a required skill?

Look at it this way. Sr Admins already tend to know quite a bit of development, scaling down to Jrs who know very little. I think that as you move up the scale development is already almost a required skill. Now with Powershell becoming more and more popular and almost required to script out some new tasks, development will be more important. Srs will know be a higher % of developer than they used to be and even Jrs will have to start learning some level of development even earlier.

BI I see has becoming more and more separated from the other two. It's really its own speciality. People will move between the two specialities but they are really separate.

Are there any particular areas of interest that you have really focused on and would you say they coincide with what you would class as your areas of expertise?

I've spent quite a bit of time with security. Not so much encryption but users, logins, roles, that type of thing. I've also done quite a bit of performance tuning. Let's, see, beyond that I enjoy playing with SSMS' capabilities and general problem solving. Those are certainly areas of my "expertise" although by no means do I consider myself an expert in any of it.

Are there any areas that you particularly dislike working in, say...replication - that crops up a lot when I ask the question? J

I'm not very good at replication (or any HA/HA type features for that matter) but I don't object to them. Probably because of that lack of experience. Honestly the only stuff that I actually dislike is creating reports. I spent probably 8-10 years creating reports in Crystal Reports and came to really dislike it. The few times I've worked with SSRS I haven't found it any more enjoyable.

Career wise, what has been your proudest moment to date?

Well, I was just awarded Microsoft MVP and it's pretty hard to beat that. I will say being nominated was pretty cool in and of itself but getting it was crazy. It's been 3 months now and it's still sinking in. Other proud moments include being featured on SQL Server Central, mentioned on Brent Ozar's weekly links and having a who's who of SQL comment on one of my blog posts.

How did that feel?!!

Completely surreal. It still hasn't completely sunk in. Although I am getting more and more used to the idea. I really have a hard time viewing myself as on par with many (most if not all) of the other MVPs.

I'm sure people will want to know and I certainly do - how did you find out about the award?!

Find out that I'd gotten it, or find out about it in general? In general I found out about MVPs by knowing a bunch of them and seeing MVP stickers on people at Summit and other Pass events. Mine specifically, well, it's easier to explain the process a bit I guess. Initially I received an email saying I was nominated. After that I had to follow a link and fill out a bunch of paperwork on what I had been doing among the community. I actually ended up having to do that part twice across 6 months. Then last Oct 1 I received an email saying I'd gotten the award. So by the time I got it I knew it was a possibility. It didn't come out of thin air. And I'll admit, even though I never really felt like I'd get it, on the first of each quarter I checked my email like crazy J

I remember seeing all the congratulations coming in via social media from people all over the SQL community, that must have felt awesome?!

It did. Still does actually.

Back to SQL, you mentioned performance tuning being a particular area that you focus on, what is the most common cause of performance issues that you find?

Well, my skills are pretty limited so I'm very much an "I have a hammer so every problem is a nail" kind of person (although I do my best not to be) but I frequently can find an index to dramatically improve a query. After that comes poor coding habits (loops for example) and data skew (causing unexpected, and sometimes unwanted, changes in query plans).

I guess this is where the "help the developers" comes in; how important do you feel it is for the DBA to integrate with other teams?

It's essential. Look at one very small piece of the puzzle, but one that everyone loves. Performance. How important to performance is storage? Or the network? Or the physical server (the host if it's a VM)? If you can't work with the teams that manage those pieces then you aren't going to be able to fix a lot of performance issues. And that's just looking from the database down. We aren't anywhere near the top. We have to work with the developers, they have to work with the business (sales, legal etc). Everyone who works at a company is part of a big whole. The better we can work together the more efficiently everything runs.

Traditionally there has been the view of development and production DBA's, would you class yourself as one or the other and would you say that those separate roles still exist?

I've always broken it down as Development, Administration, and BI. Development and Administration tend to be closely linked. Some stand more firmly on one side or the other but I think it's hard to be a developer without knowing how to do a backup for example. And administrators need development skills to manage automation etc. I think BI tends to more seperated. Some BI specialists also have skills in the other two roles but I think this role is less intertwined than the other two. As for myself? I'd say 30% Admin, 70% Dev.

You mentioned the community earlier and of course it is very vibrant and extremely active; what are your views on it, how are you involved personally and how would you encourage people to be more involved?

I love the SQL community. I've gotten more and more involved over time. These days I spend fair more time on twitter than I really should and try to answer questions on SQLHelp from time to time. I love the fact that we spend so much time encouraging each other to do better. It doesn't matter if it's speaking, trying to lose weight, blogging more, or really anything. If you mention it, you are libel to get more offers of encouragement and help than you can really use.

How to get more involved? Hang out on twitter, answer questions on forums, blog, go to SQL Saturdays. You'll find yourself getting more and more involved.

Tell me a bit more about SQLHelp. I know you're an avid helper on there, for people that don't use it or perhaps haven't heard about it, what's it all about?

SQLHelp is a hash tag on twitter. If you have a question that you can ask in 140 characters then you can ask it on twitter with that hash tag. A number of people (myself included) watch that tag and do our best to help out. If the question is something that will fit into twitter it's one of the fastest ways to get help out there.

You're a keen blog writer, how would you describe your blog?

Brent wrote an interesting post on what type of blog you have. https://ozar.me/2015/04/is-your-blog-cheers-or-mcdonalds/ In it he compares blogs to Cheers (blogs you read regularly) and McDonalds (blogs that you find when trying to solve a problem). I tend to feel like I fit into the McDonalds type mostly. I'm writing posts to explain a concept or solve a problem. I prefer reading short, to the point posts so that's what I write. Now don't get me wrong, I love having people who read my posts week in and week out but that's not my primary aim. Oh, and in general my goal is to explain senior level concepts in such a way that a junior can understand them

What do you get from blogging, what is it that you enjoy the most?

That's a bigger question than you'd think. I get a lot out of blogging. It started out as an online resume in case I lost my job. I actually had a dream that I would walk into a shop for an interview and would be told they already know my blog. Seemed unlikely at the time. Now maybe not so much. Over time I realized it's also a place to store my work. Queries I've written, a reference for knowledge I've worked out, etc. Now I've become a huge proponent of blogging. I've learned an immense amount just by doing research for blog posts. And as for what I enjoy most? That's easy. "I used one of your blogs to solve x problem", "Reading your blog helped me get a job", "I read your stuff all the time, you're really good at explaining stuff". Knowing that I'm helping people is a real rush.

You know, I've met quite a few people that started blogging about technical subjects for that very reason! Have you learnt any lessons from blogging?

Well, I already knew this but it was certainly driven home. It's ok to be wrong/make mistakes. Everyone is wrong sometimes, everyone makes mistakes. It's learning from them gracefully that's important.

What about feedback from bloggers/social media users - how do you find that generally?

It's generally very positive. I'll frequently have people point out interesting pieces of information that I'd missed or didn't know ahead of time. Those are awesome. Every now and again I'll have someone point out where I was wrong, and those are nice too. I've had to fix a number of bits and pieces over the years and even write a few correction/addition blogs. I have had a few disagreements with people but in those cases I'm always right :D. Seriously though I double check with an expert in the subject to make sure I didn't miss something.

Which other blogs do you read and why?

I'm really bad about following blogs. I read blogs as they come along. If someone asks for my opinion on a blog, if I see it pass by on twitter and the subject interests me, and of course if I'm researching something and the blog shows up on a search. I'll frequently have 5-10 blogs up that sounded interesting but I haven't had time to finish yet. The nice thing is that when I need some information I will frequently remember a blog that matches my need, go search for it, and that's when I'll finally finish it.

Where do you go for material, whether that be learning, troubleshooting etc?

Oh all over the place. Forums, twitter, friends in the business, co-workers, books, the list goes on and on. The amount of information and help we have available to us is nothing short of astonishing.

What's your favourite SQL and Non-SQL book?

Hard question. I like a lot of books. How about top three for each?

Perfect!

SQL

· Grant Fritchey's SQL Server Execution Plans books

· Knight's Microsoft SQL Server 2012 Integration Services 24-Hour Trainer

· Learn Windows PowerShell in a Month of Lunches <- Not exactly SQL and I'm just starting it. But so far so good J

Non-SQL
·         The Wheel of Time series by Robert Jordan
·         The Kencyrath series by P.C. Hodgell
·         The Valdemar series by Mercedes Lackey

What's your approach to learning, on the technical side - any particularly preferred methods?

I'm a pretty scattershot learner. I pick things up pretty randomly. A blog post here, a forum answer there, reviewing the answers on SQLHelp. I find it harder to read through a whole book or take a class on something. I'll do it, in fact I've got a book on Powershell I've started and a class on Azure that I'm 2/3rds of the way through, but it's a lot harder.

What do you think about certification, is it that important?

Well, it depends. The information you can gain in the course of getting a certification is that important. The letters themselves? Not really. It might get you in a few doors, it might not. It's almost certain you will be tested to see if you actually know the material since so many people just memorize exam dumps. That said, I like certifications. They force me to branch out and learn things I wouldn't have otherwise.

2016 was a big year for SQL Server, are you using the new release and are there any new features you're particularly interested in?
I'm just starting to get into SQL 2016 at work and most of the stuff I play with at home is older stuff. But there are a number of really cool things within the new SSMS (plan compare etc.) and I've played with delayed durability some. For the future I'm really looking forward to playing with query store.

How do you see SQL Server and indeed the Data Platform moving through 2017 and onwards?

In a word. More. More speed, more options, more OS's etc.

Speaking of OS's, what's your opinion on SQL Server being available on Linux?

I'm not a Linux person so it won't really affect me personally. Just adds more to the pile of things I don't know. On the other hand it shows Microsoft's commitment to keeping SQL Server relevant and a major player. And given that SQL is where my expertise is that can't be anything but a good thing.

Are there any emerging skills that you think people should be learning who are perhaps looking to move into the field?

The Cloud. I'm not one of those people who says "All jobs are going to the cloud." They aren't. More and more places will be hybrids though. This is a lot like becoming familiar with windows. You need to have some level of comfort and familiarity with the environments you work in. Adding the cloud doubles your workspace. You can now work in an on premises environment and a cloud environment, not to mention a hybrid one. This isn't DBA specific by the way. It's all of IT.

Yes, I think cloud adoption rates will just keep on increasing and that has an effect on, as you say, all of IT. Generally speaking, what do you see as the reasons for people to move to the cloud at a database level?

Cost will be a big one. There are times when using the cloud is just cheaper. Of course there are plenty of times where it's more expensive too. Ease of use will be another. With the cloud in place you can run a small shop with less IT knowledge than you used too. And of course if the rest of your environment is in the cloud your databases will have to be too, right?

Thank you for the wonderful words of wisdom, and so for my final question; if you had to give an aspiring database professional one bit of advice, what would it be?

Hmm, only one? Enjoy the community. Don't just join, don't just go to meetings, SQL Saturdays, etc. Don't just hang out on twitter. Enjoy it. Have fun with people who have the same job you do. You'll be amazed how much it will improve your professional satisfaction. It may not help with your current job, but it will improve how you view your career as a whole.

I was going to include this on my last post, a bit more on SQL alerting but thought I'd create a separate article instead. This is all about testing the SQL Agent alerts we've set up previously and how to do it via T-SQL.

If you're unlucky enough to repeatedly have errors being caught then you'll already be receiving notifications one way or another that things aren't going as they should do but if your system is relatively sound then we need a safe way to generate errors to test if our alert responses are working as they should be.

To do this we use the RAISERROR command to essentially create the same error as what our alerts are looking to handle. As an example we've created an alert for Severity 016 (Misc User Error) so to generate this error we use the following:

RAISERROR ('Test Severity 16',16,1)

In the results window we should be able to see:

Msg 50000, Level 16, State 1, Line 1

Test Severity 16

However, we won't have seen an alert generated in the error log and thus won't have been handled correctly. For that we need to add the WITH LOG command:

RAISERROR ('Test Severity 16',16,1) WITH LOG

Now we can see the error has been raised to the error log and will have been handled appropriately by whatever functionality we have assigned to the alert.

A final mention goes to some of the alerts we have set up previously, specifically the severity 20-25 range. These are considered fatal errors and upon generated the client connection is terminated, due to the high severity the RAISERROR command must be issued with the WITH LOG option or it will not work:

Msg 2754, Level 16, State 1, Line 1

Error severity levels greater than 18 can only be specified by members of the sysadmin role, using the WITH LOG option.

With the WITH LOG option:

Msg 2745, Level 16, State 2, Line 1

Process ID 62 has raised user error 50000, severity 20. SQL Server is terminating this process.

Msg 50000, Level 20, State 1, Line 1

Test Severity 20

Msg 0, Level 20, State 0, Line 0

A severe error occurred on the current command. The results, if any, should be discarded.

This is how to test alerts, obviously don't try any of the RAISERROR commands on a live production system otherwise any handling that is in place will be invoked and you could quite easily cause wide spread panic!!

More reading: https://msdn.microsoft.com/en-us/library/ms178592.aspx

SQL Clarity

Monday, 20 February 2017

SQL Shack: Monitoring Memory Clerk and Buffer Pool Allocations in SQL Server

Sunday, 19 February 2017

SQL 2014 Sample Databases - and a bit extra.

Saturday, 18 February 2017

All about SQL Agent alerting

Thursday, 16 February 2017

Q&A with Kenneth Fisher

Wednesday, 15 February 2017

Raising Errors - Testing SQL Agent Alerts

Even more on SQL Alerting: An Example.