/* Generated in SQL Explorer v.1.6.4.31586 */
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SET LOCK_TIMEOUT 10000

SELECT 
  CASE WHEN database_id = 32767 THEN 'ResourceDB' ELSE DB_NAME(database_id) END  as DatabaseName, 
  CAST(COUNT(*)/128. AS NUMERIC(20,2)) AS [BufferSize MB], 
  CAST(SUM(CAST(free_space_in_bytes AS BIGINT)) / (1024. * 1024) AS NUMERIC(20,2)) AS [EmptySize MB], 
  CAST(SUM(is_modified/128.) AS NUMERIC(20,2)) AS [DirtySize MB], 
  CAST(AVG(100.*(free_space_in_bytes/ (1024. * 1024))/(1/128.)) AS NUMERIC(8,2)) AS [EmptySize %], 
  COUNT(*) AS PagesInCache
FROM sys.dm_os_buffer_descriptors
GROUP BY database_id
ORDER BY PagesInCache desc
OPTION(MAXDOP 1)

/* Generated in SQL Explorer v.1.6.4.31586 */
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SET LOCK_TIMEOUT 10000

SELECT 
  CASE WHEN ISNULL(RES.container_id, 0) = 0 
    THEN '<<<Marked for deferred drop>>>'
  ELSE
    OBJECT_SCHEMA_NAME(p.[object_id]) + '.' + OBJECT_NAME(p.[object_id]) 
  END AS [TableName],
  p.index_id, 
  CASE WHEN i.index_id IS NOT NULL THEN ISNULL(i.name, '<<<HEAP>>>') ELSE NULL END AS [IndexName], 
  RES.type_desc,
  CAST(RES.buffers/128. AS NUMERIC(20,2)) AS [BufferSize MB],  
  CAST(RES.buffers_modified/128. AS NUMERIC(20,2)) AS [DirtySize MB],
  CAST(ROUND(CASE WHEN RES.used_pages < RES.buffers THEN 100. ELSE ISNULL(100.0 * RES.buffers / NULLIF(RES.used_pages,0),0) END, 4) AS DECIMAL(8,4)) AS [PagesCached %],
  CAST(RES.free_space_in_mb AS NUMERIC(20,2)) AS [EmptySize MB]
FROM
  (
    SELECT  -- Get allocation units + buffer descriptors grouped by container_id
      AU.container_id,
      AU.type_desc,
      SUM(BUF_GP.buffers) AS buffers,
      SUM(AU.used_pages) AS used_pages,
      SUM(BUF_GP.free_space_in_mb) AS free_space_in_mb,
      SUM(buffers_modified) AS buffers_modified,
      SUM(CASE WHEN AU.type = 1 THEN BUF_GP.RowCountLeaf ELSE 0 END) AS RowCountLeaf,
      SUM(CASE WHEN AU.type = 1 THEN BUF_GP.RowCountNonLeaf ELSE 0 END) AS RowCountNonLeaf
    FROM
    (
      SELECT  -- Get buffer descriptors grouped by allocation_unit_id
        BUF.allocation_unit_id,
        COUNT(*) AS buffers,
        SUM(CAST(BUF.free_space_in_bytes AS BIGINT)) / (1024. * 1024) AS free_space_in_mb, 
        SUM(CASE WHEN BUF.is_modified = 1 THEN 1 ELSE 0 END) AS buffers_modified,
        SUM(CASE WHEN BUF.page_level =  0 and BUF.page_type IN ('INDEX_PAGE','DATA_PAGE') THEN BUF.row_count ELSE 0 END ) AS RowCountLeaf,
        SUM(CASE WHEN BUF.page_level <> 0 and BUF.page_type IN ('INDEX_PAGE','DATA_PAGE') THEN BUF.row_count ELSE 0 END ) AS RowCountNonLeaf
      FROM sys.dm_os_buffer_descriptors AS BUF
      WHERE BUF.database_id = DB_ID()
      GROUP BY BUF.allocation_unit_id
    ) BUF_GP
    LEFT JOIN sys.allocation_units AS AU ON AU.allocation_unit_id = BUF_GP.allocation_unit_id
    GROUP BY AU.container_id, AU.type_desc
  ) RES
  LEFT JOIN sys.partitions AS p ON RES.container_id = p.hobt_id 
  LEFT JOIN sys.indexes AS i ON i.object_id = p.object_id AND i.index_id = p.index_id
WHERE ABS(ISNULL(p.[object_id],101)) > 100
ORDER BY RES.buffers DESC
OPTION(MAXDOP 1);

/* Generated in SQL Explorer v.1.6.4.31586 */
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SET LOCK_TIMEOUT 10000

SELECT 
  OBJECT_SCHEMA_NAME(i.[object_id]) + '.' + OBJECT_NAME(i.[object_id]) AS TableName,
  ISNULL(REPLACE(i.name, NCHAR(31), ''), '<<<HEAP>>>') AS IndexName,
  i.is_primary_key AS PK,
  i.is_unique_constraint AS UQ,
  CAST(CASE WHEN i.index_id = 1 THEN 1 ELSE 0 END AS BIT) AS Clust,
  CAST(p2.SizeMB AS DECIMAL(20, 2)) AS SizeMB,
  s.user_seeks,
  s.user_scans,
  s.user_lookups,
  s.user_seeks + s.user_scans + s.user_lookups AS total_user_reads,
  CAST(s.user_scans*SizeMB/1024. AS DECIMAL(30, 2)) AS TotalScanGB,
  CASE WHEN i.index_id IN (0,1) THEN mi.MissingIndexes ELSE NULL END AS MissingIndexes
FROM sys.indexes AS i
LEFT JOIN
    (
      select p2.object_id, p2.index_id, SUM(au.used_pages) / 128.AS SizeMB
      from sys.partitions AS p2
        INNER JOIN sys.allocation_units AS au ON p2.partition_id = au.container_id
      where au.type <> 2-- LOB_DATA
      group by p2.object_id, p2.index_id
    ) p2 ON p2.object_id = i.object_id AND p2.index_id = i.index_id
LEFT JOIN 
  (
    SELECT mid.object_id, MissingIndexes = COUNT(*)
    FROM sys.dm_db_missing_index_group_stats AS migs
      INNER JOIN sys.dm_db_missing_index_groups AS mig ON migs.group_handle = mig.index_group_handle
      INNER JOIN sys.dm_db_missing_index_details AS mid ON mig.index_handle = mid.index_handle
    WHERE mid.database_id = DB_ID()
    GROUP BY mid.object_id
  ) mi ON mi.object_id = i.object_id
LEFT JOIN sys.dm_db_index_usage_stats AS s
  ON s.[object_id] = i.[object_id] AND s.index_id = i.index_id
  AND s.database_id = DB_ID()
WHERE ((OBJECTPROPERTY(i.[object_id],'IsUserTable') = 1) OR (OBJECTPROPERTY(i.[object_id],'IsView') = 1))
ORDER BY TotalScanGB DESC
OPTION(MAXDOP 1)