/*============================================================================
  File:     2_RemoveFragmentation.sql

  Summary:  This script shows how to remove fragmentation

  Date:     April 2018

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

  (c) 2018, 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 GUIDTest;
GO

/*
	Look at the fragmentation again
*/
SELECT
	OBJECT_NAME ([ps].[object_id]) AS 'Object Name',
	[si].[name] AS 'Index Name',
	ROUND ([ps].[avg_fragmentation_in_percent], 2) AS 'Fragmentation',
	[ps].[page_count] AS 'Pages',
	ROUND ([ps].[avg_page_space_used_in_percent], 2) AS 'Page Density'
FROM [sys].[dm_db_index_physical_stats] (DB_ID ('GUIDTest'),NULL,NULL,NULL,'DETAILED') [ps]
CROSS APPLY [sys].[indexes] [si]
WHERE
	[si].[object_id] = [ps].[object_id]
	AND [si].[index_id] = [ps].[index_id]
	AND [ps].[index_level] = 0;
GO


/*
	Rebuild the clustered index online
*/
USE GUIDTest;
GO

ALTER INDEX [BadKeyTable_CL]
	ON [dbo].[BadKeyTable] REBUILD
	WITH (ONLINE = ON, FILLFACTOR = 70);
GO


/*
	Reorganize the non-clustered index
*/
ALTER INDEX [BadKeyTable_NCL]
	ON [dbo].[BadKeyTable] REORGANIZE;
GO


/*
	Look at the fragmentation again
*/
SELECT
	OBJECT_NAME ([ps].[object_id]) AS 'Object Name',
	[si].[name] AS 'Index Name',
	ROUND ([ps].[avg_fragmentation_in_percent], 2) AS 'Fragmentation',
	[ps].[page_count] AS 'Pages',
	ROUND ([ps].[avg_page_space_used_in_percent], 2) AS 'Page Density'
FROM [sys].[dm_db_index_physical_stats] (DB_ID ('GUIDTest'),NULL,NULL,NULL,'DETAILED') [ps]
CROSS APPLY [sys].[indexes] [si]
WHERE
	[si].[object_id] = [ps].[object_id]
	AND [si].[index_id] = [ps].[index_id]
	AND [ps].[index_level] = 0;
GO

/*
	Now rebuild the non-clustered index
*/
ALTER INDEX [BadKeyTable_NCL]
	ON [dbo].[BadKeyTable]  REBUILD
	WITH (ONLINE = ON, FILLFACTOR = 70);
GO


/*
	Look at the fragmentation again
*/
SELECT
	OBJECT_NAME ([ps].[object_id]) AS 'Object Name',
	[si].[name] AS 'Index Name',
	ROUND ([ps].[avg_fragmentation_in_percent], 2) AS 'Fragmentation',
	[ps].[page_count] AS 'Pages',
	ROUND ([ps].[avg_page_space_used_in_percent], 2) AS 'Page Density'
FROM [sys].[dm_db_index_physical_stats] (DB_ID ('GUIDTest'),NULL,NULL,NULL,'DETAILED') [ps]
CROSS APPLY [sys].[indexes] [si]
WHERE
	[si].[object_id] = [ps].[object_id]
	AND [si].[index_id] = [ps].[index_id]
	AND [ps].[index_level] = 0;
GO


/*
	Do not drop the GUIDTest database yet...need it for the next demo :)
*/