GetSQLBuffers_wProvider_PipeTo_Out-SquarifiedTreeMap.ps1

<# This is the code used to tell you 
   which databases are using up the RAM on 
   your SQL Server is great information to know
   Queries are from https://www.mssqltips.com/sqlservertip/2393/determine-sql-server-memory-use-by-database-and-object/ #>
$SQLInstance  = 'localhost\SQL2016'

Invoke-Sqlcmd -ServerInstance $SQLInstance -Database master -Query "
DECLARE @total_buffer INT;

SELECT @total_buffer = cntr_value
FROM sys.dm_os_performance_counters 
WHERE RTRIM([object_name]) LIKE '%Buffer Manager'
AND counter_name = 'Database Pages';

;WITH src AS
(
SELECT 
database_id, db_buffer_pages = COUNT_BIG(*)
FROM sys.dm_os_buffer_descriptors
--WHERE database_id BETWEEN 5 AND 32766
GROUP BY database_id
)
SELECT
[db_name] = CASE [database_id] WHEN 32767 
THEN 'Resource DB' 
ELSE DB_NAME([database_id]) END,
db_buffer_pages,
db_buffer_MB = db_buffer_pages / 128,
db_buffer_percent = CONVERT(DECIMAL(6,3), 
db_buffer_pages * 100.0 / @total_buffer)
FROM src
ORDER BY db_buffer_MB DESC; 
" -OutputAs DataRows |
Out-SquarifiedTreeMap -Width 1100 -Height 600 -LabelProperty db_name -DataProperty db_buffer_MB -HeatmapProperty db_buffer_percent -ShowLabel LabelProperty -PassThru | 
%{cd "SQLSERVER:\SQL\$($SQLInstance)\Databases\$($_.db_name)\"
Invoke-Sqlcmd -ServerInstance $SQLInstance -Database $_.db_name -Query "
;     WITH    src
               AS ( SELECT   [Object] = o.name ,
                             [Type] = o.type_desc ,
                             [Index] = COALESCE(i.name, '') ,
                             [BufferObject] = COALESCE(i.name, o.name) ,
                             [Index_Type] = i.type_desc ,
                             p.[object_id] ,
                             p.index_id ,
                             au.allocation_unit_id ,
                             p.data_compression_desc
                    FROM     sys.partitions AS p
                             INNER JOIN sys.allocation_units AS au ON p.hobt_id = au.container_id
                             INNER JOIN sys.objects AS o ON p.[object_id] = o.[object_id]
                             INNER JOIN sys.indexes AS i ON o.[object_id] = i.[object_id]
                                                            AND p.index_id = i.index_id
                    WHERE    au.[type] IN ( 1, 2, 3 )
                             AND o.is_ms_shipped = 0
                  )
     SELECT  src.[Object] ,
             src.[Type] ,
             src.[Index] ,
             src.Index_Type ,
     		 src.BufferObject,
             buffer_pages = COUNT_BIG(b.page_id) ,
             buffer_mb = COUNT_BIG(b.page_id) / 128 ,
             src.data_compression_desc
       FROM src
      INNER JOIN sys.dm_os_buffer_descriptors AS b ON src.allocation_unit_id = b.allocation_unit_id
      WHERE b.database_id = DB_ID()
      GROUP BY src.[Object] ,
             src.[Type] ,
             src.[Index] ,
     		 src.BufferObject,
             src.Index_Type ,
             src.data_compression_desc
      ORDER BY buffer_pages DESC;
 -- WITH CHECK OPTION
 GO" -OutputAs DataRows -QueryTimeout 300 } |
Out-SquarifiedTreeMap -Width 1100 -Height 600 -LabelProperty BufferObject -DataProperty buffer_pages -HeatmapProperty buffer_mb -ShowLabel LabelProperty -PassThru |%{
#Invoke-Sqlcmd -ServerInstance $SQLInstance -Database $_.db_name -InputFile 'C:\Users\sql2k\Documents\SQL Server Management Studio\Performance Checks\QueryPlans using a Specific Index.sql'
Invoke-Sqlcmd -ServerInstance $SQLInstance -Query "
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED 
 DECLARE @IndexName AS NVARCHAR(128) = '$($_.Index)'; 

--— Make sure the name passed is appropriately quoted 
 IF (LEFT(@IndexName, 1) <> '[' AND RIGHT(@IndexName, 1) <> ']') SET @IndexName = QUOTENAME(@IndexName); 
-- –Handle the case where the left or right was quoted manually but not the opposite side 
 IF LEFT(@IndexName, 1) <> '[' SET @IndexName = '['+@IndexName; 
 IF RIGHT(@IndexName, 1) <> ']' SET @IndexName = @IndexName + ']'; 

--— Dig into the plan cache and find all plans using this index 
 ;WITH XMLNAMESPACES 
    (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')    
 SELECT 
 stmt.value('(@StatementText)[1]', 'varchar(max)') AS SQL_Text, 
 obj.value('(@Database)[1]', 'varchar(128)') AS DatabaseName, 
 obj.value('(@Schema)[1]', 'varchar(128)') AS SchemaName, 
 obj.value('(@Table)[1]', 'varchar(128)') AS TableName, 
 obj.value('(@Index)[1]', 'varchar(128)') AS IndexName, 
 obj.value('(@IndexKind)[1]', 'varchar(128)') AS IndexKind, 
 cp.plan_handle, 
 query_plan 
 FROM sys.dm_exec_cached_plans AS cp 
 CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS qp 
 CROSS APPLY query_plan.nodes('/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple') AS batch(stmt) 
 CROSS APPLY stmt.nodes('.//IndexScan/Object[@Index=sql:variable(""@IndexName"")]') AS idx(obj) 
 OPTION(MAXDOP 1, RECOMPILE); 
" -OutputAs DataRows -OutVariable QueriesInQuestion}
<# Final step is just to show the queries it found. #>
cd c:\temp

$QueriesInQuestion | OGV

<#
($QueriesInQuestion).query_plan | Out-File -FilePath c:\temp\BadPlans\badplan.sqlplan;

#>

<# Show how to export a plan with the OutVariable & OGV
($QueriesInQuestion).query_plan | Out-File -FilePath c:\temp\BadPlans\badplan.sqlplan;

$QueriesInQuestion | OGV -PassThru | SELECT $_.query_plan |Out-File -FilePath c:\temp\BadPlans\badplan.sqlplan;
#>