I've seen issues repeatedly with "SELECT ... INTO <new_table>" causing blocking (on SQL 2016).  When we adjust the code to split the table creation and...

I've seen issues repeatedly with "SELECT ... INTO <new_table>" causing blocking (on SQL 2016).  When we adjust the code to split the table creation and the table...

I've seen issues repeatedly with "SELECT ... INTO <new_table>" causing blocking (on SQL 2016).  When we adjust the code to split the table creation and the table load the...

Just in case anyone else is reading this and doesn't know, Lynn is anything but a "Ring Knocker".  He's a regular on these forums, has written some great articles, and...

I absolutely agree that it may not "be the listener" at fault... especially after seeing so many different "speakers".  I definitely believe we're on the same page in that area.

On...

Jeff Moden perhaps that is the reason why Option A is sometimes faster and why Option B is sometimes faster my knowledge of DBA aspects is not as deep...

Ok... I just did some extensive testing using Brent's code both as is and with some mods to give the test table a bit more bulk.

Using a MAXDOP of 1,...

It's been my expectation that SQL Server will run slower because of all the stuff they're doing to figure out if a plan should be reverted and a bunch of...

My motto is...

I know you get some reduced performance from running on a virtual machine by I thought this would be maybe 20% slower, the creation of the clustered index...

Okay to comment on the (A) CREATE TABLE with INDEX then INSERT INTO versus the (B) SELECT INTO then create INDEX there is no set solution for which is...

You don't really need to partition the table if you (1) cluster the table by date first and (2) the lookup queries specify a WHERE clause on a date...

@bob-2... you made the comment in your first posts of...

Did you check for blocking?  If so, is there blocking associated with this issue?

Also another means of getting around all those WITH (NOLOCKS) is to set at the top of the stored procedure either:

SET TRANSACTION ISOLATION LEVEL SNAPSHOT

-- OR --

SET TRANSACTION ISOLATION...

Note I teach/tutor for free and have an online Discord server with 80 plus students at verying levels of expertise. My first motto is (and all my students know...