Find last statistics updated date detail ?–Maintenance TIP #59

 

Problem:- One of the pain point in 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.

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!!

How to update statistics ? TIP #14

b

In last TIP tip#13, We learn how to find last updated statistics status.

Now we know when it last updated so it may be require we need to update statistics for some of table.

So to update statistics we need to write following command ( if we want to update  statistics of entire tables objects)

Go

EXEC sp_updatestats;

GO

Now if we want to  update statistics of particular table then we need to write following command

GO

UPDATE STATISTICS tblProductStock

GO

Now if we want to update statistics of particular index of a table then we need to write following command

GO

UPDATE STATISTICS tblCustomer PK_tblCustomer;

GO

WHERE tblcustomer is table name and pk_tblCustomer is primary key

 

So enjoy !!!

GO

How to determine Last statistics update? TIP #13

To determine last statistics update we need to run following command.

Go
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;   
GO

OR

We can use following statement

Go
DBCC SHOW_STATISTICS ('users',PK_User);
GO



Enjoy !!!