--- Please follow the below comments to insert SQL statements.
-- Client version query
--------- BEGIN - GENERATED CODE, PLEASE DO NOT MODIFY ---------

SET NOCOUNT ON

DECLARE @LogDate AS BIGINT = dbo.GetUnixTime(GETUTCDATE())
DECLARE @queryId AS INTEGER = 144
DECLARE @surveyXML NVARCHAR(MAX)

--------- END - GENERATED CODE ---------



--------- BEGIN SURVEY QUERY ---------

--------- Insert your SQL statements here 
-- TODO need a value for each db: CommServ (182), DM2, WFEngine
DECLARE @DefOperationalIndexNumber INT = 182;		-- default setting
DECLARE @CSOperationalIndexNumber INT = 182;
DECLARE @DM2OperationalIndexNumber INT = 20;
DECLARE @WFEOperationalIndexNumber INT = 30;
DECLARE @CVCOperationalIndexNumber INT = 97;

DECLARE @MiminalNumberOfPages INT = 5000		--  Minimal setting is 500 pages
DECLARE @AvgFragSizeInPages FLOAT = 3.0		-- fragmentation level increases as it approaches 1.0
DECLARE @IncludeFragDetails TINYINT = 1		-- If 1, compute index fragmentation Details XML Node
DECLARE @FragDetailsType	TINYINT = 2		-- 1: FULL database index fragmentation Report, 2: SCOPE limited to index fragmentation matching set fragmentation criteria node

DECLARE @LastFullReindexOperation  VARCHAR(15)
DECLARE @LastProactiveReindex INT 

SELECT @LastFullReindexOperation=value  from gxglobalparam where name ='FullDBMaintenanceTime'
SELECT @LastProactiveReindex=dbo.GetUnixTime(max(start_execution_date))  from msdb.dbo.sysjobs s join  msdb.dbo.sysjobactivity ja on ja.job_id =s.job_id  WHERE NAME='CSProactiveDBMaintainanceJob'

IF OBJECT_ID('tempdb.dbo.#FragStats') IS NOT NULL 
	DROP TABLE #FragStats

CREATE TABLE #FragStats (
	dbId				INT,
	name				SYSNAME,
	indexNumber			INT,
	totalIndexes		INT,
	PRIMARY KEY (dbId)
)

IF OBJECT_ID('tempdb.dbo.#TblFragmentation') IS NOT NULL 
	DROP TABLE #TblFragmentation

CREATE TABLE #TblFragmentation (
	number			INT,
	dbId			INT,
	dbname			SYSNAME,
	information		SYSNAME,
	fragPctWindow	INT, 
	indexCount		INT,
	PRIMARY KEY (dbId, number)
)

IF OBJECT_ID('tempdb.dbo.#TblFragmentationDetails') IS NOT NULL
	DROP TABLE #TblFragmentationDetails

CREATE TABLE #TblFragmentationDetails (
	dbId			INT,
	DatabaseName	SYSNAME,
	tableId			INT,
	TableName		SYSNAME,
	IndexId			INT,
	IndexName		SYSNAME, 
	IndexType		INT,
	[fillFactor]	INT,
	[rowCount]		INT,
	AvgFragPercent	FLOAT(2),
	pageCount		INT,
	avgFragSizePageCount	INT,
	pageSplitForIndex		INT,
	pageAllocByPageSplit	INT,
	PRIMARY KEY (dbId, tableId, IndexName)
)

DECLARE @sqlCmd NVARCHAR(MAX) = N'

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

DECLARE @DefOperationalIndexNumber INT = ' + CAST(@DefOperationalIndexNumber AS NVARCHAR(12)) + N';
DECLARE @CSOperationalIndexNumber INT = ' + CAST(@CSOperationalIndexNumber AS NVARCHAR(12)) + N';
DECLARE @DM2OperationalIndexNumber INT = ' + CAST(@DM2OperationalIndexNumber AS NVARCHAR(12)) + N';
DECLARE @WFEOperationalIndexNumber INT = ' + CAST(@WFEOperationalIndexNumber AS NVARCHAR(12)) + N';
DECLARE @CVCOperationalIndexNumber INT = ' + CAST(@CVCOperationalIndexNumber AS NVARCHAR(12)) + N';

DECLARE @OperationalIndexNumber INT = (SELECT
											CASE 
												WHEN DB_NAME() LIKE ''commserv'' THEN @CSOperationalIndexNumber
												WHEN DB_NAME() LIKE ''DM2'' THEN @DM2OperationalIndexNumber
												WHEN DB_NAME() LIKE ''WFEngine'' THEN @WFEOperationalIndexNumber
												WHEN DB_NAME() LIKE ''CVCloud'' THEN @CVCOperationalIndexNumber
												ELSE @DefOperationalIndexNumber
											END)

INSERT INTO #FragStats
	SELECT 
		DB_ID(),
		DB_NAME(),
		@OperationalIndexNumber,
		(
			SELECT
				COUNT(*) indexCount
			FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) S
			INNER JOIN sys.indexes I ON I.object_id = S.object_id
				AND I.index_id = S.index_id
				AND I.type > 0								-- no HEAP Tables
		)

'

SET @sqlCmd += N'
DECLARE @MiminalNumberOfPages INT = ' + CAST(@MiminalNumberOfPages AS NVARCHAR(12)) + N';
DECLARE @AvgFragSizeInPages INT = ' + CAST(@AvgFragSizeInPages AS NVARCHAR(12)) + N';
DECLARE @IncludeFragDetails TINYINT = ' + CAST(@IncludeFragDetails AS NVARCHAR(12)) + N'
DECLARE @FragDetailsType TINYINT = ' + CAST(@FragDetailsType AS NVARCHAR(12)) + N'

IF (@IncludeFragDetails = 1)
BEGIN
	IF (@FragDetailsType = 2)
	BEGIN
		INSERT INTO #TblFragmentationDetails
			SELECT 
				DB_ID(),
				DB_NAME(),
				idx.OBJECT_ID,
				OBJECT_NAME(idx.OBJECT_ID),
				idx.index_id,
				idx.NAME,
				idx.type,
				idx.fill_factor,
				p.rows,
				ROUND(ips.avg_fragmentation_in_percent, 2),
				ips.page_count,
				ips.fragment_count,
				ios.leaf_allocation_count,
				ios.nonleaf_allocation_count
			FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) ips
				INNER JOIN sys.indexes idx ON 
					idx.object_id = ips.object_id
					AND idx.index_id = ips.index_id
					AND idx.type > 0
					AND ips.avg_fragmentation_in_percent >= 30.0
					AND ips.page_count >= @MiminalNumberOfPages						-- minimal number of a pages in the index.  Minimal setting is 100 pages 
					AND ips.avg_fragment_size_in_pages <= @AvgFragSizeInPages		-- fragmentation level per page increasing as it approaches 1
				INNER JOIN sys.partitions p ON 
					p.object_id = idx.object_id
					AND p.index_id = idx.index_id
				INNER JOIN  sys.dm_db_index_operational_stats(DB_ID(), NULL, NULL, NULL) ios ON
					ios.object_id = ips.object_id
					AND ios.index_id = idx.index_id
			WHERE
				ips.fragment_count IS NOT NULL
	END
	ELSE
	BEGIN
		INSERT INTO #TblFragmentationDetails
			SELECT 
				DB_ID(),
				DB_NAME(),
				idx.OBJECT_ID,
				OBJECT_NAME(idx.OBJECT_ID),
				idx.index_id,
				idx.NAME,
				idx.type,
				idx.fill_factor,
				p.rows,
				ROUND(ips.avg_fragmentation_in_percent, 2),
				ips.page_count,
				ips.fragment_count,
				ios.leaf_allocation_count,
				ios.nonleaf_allocation_count
			FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) ips
				INNER JOIN sys.indexes idx ON 
					idx.object_id = ips.object_id
					AND idx.index_id = ips.index_id
					AND idx.type > 0
					AND ips.avg_fragmentation_in_percent >= 30.0
				INNER JOIN sys.partitions p ON 
					p.object_id = idx.object_id
					AND p.index_id = idx.index_id
				INNER JOIN  sys.dm_db_index_operational_stats(DB_ID(), NULL, NULL, NULL) ios ON
					ios.object_id = ips.object_id
					AND ios.index_id = idx.index_id
			WHERE
				ips.fragment_count IS NOT NULL
	END
END

;WITH fragWindowStats AS (
	SELECT
		ROW_NUMBER() over (ORDER BY CAST(avg_fragmentation_in_percent / 10 AS INT)) number,
		DB_ID() dbId,
		DB_NAME() dbName,
		CAST(avg_fragmentation_in_percent / 10 AS INT) fragPctWindow,
		COUNT(*) indexCount
	FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) S
	INNER JOIN sys.indexes I ON I.object_id = S.object_id
		AND I.index_id = S.index_id
		AND I.type > 0								-- no HEAP Tables
		AND S.page_count >= @MiminalNumberOfPages	-- minimal number of a pages in the index.  Minimal setting is 100 pages 
		AND S.avg_fragment_size_in_pages <= @AvgFragSizeInPages		-- fragmentation level per page increasing as it approaches 1
	GROUP BY CAST(avg_fragmentation_in_percent / 10 AS INT)
	
)
INSERT INTO #TblFragmentation (
	number,
	dbId,
	dbName,
	information,
	fragPctWindow,
	indexCount 
)
	SELECT
		number,
		dbId,
		dbName,
		CASE number
			WHEN 1 THEN dbName
			ELSE ''''
		END information,
		fragPctWindow,
		indexCount
	FROM fragWindowStats;
'

IF DB_ID('CommServ') IS NOT NULL
BEGIN
	EXEC('USE CommServ; ' + @sqlCmd)
END

IF DB_ID('WFEngine') IS NOT NULL
BEGIN
	EXEC('USE WFEngine; ' + @sqlCmd)
END

--IF DB_ID('DM2') IS NOT NULL
--BEGIN
--	EXEC('USE DM2; ' + @sqlCmd)
--END

IF DB_ID('CVCloud') IS NOT NULL
BEGIN
	EXEC('USE CVCloud; ' + @sqlCmd)
END

INSERT INTO #TblFragmentation
	SELECT 101, dbId, name, 'Total Indexes in DB', 101, totalIndexes FROM #FragStats
	UNION ALL
	SELECT 102, dbId, name, 'Total Operational Indexes in DB', 102, indexNumber FROM #FragStats

SET @surveyXML = CAST(
(
	SELECT
		(
			SELECT
				@LastFullReindexOperation '@LastFullReindexOperation',
				@LastProactiveReindex '@LastProactiveReindex' 
			FOR XML PATH('FragmentationOverview'), TYPE
		),
		(
			SELECT
				@@SERVERNAME '@serverName',
				dbId '@dbid',
				dbname '@name',
				CASE WHEN rp.number >= 0 AND rp.number <= 10 THEN (rp.fragPctWindow  * 10) ELSE rp.fragPctWindow END '@fragmentationPct',
				rp.indexCount '@indexCount'
			FROM #TblFragmentation rp
			FOR XML PATH('Fragmentation'), TYPE
		),
		(
			SELECT
				DatabaseName '@dbname',
				TableId '@tableid',
				TableName '@tablename',
				[rowCount] '@rowCount', --should be max for the table
				IndexId '@indexid',
				IndexName '@indexname',
				IndexType '@indextype',
				[fillFactor] '@fillFactor',
				CAST(AvgFragPercent AS DECIMAL(6,1)) '@avgFragPercent',
				[pageCount] '@pageCount',
				avgFragSizePageCount '@avgFragSizePageCount',
				pageSplitForIndex '@pageSplitForIndex',
				pageAllocByPageSplit '@pageAllocByPageSplit'
			FROM #TblFragmentationDetails t
			FOR XML PATH('FragmentedIndex'), TYPE
		)
	FOR XML PATH(''), ROOT('IndexFragmentation'), TYPE
)
AS NVARCHAR(MAX))
--select * from #TblFragmentation
IF OBJECT_ID('tempdb.dbo.#TblFragmentationDetails') IS NOT NULL
	DROP TABLE #TblFragmentationDetails

IF OBJECT_ID('tempdb.dbo.#TblFragmentation') IS NOT NULL 
	DROP TABLE #TblFragmentation

IF OBJECT_ID('tempdb.dbo.#FragStats') IS NOT NULL 
	DROP TABLE #FragStats
--------- END SURVEY QUERY ---------

--------- BEGIN - GENERATED CODE, PLEASE DO NOT MODIFY ---------

DECLARE @EndTime AS BIGINT = dbo.GetUnixTime(GETUTCDATE())
SET @outputXML = 
(
	SELECT
		@queryId AS '@QueryId',
		@EndTime AS '@LogDate',
		(@EndTime - @LogDate) AS '@QueryRunningTime',
		@surveyXML
	FOR XML PATH('Rpt_CSSXMLDATA')
)
SET NOCOUNT OFF


--------- END - GENERATED CODE ---------