IF EXISTS (
FROM dbo.TransactionItems AS T WITH (NOLOCK)
INNER JOIN dbo.UnitTillMaps AS UTM WITH (NOLOCK) ON T.Unit = UTM.Unit
AND T.BusDate >= UTM.StartBusDate
AND T.BusDate <= UTM.EndBusDate
WHERE (T.PLU = '123') AND (UTM.TillMap = 'ABCD'))
SELECT 'Exists' AS Col
-- dbo.TransactionItems rows = 475,114,017 - adding roughly 400,000 rows a day, partitioned on Date, by Year.
-- dbo.UnitTillMaps rows = 756 - almost static - non-partitioned.
-- This was running up until 2 days ago fine.
--There are no locks, blocks, wait types or resource issues - status is Runnable and CPU and Physical IO keep going up.
--The SELECT statement by itself runs in under a second and returns 388 rows.
--Server and SANS have been powered down and restarted
--SQL Server 2005 SP2 on Windows Server 2003 SP1 8*dual core CPUs, 32Gig of memory with 28Gig allocated to SQL
--I have execution plans that look fine, but can't attach them to this message.
View Complete Post