8 Replies Latest reply on Sep 20, 2018 6:31 AM by dkv

    Missing Index Advice

    dkv

      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.

        • Re: Missing Index Advice
          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.

            • Re: Missing Index Advice
              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

                • Re: Missing Index Advice
                  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?

                    • Re: Missing Index Advice
                      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

                        • Re: Missing Index Advice
                          mandevil

                          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.

                           

                          select db.NAME

                          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.

                  • Re: Missing Index Advice
                    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 data