22Aug 2017 by Grant Fritchey 4 Comments

Runtime Metrics In Execution Plans

SQL Server 2016
Capturing query execution metrics is much easier now that you can see the runtime metrics in execution plans when you're using SQL Server 2016 SP1 or better in combination with SQL Server Management Studio 2017. When you capture an actual plan (using any method), you get the query execution time on the server as well as wait statistics and I/O for the query. Fundamentally, this changes how we can go about query tuning. Runtime Metrics To see these runtime metrics in action, let's start with a query: SELECT p.LastName, pp.PhoneNumber, pnt.Name FROM Person.Person AS p JOIN Person.PersonPhone AS pp ON pp.BusinessEntityID = p.BusinessEntityID JOIN Person.PhoneNumberType AS pnt ON pnt.PhoneNumberTypeID = pp.PhoneNumberTypeID WHERE pnt.PhoneNumberTypeID = 3; We'll run this query and capture the Actual Execution Plan using SSMS 2017. The changes…
Read More
13Mar 2017 by Grant Fritchey 10 Comments

Why You Should Change the Cost Threshold for Parallelism

SQL Server, SQL Server 2016
I've written several times about the Cost Threshold for Parallelism and it's relationship to your execution plans, how to determine your execution plan cost, and even how to decide what value to set your Cost Threshold to. What I haven't explicitly addressed in extremely clear terms is why you should adjust your Cost Threshold for Parallelism. There are two reasons to modify this value. Cost Threshold for Parallelism Default Value The primary reason to change the Cost Threshold for Parallelism is because the default value is not a good choice for the vast majority of systems. The default value is 5. This means that when a query has an estimated cost greater than 5, it may get a parallel execution plan. Microsoft set the default value for the Cost Threshold…
Read More
10Jan 2017 by Grant Fritchey 3 Comments

Query Optimizer and Data Definition Language Queries

SQL Server, SQL Server 2016, T-SQL
Data Definition Language queries don't go through the optimizer, right? While normally, my short answer to this question in the past would have been, yes. However, with testing comes knowledge and I want to be sure about the statement. I'm working with a team of people to completely rewrite the SQL Server Execution Plans book. We'll probably be published in April or May. It's going to be amazing. The structure will be completely different and the book will represent five years of additional knowledge in how execution plans work and how to read and interpret them since the last book was written. However, enough on that. Let's answer the question about Data Definition Language. First of all, we need to quickly define our terms. Data Definition Language (DDL) represents the syntax for queries that build…
Read More
06Sep 2016 by Grant Fritchey 1 Comment

Query Store, Force Plan and “Better” Plans

SQL Server 2016, T-SQL
I am endlessly fascinated by how the Query Store works. I love teaching it at every opportunity too. Plus, almost every time I teach it, I get a new question about the behavior that makes me delve into the Query Store just a little bit more, enabling me to better understand how it works. I received just such a question at SQLSaturday Norway: If you are forcing a plan, and the physical structure changes such that a "better" plan is possible, what happens with plan forcing? Let's answer a different question first. What happens when the plan gets invalidated, when the index being used gets dropped or some other structural change occurs so that the plan is no longer valid? I answered that question in this blog post. The plan…
Read More
29Aug 2016 by Grant Fritchey 7 Comments

Azure SQL Data Warehouse Execution Plans

Azure
Azure SQL Data Warehouse can sometimes feel like it's completely different from SQL Server, but under the covers, it is still (mostly) SQL Server and it does have execution plans. Let's take a look at one. I've created a new SQL Data Warehouse using the sample database available on the portal, AdventureWorksDW. Here's a query against that database: SELECT dd.FullDateAlternateKey AS OrderDate, dc.LastName, SUM(fis.SalesAmount) AS SumSalesAmount FROM dbo.FactInternetSales AS fis JOIN dbo.DimDate AS dd ON fis.OrderDateKey = dd.DateKey JOIN dbo.DimCustomer AS dc ON dc.CustomerKey = fis.CustomerKey GROUP BY dd.FullDateAlternateKey, dc.LastName HAVING SUM(fis.SalesAmount) > 5000.0 ORDER BY OrderDate DESC; If I attempt to capture an execution plan using the SQL Server Management Studio GUI, nothing happens. If I try to use T-SQL commands, I get an error that those commands are…
Read More
15Aug 2016 by Grant Fritchey 5 Comments

Query Store, Force Plan and Dropped Objects

Azure, SQL Server 2016
I love the Query Store. Seriously. It’s a huge leap forward in the capabilities of Azure SQL Database and SQL Server in support of performance monitoring and query optimization. One of my favorite aspects of the Query Store is the ability to force plans. Frankly though, it’s also the scariest part of the Query Store. I do believe that plan forcing will be one of the most ill-used functions in SQL Server since the multi-statement table-valued user-defined function (don’t get me started). However, unlike the UDF, this ill-use will be because of poor understanding on the part of the user, not a fundamental design issue. No, plan forcing and the Query Store are very well constructed. Let me give you an example of just how well constructed they are. Let’s…
Read More
18Jul 2016 by Grant Fritchey 14 Comments

Common Table Expression, Just a Name

SQL Server, SQL Server 2016, T-SQL
The Common Table Expression (CTE) is a great tool in T-SQL. The CTE provides a mechanism to define a query that can be easily reused over and over within another query. The CTE also provides a mechanism for recursion which, though a little dangerous and overused, is extremely handy for certain types of queries. However, the CTE has a very unfortunate name. Over and over I've had to walk people back from the "Table" in Common Table Expression. The CTE is just a query. It's not a table. It's not providing a temporary storage space like a table variable or a temporary table. It's just a query. Think of it more like a temporary view, which is also just a query. Every time I explain this, there are people who don't…
Read More
24Feb 2016 by Grant Fritchey 6 Comments

Query Store and Recompile

Azure, SQL Server 2016, T-SQL
One of the many advantages of SQL Cruise is the ability to have enough time during a presentation to be able to answer questions from the people there in great detail. One question came up while I was showing the new functionality of Query Store (available soon in SQL Server 2016, available right now in Azure SQL Database). What happens to plan forcing when you have OPTION RECOMPILE on a query? Great question. I have a favorite procedure I use to illustrate the functionality of parameter sniffing: ALTER PROC dbo.AddressByCity @City NVARCHAR(30) AS SELECT a.AddressID, a.AddressLine1, a.AddressLine2, a.City, sp.Name AS StateProvinceName, a.PostalCode FROM Person.Address AS a JOIN Person.StateProvince AS sp ON a.StateProvinceID = sp.StateProvinceID WHERE a.City = @City; If this procedure is called with the value of 'Mentor' you get…
Read More
10Aug 2015 by Grant Fritchey 3 Comments

Generating Estimated Plan and the Plan Cache

SQL Server, T-SQL
Does generating an Estimated Plan cause that plan to be loaded into the plan cache? No.   What? Still here? You want more? Proof? Fine. Let's first run this bit of code (but please, not on your production server): DBCC FREEPROCCACHE(); That will remove all plans from cache. Now, let's take this query and generate an Estimated Plan (CTL-L from your keyboard or by clicking on the "Display Estimated Execution Plan" button on the toolbar): SELECT * FROM Production.ProductModel AS pm; This will generate a trivial plan showing a scan against the Production.ProductModel table. Now, let's run another query: SELECT deqs.plan_handle FROM sys.dm_exec_query_stats AS deqs CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest WHERE dest.text = 'SELECT * FROM Production.ProductModel AS pm;'; That's just an easy way to see if a plan_handle exists.…
Read More
03Aug 2015 by Grant Fritchey 6 Comments

Differences Between Actual & Estimated Plans

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