2 Replies Latest reply on May 28, 2015 1:02 PM by vmc_dba_team

    How to re-enable DPA Advisor advice for SQL that I'd previously disabled?

    vmc_dba_team

      So I disabled advice for a particular SQL statement a couple of months ago and now that I've tuned it to the point where it won't be the top SQL everyday, all day, I'd like to re-enable advise for it.  Searches for the key words "SQL Advisor", "Disable", etc in the DPA documentation returns nothing on the topic. 

       

      The Advisors are under Trend Analysis, main page, bottom half of the window, first of three tabs (default config); Advisors, Resources and SQL Text.   By clicking on "more..." to the right of a particular piece of advice you'll be taken to a new screen with charts and tuning suggestions.  In the top right corner, to the left of the envelope used to email the report is a ban symbol.  To disable the advise I want to reinstate I click that ban symbol.  Now I'd like to enable it again. 

       

      Any ideas? 

        • Re: How to re-enable DPA Advisor advice for SQL that I'd previously disabled?
          spaceman

          I had same issue, see mandevils response here

          1 of 1 people found this helpful
            • Re: How to re-enable DPA Advisor advice for SQL that I'd previously disabled?
              vmc_dba_team

              That did it Spaceman.  Thanks!

               

              For anyone who may need to do this in the future, here's the script I wrote to re-enable advice using the information spaceman provided.  Note that to protect those who'd blindly run a script without first reading, then testing it in a non-prod enviroment I commented out the line that actually deletes the row for the disabled advice (and so re-enables it).  You'll need to read through the second script and uncomment said EXEC sp_executesql line before using.

               

               

              /*

              Re-enable previously disabled DPA Query Advisors


              Instructions:


              1. Change "USE <Your_DPA_Database>" to "USE UR_ACTUAL_DB"
              2. Provide the name of the instance where you disabled the advisor
              3. Run script 1
              4. Copy the Item number output by the first script and paste to @item variable initialization in script 2
              5. Provide the name of the instance where you disabled the advisor
              6. Uncomment the DELETE statement
              7. Execute Script 2

               

              Run the first script to find the ITEM number to delete by, Copy ITEM value and paste

              into SET @item variable initialization below and run second script


              */

               

               

              -- Script 1

              USE <Your_DPA_Database>

              GO

               

              DECLARE @SQL NVARCHAR(MAX), @InstanceName SYSNAME, @id INT, @item VARCHAR(800)

               

              SET @InstanceName = 'JWSSQLD' -- Name of monitored instance with disabled advice

               

                   SELECT @id = id from ignite.COND WHERE name = @InstanceName

                        SET @SQL = 'SELECT ITEM, SILENCE_DATE FROM ignite.CON_PROBLEM_SILENCE_' + CONVERT(NVARCHAR(2),@id)

                             EXEC sp_executesql @SQL

              GO

               

              /***** STOP! Do not run both scripts at the same time. Output from one is required in the second. ****/

               

              -- Script 2

              USE <Your_DPA_Database>

              GO

               

              DECLARE @SQL NVARCHAR(MAX), @InstanceName SYSNAME, @id INT, @item VARCHAR(800)

               

              SET @InstanceName = 'JWSSQLD' -- Name of monitored instance with disabled advice

               

              SET @item = '3492350683' -- @item value is filler only.  You must replace with YOUR item number.


              SELECT @id = id from ignite.COND WHERE name = @InstanceName

               

              SET @SQL = 'DELETE ignite.CON_PROBLEM_SILENCE_' + CONVERT(NVARCHAR(2),@id) + ' WHERE ITEM = ' + CONVERT(VARCHAR(64),@item)

              PRINT @SQL

              -- EXEC sp_executesql @SQL

               

              SET @SQL = 'SELECT * FROM ignite.CON_PROBLEM_SILENCE_' + CONVERT(NVARCHAR(2),@id) + ' WHERE ITEM = ' + CONVERT(VARCHAR(64),@item)

              -- PRINT @SQL

              EXEC sp_executesql @SQL

              GO