/*============================================================================
  File:     3a_tempdb_Configuration_Contention_files.sql

  Summary:  This script reviews how to view tempdb configuration,
			alter existing files and add new files to alleviate
			contention.

  Date:     April 2018

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

  For more scripts and other useful content, go to:
	http://www.SQLskills.com

  This script is intended only as a supplement to demos and lectures
  given by Erin Stellato.  
  
  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 [tempdb];
GO

SELECT 
	[file_id],	
	[type],	
	[type_desc],	
	[name],
	[physical_name],
	[size] [Size In Pages],
	[size]*8 [Size in KB],
	[max_size],	
	[growth],
	[is_percent_growth]
FROM [sys].[database_files];



USE [master];
GO

ALTER DATABASE [tempdb] MODIFY FILE ( 
	NAME = N'tempdev', 
	SIZE = 1024MB , 
	FILEGROWTH = 512MB);
GO

ALTER DATABASE [tempdb] MODIFY FILE ( 
	NAME = N'templog', 
	SIZE = 512MB , 
	FILEGROWTH = 256MB);
GO


/*
	list all trace flags enabled 
*/ 
DBCC TRACESTATUS(-1); 
GO 

/*
	enable TF 1118 globally
	note that this will only persist until next restart 
	enable this through SQL Server Configuration Manager, not DBCC TRACEON
*/
DBCC TRACEON (1118, -1);
GO

/*
	In 2016+, this setting is controlled through 
	the SET MIXED_PAGE_ALLOCATION database option 
*/
SELECT 
	[name], 
	[is_mixed_page_allocation_on]
FROM [tempdb].[sys].[databases];
GO


/*
	run 3b_tempdb_Configuration_Contention_setup.sql
	and 3c_tempdb_Configuration_Contention_setup.sql
	to setup and see performance issue
*/

/*
	add additional files to alleviate contention AFTER 
	the performance issue has been created
*/

USE [master]
GO
ALTER DATABASE [tempdb] ADD FILE ( 
	NAME = N'tempdev2', 
	FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL14.SQL2017\MSSQL\DATA\tempdev2.ndf' , 
	SIZE = 1024MB , 
	FILEGROWTH = 512MB);
GO

ALTER DATABASE [tempdb] ADD FILE ( 
	NAME = N'tempdev3', 
	FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL14.SQL2017\MSSQL\DATA\tempdev3.ndf' , 
	SIZE = 1024MB , 
	FILEGROWTH = 512MB);
GO

ALTER DATABASE [tempdb] ADD FILE ( 
	NAME = N'tempdev4', 
	FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL14.SQL2017\MSSQL\DATA\tempdev4.ndf' , 
	SIZE = 1024MB , 
	FILEGROWTH = 512MB);
GO


/*
	re-check sizes
*/
USE [tempdb];
GO

SELECT 
	[file_id],	
	[type],	
	[type_desc],	
	[name],
	[physical_name],
	[size] [Size In Pages],
	[size]*8 [Size in KB],
	[max_size],	
	[growth],
	[is_percent_growth]
FROM [sys].[database_files];