Don’t Rely Only on SolarWinds DPA for Index Recommendations

Leave a Comment / Articles / By

Start with SQL Server DMVs. Prove changes with SET STATISTICS. Use DPA to prioritize, not prescribe.

DPA is a wait-based analysis tool, which means it only surfaces tuning recommendations when the top waits are directly index-related. If the dominant waits are CPU, blocking, or something else, you won’t see index suggestions. This isn’t just my take — SolarWinds documents it clearly: their Response Time Analysis whitepaper explains that DPA measures wait events and ties them back to the queries causing delays; the “DPA approach to investigating performance issues” page notes that the product uses wait-based analysis to highlight where improvements matter most; and the Tuning Advisor documentation makes clear that index suggestions only appear when inefficient queries against a table could benefit from indexing.

If you live in SQL Server every day, DPA is still a great lens for spotting pain — it ranks wait types, bubbles up problem instances, and helps you zero in on what hurts most right now. The catch is that those recommendations are symptoms at the server and query level. While the Tuning Advisor can generate index recommendations, they come from observed waits and query plans, so if you rely only on DPA you risk adding the wrong index, missing an obvious one, or fixing the symptom instead of the real problem.

This post lays out a practical workflow:

  1. triage with DPA or wait stats,
  2. investigate with SQL Server DMVs,
  3. design a candidate index,
  4. test it with SET STATISTICS IO/TIME and execution plans,
  5. validate impact and watch for side effects.

You’ll get scripts and a repeatable approach you can drop into your own environment.

Why DPA Won’t Hand You the Index Definition

DPA is excellent at showing what your workload is waiting on. Examples:

  • PAGEIOLATCH_* or READ_* waits often point to heavy I/O and large scans.
  • LCK_* waits point to blocking and concurrency.
  • SOS_SCHEDULER_YIELD hints at CPU pressure.
  • CXPACKET or CXCONSUMER tell you about parallelism behavior.

Those signals help you prioritize where to look, but they do not translate directly to “create this nonclustered index with these columns in this order.” Waits can be caused by many things:

  • Scans because a needed index is missing
  • Scans because a predicate is non-SARGable
  • Blocking due to poor transaction design
  • Parameter sniffing causing bad plans
  • Statistics or cardinality estimation issues
  • Memory pressure leading to re-reads

DPA will not validate an index design or show the row-by-row I/O impact of a proposed index. That’s your job with DMVs, plans, and targeted testing.

The Right Starting Point: SQL Server DMVs

1) Inventory missing-index hints (then treat them as hints)

SQL Server exposes “missing index” suggestions captured from compiled plans. They are not gospel, but they are a useful starting list.

-- Top missing-index opportunities in the current database
SELECT TOP (25)
    improvement_measure =
      CONVERT(decimal(18,2),
        migs.avg_total_user_cost
        * (migs.avg_user_impact/100.0)
        * (migs.user_seeks + migs.user_scans)),
    DB_NAME(mid.database_id) AS database_name,
    OBJECT_NAME(mid.[object_id], mid.database_id) AS table_name,
    mid.equality_columns,
    mid.inequality_columns,
    mid.included_columns,
    migs.user_seeks,
    migs.user_scans,
    migs.avg_user_impact
FROM sys.dm_db_missing_index_group_stats AS migs
JOIN sys.dm_db_missing_index_groups AS mig
  ON migs.group_handle = mig.index_group_handle
JOIN sys.dm_db_missing_index_details AS mid
  ON mig.index_handle = mid.index_handle
WHERE mid.database_id = DB_ID()
ORDER BY improvement_measure DESC;

Caveats:

  • Reset on restart or plan cache flush.
  • Suggestions are per query, not workload-wide.
  • No advice on filtered indexes or column order in includes.
  • Can suggest overlapping or duplicate indexes.

Use them to seed ideas, not as an automatic “create index” list.

2) Confirm which queries are heavy

If you use Query Store (recommended), pull top readers or CPU burners

-- Top read-heavy queries in the last 24 hours via Query Store
SELECT TOP (20)
    qsq.avg_logical_io_reads,
    qsqt.query_sql_text,
    qsp.query_plan
FROM sys.query_store_query_text AS qsqt
JOIN sys.query_store_query AS qsq
  ON qsq.query_text_id = qsqt.query_text_id
JOIN sys.query_store_plan AS qsp
  ON qsp.query_id = qsq.query_id
JOIN sys.query_store_runtime_stats AS qsr
  ON qsr.plan_id = qsp.plan_id
JOIN sys.query_store_runtime_stats_interval AS qsi
  ON qsr.runtime_stats_interval_id = qsi.runtime_stats_interval_id
WHERE qsi.start_time >= DATEADD(hour, -24, SYSUTCDATETIME())
ORDER BY qsq.avg_logical_io_reads DESC;

No Query Store? Use the plan cache:

-- Top by logical reads from plan cache (beware of transient data)
SELECT TOP (20)
    total_reads = qs.total_logical_reads,
    avg_reads   = qs.total_logical_reads / NULLIF(qs.execution_count,0),
    qs.execution_count,
    text        = SUBSTRING(qt.text, (qs.statement_start_offset/2) + 1,
                            ((CASE qs.statement_end_offset
                                  WHEN -1 THEN DATALENGTH(qt.text)
                                  ELSE qs.statement_end_offset END
                              - qs.statement_start_offset)/2) + 1),
    plan_handle = qs.plan_handle
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
ORDER BY total_reads DESC;

3) Check existing index usage and overlap

Before adding a new index, see what you already have and whether it’s used.

-- Index usage since last restart
SELECT
    OBJECT_SCHEMA_NAME(i.[object_id]) AS schema_name,
    OBJECT_NAME(i.[object_id]) AS table_name,
    i.name AS index_name,
    i.index_id,
    us.user_seeks,
    us.user_scans,
    us.user_lookups,
    us.user_updates
FROM sys.indexes AS i
LEFT JOIN sys.dm_db_index_usage_stats AS us
  ON us.[object_id] = i.[object_id]
 AND us.index_id = i.index_id
 AND us.database_id = DB_ID()
WHERE i.[object_id] > 100
ORDER BY (us.user_seeks + us.user_scans + us.user_lookups) DESC;

This helps you avoid near-duplicates and spot unused or write-heavy indexes.

A Concrete Example

Workload symptom in DPA: high PAGEIOLATCH_SH and READ waits during peak hours. DPA highlights a couple of expensive queries but doesn’t tell you what index to add.

Top query:

SELECT o.OrderId, o.CustomerId, o.CreatedAt, o.Status, o.OrderTotal
FROM dbo.Orders AS o
WHERE o.CustomerId = @CustomerId
  AND o.CreatedAt  BETWEEN @From AND @To
ORDER BY o.CreatedAt DESC;

Missing index DMV hint suggests:

  • Equality columns: CustomerId
  • Inequality columns: CreatedAt
  • Included columns: Status, OrderTotal

Candidate index:

CREATE INDEX IX_Orders_CustomerId_CreatedAt
ON dbo.Orders (CustomerId, CreatedAt DESC)
INCLUDE (Status, OrderTotal);

Why this design:

  • Leading on CustomerId matches the equality predicate.
  • Then CreatedAt supports the range and the ORDER BY.
  • Including Status and OrderTotal avoids lookups.

Prove It With SET STATISTICS And Plans

Always test in a safe environment with realistic data. Warm the cache with a couple of executions before measuring.

Baseline the query

SET STATISTICS IO ON;
SET STATISTICS TIME ON;
-- optionally: SET STATISTICS XML ON;

-- Run representative parameter values multiple times
DECLARE @CustomerId int = 123;
DECLARE @From datetime2 = DATEADD(day, -30, SYSUTCDATETIME());
DECLARE @To   datetime2 = SYSUTCDATETIME();

SELECT o.OrderId, o.CustomerId, o.CreatedAt, o.Status, o.OrderTotal
FROM dbo.Orders AS o
WHERE o.CustomerId = @CustomerId
  AND o.CreatedAt BETWEEN @From AND @To
ORDER BY o.CreatedAt DESC;

Typical baseline output (example):

Table 'Orders'. Scan count 1, logical reads 120000, physical reads 300, read-ahead reads 2000.
 SQL Server Execution Times: CPU time = 620 ms, elapsed time = 910 ms.

Check the actual execution plan. If you see a clustered or heap scan with a big cost, that confirms the read storm.

Create the index and retest

CREATE INDEX IX_Orders_CustomerId_CreatedAt
ON dbo.Orders (CustomerId, CreatedAt DESC)
INCLUDE (Status, OrderTotal);
GO

-- Re-run the same test after warming up
SET STATISTICS IO ON;
SET STATISTICS TIME ON;

SELECT o.OrderId, o.CustomerId, o.CreatedAt, o.Status, o.OrderTotal
FROM dbo.Orders AS o
WHERE o.CustomerId = @CustomerId
  AND o.CreatedAt BETWEEN @From AND @To
ORDER BY o.CreatedAt DESC;

Expected improvement (example):

Table 'Orders'. Scan count 1, logical reads 230, physical reads 0, read-ahead reads 0.
 SQL Server Execution Times: CPU time = 15 ms, elapsed time = 28 ms.

Plan should now show an Index Seek on IX_Orders_CustomerId_CreatedAt with a small range scan to satisfy the date filter and the order.

Turn stats back off

SET STATISTICS IO OFF;
SET STATISTICS TIME OFF;

Double-Check the Side Effects

New indexes are not free. They add write cost and storage. Validate:

  1. Write overhead

— Operational stats to see hot metrics since last restart
SELECT
OBJECT_SCHEMA_NAME(ios.[object_id]) AS schema_name,
OBJECT_NAME(ios.[object_id]) AS table_name,
i.name AS index_name,
ios.leaf_insert_count,
ios.leaf_update_count,
ios.leaf_delete_count,
ios.leaf_page_splits
FROM sys.dm_db_index_operational_stats(DB_ID(), NULL, NULL, NULL) AS ios
JOIN sys.indexes AS i
ON i.[object_id] = ios.[object_id]
AND i.index_id = ios.index_id
WHERE i.name = ‘IX_Orders_CustomerId_CreatedAt’;

  1. Redundancy
    Compare to existing indexes to avoid overlap. If the new index completely covers another low-use index, consider consolidating.
  2. Fragmentation and fill factor
    For heavy insert patterns ordered by time, you might reduce page splits with a lower FILLFACTOR.

Where DPA Fits In This Flow

Use DPA to prioritize which time windows and queries deserve attention.

  • If top waits are predominantly I/O (PAGEIOLATCH_*, IO_COMPLETION), look for large scans and consider missing indexes.
  • If top waits are locking (LCK_*), indexing might help by shortening query time, but you may also need to tune transactions and isolation.
  • If waits are CPU (SOS_SCHEDULER_YIELD), indexing can help reduce CPU on scans, but you might also need to fix non-SARGable predicates or reduce row counts earlier.

If DPA does not show an “index recommendation,” that doesn’t mean an index isn’t needed. It means the tool is reporting symptoms, not handing you a DDL statement. Your DMV and plan analysis closes that gap.

Extra Patterns and Tips

Translate DMV suggestions into good definitions

  • Put equality predicates first in the key.
  • Put range predicates after equality.
  • Add INCLUDE columns to avoid lookups for the final projection.
  • Consider filtered indexes for skewed predicates, for example:
CREATE INDEX IX_Orders_Open_Recent
ON dbo.Orders (CustomerId, CreatedAt)
INCLUDE (Status, OrderTotal)
WHERE Status = 'Open' AND CreatedAt >= DATEADD(day, -30, SYSUTCDATETIME());
  • Beware of wide INCLUDE lists. They bloat storage and writes.

Catch non-SARGable predicates

If your filter is WHERE CONVERT(date, CreatedAt) = @d, SQL can’t seek on the index. Rewrite to a range:

WHERE CreatedAt >= @d AND CreatedAt < DATEADD(day, 1, @d);

Parameter sniffing check

Test with a hot and a cold parameter value. If plans diverge, consider option hints sparingly, or re-shape the query for stable estimates. Query Store can also help pin a better plan temporarily.

Keep stats fresh

Outdated statistics can lead to scans even when a good index exists. For critical tables:

UPDATE STATISTICS dbo.Orders WITH FULLSCAN;

Or configure async or scheduled stats updates that fit your workload.

A Short, Repeatable Checklist

  1. Use DPA (or wait stats) to pick the time block and query that matter most.
  2. Pull candidate queries from Query Store or plan cache.
  3. Review missing-index DMVs for hints.
  4. Design a candidate index that matches predicates and output.
  5. Prove the win with SET STATISTICS IO/TIME and the actual plan.
  6. Validate side effects with index usage and operational stats.
  7. Monitor in production and adjust fill factor or consolidate overlapping indexes.

Copy-Ready Script Pack

A. Missing index report (per database) – see earlier section.

B. Top cached queries by reads – see earlier section.

C. Quick plan text for a cached statement

-- Grab the plan XML for a specific plan handle you found earlier
SELECT query_plan
FROM sys.dm_exec_query_plan(@plan_handle);

D. Index usage overview – see earlier section.

E. Stats IO/Time harness

-- Wrapper for consistent tests
SET NOCOUNT ON;
SET STATISTICS IO ON;
SET STATISTICS TIME ON;

-- Place your test query here…

SET STATISTICS IO OFF;
SET STATISTICS TIME OFF;

Bottom Line

DPA is great at telling you where it hurts. SQL Server’s DMVs and execution plans tell you why it hurts and what to change. Start with DMVs, design thoughtful indexes, and prove the effect with SET STATISTICS. Let DPA validate that pain is going down during real workloads, but don’t expect it to author your indexes for you.

SolarWinds documents :

  • Whitepaper: Response Time Analysis — DPA focuses on measuring wait events and tying them to the queries causing delays.
  • DPA Documentation — “The DPA approach to investigating performance issues” specifically says it uses wait-based analysis to identify where performance improvements matter most.
  • DPA Advisors — Index advisors only appear when DPA detects inefficient queries against a table that could benefit from indexing; otherwise that section will be empty.

Discover more from SQLYARD

Subscribe to get the latest posts sent to your email.

Related Posts

Leave a Comment

Contact us
Contact us