2 Replies Latest reply on Apr 11, 2016 1:57 PM by Jason Krohn

    DPA, SQL Ineffective statistics firing on non existent table

    Jason Krohn

      Hey everyone,

      We have the SQL Server Ineffective Statistics alert setup on a SQL Server environment.

      It is sending an alert about ineffective statistics, on a table that I do not see in the database??

       

      Where does this alert query to find the table information, and if a table no longer exits, why would an alert fire???

       

      Thanks, any help would be much appreciated, getting sick of seeing these bogus emails filling up the inbox..

        • Re: DPA, SQL Ineffective statistics firing on non existent table
          jaminsql

          Jason,

           

          I think the version for SQL server 2005 may be different.

          Here’s the 2008 / 12 / 14 version.

           

          SELECT * FROM

          (SELECT

          object_name(i.id) tablename,i.name,

          stats_date(i.id,i.indid) statsdate,

          1. i.rowcnt,
          2. i.rowmodctr,
          3. s.no_recompute,
          4. sp.rows_sampled

          FROM sys.sysindexes i with (NOLOCK)

          LEFT OUTER JOIN sys.stats s with (NOLOCK)

          ON i.name = s.name

          CROSS APPLY sys.dm_db_stats_properties(s.object_id, s.stats_id) AS sp WHERE i.id > 100 AND i.indid > 0 AND i.rowcnt >= 5000 

          AND cast(i.rowmodctr as decimal)/cast(i.rowcnt as decimal) >= .03 

          AND s.name not like '_WA_Sys_%' AND last_updated <= GETDATE()- 1 )WHERE statsdate is not null