I like WROX's Beginning Transact SQL too.

1) partitioning is an ADVANCED feature, and there are MANY ways you can zig when you should zag. I HIGHLY recommend you get a professional on board to help...

a simple web search for sp_whoisactive will get you what you need. Amazingly powerful free piece of code by Adam Machanic.

1) it makes NO sense to simply put data into a temp object just to select it out to the calling entity

2) are you REALLY trying to return 50M rows...

I do not believe that changing database collation (even if you DO change all the table/column collations, which you didn't do) is a supported action by Microsoft. I think...

1) use sp_whoisactive to check for blocking, resource usage, etc.

2) do a waitstats analysis during the run to find out which waits are causing slowness

3) do a file...

Krasavita, I am with Gail on this one: if this is an important production process that is blocking (and now deadlocking I see), you REALLY need to get a professional...

Here is your proof that even a SINGLE ROW TABLE VARIABLE CAN BE HORRIBLE FOR PERFORMANCE:

create table bigtest (a int not null, b char(500) not null)

insert bigtest

select 1, replicate(' ',...

This (putting INSERTED/DELETED data into variables inside triggers) is a frighteningly common problem I see at clients, and is a critical data processing flaw. I just went recently to...

WOW - quite the thread here!

One thing I will add is that I can probably count on my fingers and toes the number of times in 15ish years...

Iain, please do not respond to years old threads like this. Thanks in advance!

You have been going back and forth for days on this issue now. Way past time to get a performance tuning professional on board to help you find a...

sql_butterfly (3/29/2011)

---

Here is the result of SET STATISTICS IO ON:

Table '#AAMaxRunVersion____________________________________________________________________________________________________000000026E3A'. Scan count 3005, logical reads 6033, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads...

The complexity of this issue isn't something that lends itself to a few forum posts. Given...

Put me down as +3 for hiring a performance tuning expert to both review your systems/applications and make recommendations as well as mentor you on how to go about keeping...