This is the current query plan before changes

I have tried changing to below doesn't seem to have improved things

SELECT ..

WHERE ... AND hs.TimeStamp < fs.ValidTo

UNION ALL

SELECT ..

WHERE ... fs.ValidTo is...

SELECT .....

FROM dbo.historicsummary hs

LEFT JOIN dbo.Status fs ON hs.ID = fs.ID

AND hs.TimeStamp >= fs.ValidFrom

AND hs.TimeStamp < ISNULL(fs.ValidTo, DATEADD(d, 1, SYSDATETIME()))

This is the left join I am trying to change

When I...

Query 1 -- where departmentValidTo is not null

Union all

Query 2 -- where it is null and replace the DATEADD expression with a literal , '20250501'  for example.

How do I make...

index doens't seem to improve performance much.

What does, which is a bit strange is that in my left join it is based on 3 different criteria, when I change the...

Yes was taking less time but today it back up around the same time. For me it seems like a storage issue, I don't think there is anything additional that...

Its on AWS.

8 CPU and 64 RAM

I need to include the varbinary column in the query.

It is a new problem, today the query is taking 59 seconds 🙁

• This reply was modified 4 months ago by juniorDBA13.

I don't see ASYNC_NETWORK_IO waits when running the query including the column that is varbinary. There was just PAGEIOLATCH_SH wait

Just some stats

--4.33min with varbinary column

--28 sec without varbinary column

Sorry forgot to mentioned the table has columnstore index with partitions enabled.

There is 88 rows returned, so not too much data

It has clustered index on ID column.

Then there is a few indexes, including one for DepartmentValidFrom, DepartmentValidTo & DepartmentID

It does suggest...

Yes I was thinking it would make it an inner join initially

Lets assume this is the query

-- Assumed table structure:

-- Employees(EmployeeID INT, Name NVARCHAR(100), DepartmentID INT, EmployeeTimeStamp DATETIME2(3))

-- Departments(DepartmentID INT,...

• This reply was modified 4 months, 1 week ago by juniorDBA13.

Unfortunately we don't have the option to setup a new instance

Could the way data is inserted into the table increase the unused space?

Ok but what if my fk aren't setup with option

I setup logging as mention below https://www.mssqltips.com/sqlservertip/4070/integrated-logging-with-the-integration-services-package-log-providers/

Unfortunately I didn't get an error in the table to suggest what the problem was.

The package runs on the older servers and the difference...

Getting this error now

Errorcode:6; ErrorDescription: the script returned a failure result ErrorCode:1; ErrorDescription: Exception has been thrown by the target of an invocation

This happens after generating a file that the...