sgmunson (2/16/2015)

---

The following part of the LEFT JOIN isn't needed:

AND O.CompanyClientMemberID IS NOT NULL

That field is already part of the rest of the JOIN condition (the specific line follows) based...

This produces the same result set in both the test environment and production. Does anyone see any logic fail on the re-write?

DECLARE @p0 DATETIME = '2099-09-09 00:00:00.000';

DECLARE @p1 INT...

sgmunson (2/13/2015)

---

Let me know if the following simplified version of that previous query is functionally equivalent:

DECLARE @p0 DATETIME = '2013-12-23 15:09:31.000';

DECLARE @p1 INT = 1;

DECLARE @p2 INT =...

I am not seeing any yellow yields on that previous plan, do I have an option turned off in SSMS? Index reorg/rebuild script ran over the weekend and I...

I have updated the data set and DECLARE variables in my posts. Thank you for those tweaks!

Another poster asked for the execution plan. I now see that you can attach .sqlplan to this forum. Very cool! The version attached is from production and shows...

This will install a sample db, tables and some objects. I put around 20 rows of anonymized data in each table:

CREATE DATABASE OutBox;

GO

USE [OutBox]

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE...

Not the CROSS APPLY, but the CROSS JOIN in [t4].

There are 92k rows in the OutBoxData table. Is this query processing 92k rows x each OutBoxAN by using the CROSS JOIN?

I took the SELECT query that creates data...

Big fan of the sp_WhoIsActive.

Still have not had a chance to try and grab the rest of the batch sql, but I like the WITH [NOLOCK]. A scan for...

Thanks for taking a look, you rock!

I know this is a batch process and may be able to snag the full thing by using Adam Mechanic's sp_WhoIsActive by changing...

Unfortunately this query is the only part of the transaction that I can grab. The rest is buried in .NET code that I do not have access to. ...

To be clear, this is a transaction that puts a lock on a table and the transaction does not complete, causing a block not a deadlock.

I did not even notice the star rating until you pointed it out. This is my first submission and certainly did not submit for a super rating, just to...