Grant Fritchey

24Jan 2022 by Grant Fritchey 3 Comments

Which Columns Are Looked Up?

A common issue in performance is the lookup, key or RID, but the question frequently asked is, which columns are looked up? You need to know in order to try to address the issue. The answer is easy enough to find. Let's take a quick look. Which Columns Are Looked Up? Let's take an example query: SELECT p.NAME, AVG(sod.LineTotal) FROM Sales.SalesOrderDetail AS sod JOIN Production.Product AS p ON sod.ProductID = p.ProductID WHERE sod.ProductID = 776 GROUP BY sod.CarrierTrackingNumber, p.NAME HAVING MAX(sod.OrderQty) > 1 ORDER BY MIN(sod.LineTotal); There is a nonclustered index on the ProductID column. Depending on the value, it's going to reduce the rows returned extremely well. So, the optimizer is likely to pick that index. Here's the index definition: CREATE NONCLUSTERED INDEX IX_SalesOrderDetail_ProductID ON Sales.SalesOrderDetail (ProductID ASC) In…

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…

10Jan 2022 by Grant Fritchey 17 Comments

Why Is “WHERE 1=0” Slow?

Uncategorized

I saw a question the other day, questioning why they're creation of temporary tables was so slow. What they were doing was (a much more complicated version of) this: SELECT soh.SalesOrderID, sod.SalesOrderDetailID, soh.SalesOrderNumber INTO #MyTempTable FROM Sales.SalesOrderHeader AS soh JOIN Sales.SalesOrderDetail AS sod ON sod.SalesOrderID = soh.SalesOrderID WHERE 1 = 0; Now, my immediate response, and no, I didn't type it, was, "Hey, you're not "creating" temporary tables. You're using SELECT...INTO." Let's be fair. That is a method to create temporary tables. Also, that method has some advantages. Biggest one being, you don't have to know, or define, the data structure. You get it for free. It does come down to one thing though. Why is "WHERE 1=0" slow? WHERE 1=0 Math may be weird these days, but in good…

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…

29Nov 2021 by Grant Fritchey No Comments

Get SQL Server 2022 Now: It’s Called Azure

Azure, SQL Server

If you're like me, your palms went into a cold sweat to get your grubby fingers on the bits for SQL Server 2022 a few nanoseconds after you heard the announcement. Well, you might be waiting a while. As will I. However, I really do want to start experimenting with some of the functionality right now. If you do to, then join me at Azure SQL Database. Azure SQL Database as a Preview for SQL Server 2022 Yeah, I know it's not the same. I get it. You have a need to run this stuff on big iron, not virtual, and certainly not in the cloud. Still, you want to learn, right? Tons of the new functionality is either already in Azure or incorporates Azure functionality. You can get started…

04Oct 2021 by Grant Fritchey 5 Comments

PASS Data Community Summit 2021: I’m Excited!

Professional Development

I sincerely hope this isn't the first time you're hearing about the Pass Data Community Summit that's coming up in just a few weeks on November 8-12, 2021. But, just in case you haven't heard, let me tell you about a few things I'm personally excited about. Networking! There is a ton of stuff getting worked on to make this an interactive event. Even though this will be an online event (and a free one), we're doing everything we can think of to get you a community experience. We're providing mechanisms for all sorts of different networking. You'll be able to give a few virtual hugs to those you've missed. Learning! Check out the list of sessions. Everything from straight up, good, old fashioned query tuning in SQL Server, to…

27Sep 2021 by Grant Fritchey 1 Comment

Login Timeouts

You Can't Do That In Profiler

I was recently approached at work about a company that was seeing tons of timeouts in SQL Server. I was asked how to troubleshoot this. There are lots of posts by people on this topic, but I found something I didn't see anywhere else, let me share it with you. Extended Events You must have known I was going to bring up Extended Events. Surely. Well, I am. Now, if you search up "timeout" in the events, you find lock timeouts, execution plan timeouts (ooh), and stuff like that. It's not related to the login timeout. So, look up "connection" or "log in". You get a lot of information, but again, none of it is related to timeouts. In fact, the best info is in process_login_finish. It does include login…

20Sep 2021 by Grant Fritchey No Comments

Containers: A Short Rant

Containers

I find myself doing more and more work with containers. Yet, I also find that a lot of people seem to be resistant to the concept. I'm always surprised when technologists reject technology without fully understanding what it does. Let's talk about this just a little. Containers Are Virtual Machines OK, not really. Containers are not actually, literally, virtual machines. However, containers are, conceptually, very similar to virtual machines. The key difference is, a container carries what it needs from the operating system it was created from in order to function. But, except for that, these things are just an extension of the concepts behind virtualization. Now, I know, in 2021, you are using virtual machines, in whole, or in part, to manage your IT infrastructure. You may host them,…

13Sep 2021 by Grant Fritchey 6 Comments

Rewriting The Query Tuning Book

Uncategorized

While I have not yet signed the contract, I have submitted an outline and proposal for a new version of my book on query performance tuning. Most of the information in the existing book is still very valid and immediately applicable. However, some of the information is out of date. Other pieces can be tweaked to tell a better story. A little bit of it is just wrong or has aged out of applicability. Because of all this, I'm not simply going to update the existing book. Instead, this time, it's a complete, from scratch, rewrite. All the way. I'm planning to drop entirely the chapters on hardware. I'm doing this for a bunch of reasons. One, hardware has changed radically over the years. Of all the information in the…

19Jul 2021 by Grant Fritchey No Comments

Filtering Extended Events Using Actions

SQL Server, You Can't Do That In Profiler

Did you know, you can use Actions to Filter Extended Events? Well, you can. Filtering is one of the greatest ways in which Extended Events differentiates itself from other mechanisms of gathering information about the behavior of SQL Server. You can put Actions to work in your filtering. Best of all, the Actions don't have to be collected in order to put them to work filtering your Extend Events. Using Actions To Filter Extended Events Actions, also called Global Fields, are additional bits of data that you can add to a given Event when you're setting up an Extended Events Session. They are programmatic additions to the Event, as described here. Think of them sort of like triggers. In practice, adding an Action, database_name, to an Event, like the rpc_completed…