Anyone know of a fix for this yet? I'm facing this on SQL 2008 (SP2), and I'm curious if this is fixed in SP3.

Thanks for the reply Gail.

I understand the optimizer's duties are to find a good plan fast, and not necessarily the best plan (although, it may very well have...

I'm wondering if this issue has been resolved, and if so, what was the solution.

Also, this KB article looks like it might be promising.......if you're not already at this patch...

I think that from the SQL error log we can conclude that SQL is able to create the snapshot, so I don't believe the TABLOCK is the issue. Here's...

I do not have any deadlocking information in SQL (no deadlocking trace info from a SQL trace, or a trace flag). However, in my experience if a process fails...

I bounced this around with a coworker and with the input he provided, the input you all provided, and some additional testing, I think I've finally wrapped my mind around...

Maybe I did something wrong, but all 4 scenarios used the index. Without the DISTINCT TOP X in there, the table variable (and the temp table) has always used...

If you're still curious, I uploaded the correct plan for the temp table (sorry for getting the wrong plan uploaded earlier).

I'm not hell bent on using a table variable, I...

I rebuilt the clustered index, and attached the new plans. I also attached a plan for the table variable after removing the "DISTINCT TOP 100" from the SELECT statement....

I've attached the SQL plans, named accordingly. Please let me know if there's anything else I can post that would be helpful.

Thanks,

Adam

But the issue isn't in scanning the table variable, or the temp table. The issue is that SQL is scanning my static table that has 100,000 rows (or in...

Oh yeah, and I forgot to mention that if you change the LIKE to "=" then SQL will use the index. So if you change this statement that does...

On our actual code (not my test code) we chose to use a hint to force the query to use the index and that works. But I just don't...

It's related to my post "How Does SQL Reuse Data Pages?", which you replied to as well. When a new row is inserted, SQL uses the IAM page to...

I’m not saying what we’re doing is the best approach, but I work at a large shop so I thought I’d chime in and let you know what we’re doing...