How is the orders table clustered?  I'm guessing by orderid.

But, for this query, since jobdatestart is the key restrictor on the number of rows, that would make a better clustering...

I believe the +/- n days works with any datetime value.  I 'm...

Do you have the latest CU applied?  That condition is often a bug in SQL code, fixed by maintenance.

You don't need to shrink the database, but you do need shrink the affected file(s).  NEVER shrink a database, only file(s).

Run:

DBCC SHOWFILESTATS

and find the FileId(s) with lots of free space. ...

I think maybe you can just change the CROSS APPLY in Steve's code?!:

...
     cross apply (values (cast(w.CREATE_DATE as datetime)+
    ...

(1) Cluster the main table on ID: a nonclus index is worthless here.

(2) Cluster the lookup table on ID.  The join of the two tables should now become a MERGE...

Sure, you can do that.  But I can't really follow the logic of the the original statement -- particularly the logic of starting from a negative starting byte, so I...

Sorry, I left out a step.

dbo.DelimitedSplit8k is a function commonly used on this site to efficiently split a string into multiple parts based on a single-character delimiter.

Here's a version of...

SELECT PERSONNUM,ORGPATHTXT,LOC    
FROM SampleData2
CROSS APPLY (
    SELECT TOP (1) LOC
    FROM (
      ...

I believe SQL caches table variables the same way it does for temp tables.  If so, an entries for them would still be in the system tables.  It would be...

For the actual tables, post the DDL, including all indexes.

For any views, post the view definition.

Agree, there's no super-easy, "automatic" way to do that.  However, you can generate a script(s) that will change each column as needed, so it's not like you have to do...

To allow me to write full code, would you provide the full DDL for the Error_Log table?  The big gain will come from encoding strings as numbers using a conversion...

Of course it's optional if you make the new clustering index a PK or not.

That is, you could drop the existing PK, create the new clustering index, then re-add the...

Get rid of the functions around the usrt_dtm_DateTime column; you don't need them and they will prevent lookup seeks, drastically harming performance:

SET @ToDate = CAST(@ToDate AS date)...