Month: January 2014

Database Design training – for free?

When I started this blog, my plan was to focus on two main subject areas. One of them is SQL Server, T-SQL, and performance. The other is database design. Looking back over my post history, I honestly cannot say I delivered on the second area. Not because I have nothing to say about database design, but because I found it doesn’t lend itself for a blog. In the SQL Server / T-SQL area, there are subjects that can be isolated and described in a single post, or in a short series. In database design, I do not see such objects.…

Parameterization and filtered indexes (part 2)

1 Comment
In my previous post, I demonstrated how the presence of a filtered index can get in the way of successful parameterization, possibly resulting in too much query compilations and procedure cache bloat. I suggest reading that first, because I will go straight where I left off. Use the Force, Luke If you read the previous post very carefully, you will have noticed that I wrote that if a plan is unstable, it is “unsafe for simple parameterization” – and that suggests that SQL Server will not have such inhibitions when using forced parameterization. Let’s have a look. Assuming you still…

Parameterization and filtered indexes (part 1)

Parameterization is a process where SQL Server slightly modifies a query in the parse phase: it replaces constant values by parameters. So if you submit — Query 1 SELECT COUNT(*) FROM   Sales.SalesOrderDetail WHERE  ProductID = 706; the parser will replace this by (and fool the optimizer into thinking you submitted): DECLARE @1 smallint = 706; SELECT COUNT(*) FROM   Sales.SalesOrderDetail WHERE  ProductID = @1; You can verify this very easily. If you go to the actual execution plan and hover over the left-most icon (the SELECT), a popup window will open that shows (a.o.) the query that was fed into the…

By continuing to use the site, you agree to the use of cookies. more information

The cookie settings on this website are set to "allow cookies" to give you the best browsing experience possible. If you continue to use this website without changing your cookie settings or you click "Accept" below then you are consenting to this.