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.

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

What is Heap table in SQL Server and How to get all the heap table from a database ? TIP # 54

 

Heap table:-

A table without cluster index is called Heap table. Now you are thinking why we are talking about this.

So as  SQL developer we should aware and In next post I will explain which is related to maintenance how to Rebuild a heap table.

Now our next question is how to determine all the heap table from a database

so below is simplest query to determine all the heap tables from a  database

SELECT T.Name ‘Heaptable’
FROM sys.indexes I    
    INNER JOIN sys.tables T
        ON I.object_id = T.object_id
WHERE I.type = 0 AND T.type = ‘U’

I ran this query on Adventureworks database and 2 tables with heap as shown in below figure

heaptable

now in next post we will discuss how to rebuild fragmented heap table which is a maintenance activity.

I hope this article may help you.

Thanks !!!

RJ!!

Performance tips – How to determine last statistics update in table ? TIP #46

Hi,

For maintenance purpose we need to determine when the stats last update and analyze the data and if data is out dated then we need to update the stats.

To determine this we need to run the following scripts

SELECT OBJECT_NAME(s.object_id) AS [ObjectName]
      ,s.name AS [StatisticName]
      ,STATS_DATE(s.object_id, [stats_id]) AS [StatisticUpdateDate]
FROM sys.stats s
INNER JOIN sys.objects obj ON obj.object_id = s.object_id
AND obj.type IN (‘U’,’V’)
ORDER BY STATS_DATE(s.object_id, [stats_id]) Asc,obj.name

 

For example I run the following code in adventurework2012

Last_Updated_Stats

 

So according to update status date we do update statistics of those tables in database.

I hope this tip will help you.

Enjoy !!!

RJ