/*============================================================================
  File:     1b_ExamineFragmentation.sql

  Summary:  This script examines fragmentation in a database.

  Date:     October 2014

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

  (c) 2014, 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.
============================================================================*/

/*
	Review of the physical stats DMV
*/

sys.dm_db_index_physical_stats ( 
    { database_id | NULL | 0 | DEFAULT }
  , { object_id | NULL | 0 | DEFAULT }
  , { index_id | NULL | 0 | -1 | DEFAULT }
  , { partition_number | NULL | 0 | DEFAULT }
  , { mode | NULL | DEFAULT }
)


USE [AdventureWorks2014];
GO


/*
	Checking indexes for a table
*/
sp_helpindex 'Sales.SalesOrderDetail';
GO

/*
	link to Kimberly's helpindex category:
	http://www.sqlskills.com/blogs/kimberly/category/sp_helpindex-rewrites/
*/
sp_SQLskills_SQL2012_helpindex 'Sales.SalesOrderDetail';
GO


/*
	Running physical stats for an index - DETAILED mode
*/
SELECT *
FROM [sys].[dm_db_index_physical_stats](DB_ID(N'AdventureWorks2014'),OBJECT_ID(N'Sales.SalesOrderDetail'),1, NULL, 'Detailed');

SELECT *
FROM [sys].[dm_db_index_physical_stats](DB_ID(N'AdventureWorks2014'),OBJECT_ID(N'Sales.SalesOrderDetail'),3, NULL, 'Detailed');

/*
	Running physical stats for an index - LIMITED mode
*/
SELECT *
FROM [sys].[dm_db_index_physical_stats](DB_ID(N'AdventureWorks2014'),OBJECT_ID(N'Sales.SalesOrderDetail'),1, NULL, 'Limited');

SELECT *
FROM [sys].[dm_db_index_physical_stats](DB_ID(N'AdventureWorks2014'),OBJECT_ID(N'Sales.SalesOrderDetail'),3, NULL, 'Limited');



/*
	Get fragmentation for the entire database
	**It's not recommended to run this script against a Production 
	database, unless you change the mode to 'Limited'**
*/
SELECT 
	[so].[name], 
	[si].[name], 
	[si].[index_id], 
	[ps].[index_type_desc], 
	[ps].[index_depth], 
	[ps].[index_level], 
	[ps].[alloc_unit_type_desc],
	[ps].[avg_fragmentation_in_percent], 
	[ps].[avg_page_space_used_in_percent], 
	[ps].[record_count], 
	[ps].[forwarded_record_count]
FROM [sys].[dm_db_index_physical_stats](DB_ID(N'AdventureWorks2014'), NULL, NULL, NULL, 'Detailed') [ps]
JOIN [sys].[objects] [so] on [ps].[object_id] = [so].[object_id]
JOIN [sys].[indexes] [si] on [ps].[object_id] = [si].[object_id] AND [ps].[index_id] = [si].[index_id]
ORDER BY [so].[name], [si].[index_id], [ps].[index_level];


/*
	further review with a *really* fragmented index
	review 1a_CreateFragmentation script first
*/

USE [GUIDTest];
GO

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

