Friday, 17 February 2017

update stats modified date in SQL server




SELECT  sp.stats_id ,
        OBJECT_NAME(s.object_id) AS tablename ,
        s.name ,
sp.last_updated,
        sp.rows ,
        sp.rows_sampled ,
        CAST(sp.rows_sampled AS NUMERIC) / CAST(sp.rows AS NUMERIC) * 100 AS Percentage_sampled ,
        CASE WHEN sp.modification_counter = 0 THEN 0
             ELSE CAST(sp.modification_counter AS NUMERIC)
                  / CAST(sp.rows AS NUMERIC) * 100
        END AS Percent_changed ,
        CASE WHEN sp.modification_counter = 0 THEN 'No Modifications'
ELSE
CAST(sp.modification_counter AS VARCHAR(20))
END AS Modification_counter
FROM    sys.stats AS s
        CROSS APPLY sys.dm_db_stats_properties(s.object_id, s.stats_id)
        AS sp
WHERE   sp.last_updated <= DATEADD(DAY, -1, GETDATE())
        AND sp.rows > 10000

No comments:

Post a Comment

I thought you'd be interested in this job at NICE or know someone who might be a good match.

Job: Tech Lead SQL Developer  I thought you'd be interested in this job at NICE or know someone who might be a good match.