Parameterization Part 4: Non-Uniform Data Distribution

Thursday, September 4, 2014

This is the fourth post in the "Parameterization" series. In the previous post I wrote about parameter sniffing, and I mentioned that this method can be a good thing in some cases, but it can also be quite a bad thing in other cases. One case in which parameter sniffing is a bad thing is when the data in some column is distributed in a non-uniform way. For example, consider the "Status" column in the "Sales.Orders" table, which has a total of about 10M rows. This column has seven unique values with the following distribution: Status Number of Rows Open 314 Pending Approval 561 Approved 28,990 Paid 17,610 Shipped 817,197 Closed 7,922,834 Cancelled 1,032,886 If parameter sniffing...

Parameterization Part 3: Parameter Sniffing

Monday, August 25, 2014

This is the third post in the "Parameterization" series. In the previous post I mentioned parameter sniffing. This is a very important concept in SQL Server, and it certainly deserves a dedicated post. It's time to dive into all the juicy details about parameter sniffing… But first, let's begin with a short reminder of what we've learned so far. A batch can be either parameterized (contain parameters) or not. A parameterized batch plan in SQL Server can be one of two object types: "Prepared" or "Proc". A "Prepared" plan corresponds to an execution of sys.sp_executesql with at least one parameter,...

Parameterization Part 2: Seven Ways to Execute Your Query

Monday, August 18, 2014

This is the second post in my series on parameterization. In the first post I wrote about plan caching and about the phases that each batch goes through before execution. I ended the previous post with a query example that demonstrates the importance of parameterization. For this post I'm going to use the following query: SELECT Id , Name , LastPurchaseDate FROM Marketing.Customers WHERE Country = N'IL'; This is a simple query that retrieves all the customers from a given country, in this case – Israel. Paul Simon has a great song – 50 Ways to Leave Your Lover. One of my favorites. I encourage you to click that...

Parameterization Part 1: Plan Caching

Monday, August 11, 2014

Many times, when I perform query tuning, the problem that causes the query to perform badly is related, one way or another, to parameterization. On one hand, parameterization is a fundamental subject in the heart of the query processor. It can affect query performance dramatically. On the other hand, it seems to me that this important subject is not covered enough and not understood well enough. So I decided to start a series about parameterization. This is the first post in the series, and in this post I would like to write about plan caching. Understanding how execution plans are cached...