09Feb 2022 by Grant Fritchey 6 Comments

SQL Server, PostgreSQL and the Future

PostgreSQL, SQL Server
This is just a quick note to talk about the future, mine, yours and this blog. First, I'm not abandoning SQL Server. I'm actively working on a revision of the query performance tuning book (something I should be working on instead of writing this post). SQL Server is my first, and best, database love. We're not going anywhere, SQL Server and I. You can expect more posts on SQL Server, query tuning, execution plans, DevOps, community, #sqlfamily, and everything I've blogged about for the last, ooh, 14 years next month. Second, our family is growing. Just like when a family brings home a new baby, well, I'm adding another database. It doesn't mean I don't love my first database. I still do. We're just going to ALSO be talking about…
Read More
07Feb 2022 by Grant Fritchey 1 Comment

Query Your Statistics: dm_db_stats_properties

SQL Server
We've always been able to look at statistics with DBCC SHOW_STATISTICS. You can even tell SHOW_STATISTICS to only give you the properties, STAT_HEADER, or histogram, HISTOGRAM. However, it's always come back in a format that you can't easily consume in T-SQL. From SQL Server 2012 to everything else, you can simply query sys.dm_db_stats_properties to get that same header information, but in a consumable fashion. dm_db_stats_properties You can pretty easily query the function dm_db_stats_properties: SELECT ddsp.object_id, ddsp.stats_id, ddsp.last_updated, ddsp.rows, ddsp.rows_sampled, ddsp.steps, ddsp.unfiltered_rows, ddsp.modification_counter, ddsp.persisted_sample_percent FROM sys.dm_db_stats_properties(OBJECT_ID('Production.Location'), 1) AS ddsp; You just have to pass in the object_id value and the stats_id value and you're off to the races. I can hear you. Why? So we can do things like this. What if I want to look at statistics that have fewer…
Read More
03Feb 2022 by Grant Fritchey No Comments

PostgreSQL Restore in Azure

Azure, PostgreSQL
I recently wrote an article about PostgreSQL restores (and by extension, backups) over on Simple-Talk. The restore process within PostgreSQL, without 3rd party involvement, can be a little tricky. However, when you are using a Platform as a Service offering, like Azure Database for PostgreSQL, things get quite a bit easier. Let's explore this just a little. Restores Come From Backups I know we all know this, but as I said in the article over on Simple-Talk, discussing backup strategy is a mistake. You should instead be discussing a restore or recovery strategy. How do you intend to get your database back? How much data are you prepared to lose? How long should a restore take? Answering these questions leads you to a Recovery Point Objective (RPO), which tells you…
Read More
24Jan 2022 by Grant Fritchey 3 Comments

Which Columns Are Looked Up?

Uncategorized
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…
Read More
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…
Read More
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…
Read More
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…
Read More
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…
Read More
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…
Read More
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…
Read More