I have just migrated from SQL-2008 to SQL-2016.
I see some of our queries get timed out ,take too long for execution.
The same queries are quick in SQL-2008.
I have tried executing them several times on SQL-2016 and execution plan doesn't help much.I don't want directly go to re-writing the queries as it would be too bigger task.
What aspects should I check on SQL-2016?
- SQL Instance memory is about 135 gb ( same as SQL-2008)
2. SQL-2016 is TDE Enabled & SSL configured.
exec sp_executesql N'WITH Query AS ( SELECT MS.*, ROW_NUMBER() OVER( ORDER BY D_DT DESC ) AS RowNumber
FROM ( SELECT DISTINCT Col1,col2
FROM ( SELECT * FROM View WHERE DID = LTRIM(RTRIM(@DID)) and Type <> ''Fruit''
AND T_DT >= LTRIM(RTRIM(@FROM_DATE)) AND TRANSACTION_DT <= LTRIM(RTRIM(@TO_DATE))
AND Types IN (SELECT types FROM fruitypes ) ) TPYT INNER JOIN Veggies NPC ON TPYT.typeid = NPC.typeid
WHERE TPYT.T_DT >= LTRIM(RTRIM(@FROM_DATE)) AND TPYT.T_DT <= LTRIM(RTRIM(@TO_DATE)) AND TPYT.DID = LTRIM(RTRIM(@DID))
AND ( FTYPES IN (''0100'',''0200'') OR VTYPES IN (''200'',''400''))
AND TPYT.Org IN ('' Fresh '' , ''Veg '') ) MS ) SELECT *, (SELECT COUNT(1) FROM Query)AS TOTALCOUNT FROM Query
WHERE ROWNUMBER >= 1 AND ROWNUMBER <= 10 ORDER BY ROWNUMBER ASC; ',N'
@TO_DATE datetime,@FROM_DATE datetime,@DID nvarchar(5)',
- This topic was modified 4 days, 22 hours ago by GonnaCatchIT.