IF (SELECT COUNT(*) FROM SYS.OBJECTS WHERE NAME LIKE 'TopPlanAdvice') = 1
DROP PROC TopPlanAdvice
GO
-- TopPlanAdvice PROC
--
-- PARMS @Instance, @DaysBack
-- EXAMPLE:
--
-- EXEC TopPlanAdvice 'DBA-001\OPSMGR', 14
CREATE PROC TopPlanAdvice
(@INSTANCE VARCHAR(50), @DaysBack VARCHAR(7))
AS
DECLARE @SQL VARCHAR(4000), @DBID VARCHAR(3);
BEGIN
SELECT @DBID = ID FROM COND WHERE NAME = @INSTANCE;
SET @SQL = 'select TOP 20 * from ( '+
' select sqlhash, sql_text, advice, impact, sum(timesecs) timesecs, (impact/100)*sum(timesecs) seconds_saved '+
' from ( '+
' select ps.sqlhash, '+
' substring(st.st,1,200) sql_text, '+
' substring(a.advice, charindex(''):'',a.advice)+3,1000) advice, '+
' convert(float,substring(a.advice, charindex(''(Impact'',a.advice)+8,4)) impact, '+
' ps.timesecs '+
' from con_plan_sum_'+@DBID+' ps '+
' inner join conspa_'+@DBID+' a on a.plan_hash_value = ps.planhash '+
' inner join const_'+@DBID+' st on st.h = ps.sqlhash and st.p = 0 '+
' where ps.datehour >= CURRENT_TIMESTAMP - CONVERT(float,'+@DaysBack+')) i '+
' group by sqlhash, sql_text, advice, impact) impact '+
'order by seconds_saved desc';
EXEC (@SQL);
END;
GO
