SET NOCOUNT ON
DECLARE @SQL VARCHAR(MAX),
@PrintSQL BIT = 0,
@ExecSQL BIT = 1
SELECT @SQL = 'IF OBJECT_ID(''tempdb..#Stats'') IS NOT NULL DROP TABLE #Stats
CREATE TABLE #Stats (Id INT, TableName VARCHAR(256), ScanCount INT, LogicalReads INT, PhysicalReads INT, ReadAheadReads INT, LobLogicalReads INT, LobPhysicalReads INT, LobReadAheadReads INT, Tag VARCHAR(256))
' + Util.dbo.StringConcat('EXEC Util.dbo.GetSQLStatisticsIO
@Database = ''' + DB_NAME() + '''
,@SQL = ''DECLARE @ID INT SELECT TOP 1 @id = 0 FROM ' + FQN + ' WITH (NOLOCK, INDEX = ' + IndexName + ')' + ISNULL(' WHERE 1 = 1 '
+ NULLIF(ISNULL(' AND ('
+ REPLACE(PartitionFilter,
'''', '''''')
+ ')', '')
+ ISNULL(' AND ('
+ REPLACE(IndexFilter,
'''', '''''')
+ ')', ''), ''), '')
+ '''
,@Tag = ''' + FQN + ',' + IndexName + ',' + CAST(IndexID AS VARCHAR) + ',' + CAST([Partition] AS VARCHAR) + ',' + CAST([Rows] AS VARCHAR) + '''', '
')
+ '
SELECT Id, TableName, ScanCount, LogicalReads, PhysicalReads, ReadAheadReads, LobLogicalReads, LobPhysicalReads, LobReadAheadReads, b.C1 AS FQN, 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
FROM #Stats
CROSS APPLY Util.dbo.ParseDelimitedColumns8(Tag, '','') b
ORDER BY LogicalReads DESC, CAST(b.c5 AS INT) DESC
'
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 @PrintSQL = 1
EXEC Util.dbo.PrintLargeText
@SQL
IF @ExecSQL = 1
EXEC(@SQL)