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 'Database' = DB_NAME(), 'Schema Name' = s.name, 'Object Name' = o.name, -- OBJECT_NAME(object_id) , 'Index Name' = stats.name, 'Last Updated' = STATS_DATE(stats.object_id, stats.stats_id) FROM sys.stats stats INNER JOIN sys.objects o on o.object_id = stats.object_id INNER JOIN sys.schemas s on s.schema_id = o.schema_id WHERE OBJECTPROPERTY(stats.OBJECT_ID, 'IsSystemTable') = 0 -- COMMENT OUT IF WANT TO SEE AUTO CREATED STATS AS WELL AND AUTO_CREATED = 0 ORDER BY [Last Updated] DESC
