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…
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…
A couple of weeks ago we had a small contest here to pick a skill for Alexa. The entries were all wonderful, but I could only pick five as winners with one of them being the top winner. Thanks everyone for playing. In no particular order, here are the four runner's up, followed by the final winner along with my comments. Adrian Porter:Â Alexa, let's get real here! Please protect me from my stupid self. Take whatever measures needed. And for the final time, I don't want to play chess, let's play Global Thermonuclear War! [major points for an excellent nerd reference and one of my favorite movies] Zingiber: Alexa, drive me to work [You have to love the short simple set of instructions here, plus, extra time to read…
The winner is: Everyone. Hey, the challenge was random. I didn't promise a prize or a winner. Instead, I suggested everyone blog based on an image of the great Tom Baker: In no particular order we have: Persist and Aggregate Index Stats Across Server Restarts by Eric Cobb Nice use of the image, incorporating it into an interesting technical post on how to persist your index usage stats. Useful information since this DMV is reset when you restart the server (or fail it over or...). Good technical blog on a useful topic, data about your system. Working with SQLSaturday SpeedPass by Wayne Sheffield +10,000 points for both using the image well and for the PowerShell. -10,000 points for making me comment on a post about PASS (I'm the EVP of…
The power and capabilities of Azure SQL Data Warehouse are pretty amazing. However, it's still basically SQL Server under the covers. SQL Server still needs maintenance and one type of maintenance is keeping statistics up to date. Statistics maintenance is doubly important within Azure SQL Data Warehouse because statistics are not created automatically, nor are they maintained automatically. It's up to you to ensure your statistics are up to date. Statistics in Azure SQL Data Warehouse It's worth re-emphasizing the point that there is no automatic creation of statistics in Azure SQL Data Warehouse. This means you need to plan to add statistics to your tables. Microsoft recommends an easy method is to add them to every column. While that does make things easy, it's not like statistics are free. If you…
Monday I got in on Sunday and chose to have a small dinner with a couple of friends, quiet, preparing. Monday was a less hectic day than the others . The Board had the morning off, although Redgate had me go and give a session at an event. Monday afternoon was one of our three in-person board meetings. The minutes will be published soon. I was responsible for running the meeting. I also presented two topics, first, and most importantly, our current financial status. Then I presented the initial set of thoughts towards some SMART goals for Global Growth, which I will share once they are further developed . Monday evening I had two events I had to attend. First, as part of the Executive Committee, I attended the kick off dinner…
I love the Query Store. Seriously. It’s a huge leap forward in the capabilities of Azure SQL Database and SQL Server in support of performance monitoring and query optimization. One of my favorite aspects of the Query Store is the ability to force plans. Frankly though, it’s also the scariest part of the Query Store. I do believe that plan forcing will be one of the most ill-used functions in SQL Server since the multi-statement table-valued user-defined function (don’t get me started). However, unlike the UDF, this ill-use will be because of poor understanding on the part of the user, not a fundamental design issue. No, plan forcing and the Query Store are very well constructed. Let me give you an example of just how well constructed they are. Let’s…
I didn't post an update last month, but I did do something more important: I elicited your feedback on a question the Board needs to act on. Here's what I've been up to. First, based on your feedback (thanks) and conversations that I've had with members of the Board, I'm putting together recommendations for how we deal with payment and PASS-branded (but not PASS run, that's already covered in the By-Laws) events. I'll be presenting that in June at the in-person Board meeting. Speaking of the Board meeting, I've also been soliciting topics (although really, my priceless partner at PASS HQ, Vicki, is doing all the real work). We're also preparing the budget for FY 2017 (again, the real work is being led by an equally priceless Sandy at HQ). Beyond a series of…