DBA

A application run a query which sometimes slow on one literal but fast on the other, this is the case study of SQL Server plan guide feature: Slow query : use 255 as literal, run duration is ~ 1 minute SELECT top 1 mh.intnum as intnum,rh.remark as remark,reportid FROM msghead mh inner join msgdetail md on md.intnum = mh.msgintnum left outer join rephistory rh on md.functionnum = 45000 AND md.functionref = rh.intnum WHERE readon < '1900/01/02' AND((isnull(rh.userid,0) = 255 AND md.functionnum = 45000 and rh.remark like 'http%'))  Fast query : use 167 or 66 as literal, complete in less than 1 second SELECT top 1 mh.intnum as intnum,rh.remark as remark,reportid FROM msghead mh inner join msgdetail md on md.intnum = mh.msgintnum left outer join rephistory rh on md.functionnum = 45000 AND md.functionref = rh.intnum WHERE readon < '1900/01/02' AND((isnull(rh.userid,0) = 167 AND md.functionnum = 45000 and rh.remark like 'http%'))  Thanks to this book : ou...

Oracle DBA use SQL plan management (SPM) to stabilize query performance, now SQL Server 2016 has similar feature called query store,  This is case study to answer a few questions: 1. Observe how query store work, comparing query plan. 2.Can similar queries which use different literal use other query's plan? if not, what DBA can do to influence optimizer to to choose better query plan compiled for other literal. 3. Can the plan be exported from one sqlserver and import to other sqlserver? Let's get into it: 1. Observe how query store work, comparing query plans. I have two queries which are different with literal, one run fast, the other one run slow. Slow query: use isnull(rh.userid,0) = 255  SELECT top 1 mh.intnum as intnum,rh.remark as remark,reportid FROM msghead mh inner join msgdetail md on md.intnum = mh.msgintnum left outer join rephistory rh on md.functionnum = 45000 AND md.functionref = rh.intnum WHERE readon < '1900/01/02' AND((isnull(rh.userid,0) = 255 AND...