IF (SELECT CAST(LEFT(CAST(SERVERPROPERTY('ProductVersion') as varchar(128)),CHARINDEX('.',CAST(SERVERPROPERTY('ProductVersion') as varchar(128)))-1) as int)) >= 9
BEGIN
IF object_id('tempdb..#BufferUsage') IS NOT NULL
DROP TABLE #BufferUsage
CREATE TABLE #BufferUsage(
[Database_ID] [int] NULL,
[Database_Name] [varchar] (512) NULL,
[object_id] [int] NULL,
[Object] [varchar] (512) NULL,
[Type] [varchar] (128) NULL,
[Index] [varchar](512) NULL,
[Index_Type] [varchar](128) NULL,
[Buffer_Pages] [bigint] NULL,
[Buffer_MB] [float] NULL
) ON [PRIMARY]
Declare @DataBase VarChar(512)
Set @Database = ''
While (@Database IS NOT NULL)
BEGIN
SET @Database = (SELECT MIN(Name)
FROM master.sys.databases
Where Name > @Database
AND state_desc = 'ONLINE'
AND Name NOT IN ('LiteSpeedLocal', 'master', 'model', 'msdb', 'tempdb')
)
/****** START LOOP ********/
IF LEN(@Database) > 0
BEGIN
EXECUTE ('
USE [' + @Database + '];
WITH [Source]
AS(SELECT [Object] = [Objects].name
,[Type] = [Objects].type_desc
,[Index] = COALESCE([Indexes].name, '''')
,[Index_Type] = [Indexes].type_desc
,[Partitions].[object_id]
,[Partitions].index_id
,[Allocation_Units].allocation_unit_id
FROM [' + @Database + '].sys.partitions AS [Partitions]
INNER JOIN [' + @Database + '].sys.allocation_units AS [Allocation_Units]
ON [Partitions].hobt_id = [Allocation_Units].container_id
INNER JOIN [' + @Database + '].sys.objects AS [Objects]
ON [Partitions].[object_id] = [Objects].[object_id]
INNER JOIN [' + @Database + '].sys.indexes AS [Indexes]
ON [Objects].[object_id] = [Indexes].[object_id]
AND [Partitions].index_id = [Indexes].index_id
WHERE [Allocation_Units].[type] IN (1,2,3)
AND [Objects].is_ms_shipped = 0)
INSERT INTO #BufferUsage ([Database_ID], [Database_Name], [object_id], [Object], [Type], [Index], [Index_Type], [Buffer_Pages], [Buffer_MB])
SELECT MAX([Database_ID]) as [Database_ID], ''' + @Database + ''', [Source].[object_id], [Source].[Object], [Source].[Type], [Source].[Index], [Source].Index_Type, buffer_pages = COUNT_BIG([Buffer_Descriptors].page_id), buffer_mb = COUNT_BIG([Buffer_Descriptors].page_id) / 128
FROM [Source]
INNER JOIN [' + @Database + '].sys.dm_os_buffer_descriptors AS [Buffer_Descriptors]
ON [Source].allocation_unit_id = [Buffer_Descriptors].allocation_unit_id
WHERE [Buffer_Descriptors].database_id = DB_ID()
GROUP BY [Source].[object_id], [Source].[Object],[Source].[Type],[Source].[Index],[Source].Index_Type
ORDER BY buffer_pages DESC
')
END
/****** END LOOP **********/
END
SELECT * FROM #BufferUsage
DROP TABLE #BufferUsage
END -- SQL Version
No comments:
Post a Comment