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:
Number of Rows
If 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,...
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:
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...
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...