performance Archives - Grant Fritchey

11Apr 2022 by Grant Fritchey 7 Comments

Query Tuning and Easy Solutions

TLDR: There ain't one. I was privileged last week to be able to present a couple of sessions at the SQL Server and Azure SQL Conference (great event, I recommend it). One of my sessions was an intro to query tuning. Basically, I went through a bunch of common code smells and suggested different possible solutions. The one thing that came up, both from my own stories and the questions from the (engaged) audience, is just how much everyone wants a magic, run fast, switch. Query Tuning The Easy Way Here you go. The single easiest way to make your queries run faster: Just throw money at the problem. Buy more hardware. Buy bigger hardware. Get more disks and disk controllers (not just more disks). Go to the next highest…

18Jan 2022 by Grant Fritchey 4 Comments

Two Clustered Indexes?

SQL Server

Everyone knows that you only get a single clustered index, right? Wouldn't it be great though if you could have two clustered indexes? Well, you can. Sort of. Let's talk about it. Two Clustered Indexes First I'm going to create a table: DROP TABLE IF EXISTS dbo.od; GO SELECT pod.PurchaseOrderID, pod.PurchaseOrderDetailID, pod.DueDate, pod.OrderQty, pod.ProductID, pod.UnitPrice, pod.LineTotal, pod.ReceivedQty, pod.RejectedQty, pod.StockedQty, pod.ModifiedDate INTO dbo.od FROM Purchasing.PurchaseOrderDetail AS pod; With that in place, let's start with a clustered index: CREATE CLUSTERED INDEX TestCIndex ON od (ProductID); And, a query to test with: SELECT od.PurchaseOrderID, od.PurchaseOrderDetailID, od.DueDate, od.OrderQty, od.ProductID, od.UnitPrice, od.LineTotal, od.ReceivedQty, od.RejectedQty, od.StockedQty, od.ModifiedDate FROM dbo.od WHERE od.ProductID BETWEEN 500 AND 510 ORDER BY od.ProductID; This results in the following execution plan: OK. Well done, Grant. That's how a clustered index works. The…

03Jan 2022 by Grant Fritchey 4 Comments

Identifying Recompile Causes

SQL Server

Strictly speaking, a recompile isn't really a performance tuning problem. In fact, a lot of time, recompiles are desired because they reflect changes in statistics which are likely to need a new plan. However, you can get really excessive levels of recompiles because of a variety of different issues. So, identifying the causes can be a pain. Here's one way to get it done. Extended Events for Recompile Causes You knew I was going there. Seriously though, we know that, since SQL Server 2005, all recompiles are at the statement level (unless you call for a recompile of a procedure or create that proc with a RECOMPILE hint). So, capturing the recompiles and getting grotty on the details means capturing statements. Understand that this can be expensive, especially in terms…

22Mar 2021 by Grant Fritchey 9 Comments

Query Store as an Upgrade Tool

SQL Server, Tools

There are a lot of uses for Query Store, but one of the most interesting is as an upgrade tool. We all know that upgrades in SQL Server can be more than a little bit nerve wracking. No matter how much you tested stuff in lower environments, deploying an update to production might result in performance issues as your code hits a regression. This is even more true when upgrading from versions of SQL Server prior to 2014 to anything 2014 and above. That's because of the new cardinality estimation engine introduced in 2014. Most queries won't notice it. Some queries will benefit from the better estimates. A few, problematic, queries will suffer. This is where Query Store can be used as an upgrade tool. The Steps We're going to…

27Apr 2020 by Grant Fritchey 7 Comments

How Does The CHOOSE Command Affect Performance?

SQL Server, T-SQL

Questions absolutely drive my blog content and I really liked this one: how does the T-SQL CHOOSE command affect performance. On the face of it, I honestly don't think it will affect performance at all, depending on where and how you use it. However, the answer is always best supplied by testing. T-SQL CHOOSE Command The CHOOSE command was added in SQL Server 2012. It's fairly straight forward. You supply an array and a numbered index for that array and CHOOSE will pull the matching value for that index. It works like this. We'll start with a simple proc and execute it: CREATE OR ALTER PROC dbo.CarrierAndFlag ( @SalesOrderID INT, @Flag INT ) AS BEGIN SELECT sod.CarrierTrackingNumber, CHOOSE(@Flag, 'A', 'B', 'C') AS Flag FROM Sales.SalesOrderDetail AS sod WHERE sod.SalesOrderID =…

13Jan 2020 by Grant Fritchey 4 Comments

Causality Tracking in Extended Events

SQL Server

If you go through all the stuff I've written about Extended Events, you'll find that I use causality tracking quite a bit. However, I've never just talked about what causality tracking is and why I use it so frequently. Let's fix that issue now. Causality Tracking Causality tracking is quite simple to understand. It's property that you set for a given session. A session, of course, is defined by one or more events and a target. You can define things about a session, like it's name, when you define the session itself. Turning on, or enabling, causality tracking is just a matter of defining that the session will have causality tracking. It looks like this in the GUI: It looks like this in the T-SQL code: CREATE EVENT SESSION QueryBehavior…

12Nov 2018 by Grant Fritchey 16 Comments

Bad Query Performance Tips Rebutted

T-SQL

Once upon a time, someone, somewhere, wrote a list of bad query performance tips and they've been copied all over the internet, over and over. Even worse, sometimes the tips are copied or edited incorrectly, making a bad suggestion even worse. Can we please, stop copying this list? I've tried to poke holes in this list before, one point at a time. This time, I'm just taking it all on. Follow This Advice I'm going to list all the bad query performance tips and I'll explain where they're good and where they're bad. However, the single most important thing I can tell you is, test your own system. Some of these tips have some merit, situationally. You'll need to see if you're in the situation. So please, don't just trust…

29Oct 2018 by Grant Fritchey 14 Comments

Explicitly Drop Temporary Tables Or Wait For Cleanup?

SQL Server, SQL Server 2016, SQL Server 2017, T-SQL

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 =…

24Sep 2018 by Grant Fritchey 14 Comments

Extended Events and Stored Procedure Parameter Values

SQL Server, SQL Server 2016, SQL Server 2017

One complaint I've received frequently is that you can't see stored procedure parameter values in Extended Events. That is patently not true. However, it does depend on where and how you capture the events and which stored procedure parameter values you're going for. I think this is a holdover from 2008 when Extended Events... well, let's be kind and say... didn't work well. Now, they do. Let's explore this a little. Capturing Stored Procedure Executions As with most things, there's more than one way to capture stored procedure execution in Extended Events. First up, it depends entirely on how they're called and on your intentions when you capture the information. Here are the three methods I know to capture just the completion metrics on stored procedure calls: rpc_completed sql_batch_completed module_end…

11Jun 2018 by Grant Fritchey 6 Comments

Plan Metrics Without the Plan: Trace Flag 7412

SQL Server, SQL Server 2016, SQL Server 2017, T-SQL

I place a lot of emphasis on capturing actual execution plans because of the runtime metrics, but with Trace Flag 7412, we don't need the plan. This is great news, because capturing execution plans, even using extended events, is an expensive proposition. However, using either the query_thread_profile event, or, Trace Flag 7412, we can get the runtime metrics without the plan. Trace Flag 7412 Here's how it works.You can either be running the extended event, query_thread_profile (a debug event, but one documented and supported by Microsoft) or, enable the Trace Flag 7412. I like to use the extended event in a targeted fashion to easily see behaviors on a query without having to capture the plan. You can even capture the information and then combine it with an estimated plan…