chunkym0nk3y (10/27/2008)

---

Thanks for your helpful comments - parameter sniffing was exactly what it was!! The fix (so to speak) was to copy the Datetime variables into variables held within the...

Christopher Stobbs (10/27/2008)

---

HI all,

Thanks for all the support.

The wierd thing is that the where clause is on a table that only has 170+- rows in it.

It seems that the problem...

assuming you have an identity PK you could do something like this (psuedo-code)

get maxid

@i = minid

while @i < @maxid

begin

begin tran

insert newtable select origtable where id between @i and @i +...

This one is easy - do the migration in batches of 100-500K rows at a time with tlog backups after each batch or couple of batches (possibly with truncate_only if...

If you force this query you WILL wind up with some of them being just as bad as you have now. You can rob Peter or you can rob...

Can you please tell us which bug it was in CU9 that was causing the problem? I checked and cannot find any FIX from that update dealing with a...

ALZDBA (10/22/2008)

---

Just another flash in the brain ..... or what's left of it;)

When you switched the NICs, did you rename the NIC ?

We tend to use "public network connection" for...

chileu17 (10/21/2008)

---

Sorry SqlGuru :hehe: I think I couldn't explain to you my question. I do understand your point, BUT how do I implement what you are talking about?More indexes?new transactions?I...

One way around this is to move all 'prevent' columns such as varchar(max) off to another table with a one-to-one link back to the base table. Then a simple...

1) are you absolutely certain it is doing a CI SEEK and not a CI SCAN??

2) I am with the others on index maintenance. I would also...

For future reference (and for others who may do a web search and stumble across this thread), for your particular situation it would almost certainly have been better to drop...

You REALLY should have given us the function code! Anyway, I can tell what is happening even without it: You are doing table accesses on the options table...

only a little! We are still talking about tlogs and data in them and them filling up.

chileu17 (10/20/2008)

---

yeah we are thinking on calling some MS performance expert on this matter, because we don't seem to get a better performance in any of our attempts. But just...

I recommend (if not done yet) that you do a forced-failover (and fail back) under load to ensure everything is truly functional. Glad you had a (relatively) simple resolution!