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

          What kind of repo do you have? Oracle, SQL, or MySQL?

              • 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