I was working for a client on their very critical and heavily used database. The application was an E-commerce website with a ratio of 1 write to 400 reads.
The application would experience sporadic performance issues. The expected response time was in sub-second and this expectation was met in general. But at times some queries would take 4-5 seconds, which was not acceptable.
so I started to look around, the CPU, memory, disk etc.. were very quiet in general and nothing would scream that there is a resource issue or any kind of manifestation of a performance problem.
I reviewed their queries, the table design, indexes etc... and everything looked like was fine. The index statistics were updated automatically by sql server as well as a SQL job that the DBAs had created to manually update statistics of key tables every few hours.
I decided to look at the transaction activities and notice that there was a pattern. most of the transaction activities were focused on few key customers data that was causing the execution plans get outdated that segments of data.
So I ended up writing this query to check when the last time stats were updated and whether the auto update stats is ON or OFF.
Please feel free to customize it per your requirements.
SELECT
OBJECT_NAME(object_id) 'Table Name',
'Index Name' = QUOTENAME(s.name, '['),
'AUTOSTATS' =
CASE s.no_recompute
WHEN 1 THEN 'OFF'
ELSE 'ON'
END,
AUTO_CREATED,
'Last Updated' = STATS_DATE(object_id, s.stats_id)
FROM sys.stats s
WHERE OBJECTPROPERTY(OBJECT_ID, 'IsSystemTable') = 0
AND OBJECT_NAME(object_id) NOT LIKE 'ifts%' -- COMMENT OUT IF WANT TO SEE FULLTEXT INDEXES
AND OBJECT_NAME(object_id) NOT LIKE 'fulltext%' -- COMMENT OUT IF WANT TO SEE FULLTEXT INDEXES
AND AUTO_CREATED = 0 -- COMMENT OUT IF WANT TO SEE AUTO CREATED STATS AS WELL
-- and object_name(object_id) = 'LOCATION' -- PARTICULAR TABLE
ORDER BY [Last Updated] DESC
