I will let others who have been following along here continue to help you with this. But I have a HUGE CAUTION for you: BEWARE doing tuning by...

Gullimeel (5/23/2012)

---

Not necessarily. Properly done indexing can provide just about the same benefit. Partitioning may give you somewhere around an additional 1% performance improvement over just indexing, but is that...

Grant Fritchey (5/22/2012)

---

By the way, if you're getting RID lookups it means that you're dealing with hash tables, tables without a clustered index. Unless you have a very thorough set...

<lol> Your 'interview' comment reminds me of an interview I suffered through a while ago. They were asking [really detailed] "What would you do if...?" questions. Finally I...

You should know about good automated tools Ami, given what DBSophic's Optimizer is capable of!! Like I have said before - it is like having the vast majority of...

SQL Kiwi (5/22/2012)

---

TheSQLGuru (5/22/2012)

---

As always you have a great breakdown of what is going on in the plan Paul! To the OP: Paul makes a great point about helping...

As always you have a great breakdown of what is going on in the plan Paul! To the OP: Paul makes a great point about helping the optimizer by...

MERGE with TRIGGERS will not function as desired in your scenario either. You will need to break the processing into discreet steps - i.e. UPDATE existing matching rows, INSERT...

In addition to the logical IO you seem to be fixated on you must also consider the TYPE of IO. Random IO on rotating media, which is what you...

I have said it many times before and will say it again. Table Partitioning in SQL Server is a very complex subsystem with lots of requirements, conditions, gotchas, etc....

okbangas (5/16/2012)

---

A small update: The first index just finished, the second started off in parallel as it should just now. So, what I've learned from this situation: If your server...

Elizabeth, I will point out that you have been going at this one performance issue for 3 days now. It is quite possible that getting a good performance tuning...

RBarryYoung (5/17/2012)

---

Well I looked at the estimated and actual query plans for several different versions of my...

Very slick try, but it doesn't seem to work:

USE tempdb

GO

CREATE TABLE tmp (a int)

INSERT tmp VALUES (1)

go

CREATE PROC pr_OrganicDissapearance AS

SELECT a FROM tmp WHERE a = 1

BEGIN TRY

;WITH...

You wanted a simple, definitive answer. Here it is: you cannot do what you want to do.