+1 for Idera SQL Diagnostic Manager. I use it and have tried Red Gate's tool as well but preferred Idera because it had a nicer "look and feel"...decent built...

How detrimental really depends on what's in the cache. FLUSHPROCINDB is specific to a certain database and doesn't affect any other databases in the procedure cache. Depending on...

My only guess is that this database has a lot of "changing" data. Your differential backup contains everything since the last full backup so if it's nearly the same...

Your differential backups will include everything since your last full backup and will grow daily (which you already know), check your transaction log file size, it could be that it's...

midnight251 (2/10/2013)

---

Thanks for the reply, that's great info. I'll be on it first thing tomorrow. I take it that I can do this while the DB is in use? ...

I wasn't positive it was a linked server problem or if it was the way I was presenting the code (clearly the poor performance is the direct result of going...

Paul, I will try the code and post back the results soon. As for obvious issues with the linked server (remote query) this will go away. All of...

Yes, good catch. Based upon Lynn's suggestion I changed the code to store the data from the CTE's locally...so now I do use both CTE's as I insert the...

I opted to your temp tables, creating an index where needed and got the update down to about 23sec. - do you think I would see any further improvement by...

You can define them to hold data, data let's say you want to pass to a proc or a function (as OPC stated). Consider this usage:DECLARE @test-2 AS ...

These links should help:

General:

http://msdn.microsoft.com/en-us/library/ms175527(v=sql.105).aspx

http://facility9.com/2009/10/an-introduction-to-sql-server-system-databases/

TempDB:

http://www.idera.com/Downloads/WhitePapers/WP_Demystifying%20tempdb.pdf

I'd recommend you create a lookup table containing available slot times, and store your records for the main table with the FK to the slots table that point to them

There's also a built in executable that's shipped with SQL Server. Here's the basic syntax and a link to an article[/url] that describes how to use it

"C:\Program Files\Microsoft SQL...

Also Bhuvnesh and Vedran have made excellent points. To give you a rough idea, I have nearly the same size DB, I use Idera's SQL Safe software (backup compression)...

Use this code (as Grant mentioned, from dm_exec_requests) - it's pretty accurate and will give you a rough idea of when it will complete once you let it run for...