SET NOCOUNT ON
DECLARE @FilePath VARCHAR(256) = 'C:\TEMP\INPUT.SQL',
@QueryOutPath VARCHAR(256) = 'c:\TEMP\OUTPUT.SQL',
@PrintSQL BIT = 0,
@SQL VARCHAR(MAX)
DECLARE @Cmd VARCHAR(256) = 'sqlcmd -S ' + @@SERVERNAME + ' -E -i ' + @FilePath + ' -o ' + @QueryOutPath + ' -d ' + DB_NAME()
SELECT @SQL = 'SET QUOTED_IDENTIFIER ON
SET ANSI_WARNINGS ON
SET STATISTICS IO ON
DECLARE @ID INT
' + Util.dbo.StringConcat('PRINT ''--' + FQN + ',' + IndexName + ',' + CAST(IndexID AS VARCHAR) + ',' + CAST([Partition] AS VARCHAR) + ','
+ CAST([Rows] AS VARCHAR) + '''
SELECT TOP 1 @id = 0 FROM ' + FQN + ' WITH (NOLOCK, INDEX = ' + IndexName + ')' + ISNULL(' WHERE 1 = 1 ' + NULLIF(ISNULL(' AND (' + PartitionFilter + ')', '')
+ ISNULL(' AND (' + IndexFilter + ')', ''),
''), ''), '
')
FROM (SELECT ROW_NUMBER() OVER (PARTITION BY objectid, IndexId ORDER BY ROWS DESC) AS RowId,
*
FROM Metadata.IndexDetail
WHERE ROWS > 0
--AND SchemaName = 'tTrans'
) k
WHERE RowId = 1
IF @FilePath <> ''
SELECT ReturnVal,
MESSAGE,
@FilePath AS FilePath,
'Saved to ' + @FilePath AS Info
FROM Util.FS.AppendAllTextToFile(@FilePath, @SQL, 1)
IF @PrintSQL = 1
EXEC Util.dbo.PrintLargeText
@SQL
--PRINT @Cmd
EXEC sys.xp_cmdshell
@Cmd,
no_output
IF OBJECT_ID('tempdb.dbo.#temp') IS NOT NULL
DROP TABLE #temp
IF OBJECT_ID('tempdb.dbo.#Result') IS NOT NULL
DROP TABLE #Result
SELECT b.FieldNum,
b.FIeld,
TableName,
RowType,
lr,
cr,
CAST(LogicalReadsStr AS INT) AS LogicalReads
INTO #temp
FROM (SELECT [Util].[FS].[ReadAllTextFromFile] (@QueryOutPath) AS INPUT) i
CROSS APPLY Util.dbo.ParseDelimited(INPUT, '
') b
CROSS APPLY (SELECT CASE WHEN LEFT (Field, 2) = '--' THEN 1 WHEN LEFT (Field, 7) = 'Table ''' THEN 2 END AS RowType) c
CROSS APPLY (SELECT CASE WHEN RowType = 1 THEN SUBSTRING(Field, 3, LEN(Field))
END AS TableName,
CASE WHEN RowType = 2 THEN CHARINDEX(', logical reads ', Field)
END AS lr) d
CROSS APPLY (SELECT CASE WHEN LR > 0 THEN CHARINDEX (',', Field, LR + 10) END AS cr) e
CROSS APPLY (SELECT CASE WHEN cr > 0 THEN SUBSTRING (Field, lr + 16, cr - 16- lr) END AS LogicalReadsStr) f
SELECT b.C1 AS FQN,
a.LogicalReads,
b.C2 AS IndexName,
CAST(b.C3 AS INT) AS IndexId,
CAST(b.C4 AS INT) AS PARTITION,
CAST(b.c5 AS INT) AS ROWS
INTO #Result
FROM #temp a
CROSS APPLY (SELECT TOP 1
TableName
FROM #temp b
WHERE b.RowType = 1
AND b.FieldNum < a.FieldNum
ORDER BY b.FieldNum DESC) c
CROSS APPLY Util.dbo.ParseDelimitedColumns8(c.TableName, ',') b
WHERE a.RowType = 2
ORDER BY LogicalReads DESC,
CAST(b.c5 AS INT) DESC
SELECT *
FROM #Result
GO