memory consumed by each database

• sqlguy80

  SSCrazy

  Points: 2763

  More actions

  August 17, 2017 at 12:54 pm

  #328035

  Hi

  I've noticed 90% memory utilization on a critical database server housing  ~ 500 databases.
  I used the query below to determine which databases are using up memory.

  Max sql server memory= 81 GB
  Total OS memory= 98 GB
  SQL Server 2014 Ent ( VMWare Virtual Machine )

  SET TRAN ISOLATION LEVEL READ UNCOMMITTED
  SELECT
  ISNULL(DB_NAME(database_id), 'ResourceDb') AS DatabaseName
  , CAST(COUNT(row_count) * 8.0 / (1024.0) AS DECIMAL(28,2))
  AS [Size (MB)]
  FROM sys.dm_os_buffer_descriptors
  GROUP BY database_id
  ORDER BY DatabaseName

  I found tempdb  taking 14 GB and one user db  XXX taking 12 GB .

  Will killing connections to db  XXX free up 12 GB?

  Thanks

• Eirikur Eiriksson

  SSC Guru

  Points: 182882

  More actions

  August 17, 2017 at 1:01 pm

  #1955715

  sqlguy80 - Thursday, August 17, 2017 12:54 PM

  Hi

  I've noticed 90% memory utilization on a critical database server housing  ~ 500 databases.
  I used the query below to determine which databases are using up memory.

  Max sql server memory= 81 GB
  Total OS memory= 98 GB
  SQL Server 2014 Ent ( VMWare Virtual Machine )

  SET TRAN ISOLATION LEVEL READ UNCOMMITTED
  SELECT
  ISNULL(DB_NAME(database_id), 'ResourceDb') AS DatabaseName
  , CAST(COUNT(row_count) * 8.0 / (1024.0) AS DECIMAL(28,2))
  AS [Size (MB)]
  FROM sys.dm_os_buffer_descriptors
  GROUP BY database_id
  ORDER BY DatabaseName

  I found tempdb  taking 14 GB and one user db  XXX taking 12 GB .

  Will killing connections to db  XXX free up 12 GB?

  Thanks

  Question, why are you running Enterprise with only 96GB?
  😎

  The way SQL uses memory is not directly related to connections or databases,  suggest you read up on the subject.