1) When you dropped/recreated the sproc (without the variable kludge), did you execute it immediately with an appropriate batchid BEFORE executing the SELECT statement in isolation? 

2) If possible (be...

>>Dropping and recreating the proc helps to remove any previous query plans from the proc. Generally its a good practice to drop/recreate as

>>Is this technic any better than...

Jeff, be careful with your derived table example:

--===== Derived table

 SELECT @Bitbucket = t1.RowNum

   FROM JBMTest t1,

        (SELECT RowNum FROM JBMTest WHERE RowNum <= 1000000) d

  WHERE t1.RowNum =...

1) A web search and also a perusal/search of BOL (book online for sql server) will provide a wealth of information about the tempdb.  Note that it's usage has changed...

I agree that in this case (70% flush) a bulk migration process is most efficent.  However, since there is already much discussion in this thread about using a batch delete...

1) Definitely limit max memory.

2) Have you check perfmon for paging?  This is likely the 'actual' cause of the slowness.  You need to set max memory for sql such that...

Personally I would be doing whatever is necessary to get what I assume is very important off-hours jobs to complete successfully.  So stop fooling around and simply make tempdb as...

>>an answer no would have probably been better than a link

I strongly disagree with that statement.  This is a forum, and thus (IMHO) a learning environment.  Not only does the...

1) IN and JOIN run in same time because the optimizer is most likely rewriting the IN as a JOIN under the covers (show estimated query plan for both to...

1) IN and JOIN run in same time because the optimizer is most likely rewriting the IN as a JOIN under the covers (show estimated query plan for both to...

Better would be to use SSIS and let it handle data cleansing/rejecting for you!  🙂

I have actually been using their (embarcadero) products for many years now.  I have become increasingly disappointed with their quality control over time.  The latest versions I upgraded too have...

I get the same problem on any number of production databases when using sys.dm_db_index_physical_stats as the source of density/frag information too.  Part of it is due to having databases that don't...

1) are you absolutely certain that it is the select statement that is blocking?  use sp_who2 active to determine the blocking spid and dbcc inputbuffer to see the statement of...

Funny how different people can read the same post and take away two completely different ideas about what was said/asked!!  :-))

In my 15 years or so of database development (10...