6 Replies Latest reply on Dec 7, 2016 6:51 PM by ezerylnick@wescom.org

    Is there a way to list all the query advisors that include Tuning Advice?

    ezerylnick@wescom.org

      One of the best features of DPA is bringing attention to queries that could use a new or altered index.  Plenty of times this has made a huge difference!  We monitor over 70 db active db servers, so it would be great if there was a way (a query from the repository or something in the UI) that could bring all those high priority Tuning Advice notes from all servers together into one output.  Does anyone in the community have a way to do that?

            • Re: Is there a way to list all the query advisors that include Tuning Advice?
              mandevil

              Have you checked out this query to generate a report from the repo?

              Custom Report - Top Plan Advice

              See if that gets you close to what you wanted.

                • Re: Is there a way to list all the query advisors that include Tuning Advice?
                  ezerylnick@wescom.org

                  That gets me very close to what I want, but for a single instance - thank you!  I may post a script that takes it the rest of the way to a single output for all servers - we'll see if that is as easy to do as I hope it is.

                    • Re: Is there a way to list all the query advisors that include Tuning Advice?
                      mandevil

                      You could wrap it in a cursor that pulls the ID column from COND and then loops through all instances by appending the ID value...

                      Food for thought.

                        • Re: Is there a way to list all the query advisors that include Tuning Advice?
                          ezerylnick@wescom.org

                          Thanks, mandevil.  I've been working along those lines, but I always try to avoid actual SQL Server cursors as they can be unreliable and slow, so I loop thru in a different manner.  Here's what I have so far that works well in my repository. 

                           

                          IF (SELECT COUNT(*) FROM SYS.OBJECTS WHERE NAME LIKE 'TopPlanAdviceAllServers') = 1

                          DROP PROC TopPlanAdviceAllServers

                          GO

                           

                          -- TopPlanAdviceAllServers PROC

                          --

                          -- PARMS  @DaysBack

                          -- EXAMPLE:

                          --

                          -- EXEC TopPlanAdviceAllServers 7

                           

                          CREATE PROC TopPlanAdviceAllServers

                          @DaysBack VARCHAR(7)

                          AS

                          DECLARE @SQL VARCHAR(4000), @DBID VARCHAR(3), @INSTANCE  VARCHAR(100);

                          BEGIN

                           

                           

                          /* Plan - create temp table for ouput; loop thru all Started Instances; fill table with query from each instance;

                            output is just select * from the temp table */

                           

                           

                          -- create temp table to hold Query results

                            IF OBJECT_ID('tempdb..#temp_queryResults') IS NOT NULL

                            DROP TABLE #temp_queryResults ;

                           

                           

                            CREATE TABLE #temp_queryResults

                            (sqlhash BIGINT, Instance VARCHAR(100), DB_clue VARCHAR(175),

                            sql_text VARCHAR(200), advice VARCHAR(1000), impact VARCHAR(20), 

                            timesecs decimal(20,1),  seconds_saved decimal(25,6)) ;

                           

                            -- create loop to do the query

                            SELECT  @DBID = 0,  @INSTANCE = '';   -- initialize

                           

                            WHILE 1 = 1

                            BEGIN

                            SELECT @DBID = MIN(ID)

                            FROM ignite.COND

                            WHERE STATUS = 'Started'

                            AND ID > @DBID;

                           

                            IF @DBID IS NULL

                            BREAK    -- break out of loop

                           

                             SELECT @INSTANCE = NAME FROM ignite.COND WHERE ID = @DBID;

                           

                             SET @SQL = 'INSERT #temp_queryResults (sqlhash, Instance, DB_clue, sql_text, advice, impact, timesecs,  seconds_saved ) ' +

                             ' 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 ';

                           

                            -- PRINT @SQL  -- debug

                           

                           

                              EXEC (@SQL);

                           

                           

                            END  /* end of loop */

                           

                           

                            SELECT * FROM #temp_queryResults ORDER BY seconds_saved DESC;

                           

                           

                            DROP TABLE #temp_queryResults;

                           

                           

                          END;

                          GO

                           

                          I output it based on number of seconds saved over all servers.  One could easily change that order by to Instance, seconds_saved DESC.

                           

                          I also have questions about my DB_clue column, but overall it is meeting my needs.  Thanks for your help!

                           

                          Ed

                          1 of 1 people found this helpful