Tag Archives: memory

MSSQL: Whitch database used most memory

With this query, you can see whitch database that is using most memory

SELECT COUNT(*) AS cached_pages_count , 
    ( COUNT(*) * 8.0 ) / 1024 AS MB , 
    CASE database_id 
      WHEN 32767 THEN 'ResourceDb' 
      ELSE DB_NAME(database_id) 
    END AS Database_name 
FROM sys.dm_os_buffer_descriptors 
GROUP BY database_id

Limit SQL Server maximum memory SQL instance

Start SQL Server Management Studio
Connect to you SQL server instance
Start a “New Query”
And then put this into you SQLQuery1.sql window

sp_configure ‘show advanced options’,1
reconfigure with override
go
sp_configure ‘max server memory’, 4096
reconfigure with override
go

Then push the execute buttom, this will limit you SQL instance to 4096MB or 4GB. Please beware of, if you have multiple instance each instance you wan’t to limit

Limit SQL Server maximum memory SQL of WSUS

Start SQL Server Management Studio
Connect to \\.\pipe\MSSQL$MICROSOFT##SSEE\sql\query
Start a “New Query”
And then put this into you SQLQuery1.sql window

sp_configure ‘show advanced options’,1
reconfigure with override
go
sp_configure ‘max server memory’, 256
reconfigure with override
go

Then push the execute buttom, this will limit you SQL of WSUS to 256MB, whitch should be enough.