Set based operations means you should put everything into a single statement, right? Well, not really. People seem to think that having two queries is really bad, so when faced with logical gaps, they just cram them into the query they have. This is partly because SQL Server and T-SQL supports letting you do this, and it's partly because it looks like a logical extension of code reuse to arrive at a query structure that supports multiple logic chains. However, let's explore what happens when you do this on particular situation, a CASE statement in a GROUP BY clause. You see this a lot because a given set of data may be needed in slightly different context by different groups within the company. Like many of my example queries, this…
I love questions. I recently received one about new plans in the Query Store (available in Azure SQL Database now and in SQL Server 2016 after June 1). Let's say you have selected a plan that you want to force. You set it up. Now, let's say the plan ages out of cache or even goes through a recompile. During the recompile, due to out of date statistics or skew in the statistics, you would, under normal circumstances, get a new plan. However, with Query Store and plan forcing, the plan that's going to be used is the plan that is being forced. But, does that other plan, the one not used, get stored in Query Store? I have no idea. Let's find out. The Setup To start with, a small stored procedure…
A lot of times you'll hear how people are experiencing sudden, intermittent, poor performance on a query, bad parameter sniffing at work, so they'll fix it by running the following code: DBCC FREEPROCCACHE(); BOOM! Yeah, you just nuked the cache on your server because you wanted to take out a single terrorist query. Now, yes, that problematic query is going to recompile and hopefully have a better execution plan. Also, so are all the other queries on your system. That spike in CPU and the slow-down all your business people are experiencing... Your fault for going nuclear. Instead of a nuke, why not use a sniper rifle to just remove the one problematic plan. Here's a little piece of code to help out: DECLARE @PlanHandle VARBINARY(64); SELECT @PlanHandle = deps.plan_handle FROM…
I have, in the past, made way too much of the need for Actual Plans when doing performance troubleshooting. The primary reason for this is to get the Actual Plan in order to see the differences between the Actual and Estimated Row Counts as a means of understanding how the optimizer saw the data. But, is that the only thing that's different between Actual & Estimated Plans? Well, pretty much, yeah. I took two fairly average execution plans from SQL Server 2014 and ran them through Altova's XML Spy, which does XML comparisons similar to how Redgate SQL Compare will compare two data structures for you. Here is every single difference I found. Everything was additional information in the Actual Plan. In the information for the first operator, in my case,…
Another month another bunch of great presentations. I almost don't want to do this any more. It's hard. I sit through a presentation and I think, "Well, here's the winner this month." Then I go to another presentation and I think, "Well, fudge, now one of these people loses." Then I go to a third and I'm simply blown away. And now I have to pick. Well, it's hard. So let me do this, I'm going to declare two winners this month, but only review one of them. Hey, my blog, my rules. First, I want to award speaker of the month for July 2014 to Wayne Sheffield(b|t) and his presentation Table Variables and Temp Tables that I saw at SQL Saturday 294. What's my measure? That I learned stuff…
I've been exploring how natively compiled procedures are portrayed within execution plans. There have been two previous posts on the topic, the first discussing the differences in the first operator, the second discussing the differences everywhere else. Now, I'm really interested in generating bad execution plans. But, the interesting thing, I wasn't able to, or, rather, I couldn't see evidence of plans changing based on silly things I did to my queries and data. To start with, here's a query: CREATE PROC [dbo].[AddressDetails] @City NVARCHAR(30) WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER AS BEGIN ATOMIC WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'us_english') SELECT a.AddressLine1, a.City, a.PostalCode, sp.Name AS StateProvinceName, cr.Name AS CountryName FROM dbo.Address AS a JOIN dbo.StateProvince AS sp ON sp.StateProvinceID = a.StateProvinceID JOIN dbo.CountryRegion AS cr ON…
The combination of in-memory tables and natively compiled procedures in SQL Server 2014 makes for some seriously screaming fast performance. Add in all the cool functionality around optimistic locking, hash indexes and all the rest, and we're talking about a fundamental shift in behavior. But... Ah, you knew that was coming. But, you can still write bad T-SQL or your statistics can get out of date or you can choose the wrong index, or any of the other standard problems that come up that can negatively impact all those lovely performance enhancements. Then what? Well, same as before, take a look at the execution plan to understand how the optimizer has resolved your queries. But... Yeah, another one. But, things are a little different with the natively compiled procedures and…
KILT DAY! Today we have to eat our vegetables and then get lots and lots of sweet desert. Or. Today we hear about PASS Finances as a part of the official annual meeting and then we get to hear Dr. David Dewitt speak (completely and utterly getting our nerd on and squeeing like teenage girls at a Bieber concert). I will be live-blogging this event, so watch this space. 8:20: Douglas McDowell kicks off the key note today. Â the vast majority of the money that runs PASS comes from the Summit. That's right, by attending the Summit you're also supporting the organization. The Business Analytics Conference, which kicked off this year also provides quite a bit more money to the organization. 8:25: PASS has changed its budgeting process. At this…
I write quite frequently about SQL Server Execution Plans. I started in that area just because that’s how you figure out what a query is doing and sooner or later, we all have to tune a query. I found I was doing it sooner and more frequently. When the opportunity came up to write a book , I jumped on it. Now I find myself presenting, rather frequently, on execution plans. One of the people I’ve learned from over the last several years is Gail Shaw (blog|twitter). I first saw Gail on stage at the PASS Summit, I think it was 2007. A co-worker of mine was picked, along with Gail, to go on stage for the Quiz Bowl. Gail was answering all the questions. If you go over to…
Tom LaRock has a new meme for Meme Monday. It’s all about the problems caused in your system other than disks. Thankfully, despite the title, I don’t have to list 99 separate things, only 9, but you know what, 99 is possible. I’m going to present the problems. You find the solutions on your own today. Let’s go. Recompiles I’ve seen queries so big that they take more than three minutes to compile. That’s the edge case, but as an edge case it is educational. The most important thing to remember about recompiles is that they are driven by data changes. Once a threshold is reached on any given set of statistics, all queries referencing that set of statistics gets marked for recompile. The key words and tricky phrase here…