Need help to rewrite the query for faster performance.
SELECT c.otherID, a.shortname, c.priority, c.amended_date, cast(c.SomeVarcharMaxColumn as xml)
FROM [ABC_Schema].[ABC1_Table] a WITH (NOLOCK),
[ABC_Schema].[ABC2_Table] b WITH (NOLOCK),
[PQR_Schema].[PQR1_table] c WITH (NOLOCK)
cc.ManagerId = c.Id
and a.ManagerId = c.ManagerId
and a.shortname = 'ABC'
and c.amended_date >= cast('2018-10-01 00:00:00' as DATE) --(amended_date is the datetime column and having 9 years data)
and c.amended_date <= cast('2018-10-30 00:00:00' as DATE) --(amended_date is the datetime column and having 9 years data)
and c.priority >7-- ( b.priority column has values from 1 to 16)
order by c.amended_date desc
here [PQR_Schema].[PQR1_table] is 120GB table ; rest 2 are less than 1 GB tables
creating any index isn't an option as we are thinking just not to disturb any other inserts/updates/delete. db maintenance jobs(index rebuild/reorg/stats) jobs are regularly running fine.