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.
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?
404 949 5028
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?
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?
So that advice is coming from SQL Server directly, so I wonder why it's not capturing the db name? I take it CLA is the schema. Plan is used for multiple databases? Likely.
OK, let's try this. We can join back to CONSW and then on to CONO. Forgive formatting, Oracle guy... Should work in SQL though.
from CONO_<id> db, CONSW_<id> sw, CONSPA_<id> spa
where spa.plan_hash_value = sw.orph
and sw.ixoy = db.id
and spa.plan_hash_value = <plan hash here>
That should pull back the databases "caught" using the plan with the advice in it. LMK.
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]
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.
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.