Bad Parameter Sniffing Decision Flow Chart

SQL Server 2005, SQL Server 2008, SQL Server 2012, SQL Server 2014, SQL Server 2016
Lots of people are confused by how to deal with bad parameter sniffing when it occurs. In an effort to help with this, I'm going to try to make a decision flow chart to walk you through the process. This is a rough, quite rough, first draft. I would love to hear any input. For this draft, I won't address the things I think I've left out. I want to see what you think of the decision flow and what you think might need to be included. Click on it to embiggen. Thanks to the attendees at my SQLSaturday Louisville pre-con for the great questions and the inspiration to get this done. Thank you in advance for any and all feedback.
Read More

Why You Should Change the Cost Threshold for Parallelism

SQL Server 2005, SQL Server 2008, SQL Server 2012, SQL Server 2014, 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

PowerShell to Test a Query

DevOps, PowerShell, SQL Server 2005, SQL Server 2008, SQL Server 2012, SQL Server 2014, SQL Server 2016, TSQL
So you want to do some tuning, but you're not sure how to test a query on it's performance. Not a problem. Here's a very rough script that I use to do some recent testing. This script to test a query is post #11 of the #enterylevel #iwanttohelp effort started by Tim Ford (b|t). Read about it here. The Script The goal here is to load a bunch of parameter values from one table and then use those values to run a query to test it. To do this I connect up to my SQL Server instance, naturally. Then I retrieve the values I'm interested in. I set up the query I want to test. Finally a loop through the data set, calling the query once for each value. [reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | out-null #…
Read More

sp_executesql Is Not Faster Than an Ad Hoc Query

SQL Server 2005, SQL Server 2008, SQL Server 2012, SQL Server 2014, SQL Server 2016, TSQL
This requires an immediate caveat. You should absolutely be using sp_executesql over any type of non-parameterized execution of T-SQL. You must parameterize your T-SQL because the lack of parameters in building up and executing strings is a classic SQL Injection attack vector. Using straight ad hoc T-SQL is an extremely poor coding choice because of SQL Injection, not because there is something that makes one method faster than the other. Yet, I see in performance checklists that you should be using sp_executesql over straight ad hoc T-SQL because it will perform faster. That statement is incorrect. Some Discussion Let me reiterate the caveat before we continue. I 100% advocate for the use of sp_executesql. This function is preferred over ad hoc SQL because, used properly (and isn't that usually one of the main problems,…
Read More

Stored Procedures Are Not Faster Than Views

SQL Server 2005, SQL Server 2008, SQL Server 2012, SQL Server 2014, SQL Server 2016, TSQL
A performance tuning tip I saw recently said, "Views don't perform as well as stored procedures." <sigh> Let's break this down, just a little. Definitions A view is nothing but a query. The definition given by Microsoft is that it's a virtual table that's defined by a query. It's a query that is used to mask data or perform a complex join or similar behaviors. Views are queries that get stored in the database. Views can be easily referred to as if they were a tables. That's it. I've written in the past about views, including how they can possibly perform poorly. A stored procedure is also a query, or a series of queries, or, a whole lot more. Microsoft's definition of a stored procedure basically defines it as programming object that can accept input through…
Read More

A Sub-Query Does Not Hurt Performance

SQL Server 2005, SQL Server 2008, SQL Server 2012, SQL Server 2014, SQL Server 2016, TSQL
The things you read on the internet, for example, "don't use a sub-query because that hurts performance." Truly? Where do people get these things? Let's Test It I've written before about the concept of cargo cult data professionals. They see one issue, one time, and consequently extrapolate that to all issues, all the time. It's the best explanation I have for why someone would suggest that a sub-query is flat out wrong and will hurt performance. Let me put a caveat up front (which I will reiterate in the conclusion, just so we're clear), there's nothing magically good about sub-queries just like there is nothing magically evil about sub-queries. You can absolutely write a sub-query that performs horribly, does horrible things, runs badly, and therefore absolutely screws up your system. Just as…
Read More

SELECT * Does Not Hurt Performance

SQL Server 2005, SQL Server 2008, SQL Server 2012, SQL Server 2014, SQL Server 2016, TSQL
I read all the time how SELECT * hurts performance. I even see where people have said that you just have to supply a column list instead of SELECT * to get a performance improvement. Let's test it, because I think this is bunkum. The Test I have here two queries: SELECT * FROM Warehouse.StockItemTransactions AS sit; --and SELECT sit.StockItemTransactionID, sit.StockItemID, sit.TransactionTypeID, sit.CustomerID, sit.InvoiceID, sit.SupplierID, sit.PurchaseOrderID, sit.TransactionOccurredWhen, sit.Quantity, sit.LastEditedBy, sit.LastEditedWhen FROM Warehouse.StockItemTransactions AS sit; I'm basically going to run this a few hundred times each from PowerShell. I'll capture the executions using Extended Events and we'll aggregate the results. The Results I ran the test multiple times because, funny enough, I kept seeing some disparity in the results. One test would show a clear bias for one method, another test would…
Read More

Correlated Datetime Columns

SQL Server 2005, SQL Server 2008, SQL Server 2012, SQL Server 2014, SQL Server 2016, TSQL
SQL Server is a deep and complex product. There's always more to learn. For example, I had never heard of Correlated Datetime Columns. They were evidently introduced as a database option in SQL Server 2005 to help support data warehousing style queries (frequently using dates and times as join criteria or filter criteria). You can read up on the concept here from this older article from 2008 on MSDN. However, doing a search online I didn't find much else explaining how this  stuff worked (one article here, that didn't break this down in a way I could easily understand). Time for me to get my learn on. The concept is simple, turning this on for your database means that dates which have a relationship, the example from MSDN uses OrderDate and…
Read More

Query Store, Force Plan and “Better” Plans

SQL Server 2016, TSQL
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

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