Thanks for posting the update.

Sounds like you have some work to do optimising your tempdb setup & usage. Let me know if you have any questions around that -...

Sorry, totally missed the CLR references and thought you were calling it from app code.

You should check your wait stats. If you can run the SP in isolation on...

First thing to do is to check how long SQL Server thinks it took to run. There are example queries all over the web for this - you can...

I'm glad you named and shamed the culprit!

Below is a very quick tally table:

declare @NoOfRows int = 1000000

-- cross join allows up to 2047*2047 rows = 4,190,209.

;with tally...

Do you have an excessive number of linked servers, CLRs etc? Multi-page allocations, BLOBs? Are you locking pages in memory?

Try running select * from [sys].[dm_os_process_memory] - posting the...

vk-kirov (1/20/2010)

---

mzz3lh (1/20/2010)

---

However, when I ran this to try it out, I got an error:

(1 row(s) affected)

(1 row(s) affected)

this is an exception

Msg 208, Level 16, State 0, Line 10

Invalid object...

Yeah, I assumed SQL2008 might have been ok for this. I ran it on SQL2005 and got the error.

The Rollback Transaction statement seems to drop the temp table.

I have to agree here. None of the answers match what I would expect, which would be 'This is an exception' in the message pane, followed by the contents...

I like the concept of this. It's essentially an "intelligent trial and error" approach.

If you can lock down the random actions, and add a bit of intelligence here and...