Over a million developers have joined DZone.
{{announcement.body}}
{{announcement.title}}

Find Last Statistics Updated Date Detail: Maintenance TIP #59

DZone's Guide to

Find Last Statistics Updated Date Detail: Maintenance TIP #59

· Java Zone
Free Resource

Microservices! They are everywhere, or at least, the term is. When should you use a microservice architecture? What factors should be considered when making that decision? Do the benefits outweigh the costs? Why is everyone so excited about them, anyway?  Brought to you in partnership with IBM.

Problem: One of the pain points for any SQL engineer  is “Performance”. There are various reasons due to which your SQL Server database is slow.

One of the possible reason is your maintenance.  You don’t know when statistics last updated and take further step if those are not updated

Solution:-

Here we have simple query to find when the statistics was last updated for a table.

SELECT o.name,
  i.name AS [Index Name], 
  STATS_DATE(i.[object_id], i.index_id) AS [Statistics Date],
  s.auto_created,
  s.no_recompute,
  s.user_created
FROM sys.objects AS o WITH (NOLOCK)
INNER JOIN sys.indexes AS i WITH (NOLOCK) ON o.[object_id] = i.[object_id]
INNER JOIN sys.stats AS s WITH (NOLOCK)  ON i.[object_id] = s.[object_id]
  AND i.index_id = s.stats_id
WHERE o.[type] = ‘U’
ORDER BY STATS_DATE(i.[object_id], i.index_id) ASC;  

When you run it you will find last statistics update date if it is too old it means you have to run the maintenance for those tables.

see below screenshot which I run on my machines adventureworks2012 database.

Last_update_Date

I am sure you will analyze your database tables stats and run maintenance accordingly.

I hope this tip may help you some where.

Enjoy !!!

Rj!!

 

Discover how the Watson team is further developing SDKs in Java, Node.js, Python, iOS, and Android to access these services and make programming easy. Brought to you in partnership with IBM.

Topics:

Published at DZone with permission of Rajat Jaiswal, DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.

THE DZONE NEWSLETTER

Dev Resources & Solutions Straight to Your Inbox

Thanks for subscribing!

Awesome! Check your inbox to verify your email so you can start receiving the latest in tech news and resources.

X

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}