/*============================================================================
  File:     5b_BaseballStatistics.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.
============================================================================*/
USE [master];
GO

IF EXISTS (SELECT * FROM [sys].[databases] WHERE [name] = 'BaseballData')
	DROP DATABASE [BaseballData];

RESTORE DATABASE [BaseballData] 
	FROM  DISK = N'C:\Backups\BaseballData.bak' 
	WITH  FILE = 1,  
	MOVE N'Baseball' TO N'C:\Databases\BaseballData\BaseballData.mdf',  
	MOVE N'Baseball_log' TO N'C:\Databases\BaseballData\BaseballData_log.ldf',  
	REPLACE,  
	STATS = 5;
GO

USE [BaseballData];
GO

ALTER DATABASE [BaseballData] SET AUTO_CREATE_STATISTICS ON;
GO
ALTER DATABASE [BaseballData] SET AUTO_UPDATE_STATISTICS ON;
GO

IF EXISTS (SELECT * FROM sys.tables WHERE name = 'PlayerInfo')
	DROP TABLE [dbo].[PlayerInfo];

/* 
	create dbo.PlayerInfo as a COPY of dbo.players 
*/
SELECT TOP 0 * 
	INTO [dbo].[PlayerInfo] 
	FROM [dbo].[players];

/*
	create a clustered and non-clustered index for the table
*/
CREATE UNIQUE CLUSTERED INDEX [CI_PlayerInfo_ID] 
	ON [dbo].[PlayerInfo]([lahmanID]);
CREATE NONCLUSTERED INDEX [IX_PlayerInfo_nameLast_nameFirst] 
	ON [dbo].[PlayerInfo]([nameLast],[nameFirst]);


/*
	manually create a column statistic
*/
CREATE STATISTICS [BirthState] 
	ON [dbo].[PlayerInfo] ([birthState])
	WITH FULLSCAN;


/*
	manually create a filtered statistic
*/
CREATE STATISTICS ActivePlayers ON [dbo].[PlayerInfo] ([finalGame]) 
	WHERE [finalGame] IS NULL
	WITH FULLSCAN;


/*
	load some data
*/
INSERT INTO [dbo].[PlayerInfo]
	(lahmanID, playerID, managerID, hofID, birthYear, birthMonth, birthDay, birthCountry, birthState, birthCity, deathYear, deathMonth, deathDay, deathCountry, deathState, deathCity, nameFirst, nameLast, nameNote, nameGiven, nameNick, weight, height, bats, throws, debut, finalGame, college, lahman40ID, lahman45ID, retroID, holtzID, bbrefID)
	SELECT
		lahmanID, playerID, managerID, hofID, birthYear, birthMonth, birthDay, birthCountry, birthState, birthCity, deathYear, deathMonth, deathDay, deathCountry, deathState, deathCity, nameFirst, nameLast, nameNote, nameGiven, nameNick, weight, height, bats, throws, debut, finalGame, college, lahman40ID, lahman45ID, retroID, holtzID, bbrefID
	FROM [dbo].[players]
	WHERE [lahmanID] <= 12000;
GO

/*
	check statistics 
*/
DBCC SHOW_STATISTICS (N'dbo.PlayerInfo', [CI_PlayerInfo_ID]) WITH STAT_HEADER;
GO

DBCC SHOW_STATISTICS (N'dbo.PlayerInfo', [IX_PlayerInfo_nameLast_nameFirst]) WITH STAT_HEADER;
GO

DBCC SHOW_STATISTICS (N'dbo.PlayerInfo', [ActivePlayers] ) WITH STAT_HEADER;
GO

DBCC SHOW_STATISTICS (N'dbo.PlayerInfo', [BirthState] ) WITH STAT_HEADER;
GO

/*
	rebuild clustered index on the table
*/
ALTER INDEX [CI_PlayerInfo_ID] ON [dbo].[PlayerInfo] REBUILD;


/*
	check statistics again - stats get updated for the CI only
*/
DBCC SHOW_STATISTICS (N'dbo.PlayerInfo', [CI_PlayerInfo_ID]) WITH STAT_HEADER;
GO

DBCC SHOW_STATISTICS (N'dbo.PlayerInfo', [IX_PlayerInfo_nameLast_nameFirst]) WITH STAT_HEADER;
GO

DBCC SHOW_STATISTICS (N'dbo.PlayerInfo', [ActivePlayers] ) WITH STAT_HEADER;
GO

DBCC SHOW_STATISTICS (N'dbo.PlayerInfo', [BirthState] ) WITH STAT_HEADER;
GO


/*
	reorganize the nonclustered index 
*/
ALTER INDEX [IX_PlayerInfo_nameLast_nameFirst] ON [dbo].[PlayerInfo] REORGANIZE;


/*
	check statistics again - statistics for the NCI are NOT updated
*/
DBCC SHOW_STATISTICS (N'dbo.PlayerInfo', [CI_PlayerInfo_ID]) WITH STAT_HEADER;
GO

DBCC SHOW_STATISTICS (N'dbo.PlayerInfo', [IX_PlayerInfo_nameLast_nameFirst]) WITH STAT_HEADER;
GO

DBCC SHOW_STATISTICS (N'dbo.PlayerInfo', [ActivePlayers] ) WITH STAT_HEADER;
GO

DBCC SHOW_STATISTICS (N'dbo.PlayerInfo', [BirthState] ) WITH STAT_HEADER;
GO



/*
	rebuild ALL indexes 
*/
ALTER INDEX ALL ON [dbo].[PlayerInfo] REBUILD;


/*
	check statistics again - column stats not updated (even if rebuild all indexes)
*/
DBCC SHOW_STATISTICS (N'dbo.PlayerInfo', [CI_PlayerInfo_ID]) WITH STAT_HEADER;
GO

DBCC SHOW_STATISTICS (N'dbo.PlayerInfo', [IX_PlayerInfo_nameLast_nameFirst]) WITH STAT_HEADER;
GO

DBCC SHOW_STATISTICS (N'dbo.PlayerInfo', [ActivePlayers] ) WITH STAT_HEADER;
GO

DBCC SHOW_STATISTICS (N'dbo.PlayerInfo', [BirthState] ) WITH STAT_HEADER;
GO


/*
	update stats manually
*/
UPDATE STATISTICS [dbo].[PlayerInfo] [ActivePlayers];


/*
	check statistics 
*/
DBCC SHOW_STATISTICS (N'dbo.PlayerInfo', [ActivePlayers] ) WITH STAT_HEADER;
GO


/*
	update stats manually with a specific percentage
*/
UPDATE STATISTICS [dbo].[PlayerInfo] [ActivePlayers] WITH SAMPLE 10 PERCENT;


/*
	check statistics 
*/
DBCC SHOW_STATISTICS (N'dbo.PlayerInfo', [ActivePlayers] ) WITH STAT_HEADER;
GO


/*
	let's try for a larger table...
*/
DBCC SHOW_STATISTICS (N'dbo.batting', [PK__batting__4633C1B78430B3C8] ) WITH STAT_HEADER;
GO

/*
	update stats manually
*/
UPDATE STATISTICS [dbo].[batting] [PK__batting__4633C1B78430B3C8] WITH SAMPLE 10 PERCENT;


/*
	check stats again
*/
DBCC SHOW_STATISTICS (N'dbo.batting', [PK__batting__4633C1B78430B3C8] ) WITH STAT_HEADER;
GO


/*
	enable execution plan
	ensure auto-update stats enabled
	clear cache
*/

DBCC FREEPROCCACHE;

ALTER DATABASE [BaseballData] SET AUTO_UPDATE_STATISTICS ON;
GO

/*
	verify that no stats info exists
*/
DBCC SHOW_STATISTICS (N'dbo.PlayerInfo', [BirthState] ) WITH STAT_HEADER;
GO

/*
	run the query
*/
SELECT 
	[nameFirst], 
	[nameLast]
FROM [dbo].[PlayerInfo]
WHERE [BirthState] = 'MI';


/*
	check stats again
*/
DBCC SHOW_STATISTICS (N'dbo.PlayerInfo', [BirthState] ) WITH STAT_HEADER;
GO


/*
	how does the engine know when statistics are out of date?
*/
SELECT  
	[sch].[name] + '.' + [so].[name] [TableName],
	[si].[index_id] [Index ID],
	[ss].[name] [Statistic],
	[sp].[last_updated] AS [StatsLastUpdated],
	[sp].[rows] AS [RowsInTable] ,
	[sp].[rows_sampled] AS [RowsSampled],
	[sp].[unfiltered_rows] AS [UnfilteredRows],
	[sp].[modification_counter] AS [RowModifications]
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]
LEFT OUTER JOIN [sys].[indexes] [si] 
	ON [so].[object_id] = [si].[object_id]
	AND [ss].[name] = [si].[name]
OUTER APPLY [sys].[dm_db_stats_properties]([so].[object_id],[ss].[stats_id]) sp
WHERE [so].[object_id] = OBJECT_ID(N'dbo.PlayerInfo')
ORDER BY [ss].[user_created], [ss].[auto_created], [ss].[has_filter];
GO


/*
	Let's run an update that would use our [IX_PlayerInfo_nameLast_nameFirst] index
*/
UPDATE [dbo].[PlayerInfo] SET namefirst = 'Theodore' WHERE namefirst = 'Ted' AND namelast = 'Lyons'
OPTION
(
	QUERYTRACEON 3604,
	QUERYTRACEON 9204
)

/*
	If you want to play with these trace flags, read more here (but be wary of production):
	http://sqlblog.com/blogs/paul_white/archive/2011/09/21/how-to-find-the-statistics-used-to-compile-an-execution-plan.aspx
*/


/*
	load in more data
*/
INSERT INTO [dbo].[PlayerInfo]
	SELECT *
	FROM [dbo].[players]
	WHERE [lahmanID] > 12000;
GO

/*
	check stats again...nothing updated, but we see the modification counter has changed
*/
SELECT  
	[sch].[name] + '.' + [so].[name] [TableName],
	[si].[index_id] [Index ID],
	[ss].[name] [Statistic],
	[sp].[last_updated] AS [StatsLastUpdated],
	[sp].[rows] AS [RowsInTable] ,
	[sp].[rows_sampled] AS [RowsSampled],
	[sp].[unfiltered_rows] AS [UnfilteredRows],
	[sp].[modification_counter] AS [RowModifications]
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]
LEFT OUTER JOIN [sys].[indexes] [si] 
	ON [so].[object_id] = [si].[object_id]
	AND [ss].[name] = [si].[name]
OUTER APPLY [sys].[dm_db_stats_properties]([so].[object_id],[ss].[stats_id]) sp
WHERE [so].[object_id] = OBJECT_ID(N'dbo.PlayerInfo')
ORDER BY [ss].[user_created], [ss].[auto_created], [ss].[has_filter];
GO


/*
	re-run our statement
*/
UPDATE [dbo].[PlayerInfo] SET namefirst = 'Theodore' WHERE namefirst = 'Ted' AND namelast = 'Williams'
OPTION
(
	QUERYTRACEON 3604,
	QUERYTRACEON 9204
)

/*
	check stats again...they've updated
*/
SELECT  
	[sch].[name] + '.' + [so].[name] [TableName],
	[si].[index_id] [Index ID],
	[ss].[name] [Statistic],
	[sp].[last_updated] AS [StatsLastUpdated],
	[sp].[rows] AS [RowsInTable] ,
	[sp].[rows_sampled] AS [RowsSampled],
	[sp].[unfiltered_rows] AS [UnfilteredRows],
	[sp].[modification_counter] AS [RowModifications]
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]
LEFT OUTER JOIN [sys].[indexes] [si] 
	ON [so].[object_id] = [si].[object_id]
	AND [ss].[name] = [si].[name]
OUTER APPLY [sys].[dm_db_stats_properties]([so].[object_id],[ss].[stats_id]) sp
WHERE [so].[object_id] = OBJECT_ID(N'dbo.PlayerInfo')
ORDER BY [ss].[user_created], [ss].[auto_created], [ss].[has_filter];
GO


/*
	What about sp_updatestats?
*/
SELECT  
	[sch].[name] + '.' + [so].[name] [TableName],
	[si].[index_id] [Index ID],
	[ss].[name] [Statistic],
	[sp].[last_updated] AS [StatsLastUpdated],
	[sp].[rows] AS [RowsInTable] ,
	[sp].[rows_sampled] AS [RowsSampled],
	[sp].[unfiltered_rows] AS [UnfilteredRows],
	[sp].[modification_counter] AS [RowModifications]
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]
LEFT OUTER JOIN [sys].[indexes] [si] 
	ON [so].[object_id] = [si].[object_id]
	AND [ss].[name] = [si].[name]
OUTER APPLY [sys].[dm_db_stats_properties]([so].[object_id],[ss].[stats_id]) sp
WHERE [so].[object_id] = OBJECT_ID(N'dbo.PlayerInfo')
ORDER BY [ss].[user_created], [ss].[auto_created], [ss].[has_filter];
GO


/*
	run it :(
	TURN OFF execution plan!
*/
EXEC sp_updatestats

/*
	Check one last time...
*/
SELECT  
	[sch].[name] + '.' + [so].[name] [TableName],
	[si].[index_id] [Index ID],
	[ss].[name] [Statistic],
	[sp].[last_updated] AS [StatsLastUpdated],
	[sp].[rows] AS [RowsInTable] ,
	[sp].[rows_sampled] AS [RowsSampled],
	[sp].[unfiltered_rows] AS [UnfilteredRows],
	[sp].[modification_counter] AS [RowModifications]
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]
LEFT OUTER JOIN [sys].[indexes] [si] 
	ON [so].[object_id] = [si].[object_id]
	AND [ss].[name] = [si].[name]
OUTER APPLY [sys].[dm_db_stats_properties]([so].[object_id],[ss].[stats_id]) sp
WHERE [so].[object_id] = OBJECT_ID(N'dbo.PlayerInfo')
ORDER BY [ss].[user_created], [ss].[auto_created], [ss].[has_filter];
GO
