/*============================================================================
  File:     5a_Statistics.sql

  Summary:  This script shows how to see what statistics exist,
			how to view statistics information, and how to
			update them.

  Date:     April 2017

  SQL Server Version: 2005/2008/2008R2/2012/2014/2016
------------------------------------------------------------------------------
  Written by Erin Stellato, SQLskills.com

  (c) 2015, SQLskills.com. All rights reserved.

  For more scripts and sample code, check out 
    http://www.SQLskills.com

  You may alter this code for your own *non-commercial* purposes. You may
  republish altered code as long as you include this copyright and give due
  credit, but you must obtain prior permission before blogging this code.
  
  THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF 
  ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED 
  TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A
  PARTICULAR PURPOSE.
============================================================================*/

/*
	Check to see what stats-related options are enabled for the database
*/
SELECT 
	[database_id],
	[name], 
	[is_auto_create_stats_on], 
	[is_auto_update_stats_on], 
	[is_auto_update_stats_async_on]
FROM [sys].[databases]
WHERE [name] = 'AdventureWorks2016';
GO


/*
	How do you know what statistics exist?
*/
USE [AdventureWorks2016];
GO


/*
	This option is deprecated and will be removed after SQL2012
*/
EXEC sp_helpstats 'Sales.SalesOrderDetail', 'ALL';
GO


/*
	Use sys.stats instead
*/
SELECT  
	[sch].[name] + '.' + [so].[name] AS [TableName] ,
	[si].[index_id] AS [Index ID] ,
	[ss].[name] AS [Statistic] ,
	STUFF(( SELECT  ', ' + [c].[name]
		FROM    [sys].[stats_columns] [sc]
			JOIN [sys].[columns] [c] ON [c].[column_id] = [sc].[column_id]
						    AND [c].[object_id] = [sc].[OBJECT_ID]
		WHERE   [sc].[object_id] = [ss].[object_id]
			AND [sc].[stats_id] = [ss].[stats_id]
		ORDER BY [sc].[stats_column_id]
	      FOR
		XML PATH('')
	      ), 1, 2, '') AS [ColumnsInStatistic] ,
	[ss].[auto_Created] AS [WasAutoCreated] ,
	[ss].[user_created] AS [WasUserCreated] ,
	[ss].[has_filter] AS [IsFiltered] ,
	[ss].[filter_definition] AS [FilterDefinition] ,
	[ss].[is_temporary] AS [IsTemporary]
FROM [sys].[stats] [ss]
JOIN [sys].[objects] AS [so] ON [ss].[object_id] = [so].[object_id]
JOIN [sys].[schemas] AS [sch] ON [so].[schema_id] = [sch].[schema_id]
LEFT OUTER JOIN [sys].[indexes] AS [si] ON [so].[object_id] = [si].[object_id]
						AND [ss].[name] = [si].[name]
WHERE [so].[object_id] = OBJECT_ID(N'Sales.SalesOrderDetail')
ORDER BY [ss].[user_created] ,
	[ss].[auto_created] ,
	[ss].[has_filter];
GO



/*
	If you want to view information for a statistic, you have two options
*/
DBCC SHOW_STATISTICS ('Sales.SalesOrderDetail',IX_SalesOrderDetail_ProductID);
GO


/*
	DMF available only in SQL 2008R2SP2+ and SQL 2012SP1+
*/
SELECT  
	[sch].[name] + '.' + [so].[name] AS [TableName] ,
	[ss].[name] AS [Statistic] ,
	[ss].[auto_Created] AS [WasAutoCreated] ,
	[ss].[user_created] AS [WasUserCreated] ,
	[ss].[has_filter] AS [IsFiltered] ,
	[ss].[filter_definition] AS [FilterDefinition] ,
	[ss].[is_temporary] AS [IsTemporary] ,
	[sp].[last_updated] AS [StatsLastUpdated] ,
	[sp].[rows] AS [RowsInTable] ,
	[sp].[rows_sampled] AS [RowsSampled] ,
	[sp].[unfiltered_rows] AS [UnfilteredRows] ,
	[sp].[modification_counter] AS [RowModifications] ,
	[sp].[steps] AS [HistogramSteps]
FROM [sys].[stats] [ss]
	JOIN [sys].[objects] [so] ON [ss].[object_id] = [so].[object_id]
	JOIN [sys].[schemas] [sch] ON [so].[schema_id] = [sch].[schema_id]
	OUTER APPLY [sys].[dm_db_stats_properties]([so].[object_id],
						   [ss].[stats_id]) sp
WHERE   [so].[name] = 'SalesOrderDetail';
GO


/*
	Update statistics for one statistic
*/
UPDATE STATISTICS [Sales].[SalesOrderDetail] [IX_SalesOrderDetail_ProductID];
GO


/*
	Update all statistics for a table
*/
UPDATE STATISTICS [Sales].[SalesOrderDetail];
GO


/*
	Update all statistics for a table with a specific sample rate
*/
UPDATE STATISTICS [Sales].[SalesOrderDetail] WITH SAMPLE 10 PERCENT;
GO


/*
	Update all statistics for a table with a FULLSCAN
*/
UPDATE STATISTICS [Sales].[SalesOrderDetail] WITH FULLSCAN;
GO


/*
	Update all statistics in the database
	This is not a good option - it will update all stats
	as long as one (1) row has changed
*/
EXEC sp_updatestats;
GO


/*
	check all statistics, ordered by update date desc
*/
SELECT  [sch].[name] + '.' + [so].[name] AS [TableName] ,
	[ss].[name] AS [Statistic] ,
	[ss].[auto_Created] AS [WasAutoCreated] ,
	[ss].[user_created] AS [WasUserCreated] ,
	[ss].[has_filter] AS [IsFiltered] ,
	[ss].[filter_definition] AS [FilterDefinition] ,
	[ss].[is_temporary] AS [IsTemporary] ,
	[sp].[last_updated] AS [StatsLastUpdated] ,
	[sp].[rows] AS [RowsInTable] ,
	[sp].[rows_sampled] AS [RowsSampled] ,
	[sp].[unfiltered_rows] AS [UnfilteredRows] ,
	[sp].[modification_counter] AS [RowModifications] ,
	[sp].[steps] AS [HistogramSteps]
FROM    [sys].[stats] [ss]
	JOIN [sys].[objects] [so] ON [ss].[object_id] = [so].[object_id]
	JOIN [sys].[schemas] [sch] ON [so].[schema_id] = [sch].[schema_id]
	OUTER APPLY [sys].[dm_db_stats_properties]([so].[object_id],
						   [ss].[stats_id]) sp
WHERE   [so].[type] = 'U'
ORDER BY [sp].[last_updated] DESC;
GO


/*
	statistics with more than 10% change
*/
SELECT  [sch].[name] + '.' + [so].[name] AS [TableName] ,
	[ss].[name] AS [Statistic] ,
	[ss].[auto_Created] AS [WasAutoCreated] ,
	[ss].[user_created] AS [WasUserCreated] ,
	[ss].[has_filter] AS [IsFiltered] ,
	[ss].[filter_definition] AS [FilterDefinition] ,
	[ss].[is_temporary] AS [IsTemporary] ,
	[sp].[last_updated] AS [StatsLastUpdated] ,
	[sp].[rows] AS [RowsInTable] ,
	[sp].[rows_sampled] AS [RowsSampled] ,
	[sp].[unfiltered_rows] AS [UnfilteredRows] ,
	[sp].[modification_counter] AS [RowModifications] ,
	[sp].[steps] AS [HistogramSteps] ,
	CAST(100 * [sp].[modification_counter] / [sp].[rows] AS DECIMAL(18, 2)) AS [PercentChange]
FROM    [sys].[stats] [ss]
	JOIN [sys].[objects] [so] ON [ss].[object_id] = [so].[object_id]
	JOIN [sys].[schemas] [sch] ON [so].[schema_id] = [sch].[schema_id]
	OUTER APPLY [sys].[dm_db_stats_properties]([so].[object_id],
						   [ss].[stats_id]) sp
WHERE   [so].[type] = 'U'
	AND CAST(100 * [sp].[modification_counter] / [sp].[rows] AS DECIMAL(18, 2)) >= 10.00
ORDER BY CAST(100 * [sp].[modification_counter] / [sp].[rows] AS DECIMAL(18, 2)) DESC;
GO