Community
- Command Central
- MVP Program
- Monthly Mission
- Blogs
- Groups
- Events
- Media Vault
Products
- Observability
- Network Management
- Application Management
- IT Security
- IT Service Management
- System Management
- Database Management
Content Exchange
- SolarWinds Platform
- Server & Application Monitor
- Database Performance Analyzer
- Server Configuration Monitor
- Network Performance Monitor
- Network Configuration Manager
- SQL Sentry
- Web Help Desk
Free Tools & Trials

Can I create a report that will send me the top index recommendations from a server?

danglenn

For example, can it send me these values in a report once a week?

Find more posts tagged with

dpa

report

Accepted answers

All comments

darichar

Hi @danglenn , DPA does not include a report for index recommendations, but here is a script you can use to mine the DPA repository for this information. Note that this will show advisors from all instances and rank them globally by the time savings metric: https://thwack.solarwinds.com/kb/articles/3434-global-index-advisor-with-time-savings

HiThwack

Yes, you can. You can do anything you like with the data within the dpa database, as long as you can figure out how to query it.

Simply setup an agent job against the dpa database and send this info to you. If your dpa database is an azure sql db, then use a runbook or elastic jobs to run this on against it on a schedule and email you the info.

Here is the base SQL you can start off with, feel free to modify to your needs.

SET NOCOUNT ON;

DECLARE @DaysBack INT = 7; -- Set to NULL for ALL history

DECLARE @InstanceID NVARCHAR(50);
DECLARE @ConspaTable NVARCHAR(255);
DECLARE @AnalysisTable NVARCHAR(255);
DECLARE @Sql NVARCHAR(MAX);
DECLARE @DateFilter NVARCHAR(MAX);

DROP TABLE IF EXISTS #IndexAdvice;
CREATE TABLE #IndexAdvice (
    [Instance_ID] INT,
    [Advice_Type] NVARCHAR(50),
    [Raw_Advice_Text] NVARCHAR(MAX),
    [Last_Seen_Date] DATETIME NULL
);

DECLARE table_cursor CURSOR FOR 
    SELECT t.name 
    FROM sys.tables t
        INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
    WHERE t.name LIKE 'CONSPA_%' 
      AND s.name = 'ignite';

OPEN table_cursor;
FETCH NEXT FROM table_cursor INTO @ConspaTable;

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @InstanceID = SUBSTRING(@ConspaTable, CHARINDEX('_', @ConspaTable) + 1, LEN(@ConspaTable));
    SET @AnalysisTable = 'CON_INDEX_ANALYSIS_' + @InstanceID;

    IF EXISTS (SELECT 1 FROM sys.tables WHERE name = @AnalysisTable AND schema_id = SCHEMA_ID('ignite'))
    BEGIN
        SET @DateFilter = '';

        IF @DaysBack IS NOT NULL
        BEGIN
            SET @DateFilter = ' AND ia.D >= DATEADD(day, -' + CAST(@DaysBack AS NVARCHAR(10)) + ', GETDATE()) ';
        END

        SET @Sql = '
        INSERT INTO #IndexAdvice ([Instance_ID], [Advice_Type], [Raw_Advice_Text], [Last_Seen_Date])
            SELECT 
                ' + @InstanceID + ',
                CAST(c.ADVICE_TYPE AS NVARCHAR(50)), 
                CAST(c.ADVICE AS NVARCHAR(MAX)),
                MAX(ia.D)
            FROM [ignite].' + QUOTENAME(@ConspaTable) + ' c
                INNER JOIN [ignite].' + QUOTENAME(@AnalysisTable) + ' ia 
                    ON c.PLAN_HASH_VALUE = ia.PLANHASH
            WHERE c.ADVICE_TYPE = ''MI'' ' + @DateFilter + '
            GROUP BY c.ADVICE_TYPE, CAST(c.ADVICE AS NVARCHAR(MAX))';
        
        BEGIN TRY
            EXEC sp_executesql @Sql;
        END TRY
        BEGIN CATCH
            
        END CATCH
    END
    FETCH NEXT FROM table_cursor INTO @ConspaTable;
END
CLOSE table_cursor;
DEALLOCATE table_cursor;

SELECT 
    c.NAME AS [Instance Name],
    CASE 
        WHEN CHARINDEX('Impact ', ia.Raw_Advice_Text) > 0 
        THEN CAST(SUBSTRING(
            ia.Raw_Advice_Text, 
            CHARINDEX('Impact ', ia.Raw_Advice_Text) + 7, 
            CHARINDEX(')', ia.Raw_Advice_Text, CHARINDEX('Impact ', ia.Raw_Advice_Text)) - (CHARINDEX('Impact ', ia.Raw_Advice_Text) + 7)
        ) AS DECIMAL(10,4))
        ELSE 0
    END AS [Impact Score],
    CASE 
        WHEN CHARINDEX(': CREATE', ia.Raw_Advice_Text) > 0 
        THEN SUBSTRING(ia.Raw_Advice_Text, CHARINDEX(': CREATE', ia.Raw_Advice_Text) + 2, LEN(ia.Raw_Advice_Text))
        ELSE ia.Raw_Advice_Text 
    END AS [Create Statement],
    ia.Last_Seen_Date
FROM #IndexAdvice ia
    LEFT JOIN ignite.COND c ON ia.Instance_ID = c.ID
ORDER BY 
    c.NAME ASC,
    [Impact Score] DESC;
GO

Quick Links

All Categories
Recent Posts
Activity
Unanswered
Groups
Help
Best Of