Log Shipping – NORECOVERY vs. STANDBY mode

Statistics are very important for sql server engine,  it is  used by the SQL Server optimizer to choose the most efficient plan.  When we don’t have up to date statistics
it may end with SQL server optimizer choosing inefficient query plan. You can use below query to identify when SQL Server statistics were last updated

 

SELECT  DB_ID() AS dbid ,
        CASE WHEN indid IN ( 0, 1 ) THEN CONVERT (CHAR(12), rows)
             ELSE ( SELECT  rows
                    FROM    dbo.sysindexes i2
                    WHERE   i2.id=i.id
                            AND i2.indid IN ( 0, 1 )
                  ) -- ''-''
        END AS rowcnt ,
        CASE WHEN indid IN ( 0, 1 ) THEN rowmodctr
             ELSE CONVERT (BIGINT, rowmodctr)
                  +( SELECT rowmodctr
                     FROM   dbo.sysindexes i2
                     WHERE  i2.id=i.id
                            AND i2.indid IN ( 0, 1 )
                   )
        END AS row_mods ,
        CASE rows
          WHEN 0 THEN 0
          ELSE CONVERT (BIGINT, CASE WHEN indid IN ( 0, 1 )
                                     THEN CONVERT (BIGINT, rowmodctr)
                                     ELSE rowmodctr
                                          +( SELECT CONVERT (BIGINT, rowmodctr)
                                             FROM   dbo.sysindexes i2
                                             WHERE  i2.id=i.id
                                                    AND i2.indid IN ( 0, 1 )
                                           )
                                END
               /CONVERT (NUMERIC(20, 2), ( SELECT   CASE CONVERT (BIGINT, rows)
                                                      WHEN 0 THEN 0.01
                                                      ELSE rows
                                                    END
                                           FROM     dbo.sysindexes i2
                                           WHERE    i2.id=i.id
                                                    AND i2.indid IN ( 0, 1 )
                                         ))*100)
        END AS pct_mod ,
        CONVERT (NVARCHAR, u.name+'.'+o.name) AS objname ,
        CASE WHEN i.status&0x800040=0x800040 THEN 'AUTOSTATS'
             WHEN i.status&0x40=0x40
                  AND i.status&0x800000=0 THEN 'STATS'
             ELSE 'INDEX'
        END AS type ,
        CONVERT (NVARCHAR, i.name) AS idxname ,
        i.indid ,
        STATS_DATE(o.id, i.indid) AS stats_updated ,
        CASE i.status&0x1000000
          WHEN 0 THEN 'no'
          ELSE '*YES*'
        END AS norecompute ,
        o.id AS objid ,
        rowcnt ,
        i.status
FROM    dbo.sysobjects o ,
        dbo.sysindexes i ,
        dbo.sysusers u
WHERE   o.id=i.id
        AND o.uid=u.uid
        AND i.indid BETWEEN 1 AND 254
        AND o.type='U'
ORDER BY pct_mod DESC ,
        CONVERT (NVARCHAR, u.name+'.'+o.name) ,
        indid
GO