Monday, November 3, 2014

When statistics was updated ?

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

0 comments:

Post a Comment