Posted by
Jason Massie 
on Friday, March 21, 2008 at 11:40 PM to
SQL Server 2008, query optimizer, Indexes, CTP6, Procedure Cache
298 Views |
1 Comments |
Before the original post, I had submitted a connect item for something similar.
It turns out that filtered indexes may not be used when auto-parameterization(or for stored proc) occurs without a recompile hint. This is sort of understandable but I can think of workarounds and I suspect additional logic will be added in the future as mentioned below. Here is the feedback from a connect item I submitted.
"
Thanks for your feedback. The query in question, select lastname from Person.Contact where ContactID <=(100-20) is auto-parameterized by SQL Server into the following form, using the standard auto-parameterization rules: select lastname from Person.Contact where ContactID <=(@1-@2) These rules are designed to minimize compile time cost for simple queries like this, possibly at the expense of additional optimizations such as using a filtered index. Admittedly, it is a difficult tradeoff. In a future release, we'll consider extending the design to make a better decision in a cost-based way. For now, this behavior is by design.
"
alt head: You can't have your cake and eat it too.
read more
Posted by
Jason Massie on Friday, March 21, 2008 at 1:27 PM to
SQL Server 2008, SQL Shorts, SQL Server 2005, Windows Server 2008, tsql, Indexes, Offtopic, Virtualization, CTP6
356 Views |
1 Comments |
We regret to inform you of a passing in the SQL community. Read more here.
Rick Heiges has a post on my favorite new feature in SQL Server 2008, filtered indexes.
So does Decipherinfosys along with a good description on the difference of indexes and statistics if you need some background.
SQLBlogcasts has gotten an upgrade and Tony posts some great stats. Congrats!
Not SQL per say but the Hyper-V release candidate has been released. Speaking of Hyper-V, Sriram posts his slide decks on virtualizing SQL. Part 1 and Part 2.
This is a great starting point for SQL Server 2008 as is this. These come by way of the MSDN\Technet update blog.
The SQL Server 2005 sp3 debate continues.
The SQL ISV team posts a performance improving cursor rewrite sample. However, it is not ANSI compliant :) which is odd since most ISV tsql code needs to be portable.
Paul Nielson will be releasing a DVD.
While we are at it, check out the new SQL Server social network.
alt head: Got ADD?
read more
Posted by
on Sunday, March 16, 2008 at 5:31 AM to
SQL Server 2008, query optimizer, Indexes, CTP6, Procedure Cache
455 Views |
0 Comments |
So I was messing around with filtered indexes tonight. Yes, beta software is my idea of a wild Saturday night. Hey, I played some Smash Bros first. ;) I was trying to figure out what happens if a plan is created using a covering but filtered index and try to reuse it with a parameter that is covered vertically but not horizontally. Let's look at this example:
read more
Posted by
Jason Massie on Saturday, February 23, 2008 at 11:11 PM to
SQL Server 2008, SQL performance tuning, tsql, Indexes, CTP6, In