Skip to content

Instantly share code, notes, and snippets.

Show Gist options
  • Save SQLvariant/2ea6175460736691807e5a4663c17788 to your computer and use it in GitHub Desktop.
Save SQLvariant/2ea6175460736691807e5a4663c17788 to your computer and use it in GitHub Desktop.
<# 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;
#>
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment