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.

 

Feb 24 2015

DevOps, the DBA, and the word “No”

Check out this DevOps Reactions animated GIF and caption.

It’s funny on multiple levels, but it also makes me both mad and disappointed.

I get mad because it’s 2015. Surely by now most of us, especially those who have worked in the enterprise with development teams, know that the old 1970s vision of a walled off data center with DBAs in white lab coats acting as gatekeepers to the data is long discredited. As DBAs, even if you’re not working with development teams at all, you’re just offering a service to the business. This whole, a DBAs favorite word is “NO”, meme needs to die a quick, hard, death. All those “Technology X” is going to eliminate the DBA articles that come out every six months like a comet with a small orbit are partially predicated around many teams attempting to get around, over or through the DBA because we still think our purpose in life is to say “No” to every single request. Come on people. You’re part of a team. That team has a very simple goal, keep the company afloat so it can make money. That’s it. If your goals are narrower than that, if you’re a road block to progress and motion, if you’re trying to stop every development proposition that comes down the road, well then, heck yes, your dev team is going to try out “Technology X” so maybe they can get rid of you. Deservedly so. You have it coming and you brought it on yourself.

I get disappointed because this whole attitude is just going to make things harder for data professionals down the line. Why do I say that? Because, inevitably, data management is going to fall back under specialized knowledge sets because it’s actually a pretty specialized skill. And I’m not talking about running backups. I’m talking about 1) Optimization, even if you’re on Hadoop and are luvin life with NoSQL, you can still do it wrong and hurt performance, 2) Emergencies, recovery from corruption or point in time recoveries or proper management of whatever failover system is set up (assuming one is) requires specialized knowledge that most developers just don’t have the time or inclination to learn (BIG NOTE: not talking ability). Those two areas are where everyone I know who makes big money consulting is working. Why? Because that’s where things break down. And this entire attitude of “NO” from DBAs mean we’re going to be excluded by development teams (and I don’t blame them, in fact, I encourage it if your DBA is still saying “NO”) so that they can get their jobs done. But… Later, stuff will go wonky because of load or the SAN getting switched off or a limited Zombiepocalypse and suddenly, businesses and dev teams, and probably the one or two data pros still in the company, are playing catch up in ways that could have been avoided.

This has to stop.

DBAs must be a part of DevOps because we’re part of Dev and we’re part of Ops. I’ll put up another post on this topic shortly.

Jan 28 2015

Oh Look, A Horseless Carriage

Never forget, we’re making buggy whips. And everybody we know drives little buggies and they need our buggy whips. We’ve got a special talent, a unique knowledge set, and it’s fulfilling a defined need. So we’re all set, right?

Well, other than that Stanley Steamer over there. And maybe that Ford. Oh, and there’s a Grant.

I worry about this stuff all the time. I know SQL Server. Before that, back in the day, I worked on Paradox, PAL & OPAL. I learned and programmed in Visual Basic, Java, C# and .Net. I’ve made sure that I’ve explored, let’s see, Hadoop, Mongo, MySQL, and others, structured and unstructured, relational and non, you name it. Why? Because, I want to keep an eye out for the automobiles that are going to ruin my nice little buggy whip manufacturing business. I make money on these buggy whips and that feeds my family.

Now, here’s my current question/thought/worry/thingie… How does business analytics fit into this? Is there a path that I may need to explore that moves me from working primarily within a focused technical sphere to working with PowerWhatever? Is that a path that people take? Or, is that actually leaving the technology path to become primarily business focused? That’s what I’m trying to figure out. Yesterday we did Oracle. Today we’re doing SQL Server. Tomorrow we’re working on Hadoop. Next week… Wouldn’t it be data vNext for the data professional? Or is it that the data pro’s path lies, at least what appears to me, outside of a pure technical scope?

I’m not sure. But it’s something I’ve realized I might need to at least explore a little before I dismiss it out of hand. I may sound a little snide or scornful, but I’m not trying to be. I absolutely recognize the size of the analytics market. It’s vast. And, I’m actively concerned. Does this represent a horseless carriage? I am unsure, but I’m also a little nervous. It feels like I would be abandoning technology, to a degree (recognizing that this too requires technical know-how). Technology (buggy whips!) has been my primary driving force, even when I was in the Navy.

But, we all do have to worry about this. You absolutely don’t want to be trying to sell those buggy whips when everyone is buying cars. If you do think the next step is analytics and you’re ready to go down that analytics path, I can help a little. I’ve got a discount code that will get you into the PASS Business Analytics Conference for a reduced rate. Just enter BFFGF when prompted. This very well could be the right choice to avoid the whole buggy whip problem (until the next time, because it’s buggy whips all the way down). Or, if you just want to get your feet wet, check out the BAC Marathon.

In the meantime, I think I’ll explore how this DocumentDB thing is working. I’m just not sure I want to give up on technology to focus primarily on the business just yet. But I’m seriously curious what others think about this. Is analytics the logical next step for the data pro? Is that a horseless carriage?

Jan 26 2015

My First Board Meeting

Parents, you know that feeling you get after you’ve brought home that brand new baby (or in my case, babies) where you suddenly say to yourself, “Oh damn. What have I done?” Yeah, that’s my feeling after my first board meeting. But let me give you a run-through of how it was. Note, I can’t pre-publish any board decisions. I’m also not giving you a blow-by-blow account of the discussions (especially since there were no blows, at all). This is a story of the types of things we did, how it went, how I feel about it. This is my blog, my blog post, my thoughts and feelings. This does not represent an official PASS communication. We good?

While the board meeting itself is, for me, only a two day affair, we tacked a day on at the beginning to take advantage of an offer from Microsoft to tour one of their data centers. Let’s put the entire day into one word: Nerdgasm.

On to the work of the board.

The first half of day one of the meeting was dedicated to going through Insights communications styles seminar. I found this extremely useful and, I think it’s going to be even more useful over time going forward. Understanding how my fellow board members and the HQ people communicate will help facilitate that communication and will help PASS. I’m glad the board chose to do this before I was involved. Communication can just be hard. Any kind of tools or practices that make it easier are very important.

We had a nice discussion around some points of governance that we’re going to be taking out to the community in order to get feedback on. Once we get a feel for how people think, we can make more informed decisions. We got an overview of global growth and it’s current status. It’s important to keep this in mind since it’s no longer a portfolio, but is folded into all the others. There was a long and useful discussion around SQL Rally. We got a review of the speaker selection process from the 2014 Summit. It was a good day. I feel like I have a better understanding of what’s going on. I was able to contribute in areas where I had some knowledge. It was a good day.

The second day was primarily focused around discussions on the Business Analytics Conference. I can’t discuss much about this because there’s a whole bunch of stuff that’s going to have to be context driven and should absolutely come out of the mouth of the executives, not me. Suffice to say, the discussion and information was extremely interesting, very helpful to me personally, and I really appreciate the incredible amounts of work that has gone into this event. I will suggest, if you’re interested in PASS and it’s direction as an entity, you should be paying attention to this. It’s important.

Finally we got to the goals. Interestingly enough, I misunderstood the purpose of the goals. I thought I was going in there to get approval, and man I was armed for bear. Come to find out, nah, it’s a chance for feedback from the board proper and then, “Have fun storming the castle.” It’s up to me to pick the goals I want to achieve for the Chapters and then work through the processes we have in place to get them accomplished. Well heck, that’s easy. Now, my plan was to share my goals right here and now, but, I got such good feedback on the goals, that I want to rework them a little, and then I’ll publish them here, there, everywhere. I’m honestly feeling a little bit better about accomplishing something useful for the Chapters than I was heading into the Board Meeting.

That’s about all I have. The minutes will be posted… on whatever schedule they get posted on (not sure), and I could maybe do another blog post after the decisions & details get released. One point that I took away from everything, and it’s something that’s hard for me as a technologist, decisions get made and stuff gets set in motion. Sometimes, it might be stuff that you certainly would have done differently, but you weren’t part of the team. Regardless, you need to look at it, understand it, and then, figure out how best to add your own contributions in order to make the team successful, even if, given your druthers, you’d have done this completely differently. And no, that’s not some veiled reference at some fight or huge disagreement. It’s an overall feeling and directly relates back to the very first thing I said. This whole Board thing is a bigger, tougher, more fascinating challenge than I realized going in. I’m actually a little more excited about it now because of that.

Jan 19 2015

Understand the True Source of Problems

There’s an old joke that goes, “Doctor, doctor, it hurts when I do this.” While the person in question swings their arm over their head. The doctor’s response is, “Don’t do that.” Problem solved, right? Well, maybe not. Let’s take a quick example from life. I do crossfit (yeah, I’m one of those, pull up a chair I’ll tell you all about my clean & jerk progress… kidding). I’ve been experiencing pain in my shoulder. “It hurts when I do this.” But, I’m not going to stop. I’ve been working with my coach to identify where the pain is and what stretches and warm-ups I can do to get around it (assuming it’s not a real injury, and it isn’t). In short, we’re identifying the root cause and addressing the issue rather than just coming up with a “don’t do that” style solution.

As is usual with one of my rambling intros, I’m going to tie this back to our work as data professionals, hang on.

I recently ran into a set of requirements that a new DBA was told are “best practices” and that he had to comply with. I’m editing them in order to save anyone embarrassment (although, if you wrote these, you should be embarrassed). I think that each one represents a moment of “it hurts” followed by “don’t do that” which, as you’re going to see, is absolutely the wrong response. As crazy as they all are, it’s not the first time I’ve seen them. This information is clearly coming from some fever-filled internet-swamp where stupid mutates and grows. Let’s nip these in the bud.

1 – All queries against the production database must be executed by using RDP to connect to the production system. This is because connecting through SSMS will cause the server to crash

I couldn’t even begin to guess what happened that caused a system outage because of a query from SSMS, but I suspect it was some sort of crazy, extreme outlier of a query that probably didn’t run much better while RDPed into the server, but somehow avoided a network issue (or six). Who knows. Suffice to say, no. This is crazy. On multiple levels. The most important being, you’re giving people access to the operating system on production that really probably shouldn’t have it. And, you can only have one person connecting to production at any one time. Two teams ready for a deployment? Tough. Oh, and your production system, in addition to all the work it’s doing for SQL Server, it now has to handle all the work of SSMS and displaying the results of your queries. Was your server under stress when you started? It’s worse now. I really don’t know of anyone, including a very large number of remote DBAs, who don’t connect to SQL Server through SSMS running on their desktop.

2 – NOLOCK is required on every query. It makes things run faster.

The magic Turbo Button of SQL Server rears its ugly head, yet again. I suspect that if you went and looked at all the responses to questions on forums, blog posts, articles and presentations, there are more places defining exactly why this is bad than almost any topic except backups. One more time. Using NOLOCK on all your queries…  and if you’re putting it everywhere anyway, why not just use READ_UNCOMMITTED on your connections, it’s easier… Anyway, Using NOLOCK on your queries results in dirty reads. Yes, everyone says, “Oh, that means if someone is change ‘dog’ to ‘cat’ I could see either ‘dog’ or ‘cat’. I don’t care.” Right. That’s true. It also means when pages split and rearrange, you may miss rows or have rows duplicated. All the businesses I know love it when data is missing from the queries they’re running. I’m sure you wouldn’t mind if your bank used NOLOCK and told you that you didn’t have any money in it, right?

3 – Transactions take the server offline. Never use them in queries.

Again, I’m having a hard time imagining what exactly occurred to make this seem like a good idea. You do know that every query has an implicit transaction? And your server is still online. Can you over use transactions, your transactions can be overly large, your transactions can run too long, or you can have too many transactions. But no, just using a transaction will not bring the server down.

4 – Do not JOIN tables because it leads to performance problems.

I’m actually used to a slight variation on this, “Never join more than three tables.” This is just a holdover from the old days when several things were going on. One, the old query optimizers just sucked, so choices on loops or merge or hash joins were really bad. We also didn’t really know how to index our tables properly back in the day (well, I didn’t), so it hurt performance a lot to join between tables. Our code was horrendous back in the day too, so it didn’t help. But, since at least SQL Server 2000, the optimizer is good, performance is fine. I saw an 86 table join (not one I wrote) run in under 200ms on SQL Server 2000 on ancient processors and small memory. Talk about completely busting that myth. This rule is crazy. Seriously crazy. You’re using a RELATIONAL storage engine and then saying that you can’t actually use relationships. If you don’t have or need relational data, certainly a very valid option, use a non-relational data storage engine. But if you have relational data and you’re using a relational engine, I strongly, very strongly, recommend you use the all the relational tools. That includes JOINs but also includes, primary keys, foreign keys, enforced referential integrity, and all the rest.

5 – Don’t use SSMS’s ‘Edit Top 200 Rows’ because it leaves locks on the table.

One I actually agree with. But not because of what they’re saying. I suspect someone must have found a situation where the UPDATE process from this window held a lock. But I don’t think this is a good way to work because I don’t like the Top 200 rows approach because, well, here’s a query from this menu choice in SSMS:

SELECT TOP (200) BusinessEntityID, CreditCardID, ModifiedDate FROM Sales.PersonCreditCard

Spot any issues? Apart from the missing semi-colon and missing column owners? Yeah a top without an ORDER BY. Which 200 rows are we getting? Yes. If you want to edit data, use an UPDATE command or a proper interface for the data, not this.

6 – Stored procedures should never be used. Prepared statements should never be used.

Plan cache should never be used. Oh, I thought that was the next statement. Look, we don’t have to get into the stored procedures are a blessing from Odin vs. stored procedures are a frost giant trick argument. We can say that using parameterized queries (stored procedures or prepared statements) leads to the reuse of plans for cache. Whereas, writing nothing but ad hoc queries results in massive amounts of queries piled into the plan cache, running through the full optimization process, chewing up your memory, your CPU time, and then you never use them ever again. Can you think of a more efficient mechanism for hurting your performance than taking away these tools? Me neither. Parameterize your queries, somehow. Every ORM tool I’ve worked with or read about can do this (and they can all use stored procedures too, just saying). There is no good excuse for not using parameterized queries for most of your code. And yes, there are places where ad hoc or dynamic T-SQL makes sense. But I’d argue that they’re the exception, not the rule.

Please, take the time to understand what went wrong when you hit a problem. Don’t just shy away from the thing associated with it. It’s like the Dark Ages when they executed inanimate objects after an accident. Surely we’re a little more sophisticated than that. Identify the problem, understand it, then apply appropriate solutions.

If you want to talk more about performance tuning, and you’re attending SQL Rally Nordic 2015 in Copenhagen, I’m doing a full day pre-conference seminar. Click here now to register.

Jan 16 2015

Two Weeks on the PASS Board

The one absolute promise I made about serving on the PASS Board is that I would let you know what I was doing there. Well, we’re two weeks in and I figured now was a good time for a report.

Next week is my first board meeting (and I will report back on how that goes). I’ll be presenting the goals I’ve worked up for the Chapters and see how many of them we can get approved and then how many of them we can deliver.

In the time I’ve had on the board so far, a big part of what I’ve been doing is learning about what has gone before. What kind of goals did we have for last year. Which of them were delivered. Which weren’t. Why. What’s our budget. That sort of thing. It’s been very interesting and enlightening. I’ll say this right here, send Wendy Pastrick (b|t) a note thanking her for her hard work for the PASS Chapters. She’s done some good stuff.

Another thing I’ve been doing is getting hooked into all the communications with the Chapter Leaders and the Regional Mentors. So far, most of that has just been along the lines of welcoming new Chapter Leaders, “Welcome to your new job, sucker, uh, I mean valued volunteer…” Kidding. I ran a chapter for four years. It’s tough, but rewarding. We’re setting up calls with the Chapter Leaders and with the Regional Mentors so that I can talk to them directly to get a good sense of what the heck PASS is doing for them or needs to do for them.

I’ve also been getting connected with my cohorts at the management company. I won’t betray who they are because they haven’t said I could (although, pretty sure a certain “Wonder Woman” is going to be upset that I didn’t drop her name, you figure it out). These are the people that are doing most of the work managing the relationships between PASS and the Chapters and the Regional Mentors. I wouldn’t say I’m just a figurehead, but I don’t get paid to do what they get paid to do. We’ve had several calls in order to keep things going on the goals already set and to get going on the new goals. I even got to approve a thing. WHOOP!

I’ll post again after the board meeting, especially once I see which of the goals I’m trying to get going I manage to get into the plan.

Anyone not seeing anything you expected to see in these updates or you have any other comments, suggestions, etc., post here or send me an email.

And no, I still haven’t changed everything.

Jan 14 2015

Common Table Expressions Are Not Tables

There’s power in naming things. Supposedly some types of magic are even based on knowing the correct names for things. The name for the T-SQL clause Common Table Expression (CTE) is actually pretty accurate. It’s an expression that looks like a table and can be used in common across the entire query (at least I think that’s what the common part refers to). But note, I didn’t say it was a table. It’s not. It’s an expression. If you look at the T-SQL definition at the link, it refers to a “temporary” result set. Now, to a lot of people, that means table. But it isn’t. Let’s look at this in more detail.

Here’s a query that defines a simple CTE and then uses it to query the date in the next T-SQL statement:

WITH    x AS (SELECT    soh.OrderDate,
                        soh.SalesOrderID,
                        sod.LineTotal,
						sod.ProductID
              FROM      Sales.SalesOrderHeader AS soh
              JOIN      Sales.SalesOrderDetail AS sod
                        ON sod.SalesOrderID = soh.SalesOrderID
              WHERE     soh.SalesOrderID BETWEEN 43683 AND 43883
             )
    SELECT  x.OrderDate,
            x.LineTotal,
            p.Name
    FROM    x
    JOIN    Production.Product AS p
            ON p.ProductID = x.ProductID;

If we run this, we get the following execution plan:

CommonExecPlan

Let’s not worry about the plan for the moment. Instead, I want to look at a couple of more queries:

--Derived Table
SELECT  x.OrderDate,
        x.LineTotal,
        p.Name
FROM    (SELECT soh.OrderDate,
                soh.SalesOrderID,
                sod.LineTotal,
				sod.ProductID
         FROM   Sales.SalesOrderHeader AS soh
         JOIN   Sales.SalesOrderDetail AS sod
                ON sod.SalesOrderID = soh.SalesOrderID
         WHERE  soh.SalesOrderID BETWEEN 43683 AND 43883
        ) AS x
JOIN    Production.Product AS p
        ON p.ProductID = x.ProductID;

--Just a JOIN
SELECT  soh.OrderDate,
        sod.LineTotal,
        p.Name
FROM    Sales.SalesOrderHeader AS soh
JOIN    Sales.SalesOrderDetail AS sod
        ON sod.SalesOrderID = soh.SalesOrderID
JOIN    Production.Product AS p
        ON p.ProductID = sod.ProductID
WHERE   soh.SalesOrderID BETWEEN 43683 AND 43883;

Three, nominally, different queries. Yet, if you run these queries, all of them return exactly the same data and all of them have exactly the same execution plan. That’s right, the CTE didn’t change the way that SQL Server derived these queries in any way. Nor would it. That’s because, the CTE is absolutely not a table. It’s an expression.

That name appears to hold magic. We see the word table and we think “Tables, I know all about them” and we proceed to start treating our CTEs as if they were tables, but they’re not. And Microsoft’s definition is completely accurate, if maybe just a touch misleading. These are not tables we’re dealing with when we work with Common Table Expressions.

Let’s look at the difference:

--Table Valued Parameter
DECLARE @TVP AS TABLE
(OrderDate DATETIME,
SalesOrderID INT,
LineTotal NUMERIC(38,6),
ProductID INT,
INDEX tt_ci CLUSTERED (SalesOrderID),
INDEX tt_i NONCLUSTERED(ProductID));

INSERT @TVP
        (OrderDate,
         SalesOrderID,
         LineTotal,
         ProductID
        )
SELECT soh.OrderDate,
                soh.SalesOrderID,
                sod.LineTotal,
				sod.ProductID
         FROM   Sales.SalesOrderHeader AS soh
         JOIN   Sales.SalesOrderDetail AS sod
                ON sod.SalesOrderID = soh.SalesOrderID
         WHERE  soh.SalesOrderID BETWEEN 43683 AND 43883;


SELECT  x.OrderDate,
        x.LineTotal,
        p.Name
FROM    @TVP AS x
JOIN    Production.Product AS p
        ON p.ProductID = x.ProductID;



--Temporary Table
CREATE TABLE #TT
(OrderDate DATETIME,
SalesOrderID INT,
LineTotal NUMERIC(38,6),
ProductID INT,
INDEX tt_ci CLUSTERED (SalesOrderID),
INDEX tt_i NONCLUSTERED (ProductID));

INSERT #TT
        (OrderDate,
         SalesOrderID,
         LineTotal,
         ProductID
        )
SELECT  soh.OrderDate,
                soh.SalesOrderID,
                sod.LineTotal,
				sod.ProductID
         FROM   Sales.SalesOrderHeader AS soh
         JOIN   Sales.SalesOrderDetail AS sod
                ON sod.SalesOrderID = soh.SalesOrderID
         WHERE  soh.SalesOrderID BETWEEN 43683 AND 43883;


SELECT  x.OrderDate,
        x.LineTotal,
        p.Name
FROM    #TT AS x
JOIN    Production.Product AS p
        ON p.ProductID = x.ProductID;

These two queries are using table objects, a table valued parameter and a temporary table. The execution plans are decidedly different from the one above and each other:

TableExecPlans

Interesting enough it chose to do a Merge instead of the Nested Loops and ordered the table scan to accomplish it. Yet, that query runs faster and has less reads than all the others… As long as you don’t count the time to load the temporary table. Then it just doesn’t work as well as the others, at all. Speaking of execution times, the first three queries all had exactly 27 reads and, within some variations, ran in about 30ms on my system. Anyway, query tuning is not the point of the discussion.

You can see the differences in the execution plans that you get between an expression, a CTE, and actual temporary storage, either table variables or temporary tables. Yet, you can also see that the CTE, an expression, just an expression, was resolved in the exact same way as the derived table and the simple JOIN. In short, a common table expression is not a table. Absolutely not. It’s an expression.

On a side-note, if you’re thinking of attending SQL Rally Nordic 2015 in Copenhagen and you want to spend some time talking about query tuning, I’m doing a full day pre-conference seminar. Click here now to register.

Jan 09 2015

I Am Grateful For

A while back I wrote about saying “Thank you” to people. Last night I was listening to NPR on my ride home from a great New England SQL Server User Group meeting (yes, I listen to NPR). Two different reports came on during my ride that got me thinking again about my previous blog post. The first talked about how negative emotions directly impact your ability to make rational decisions. They’ve found that people actually spend more money on items when they’re depressed than they do when they’re happy. There’s a bunch of research around it, but I missed where it was published. It was a great discussion. They found that getting yourself into a positive mood directs your focus outwards rather than inwards. One of the best ways to get that positive emotion train running is to think about what you’re grateful for. More specifically who you’re grateful for in your life.

Then, a couple of segments later the author of this book, Connected: The Surprising Power of Our Social Networks and How They Shape Our Lives
(on my reading list, I’ll get back to you) was on talking about how positive/negative emotions transmit themselves through our social networks, and not just the people next to you, but the people they’re next to and the people they’re next to. Three layers of separation.

I’m not the brightest fellow, but taking my “Thank you” post, in combination with the two segments on NPR, I’ve decided to get off my behind and start a blog series, “I Am Grateful For…” and I’m going to single out someone that I’m personally grateful for and tell you why. Still trying to decide on frequency, but probably one a month for a little while. The purpose is purely selfish. I want to think better, so I’m going for that positive emotion. I want to be in a positive environment, in order to maintain the improvements, so I’m going to spread that positive emotion.

Let’s get started.

I am grateful for Aaron Bertrand (b|t).

I consider Aaron a friend. We’ve known each other quite a few years although I don’t remember where we met exactly (had to have been at an SQL event somewhere). Aaron lives nearby in Rhode Island (and I forgive him for that) so we actually get to see each other occasionally in the real world. I’m ashamed to say that it’s Aaron that usually reaches out for these get-togethers. He’s great that way. He invited me down to his house to help raise money and awareness for ALS. Even though he works for a company in competition with mine, we get along great and he regularly invites me to events his company is putting on. He’s also terribly smart and shares that through his blog all the time, stuff that I learn from and incorporate into what I do (no, I don’t steal it). I appreciate his voice when we get in discussions (you should hear him at an MVP meeting) because he always seems to come at things from such an informed place. Funny enough, he’s one of the meanest Canadians you’ll ever meet, but I think that makes him great too. He takes time away from a very young family to get out to events and share all the stuff he knows on a pretty frequent basis.

Thanks Aaron.

Jan 06 2015

Execution Plans, Performance Tuning and Rum

captain-jack-sparrow-captain-jack-sparrow-2485446-1024-768

This is me on the last cruise

In just a few more weeks I’ll be setting sail on the first of two SQL Cruise events this year. I’m honored beyond my ability to appropriately express it to be included as one of the Technical Leads for both the cruise in February (7-14, 2015, I think there’s a seat or two left) to the Caribbean and the one in June (14-21, 2015, definitely a couple of seats left) to the Mediterranean. Lest you think that this is just an excuse to hang out and drink, you ought to know a little about how sessions are presented on the cruise and the sessions I’m presenting.

Don’t mistake sessions on the boat for your typical one hour session at a SQL Saturday event. These sessions are two hours long. That means two things. First, I can spend quite a bit more time on my topic so that I get to put out some real in-depth information rather than the high level or glossed presentations you can do in one hour. Second, you get to ask questions, make comments, directly interact in an environment where, even if I say we’ll have to talk about this after the session, I’m right there so we’ll be talking about it after the session. Plus, you’ll still have the other Technical Leads there to help answer questions.

Because the most important aspect of the SQL Cruise is your access to both the Technical Leads and the other cruisers (including MVPs, MCMs, and just awesome and amazing people). This is the single most important aspect of SQL Cruise, the ability to sit down with people like Kevin Kline and pick his brain, without pressure, without the need to run to another session, without the ability of Kevin to escape (ha, not that he’d try, he’s one of the most helpful and kind people I know). The same goes for all the other cruisers and Tech Leads.

I’m doing two sessions on the cruise. The first is called “Execution Plans, What Can You Do With Them.” Yeah, yeah, yeah, query tuning. Whatever. You’re right, it is one of the important aspects of what execution plans do. But, in this session, instead of exploring an execution plan to try to improve performance, we’re going to explore execution plans to see what they tell us about how SQL Server works. What happens when you insert data? What happens when there’s a calculated column? How do the queries and the internals of the structures get resolved by SQL Server? Well, I can’t tell you everything about that because the execution plan is still an abstraction layer. But, there’s a ton of really interesting information available within these things. We’re going to dig around in them and see what we can see and just talk about what an execution plan shows us and how we can explore it. I’ve been working obsessively on a bunch of, what I think, are interesting plans with fun little details to explore. And, I’ll share all the scripts so you can explore them on your own.

Next, I’m doing a session on a performance checklist. I’m going to just flat out give away the stuff I go through and look at when setting up a server, designing a database, or consulting on performance problems. But more importantly, I want to discuss with you why I’m doing everything I’m doing. I’m counting on the fact that with all the MVPs and MCMs in the room, I’m going to be able to say a few things that they’re going to jump into the middle of. Oh yes, we’re going to have some excellent conversations during this session. I promise. I make a few suggestions that almost no one agrees with. We’re going to have fun. You won’t want to miss it.

Oh, yeah, and we get to do all this awesome learning, sharing, talking and laughing while basking in warm sunshine and sipping on the beverage of your choice (make mine a Mojito, no, wait, a Mulata Daisy, hang on, how about a Caipirinha? Yeah, I know, that’s not rum, it’s still good, and who knows, the rum might be gone).

SQL Cruise quite literally changes the life trajectory of people involved with it. The direct, extended, involvement between the people on the cruise energizes people to make positive changes in their lives. And we learn stuff. And we have fun. Don’t miss out.