Custom Report - Top Plan Advice

Thanks,

The below line of code was failing for some conditions

' convert(float,substring(a.advice, charindex(''(Impact'',a.advice)+8,4)) impact, '+

I replaced with this by

' convert(float,SUBSTRING(a.advice,charindex(''('',a.advice)+7, (charindex('')'',a.advice)-7)-charindex(''('',a.advice))) impact, '+

Is there a way to add  database name in the output?

I understand your desire to list the database name for instances with many databases on them and could use it as well.  I found that in the const_<DBID> table there was a pname field (proc name?) which would start out with a database name.  I added that to the code in a field I call DB_clue and I also added an instance name field.  Sometimes the DB_clue name field is NULL, but often it is filled, and often when it is not, the sql_text field includes the db name somewhere inside it.  So see if the following is helpful and see the two notes at the bottom of the script.

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 ignite.COND WHERE NAME = @INSTANCE;

  SET @SQL = 'select TOP 20 * from ( '+

  ' select sqlhash, ''' + @INSTANCE + ''' as ''Instance'', DB_clue,  sql_text, advice, impact, sum(timesecs) timesecs, (impact/100)*sum(timesecs) seconds_saved '+

  ' from ( '+

  ' select substring(st.pname,1,charindex(''.'',st.pname) - 1) AS ''DB_clue'', '+

  ' ps.sqlhash, '+

  ' substring(st.st,1,200) sql_text, '+

  ' substring(a.advice, charindex(''):'',a.advice)+3,1000) advice, '+

  ' convert(float,SUBSTRING(a.advice,charindex(''('',a.advice)+7, (charindex('')'',a.advice)-7)-charindex(''('',a.advice))) impact, '+

  ' ps.timesecs '+

  ' from ignite.con_plan_sum_'+@DBID+' ps '+

  ' inner join ignite.conspa_'+@DBID+' a on a.plan_hash_value = ps.planhash '+

  ' inner join ignite.const_'+@DBID+' st on st.h = ps.sqlhash and st.p = 0 '+

  ' where ps.datehour >= CURRENT_TIMESTAMP - CONVERT(float,'+@DaysBack+')) i '+

  ' group by DB_Clue, sqlhash, sql_text, advice, impact) impact '+

  'order by seconds_saved desc';

  EXEC (@SQL);

END;

GO

Note 1 - my repository has the schema name of ignite for every table (rather than dbo), so I added it into the script.  If your repository does not, remove or change it.

Note 2 - I'm not sure about this, but if you have multiple databases that are very similar, you might have a sqlhash that is used by several db's?  That is why I call one of my new fields DB_clue rather than the authoritative DB_Name.  Can someone confirm or deny that sqlhash can be used by several db's?

For me, DB_clue is populated for all stored procedures, but not for ad hoc statements. Just FYI.