Gianluca Sartori (11/4/2010)

---

Thank you!

It's strange, indeed. STATIC cursors have to make a tempdb copy of the data, I would expect this to take some time and lose compared to KEYSET....

sjimmo (11/4/2010)

---

SQLGuru

That is exactly the issue that most of my clients THINK is true, yet almost always is not. You have to scan the big temp table (tempdb read) then...

http://sqlblog.com/blogs/hugo_kornelis/archive/2007/11/21/curious-cursor-optimization-options.aspx

Note how Adam Machanic states that CLR cursors are even faster. 😀

>>I have seen cases where there are multiple indexes on multiple temp tables but these have many rows and are being joined upon with permanent tables. This would reduce table...

I am going to step outside the box here and ask WHY you want to put a nonclustered index on a temp table?? In almost 15 years of doing...

Performance will likely be horrible here. You simply MUST refactor the code to inline the UDFs into the update itself. Even if you need to use interim temp...

Despite what others have said, precleaning the data is not necessarily a workable solution for some cases. If you need a cursor to trap and log individual errors and...

ola.hallengren.com!! Full maintenance suite, highly configurable and documented! Best of all it is FREE!

I hope you are aware that if you restrict the log size, and you fill it up, then all DML activity will cease against the database.

homebrew01 (11/3/2010)

---

I don't know of a way after the fact unless you had a trace running ....

I run transaction log backups every 15 min, so I look at when...

1) Forums, 'staffed' by volunteers, are for short, relatively straight-forward help and advice scenarios. What you have posted could take hours or even days to really get things tweaked...

If you want to do stuff properly and reliably with the tlog, get a copy of ApexSQL's log tool(s).

I hope you understand that if your job takes > 20 minutes to complete you will skip any 'overlapped' executions of the job.

Stop using maintenance plans. PLEASE do yourself a HUGE favor and get a copy of Ola Hallengren's FREE and DOCUMENTED maintenance suite (ola.hallengren.com). Learn to use it's goodness....

1) the fact that table variables are used in the demos is likely creating execution plans that will not play out in the real world where actual tables are used....