I've always felt responsible for making such a big deal about the differences between estimated and actual plans. I implied in the first edition of the execution plans book (get the new, vastly improved, 3rd edition in digital form for free here, or you can pay for the print version) that these things were so radically different that the estimated plan was useless. This is false. All plans are estimated plans. However, actual plans have some added runtime metrics. It's not that we're going to get a completely different execution plan when we look at an actual plan, it's just going to have those very valuable runtime metrics. The problem with getting those metrics is, you have to execute the query. However, this is no longer true in SQL Server…
To my Data Family, Redgate Family, #SQLFamily, and Family Family:Thank you!I love you!Keep going!
I'm going to start with a sentence that makes a lot of people crazy; As a DBA and database developer, I love Entity Framework. That's right. Entity Framework is the bomb. It's amazing. It does wonderful stuff. Entity Framework makes the developers lives and jobs easier. It makes my life and job easier. Yes, Entity Framework will improve your job quality and reduce stress in your life. With one caveat, it gets used correctly. That's the hard part right? There is tons of technology that makes things better, if used correctly. There are all sorts of programs that make your life easier, if used correctly. Yet, all of these, used incorrectly, can make your life a hell. One nit that I've always had with Entity Framework is that it's very…
I am quite excited to announce that the latest, most up to date, and by far the largest, copy of my book on query tuning is now available. 900 pages of information on how to improve the performance on your Azure SQL Database and SQL Server instances has been published. You can get your hands on the digital copies now and the print copies will be available shortly. Follow this link to Amazon to get your copy. Special, public, and heart-felt thanks to my technical editor, my hero, Joseph Sack. He's worked with me through four editions of this monster and the book wouldn't be what it is without him. If you'd like to learn some of the material in the book, I'll be presenting an all-day seminar two more…
Four years ago, after a bunch of dithering and some negotiations with Tony Davis, my editor, I started to update my book, SQL Server Execution Plans. We managed to convince Hugo Kornelis to be the tech editor. I started to do the real writing in early 2015. I was most of the way through a first draft and no one liked it. Tony was unhappy. Hugo was unhappy. I was unhappy. I was just trying to update the existing book, SQL Server Execution Plans. It wasn't working. We all came to the conclusion that the old book was wrong. Not simply in a technical sense, although there was a lot of that, but in a structural sense. So we started rearranging things. SQL Server 2014 came out, but I was…
I'm desperately working to finish up a new version of my book on Execution Plans. We're close, so close. However, you do hit snags. Here's one. My editor decided to change one of my queries. I used a local variable so that I got one set of behaviors. He used a hard-coded value to get a different set. However, the really interesting thing was that his query, at least according to the execution plan, went to simple parameterization. Or did it? Simple Parameterization The core concept of simple parameterization is easy enough to understand. You have a trivial query using a hard-coded value like this: SELECT * FROM Person.Person AS p WHERE p.BusinessEntityID = 42; The resulting execution plan looks like this: The initial, graphical, pointer that we're seeing parameterization…
Let's face it, the core of a presentation is you. Your knowledge and your ability to share that knowledge through whatever means you choose, slides, demos, sock puppets, whatever, is the primary tools you need to hone. Know what you know and be ready to acknowledge what you don't. Put out the information as only you can. That's the key to presenting. But... A few things can help reduce your stress and make your life easier. Let's talk about them. My Presentation Tools Whether your travel to the event or not, chances are good, that whatever code camp, SQLSaturday, or awesome event where you are presenting is not your home or office. This means, you can't know what their set up is. Because of this, road warrior or not, if…
Query Store has mechanisms for automatically cleaning your data. It is possible to cause them to break down. While presenting a session about the Query Store recently, I was asked what happened if you set the size of the Query Store below the amount of data currently in the store. I didn't know the answer, so we tried it. Things got a little weird. Bryan Hundley of Marathon Consulting asked the question, so Bryan, this blog post is for you. Automated Cleanup There are actually two kinds of automated cleanup inside the Query Store. First, you have a time-based cleanup. By default it keeps queries that have been accessed within the last 30 days. Anything older, it tosses. If you have the stale_query_threshold_days (all in sys.database_query_store_options) set to 0, it…
I know that when some people see AdventureWorks, their vision turns all red around the edges, their blood pressure spikes and they begin to foam at the mouth. I do understand. AdventureWorks, Microsoft's very old, near ubiquitous, sample database suffers from a lot off issues. What's The Matter With AdventureWorks? Let's start off with the general design of the schemas. It's broken up in odd ways. Production has a TransactionHistory and a TransactionHistoryArchive instead of Sales? That doesn't make any sense. What the heck is up with the general database design anyway? I mean, pick on one example. A person can have more than one phone number. Fine. But, what about people who share phone numbers like that ancient land line in my house that is only EVER used by…
Lots of people are confused by how to deal with bad parameter sniffing when it occurs. In an effort to help with this, I'm going to try to make a decision flow chart to walk you through the process. This is a rough, quite rough, first draft. I would love to hear any input. For this draft, I won't address the things I think I've left out. I want to see what you think of the decision flow and what you think might need to be included. Click on it to embiggen. Thanks to the attendees at my SQLSaturday Louisville pre-con for the great questions and the inspiration to get this done. Thank you in advance for any and all feedback.