Query Store Wait Statistics with sys.query_store_wait_stats

Azure, SQL Server 2017
The second best thing to questions that people ask is when I sit down to write a book. It's so easy to miss things in the day-to-day grind of doing work. Then, late at night, you're working on a chapter, so you read up on the documentation to ensure that you're not missing anything. Of course, then you find, yes, you are missing something. In my case, sys.query_store_wait_stats. sys.query_store_wait_stats. If you follow the link above, it'll give you what you need to know, but, I figured I'd provide a little more clarity because I think there are some pitfalls in using this data. I love Query Store (do a search to see all the exploration I've done with it). One of my favorite things is the time intervals. It breaks…
Read More

Execution Plan Metrics and Units of Measure

SQL Server, SQL Server 2016, SQL Server 2017
Have you ever looked at the properties inside an execution plan and wondered what the units of measure were? I know I have. I also get the question pretty frequently about all sorts of properties. What does EstimatedAvailableMemoryGrant display it's units in anyway? For that matter, what the heck is an EstimatedAvailableMemoryGrant? Showplan Schema and Units of Measure The answer to those questions and a whole lot more is pretty easy to find. You just have to look to the ShowPlan Schema. Microsoft has all the schema's published, going back to 2005. The next time you're faced with a question such as, what are the units of measure of the MemoryGrantInfo: Or, what the heck is SerialDesiredMemory and why is it exactly matching RequestedMemory, you just have to go and…
Read More

Why I Use AdventureWorks for Demos

SQL Server, SQL Server 2016, SQL Server 2017
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…
Read More

Database Fundamentals #15: Modifying Data With T-SQL

Database Fundamentals, SQL Server, SQL Server 2016, SQL Server 2017
The preferred method for modifying your data within a database is T-SQL. While the last Fundamentals post showed how to use the GUI to get that done, it's not a very efficient mechanism. T-SQL is efficient. UPDATE The command for updating information in your tables is UPDATE. This command doesn’t work the same way as the INSERT statement. Instead of listing all the columns that are required, meaning columns that don’t allow for NULL values, you can pick and choose the individual columns that you want to update. The operation over-writes the information that was stored in the column with new information. In addition to defining the table and columns you want to update, you have to tell SQL Server which rows you’re interested in updating. This introduces the WHERE…
Read More

Get That Profiler Feel in Extended Events

SQL Server, SQL Server 2016, SQL Server 2017
I know. You love Profiler. I hear you. You're wrong, but that's OK. Kidding... mostly. Unfortunately though, I think a lot of what passes for issues and problems with Extended Events is actually a lack of knowledge about how they work. Let's take an example and run with it. No Grid in Extended Events One of the pushbacks I hear about using Extended Events is that the Live Data GUI just doesn't have that neat Profiler grid output. Instead you see a list of events in the top pane and then you have to look at the details in the bottom pane. It looks like this out of the gate: You're right. That's a royal pain. That's it. Toss Extended Events. Back to Profiler. Well, hang on a second. Let's…
Read More

Extended Events and Profiler: XE Profiler

SQL Server 2017
There's a war on in the SQL Server world. On the one side is Profiler (although, really, everyone uses Trace Events). On the other, the "new" (they came out in 2008 with a full GUI in 2012, so...) Extended Events. Lots of people have picked sides on this, including Microsoft. New Trace Events There are none. All the new functionality of every sort from Availability Groups to Query Store to R & Python, have Extended Events created for them. Trace Events, and the technologies supporting them in the form of Profiler, are a dead end. Don't fear. While Trace is on the deprecation list, there doesn't appear to be any fear of that technology being removed completely. At least it won't be removed in the foreseeable future. A future which,…
Read More

There Is No Difference Between Table Variables, Temporary Tables and Common Table Expressions

SQL Server, SQL Server 2016, SQL Server 2017, T-SQL
I actually saw the above statement posted online. The person making the claim further stated that choosing between these three constructs was "personal preference" and didn't change at all the way SQL Server would choose to deal with them in a query. Let's immediately say, right up front, the title is wrong. Yes, there are very distinct differences between these three constructs. Yes, SQL Server will absolutely deal with these three constructs in different ways. No, picking which one is correct in a given situation is not about personal preference, but rather about the differences in behavior between the three. To illustrate just a few of the differences between these three constructs, I'll use variations of this query: [crayon-5c6dba5096383604697950/] The execution plan for this query looks like this: The number of reads is 1,269 and the duration…
Read More