I absolutely love Query Store and the ability it provides to force a plan is amazing. However, there are a lot of little gotchas in this functionality. I just recently found one that has quite a far reaching effect. Let's talk about what happens when you DROP and then CREATE a stored procedure. Query Store and Plan Forcing Let's quickly recap how Query Store works and how Plan Forcing works. First, Query Store is driven by query, not by procedure, not by batch, but by query. Second, plan forcing is also by query. However, there are queries and there are queries. Let's take this as an example: CREATE PROC dbo.AddressByCity @City NVARCHAR(30) AS BEGIN SELECT a.AddressID, a.AddressLine1, a.AddressLine2, a.City, sp.Name AS StateProvinceName, a.PostalCode FROM Person.Address AS a JOIN Person.StateProvince AS…
I've decided that, in fact, it is time to start moving people off the ancient technology, Profiler. Before, I always said, stay where you're comfortable. However, keeping people comfortable means that they're also going to keep promoting Profiler/Trace to new people on new platforms. That is a real problem. To fix the problem of old school, slow, inferior, methods of data collection, troubleshooting, and consuming metrics, we need to educate people. Extended Events are not simply a replacement for Trace. They're not simply another way to gather query metrics. No, in fact, this is a whole new tool, with new functionality and a very high level of support and engagement from Microsoft. Extended Events are where all new functionality since 2012 provides mechanisms for monitoring behavior. As technologists we should…
Query tuning is not easy. In fact, for a lot of people, you shouldn't even try. It's much easier to buy more, bigger, better hardware. Yeah, the query is still slow on newer, faster hardware, but not as a slow as it was. However, sooner or later, you're going to have to start to spend time fixing queries. In fact, you can find that fixing queries actually is more cost effective than buying more hardware. The problem is, query tuning is not easy. So, what do you do? Microsoft Can Help There are a number of tools available to you, right now, provided by Microsoft that can help you better and more easily tune your queries. This ranges from extended events to query store, and absolutely includes execution plans and…
The first time you see a new execution plan that you're examining to fix a performance problem, something broken, whatever, you should always start by looking at the first operator. First Operator The first operator is easily discerned (with an exception). It's the very first thing you see in a graphical execution plan, at the top, on the left. It says SELECT in this case: This is regardless of how you capture the execution plan (with an exception). Whether you're looking at an execution plan from the plan cache, Query Store, or through SSMS, the execution plan, regardless of complexity, has this first operator. In this case, it says UPDATE: If you get an execution plan plus runtime metrics (previously referred to as an "actual" execution plan), you'll still see…
I saw this question on SQL Server Central the other day and had an immediate, visceral reaction. I know why. Now, before I explain my answer, please, let me reassure you. I get it. You're busy. What I'm about to suggest is not meant as a direct critique of you. It's just an observation of the human condition. Heck, maybe I'm wrong. So, before you write the angry screed about how busy you are and why you can't possibly do what I'm about to suggest, believe me, I already understand. I'm still going to suggest something that's going to make some of you angry. Common Knowledge If you'll permit me, I want to talk about Extended Events before we talk about Columnstore. SIDE NOTE: Standing invitation, any time I'm at…
A question that I've seen come up frequently just recently is, how to track CPU use over time. Further, like a disk filling up, people want to know how to predict their CPU usage, so that they can easily decide "now is when I upgrade the hardware". Well, the bad news is, that ain't easy. CPU Use Over Time There are a bunch of ways to look at processor usage. The simplest, and probably most common, is to use the Performance Monitor counters such as '% Processor Time'. Query this, you can get an average of the processor usage at a moment in time. Ta-da! Fixed it. I thought you said this was hard Grant. Well, hang on. Are you running on a single processor machine? If so, cool, maybe…
I consider myself to be the most responsible for making such a huge deal about the differences between what is labeled as an Estimated Plan and an Actual Plan. I walked it back in the second edition of the Execution Plans book. Hugo and I completely debunked the issue in the third edition of the Execution Plans book. That is the one you should all be referencing now. As I like to joke, the guy who wrote the first two editions of the book was an idiot (and lest anyone take offense, let's be clear, I'm the idiot). Now, I'm trying my best to make this whole issue more clear. Let's talk about the "different" plans you can capture in SQL Server. Estimated Plan This is where you have a…
Not really, but sort of. The beauty of containers, at least in a dev/test environment, is the ability to spin them up while you need them and then throw them away when you're done. Containers give you a bunch of functionality not otherwise available through a VM. However, once you've spun up a container, they're so dull. Why Are Containers Boring Grant? I'm so glad you asked. Last week I was presenting at SQLIntersection (great show, you should consider attending). I was talking about Query Store in SQL Server 2019. One person in the audience asked, "Can Query Store run inside a container?" I responded, "Great question, let's check." I then switched over to VS code to show this: docker run ` --name DemoSharedVol ` -p 1460:1433 ` -e "ACCEPT_EULA=Y"…
It's a somewhat late addition, but I have an all-day Training Day at SQLBits. It takes place on Thursday, February 28th. You can read all about it on the SQLBits web site. I want to take a moment here to expand on the information that we're going to cover. I think the abstract does a good job of conveying what we'll be doing all day, but I figured a little more detail won't hurt. Query Tuning is Hard This is the very first thing I talk about. Query tuning is hard. I've got a nearly 1,000 page book on the topic, which should give you an idea of just how much material there is to cover. With the training day I've decided to focus on the tools that Microsoft gives…
I was recently asked if we are going to see performance differences if we explicitly drop temporary tables. I couldn't remember the specifics, but I said it actually didn't matter. However, that answer has bugged me, so I set up a quick test. Explicitly Drop Temporary Tables We could make this a crazy set of tests, but I wanted to keep things relatively simple. I created two procedures that create identical temporary tables. One drops the tables, the other doesn't: CREATE PROC dbo.BOMDrop AS CREATE TABLE #BOMData (BOMLevel SMALLINT, PerAssemblyQty DECIMAL(8, 2), ComponentName VARCHAR(50), ProductAssemblyName VARCHAR(50), UnitMeasure VARCHAR(50)); INSERT #BOMData (BOMLevel, PerAssemblyQty, ComponentName, ProductAssemblyName, UnitMeasure) SELECT bom.BOMLevel, bom.PerAssemblyQty, c.Name, pa.Name, um.Name FROM Production.BillOfMaterials AS bom JOIN Production.Product AS pa ON pa.ProductID = bom.ProductAssemblyID JOIN Production.Product AS c ON c.ProductID =…