11Nov 2022 by Grant Fritchey 2 Comments

SQL Server 2022 Query Performance Tuning

SQL Server
If you're interested in getting a digital copy, my brand spanking new book is now available here. It's in the intro, but let me tell you a little bit about the new book. It's really new. Some of the older versions of the book were simply updated, a bunch of changes to most chapters, a couple of new chapters, fixes for old mistakes, ta-da, new book. Not this time. This time, I rewrote it all. From scratch. Now, some of the chapter titles are the same. Quite a few of the examples are the same (if code illustrates something successfully, I'm reusing it). However, overall, it's a brand new book. There's a lot of new material too. The last update was for SQL Server 2017. There has (almost) been two…
Read More
03Oct 2022 by Grant Fritchey 2 Comments

Function Vs. Performance

SQL Server
Recently I was looking through DBA.StackExchange when I saw a pretty simple question that I decided to answer. I went off, set up a test database and some test tables and quickly wrote a query to answer the question. I got it all formatted pretty and was on my way to post it when I saw that another answer was already there. Yeah. Identical to mine. Almost line for line. Well, nuts. I know. I'll write a blog post. The Setup The original poster had two tables that, frankly, are badly designed. However, they share enough data that they are "related" if not relational. Here's the code: CREATE DATABASE Testing; GO USE Testing; GO CREATE TABLE Table_A ( ID INT IDENTITY(1, 1), Score INT ); CREATE TABLE Table_B ( FromPoint…
Read More
28Sep 2022 by Grant Fritchey No Comments

Query Tuning At PASS Data Community Summit

PASS
The all new, in-person, PASS Data Community Summit is in just a few weeks. Since I'm shortly going to be publishing a 100% rewritten book on query tuning, I decided to take a look at what kind of sessions on the topic are going to be available at the Summit. Query Tuning With The Best You can search through the published sessions here. I didn't even bother typing in "query tuning". Far too lazy. I just typed "query" and already saw some amazing stuff. In no particular order... One of the people I've ALWAYS learned from, Kimberly Trip is delivering a precon, Indexing for Performance. That's going to be a great day of good learning. Kim is insanely knowledgeable and a fantastic presenter. Well worth your time. Erin Stellato, another…
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
11May 2020 by Grant Fritchey 7 Comments

Find Indexes Used In Query Store

SQL Server
One of the most frequent questions you'll hear online is how to determine if a particular index is in use. There is no perfect answer to this question. You can look at the sys.dm_db_index_usage_stats to get a pretty good picture of whether or not an index is in use. However, this DMV has a few holes through which you could be mislead. I thought of another way to get an idea of how and where an index is being used. This is also a flawed solution, but, still, an interesting one. What if we queried the information in Query Store? Indexes Used in Query Store Now Query Store itself doesn't store index usage statistics. It stores queries, wait statistics and runtime metrics on individual queries. All useful stuff. Oh, and,…
Read More
21Jan 2020 by Grant Fritchey No Comments

Microsoft Tools That Help Query Tuning

SQL Server, T-SQL, Tools
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…
Read More
03Jun 2019 by Grant Fritchey 2 Comments

Which Query Used the Most CPU? Implementing Extended Events

SQL Server, T-SQL, Tools
A question that comes up on the forums all the time: Which query used the most CPU. You may see variations on, memory, I/O, or just resources in general. However, people want to know this information, and it's not readily apparent how to get it. While you can look at what's in cache through the DMVs to see the queries there, you don't get any real history and you don't get any detail of when the executions occurred. You can certainly take advantage of the Query Store for this kind of information. However, even that data is aggregated by hour. If you really want a detailed analysis of which query used the most CPU, you need to first set up an Extended Events session and then consume that data. A…
Read More
11Mar 2019 by Grant Fritchey 4 Comments

Missing Indexes in the Query Store

SQL Server 2016, SQL Server 2017, T-SQL, Tools
I've shown before how to use the DMVs that read the plan cache as a way to connect the missing indexes suggestions with specific queries, but the other place to find missing index suggestions is the Query Store. Pulling from the Query Store The plans stored in the Query Store are exactly the same as the plans stored within the plan cache. This means that the XML is available and you can try to retrieve information from it directly, just as we did with the missing index queries against the DMVs. Here's the query modified for the Query Store: WITH XMLNAMESPACES (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan') SELECT qsqt.query_sql_text, rts.plan_id, rts.NumExecutions, rts.MinDuration, rts.MaxDuration, rts.AvgDuration, rts.AvgReads, rts.AvgWrites, qsp.QueryPlan, qsp.QueryPlan.value(N'(//MissingIndex/@Table)[1]', 'NVARCHAR(256)') AS TableName, qsp.QueryPlan.value(N'(//MissingIndex/@Schema)[1]', 'NVARCHAR(256)') AS SchemaName, qsp.QueryPlan.value(N'(//MissingIndexGroup/@Impact)[1]', 'DECIMAL(6,4)') AS ProjectedImpact, ColumnGroup.value('./@Usage', 'NVARCHAR(256)') AS ColumnGroupUsage, ColumnGroupColumn.value('./@Name',…
Read More
11Feb 2019 by Grant Fritchey 2 Comments

Query Store and a READ_ONLY Database

T-SQL, Tools
What happens in Query Store when the database itself is READ_ONLY? Yeah, I don't know. Let's find out. READ_ONLY The only way to find out how this works is to test it. So, let's write some code: CREATE DATABASE testquerystore; GO ALTER DATABASE testquerystore SET READ_ONLY; GO ALTER DATABASE testquerystore SET QUERY_STORE = ON; Executing that resulted in a small glitch in the Matrix: 8:00:54 AMStarted executing query at Line 1Commands completed successfully.8:00:54 AMStarted executing query at Line 2Commands completed successfully.8:00:54 AMStarted executing query at Line 5Msg 5004, Level 16, State 6, Line 5To use ALTER DATABASE, the database must be in a writable state in which a checkpoint can be executed.Msg 5069, Level 16, State 1, Line 5ALTER DATABASE statement failed.Total execution time: 00:00:01.448 Well that's not going to…
Read More
23Jan 2019 by Grant Fritchey 2 Comments

All Day, Training Day at SQLBits

Azure, SQL Server 2016, SQL Server 2017, T-SQL
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…
Read More