ps_vbdev (10/5/2015)
Its another team that are in-charge of the source db so Im not sure why these datatypes have been selected, I know c# guids are used all over the place for ids (UserID & StockTransactionType) but they never exceed 36 nvarchar.
GUIDs should be stored in the UNIQUEIDENTIFIER data type, not varchar (and they're not unicode, so they don't need nvarchar, a GUID contains 0-9 and A-F). Please have a chat with the other team about fixing the data types, a poor design almost always leads to poor performance.
WHERE
DATEPART(isowk, pish.[InductedDate]) >= DATEPART(isowk, GETDATE())-1 AND pish.[InductedDate] <= DATEADD(ms, -3, DATEADD(dd, DATEDIFF(dd, 0, GetDate()), 0))
That's a table scan, no matter what indexes you have. If you partition, it'll still be a full table scan, reading all partitions.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability