Mar 31 2015

PASS Board Update: March 2015

Hello all,

It’s been a while since my last update. Sorry. I’ve just been traveling and presenting and working on books and this slipped through the cracks a bit. I’ll try to do better in the future.

Nothing major to report. As a board member I’ve been working primarily on two things. First, I’ve been polishing my goals for the Chapters Portfolio. I’ll publish those below. Next, I’m working on the budget for Chapters for next year. In between times, a lot of what I do, is work with Regional Mentors (RM) and Chapter Leaders (CL) on an individual basis. As much as the stuff that PASS as an organization is involved with is around technology, the PASS organization, especially Chapters, is all about people. And you know how people are. I’ve already had online meetings with the RMs and CLs in which I let them know what was going on with the Board and with my goals. The one point I made that I want to share is my own focus. I told everyone, and I’m telling you, that I see my role as a means of figuring out what the PASS organization can do for the Chapters rather than telling them what they need to do for the organization. Other than that, at these meetings, I’ve listened and taken notes. I want to know what’s needed by the CLs and the RMs to do their work as volunteers, and, where possible, make that work easier.

I also had the opportunity to have a meeting with the user groups of the UK (whether they were PASS chapters or not). This was an opportunity to listen to what their needs are and to understand whether or not PASS is meeting them. One of the most interesting things I learned revolved around Chapter Leaders and the Summit. There are a number of requirements for a Chapter to be in good standing with the PASS organization. And, if the Chapter meets all those requirements, then they can receive one free registration for Summit. Cool beans… unless you’re overseas where the cost of getting to Summit is much higher than here in the States. Suddenly, this seemingly HUGE benefit just isn’t that big. I’ll see what I can do about this one. Not sure what yet, but I’m thinking about it.

Here is a rough draft of the goals I’ve been working on:

PASS Growth

  • Create a pilot program that allows a regional email to be sent to PASS membership that highlights user group events within that region
    • Purpose is to measure the work involved in order to understand the needs for expanding this to all regions
    • Success is arriving at a measure of work that allows for creating plan for next steps
    • Run for three months to arrive at measure
  • Increase communication between Director for Chapters, HQ and Chapter Leaders by hosting quarterly meetings

Global Growth

  • Create interface to connect Speakers with Chapters
    • Purpose is to provide for Chapters to get in touch with Speakers using our existing local speaker list in SQL Saturday
    • Success is mechanism for Chapter Leaders to find Speakers within their region
    • 3 months to arrive at minimal functionality
  • Complete definition of Regional Mentor Annual Rating process
    • Purpose is to provide Chapters with mechanism for reviewing RMs
    • Success is a completed document that has been reviewed by CL and RM represenatives
    • Time frame requires completion by EOY 2015
  • Continue efforts to establish chapters in countries with no or few existing chapters
    • Targeting Baltics, Mexico, Saudi Arabia, Argentina

That’s about it for now.

Coming up I’ll be going to the PASS Business Analytics Conference (use the code BFFGF, that’s my Friends & Family discount, to get some money off the registration). I’ll write something up about that right after it’s over.

Mar 30 2015

Does the New Cardinality Estimator Reduce Bad Parameter Sniffing

parameter sniffing https://www.flickr.com/photos/nalbertini/6224914311/in/photolist-au5j9P-oovkHJ-j5hbfL-o2tQP4-5rjH9-o63z6j-e16cSr-o6eBtd-83UkyT-61apWt-xMWdz-6vUmcL-ojqHXt-krd1A8-bwvg6r-4wvJLh-pweDW5-5UzK52-NwvaB-4uQ6em-dZ7BLC-reoTH4-7RN6Bi-kr7ViJ-5BgntE-83a6sH-duXGC-6c3j6-Rphmr-4ceWRz-9pMBSV-4mz1HK-2kLC5w-6N2QGL-bcP2Qc-62i9cA-dPe47C-6ugVmj-59x1pS-6PYR8J-9QpqJK-83XraW-xJ3MS-mkE3Fq-5uJunA-oMMXp-rEX8bF-of5Fpr-3aJrZd-6WLBU7/No. Next question.

Although, that answer can be slightly, ever so slightly, nuanced…

Parameter sniffing is a good thing. But, like a good wine, parameter sniffing can go bad. It always comes down to your statistics. A very accurate set of statistics with very little data skew (some values that have radically more/less data than other values) and a very even distribution (most values have approximately similar cardinality), and parameter sniffing is your bestest buddy on the planet (next to a tested backup). But, introduce some data skew, let the stats get wildly out of date, or suffer from seriously uneven distribution, and suddenly your best friend is doing unspeakable things to your performance (kind of like multi-statement table valued user defined functions).

SQL Server 2014 has the first upgrade to the cardinality estimator since SQL Server 7. To get the best possible understanding of what that means and how it works, I can only recommend Joseph Sack’s white paper. It’s THE source of good information. All the new functionality within the estimator, for the most part, in most situations, will probably lead to more accurate row estimates. This means, in theory, less bad parameter sniffing. But, if you are benefiting from the old estimation engine, and in some cases you absolutely are, then you may see an increase in bad parameter sniffing. It comes right back around to your statistics and your data distribution.

So, does the new cardinality estimator lead to a reduction in bad parameter sniffing? It still mostly depends on your statistics. So, I can’t say, with certainty that the new cardinality estimator in SQL Server 2014 literally reduces bad parameter sniffing. In many instances, probably even most, the changes to the model are going to resort in better row estimates which means better choices by the optimizer. In some instances this will reverse and you may see pain, directly related to the changes in the cardinality estimator. But, overall, the fundamental behavior of parameter sniffing, again, a very positive thing, are unchanged. So, to a large degree, at least in my mind, no, the new cardinality estimator doesn’t reduce bad parameter sniffing because, most of the time, it’s due to the three causes mentioned before, out of date statistics, data skew and uneven data distribution. The new cardinality estimator doesn’t change these fundamentals, so, mostly, it won’t reduce bad parameter sniffing.

Mar 25 2015

Toshiba Portege Z301-A Review

Last year I purchased a Lenovo W530. Nightmares then ensued. Nice laptop if you just want to use it as a laptop, and only a laptop. Horror show if you need to present from it. So, I replaced it with an HP… which also gave me some troubles (try presenting with your laptop bricked in front of your boss AND 100 people), but that’s OK because I dropped it the next day (totally by accident, it had just worked great all day, I was happy with it, mostly). So there I was, laptop free and in need of something that would let me work, present, and be an all round road warrior. PLUS, I had just broke a laptop and was feeling bad that company would be replacing it. So I wanted to keep the costs down. After a lot of research, I decided to pull the trigger on the Toshiba Portege.

I couldn’t be happier.

This has been my best experience with a laptop since my Lenovo W520 (which just wore out from three years on the road). This thing is snappy fast to start up. It sleeps just fine. 16GB of RAM so I can run a couple of VMs (or one really fast VM). It’s got an SSD, so that’s it for quick I/O. The smaller footprint of the 13 inch monitor is great (and, I’m not at all bothered dealing with reduced screen real estate on the road). Heck, I’m typing this blog post on the thing right now. The battery life is awesome (I’ve been working in a hotel lounge for about four hours at this point and it’s still got another four hours to go. Oh, and the power brick is tiny so my overall weight when travelling has dropped.

Nothing is perfect. The touchpad tends to catch my palm and redirects where I’m typing sometimes. That is because I’m a little lazy with my hands sometimes while typing, so, to a degree that’s actually a plus, forcing me to watch dropping my hands. I think the keyboard is just a tad tight for my hands, but not too bad. I’m still not used to the function key locations.

But, I’ve been presenting all over the world with this thing on both VGA and HDMI connections. No issues. Ever. Of any kind. I’m in absolute love with it as a presentation machine. Oh, and did I mention it has both HDMI and VGA plugs, so no dongle in either direction. Three USB ports. It’s just great.

Screen quality and resolution is good enough for my tired old eyes. It’s also touch screen so I can scroll & zoom when reading stuff on it. I’m just happy with my laptop.

Now, if I can just keep from dropping it.

Mar 24 2015

Simple Parameterization and Data Types

Simple paramaterization occurs when the optimizer determines that a query would benefit from a reusable plan, so it takes the hard coded values and converts them to a parameter. Great stuff. But…

Let’s take this example. Here’s a very simple query:

SELECT ct.*
FROM    Person.ContactType AS ct
WHERE   ct.ContactTypeID = 7;

This query results in simple parameterization and we can see it in the SELECT operator of the execution plan:

param

We can also see the parameter that was defined in use in the predicate of the seek operation:

predicate

Hang on.

Who the heck put the wrong data type in there that’s causing an implicit conversion? The query optimizer did it. Yeah. Fun stuff. If I change the predicate value to 7000 or 700000 I’ll get two more plans and I can see them all by querying the cache. But, here’s a fun little bit. I just searched the XML of the plan, the data type isn’t stored anywhere that I can see. But, if you query the plan cache, look what the text of the query is:

(@1 tinyint)SELECT [ct].* 
FROM [Person].[ContactType] [ct] 
WHERE [ct].[ContactTypeID]=@1

Now, in this case, this type of conversion doesn’t hurt performance in any way. We’re still getting an index seek. I also ran some tests with strings. The optimizer was setting them to VARCHAR(8000) and I got an implicit conversion that, again, didn’t hurt performance. But, I wouldn’t be completely shocked, if, at some point, that lead to scans. What I find it really interesting is, that the data type isn’t stored with the plan. I fully expected it to be there.

Mar 16 2015

How To Speak At SQL Saturday Events

The PASS SQL Saturday events are meant to be a place to grow the pool of speakers, provide a mechanism for the speakers to learn, and fulfill the PASS goals of Connect, Share and Learn. So, you’ve decided you want to start speaking at a SQL Saturday event. Cool. You went to the <Insert Large, Popular, SQL Saturday> event last year, so you submitted this year… and didn’t get accepted. Now what?

First, submit. You won’t get accepted if you don’t try.

SQL Saturday, especially the big, popular ones, may not be the best place to present for your very first time. In fact, with the large ones, you may not get accepted because people who already have a reputation are submitting to those (we all want to talk to big audiences and go to the good venues). So, start smaller. Find your local user group and speak there to get started. Better still, contact the person running the SQL Saturday event. Most of them are also running their local user group, or are associated with it, and most user groups are desperate for speakers. Go and speak at the organizers user group. That’s going to do two things for you. You’ll get some practice in a friendly space, and you’ll get your face and name in front of the SQL Saturday organizer. PASS maintains a list of local user groups that are associated with PASS as Chapters.

Next, go to the SQL Saturday web site. Find the one that you’re thinking of submitting to. Go to the Schedule page. You’ll see a list of people and topics they submitted. At the bottom, there is a place for suggested topics. Sometimes, but not always, you’ll see the stuff there that the organizer wants to see. They’re looking for a session on the VARIANT data type and you’ve written 33 blog posts on VARIANT in the last six months? GOLDEN! Submit under that topic. You can also try contacting the organizer to see what they’re looking for. Ask them, what topics they’re interested in presenting? Now, be sure you actually can present on that topic, but, here’s your in, giving them what they want.

Go to a smaller event. Yeah, presenting at the big event so you can hang out in the speaker room with <Insert Popular Speaker> would be cool. But, it really is hard to get into some of those events because everyone wants to do the same thing. So, start smaller. Some of the events just aren’t drawing lots of big name speakers. Personally, I don’t think that matters at all (people disagree on this topic), but, it’s an opportunity for you. These events need speakers. I know some where they’ve accepted every single session submitted, turning down no one (and I even presented a couple of extra sessions while I was there to help out). So, if you want to speak and, if you drove an extra hour or three you could speak, done.

Finally, your title and abstract do matter. The abstract needs to define a clear problem and solution that you’re going to present. The title… my opinion, let’s have a clear, descriptive title. I intensely dislike the cute and clever titles. Further, I don’t think they help people, especially new people, get accepted. I think they work well for the name speakers because it shows off their personality, and sometimes that’s what people are going for. You’re just getting started, tell us what you’re presenting.

None of this guarantees you get in, but it should all collectively help to get you in front of an audience at SQL Saturday.

Mar 13 2015

Speaker of the Month: February & March 2015

It’s not a question of scheduling. I just haven’t been to lots of community events in the last several months so that I can see community speakers and find one to give an award to. I’ve been trying. So, we’re giving out two awards this month (my blog, my award, my rules). Unfortunately, neither one is going to a full-blown community speaker. Hey, not my fault. I’m trying. Anyway, on with the show.

The first award goes to ALL the speakers on the SQL Cruise. That’s Jes Borland(b|t), Jeff Lehman(L), David Klee(b|t), and Kevin Kline(b|t). Look, I get it, Tim Ford(b|t), the guy who runs SQL Cruise, goes after top speakers (and, somehow I get in too). But, until you’ve watched these people present, in the long form sessions that SQL Cruise offers, you might not have seen these people at their very best. I did. I learned things about Amazon Web Services, VMWare, Automation, and just being a DBA, that I really didn’t know going in. And, I was entertained by witty people. And I was energized by animated, interesting talks on useful topics. Look, the main reason I didn’t pick a Speaker of the Month for February was because I wanted to do this and just didn’t feel like I should. I finally just gave in. So, here you go guys. You win. Amazing stuff. Thank you so much for sharing. And you, dear reader, should try to go on one of these cruises. It’s one of the best learning opportunities you’re going to hit.

The next award goes to Justin Langford(b|t) and his session on Get Started with Troubleshooting SQL Server presented at SQLBits.

This was a very good session. I really liked how Justin delivered the content. Not surprising since he’s an MVP and an MCM. He made good eye contact through the whole presentation. He presented troubleshooting in a way that people should actually follow. I also liked how he presented all the various tools (and seeing them listed out, there are a ton of native tools in SQL Server) and their uses. I learned a few things on some of the tools that I hadn’t worked with much, especially SQL Nexus. It was a great session that I can recommend.

The only thing I might change if I were Justin is that I’d rearrange the order just a little. I’d show the problem and then show the tool that solves it. He kind of approached it the other way around. It still worked great, but I think it would have worked a little better, maybe. But that’s it. It was a very solid session.

Next month, things should get back to normal. I’m hitting a SQL Saturday, so I should be able to track down some community people and get away from all these talented professionals.

Mar 12 2015

Monitoring for Timeouts

The question came up at SQL Rally, “Can you use Extended Events to monitor for query timeouts?”

My immediate response was yes… and then I stood there trying to think of exactly how I’d do it. Nothing came quickly to mind. So, I promised to track down the answer and post it to the blog.

My first thought is to use the Causality Tracking feature to find all the places where you have a sql_batch_starting without a sql_batch_completed (or the same thing with rpc calls). And you know what, that would work. But, before I got too deep in trying to write the query that would find all the mismatched attach_activity_id values that have a sequence of 1, but not one of 2, I did some additional reading. Seems there’s another little trick that works even better (sort of). It’s to set up an Event Pairing Target.

This is pretty slick. Here’s the code I used to set up for “timeouts” in SQL Server Management Studio:

CREATE EVENT SESSION [Timeouts] ON SERVER
ADD EVENT sqlserver.sql_batch_completed (
    ACTION (sqlserver.session_id)
    WHERE ([result] <> (2))),
ADD EVENT sqlserver.sql_batch_starting (
    ACTION (sqlserver.session_id))
ADD TARGET package0.pair_matching (SET begin_event = N'sqlserver.sql_batch_starting',
                                   begin_matching_actions = N'sqlserver.session_id',
                                   begin_matching_columns = N'batch_text',
                                   end_event = N'sqlserver.sql_batch_completed',
                                   end_matching_actions = N'sqlserver.session_id',
                                   end_matching_columns = N'batch_text')
WITH (MAX_MEMORY = 4096 KB,
      EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS,
      MAX_DISPATCH_LATENCY = 30 SECONDS,
      MAX_EVENT_SIZE = 0 KB,
      MEMORY_PARTITION_MODE = NONE,
      TRACK_CAUSALITY = ON,
      STARTUP_STATE = OFF)
GO

A couple of notes. You see the “timeout” in quotes above. That’s because I wasn’t getting a timeout in SSMS, despite setting one. Rather than muck around (I’ll figure that out later), I just set a long running query and then stopped it, but, I put a filter in place to not capture batches that terminated from an Abort command, [result] <> 2. But that did it. As soon as I aborted, I could use the query supplied at the link for Event Pairing Target to immediately see my unmatched pair. That’s quick and easy timeout monitoring using extended events. Output here:

<event name="sql_batch_starting" package="sqlserver" timestamp="2015-03-10T17:25:14.089Z">
    <data name="batch_text">
      <type name="unicode_string" package="package0" />
      <value>SELECT  *
FROM    Production.ProductCostHistory AS pch,
        Production.BillOfMaterials AS bom,
        Person.ContactType AS ct;</value>
    </data>
    <action name="session_id" package="sqlserver">
      <type name="uint16" package="package0" />
      <value>63</value>
    </action>
    <action name="attach_activity_id_xfer" package="package0">
      <type name="activity_id_xfer" package="package0" />
      <value>1BF4899F-4B74-434B-9CDE-2B646A560FB1-0</value>
    </action>
    <action name="attach_activity_id" package="package0">
      <type name="activity_id" package="package0" />
      <value>4B92C757-F780-4D8C-B1DA-F95C206A32EE-1</value>
    </action>
  </event>

So, here’s the biggest question, would I actually use this in production?

I’m a little nervous about using the batch_text as a matching point. With rpc_completed you could just use session_id and object_name or object_id. That’d be great. So, if I were going to use this in production, I’d probably only monitor for certain databases, putting tighter filtering in place for batch commands. But, other than that, I can query the target and pull out any timeout objects, so this is actually pretty slick and works well.

The only shortcoming I see is that you can’t add two pair matching targets in a session, so you would need one session for batch timeouts and another for rpc timeouts. Not the end of the world, but worth noting.

 

 

Mar 09 2015

Upcoming Events Where We Can Chat

I get around quite a bit.

Next week I’ll be visiting three cities in Germany talking to user groups in Frankfurt, Cologne and Munich on the 17th, 18th, and 19th of March, respectively. Here’s the one link I can find for information. EDIT: Here’s Cologne and here’s Munich.

Next, you can see me at the Redgate DLM Seminar in Silicon Valley on March 27th. It’s a free event and there are a few seats left. Click here to register. The next day I’ll be presenting at Silicon Valley SQL Saturday on March 28th.

After that, I’m staying close to home to get to Boston SQL Saturday on April 18th.

Then, one of the big events this year, I’ll be travelling to my very first PASS Business Analytics Conference in Santa Clara, California, on April 20-22. Analytics is a fast growing aspect of being a data pro. You’re going to need to understand it the way you understand Azure, NoSQL, and all those other technologies that are looming on the horizon and will be changing the DBAs job. Click here to register now. I think I still have a few of my “Friends and Family” discounts left. Enter “BFFGF” when prompted.

Next, it’s off to beautiful Belgium for Techorama, May 12-13. I love visiting that country and I really love that conference.

I’m not done. In June, I’m going to go on SQL Cruise Mediterranean. You do NOT want to miss this. If you’re in Europe wishing you could afford to fly over for the Caribbean trips, now is your shot. A train or a flight down to Barcelona is a lot cheaper. SQL Cruise changes lives. I’m not kidding or exaggerating. It’s very intense training and networking with fantastic people. You’ll come away energized, ready to conquer the world. And there’s rum.

I’m taking July off, but I have a bunch more trips scheduled for late summer and the fall. I’ll post them later.

I live off of questions, so I really do want to meet you at one of these events and have a chat.

Mar 02 2015

No Such Thing as a DevOps DBA

Sjor Takes (b|t) has just barely started blogging, but he’s got a great post about a discussion he had with a colleague. It’s worth a read. When you get done, I’ll provide my answer to the question posed at the start and conclusion of his post.

I had a great discussion with one of the smarter people I know late last year. Since I’m going to disagree with this person rather vehemently, I’m going to keep them nameless. We were discussing databases and DevOps and how it relates to the developer, the data professional, specialized DBAs and businesses. It was mostly a great conversation except for this person’s opening. This isn’t an exact quote, but it paraphrases their beliefs fairly well:

The DevOps movement is, intentionally, about getting rid of the DBA. We shouldn’t have them involved in the process any more. The technology coming out is helping us to eliminate that job and that is how DevOps is supposed to work. We’re just going to put all the power into the hands of developers and we won’t need operations people, especially DBAs, any more.

Sigh.

One, this goes back to my most recent post on the word “NO” and DBAs. Two, it’s just wrong.

Doing IT work is hard. Being a very good C# developer who has a full grasp of appropriate patterns, service methods, proper use of tools & tooling, and all the rest, that takes time. Let’s toss in learning T-SQL and database fundamentals on top of that. More time. Let’s also throw in server hardware and OS configurations and PowerShell to manage it all. While we’re at it, virtual machines and virtual machine management. Oh, and Azure.  So, there are probably, a few people, who are legitimately good at all this stuff. But, it’s been my experience that most of humanity (myself included) are adequate at a few things, less than adequate but functional on a whole lot more, and, frankly, suck at everything else. And that adequacy assumes you work at it. So, all developers, by virtue of the magic of DevOps, are not only going to learn all of the above and more, but they’re going to be so capable that they’re able to appropriately respond in emergency situations with all of the above and more. No.

That means that there’s going to be specialization. Let’s assume one area of specialization is around data management. Even if it’s just in DocumentDB (and saying “just” about most technologies is setting yourself up to fail). There’s going to be a developer in the organization who catches the first glitch or hiccup. They’re going to do a little extra work and gather a bit of knowledge to deal with this. Next time a problem comes up, guess who gets called? In short order, guess who is the DocumentDB expert and is consulted on development, deployment, tuning, troubleshooting and disaster recovery? And, guess who is spending LOTS more time in and around DocumentDB and less and less in C#? But we won’t call this person a DBA because that would be bad…. even though that’s the job they’re doing now.

Sorry, but specialization of knowledge means that there are going to be people who do the job currently occupied by a DBA, full time. Further, these NotCalledDBAs will need to be directly involved in DevOps. The NotCalledDBAs will be the experts in the right way to set up automation around deployment. NotCalledDBAs know the pitfalls to avoid in design. NotCalledDBAs have a better grasp of the internals so will be important during development and later during optimization. NotCalledDBAs will have a full understanding of how DR works and some of the design choices necessary to ensure business continuity. All of this is fundamental to a well functioning DevOps team. Oh, and by the way, it’s all the stuff that your DBA should be doing today.

Yes, a DBA is, and should be, a part of the DevOps team. But if it makes you feel better, we can call them NotCalledDBAs.

Feb 26 2015

How to do DevOps as a SQL Server DBA

You recognize that you need to provide a pipeline for database deployments, that you need to automate as much support for your development teams as you possibly can, that you have to have testing in place to ensure protection of the production environment, that you need to speed your processes. In short, you recognize the need for taking on a DevOps approach, an Application Lifecycle Management (ALM) approach, even, a Database Lifecycle Management (DLM) approach.

Cool.

Now what?

Well, there are three fundamentals that you need to get under your belt. You need to get your database into source control. You need to set up a continuous integration process. You need to set up automated deployments. All tough nuts to crack.

Hey, we get it. That’s why Redgate Software is going to provide hands-on training in these skill sets and disciplines. Check out the DLM Training and Consultancy page.

As of this writing you can sign up for both free and paid courses (the free courses are just in March to get things started) to get your hands on some of these technologies. Steve Jones (b|t) will be teaching continuous integration in Dublin on March 10th. Alex Yates (b|t) will cover London on March 20th. I’m taking on Silicon Valley on March 27th. This is your chance to see how CI works and how it can be applied within your development processes so that you can begin working on DLM and DevOps within your organization. The paid events start in April.

Watch the DLM Training and Consultancy page because we’re going to be expanding this training more and more. There will also be introductory courses that give you a complete overview of DLM, but without the hands-on training.