Checking SQL server stats

-- use dm_db_stats_properties to get stats info
-- for SQL 2012 and above
SELECT
 OBJECT_NAME([sp].[object_id]) AS "Table"
 ,[sp].[stats_id] AS "Statistic ID"
 ,[s].[name] AS "Statistic"
 ,[sp].[rows_sampled]
 ,[sp].[modification_counter] AS "Modifications"
 ,[sp].[last_updated] AS "Last Updated"
 ,[sp].[rows]
 ,[sp].[unfiltered_rows]
 FROM [sys].[stats] AS [s]
   OUTER APPLY sys.dm_db_stats_properties ([s].[object_id],[s].[stats_id]) AS [sp]
 --WHERE [s].[object_id] = OBJECT_ID(N'Sales.TestSalesOrderDetail');
 ORDER BY 1,2

--
-- quick way to check which stats objects are used by query

DBCC FREEPROCCACHE

SELECT 

    p.Name,

    total_quantity = SUM(th.Quantity)

FROM AdventureWorks.Production.Product AS p

JOIN AdventureWorks.Production.TransactionHistory AS th ON

    th.ProductID = p.ProductID

WHERE

    th.ActualCost >= $5.00

    AND p.Color = N'Red'

GROUP BY

    p.Name

ORDER BY

    p.Name

OPTION

(

    QUERYTRACEON 3604,

    QUERYTRACEON 9292,

    QUERYTRACEON 9204

)

--
-- checking auto created stats which are never updated or updated before 1 week
DECLARE @NumOfDays int, @StateCheckDateTimeDeadLine datetime
SET @NumOfDays=7
SET @StateCheckDateTimeDeadLine=DATEADD(hour, -24*@NumOfDays, getdate())
--SELECT @StateCheckDateTimeDeadLine

SELECT object_name(object_id) as TableName
,name AS StatisticsName
,STATS_DATE(object_id, stats_id) AS StatisticsUpdatDate
,auto_created AS IsAutoCreated
FROM sys.stats
WHERE auto_created=1
-- never updated or updated before dead line
AND (STATS_DATE(object_id, stats_id) IS NULL
OR STATS_DATE(object_id, stats_id)<=@StateCheckDateTimeDeadLine)
order by 1,2

-- after this check detail of each stat
DBCC SHOW_STATISTICS ( table_or_indexed_view_name , target )
[ WITH [ NO_INFOMSGS ] < option > [ , n ] ]
< option > :: =
    STAT_HEADER | DENSITY_VECTOR | HISTOGRAM | STATS_STREAM

DBCC SHOW_STATISTICS ("MyTable", MyPrimayKey);
DBCC SHOW_STATISTICS ("MyTable", MyStatsName);

reference:
1)
http://sqlblog.com/blogs/paul_white/archive/2011/09/21/how-to-find-the-statistics-used-to-compile-an-execution-plan.aspx
2)
http://www.sqlskills.com/blogs/erin/understanding-when-statistics-will-automatically-update/