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
Store

Missing Index Advice

I see missing index advice in [dpa_repository].[ignite].[CONSPA_1] table. How can I get the database name related to this schema? I may have the same schema and table names on my sandbox database as in production database on the same database instance. The queries in sandbox may be different from those in production; most queries on sandbox are adhoc during the development phase. I want to be sure I only review the advice for production database.

Thanks.

Find more posts tagged with

Accepted answers

All comments

mandevil

Are you on DPA 12.0? The database is the dpa_repository. The format is db.schema.table.

Looks like you are monitoring the instance housing the DPA repo and we hooked one of our own! 8 ) Kind of ironic, eh?

The good thing is that we're drinking our own champagne - we've started using DPA to look at ways to make DPA more efficient/performant.

dkv

I am on DPA 11.1.468 and I am not monitoring the instance housing the DPA repo. I am monitoring the instance housing our data warehouse. Is there a way to get the db name for index advice on DPA 11.1.468?

Thanks

404 949 5028

mandevil

Ah, gotcha - should be in the advice column itself. Something like:

select advice from conspa_<id> where ...

Missing Index (Impact 10.2865): CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname, >] ON [tpch].[dbo].[lineitem] ([l_orderkey],[l_tax])

Any plans for upgrading to 12.0 which is in RC now?

dkv

The advice column has the following:

Missing Index (Impact 96.7761): CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname, >] ON [CLA].[PATTBL] ([VISIT_ID],[CONTACT_DATE],[ENC_TYPE]) INCLUDE ([PAT_ID],[PAT_CSN_ID],[PRIME_ID])
No db name there.

Is there another table tied to PLAN_HASH_VALUE in [dpa_repository].[ignite].[CONSPA_1] that shows db name in this version (DPA 11.1.468)?

We may be going to 12.0 soon. When we update to 12.0, do we have to re create custom alerts? What about trends stored in the repository now? Do they stay or get wiped out?

Thanks

dkv

We still have SQL Server 2008 R2. I tried upgrading to DPA 12.0, but it does not support SQO 2008R2.

Any other ideas on how I can get the database name on the missing index alert?

I am using the data in to generate alert, but it only shows schema and table name, not database name.

SELECT TOP 100 * FROM <myserver>.[dpa_repository].[ignite].[CONSPA_1]

missing index alerts.png

mandevil

Any chance you can upgrade your repo version or move to another supported SQL Server version?

In DPA 12.0 has a TON of goodness that I don't want you to miss out on.

dkv

Does DPA 12.0 support SQL 2008 R2.

We are planning to upgrade to SQL 2016 sometime next year. Until then we have to work with SQL 2008R2.

Deep