Another update, now when i drop fk/truncate/insert data back/ add fk the good table(table1) also gives bad performance? Why would that be.. In the interim i will tryt o get...

I think i should have provided more details. I certainly did look at the execution plan and yes the actual number of rows on one of the tables is...

Thanks. I am sorry i couldn't put my question right. Mentioned below is the query i am using to enable deadlocks

dbcc traceon(1222,-1)

My question is will this trace be enabled always...

Jeff you are absolutely correct."r" has about 52K records and "r2" 16 million:).

sp_lock gave me this

83756723860TAB[UPDSTATS] XGRANT

83756723860TAB ...

makes sense..thanks

Did you change the table you wanted to change? you should be able to find some good article online. Let us know how...

TheSQLGuru (3/10/2011)

---

First-execution times being slow is almost certainly due to simply reading data from disk. After that you are hitting data directly in RAM, which is way faster. ...

GilaMonster (3/9/2011)

---

No. Not unless you consistently have stale stats on the prod database. If you do, that's a far bigger problem than for refresh.

To clarify, there is no requirement to...

Do i need to...

Correct i mean re-intialize. Will copy over a fresh...

Jeff Moden (3/9/2011)

---

sqldba_icon (3/9/2011)

---

i have a procedure which gives 15 records as output, i also have a trace running simultaneously and i see that "rowcounts" counter for that procedure is...

Grant Fritchey (3/9/2011)

---

In general, the way you can tell something is parameter sniffing is not simply if it runs faster or slower, that could be contention or any number of...

Yes. thanks i got my answer here on a different post.

http://www.sqlservercentral.com/Forums/Topic720642-338-2.aspx

i) remove from replication

ii) make changes

iii) re-sync. This will take a fresh snapshot.