已用記憶體量/可用記憶體量

我需要找出使用了多少記憶體，以及是否有可用的免費記憶體。

背景：

當伺服器負載過重時，生產伺服器上的查詢會花費大量時間。我懷疑可用的記憶體太小而無法為大量不同的查詢提供服務。

確保您已經為您的伺服器設置了適當的max server memory (mb)和min server memory (mb)選項。sys.sp_configure請參閱我的問題以確定這些數字應該是多少。

檢查頁面預期壽命以查看您的緩衝池是否太小。根據緩衝池的大小，通常提到的數字是 300 秒（5 分鐘），但這取決於您的需要。這段程式碼應該執行良好：

DECLARE @Version NVARCHAR(255);
DECLARE @VersionINT INT;
SET @Version = CONVERT(NVARCHAR(255),SERVERPROPERTY('ProductVersion'));
SET @VersionINT = CONVERT(INT, SUBSTRING(@Version,1 ,CHARINDEX('.',@Version)-1));
DECLARE @cmd NVARCHAR(4000);
SET @cmd = '';
IF @VersionINT >= 9
BEGIN
   SET @cmd = 
'SET NOCOUNT ON;

DECLARE @SecondsSinceRestart INT;
DECLARE @cpu_count INT;
DECLARE @physical_memory_kb INT;
DECLARE @committed_target_kb INT;
DECLARE @committed_kb INT;

DECLARE @BootTime DATETIME;
DECLARE @output TABLE
(
   RowNum INT NOT NULL IDENTITY(1,1)
   , [output] VARCHAR(1000) NULL
);
INSERT INTO @output ([output])
EXEC sys.xp_cmdshell @command = ''wmic os get lastbootuptime'';

;WITH c AS (
   SELECT Year = SUBSTRING(o.output, 1, 4)
       , Month = SUBSTRING(o.output, 5, 2)
       , Day = SUBSTRING(o.output, 7, 2)
       , Hour = SUBSTRING(o.output, 9, 2)
       , Minute = SUBSTRING(o.output, 11, 2)
       , Second = SUBSTRING(o.output, 13, 2)
   FROM @output o
   WHERE o.RowNum = 2
)
SELECT @BootTime = CONVERT(DATETIME, c.Year + ''-'' + c.Month + ''-'' + c.Day + ''T'' + c.Hour + '':'' + c.Minute + '':'' + c.Second, 126)
FROM c;

SET @SecondsSinceRestart = DATEDIFF(SECOND, @BootTime, GETDATE());

';
IF @VersionINT = 11
SET @cmd = @cmd + '
SELECT @cpu_count = cpu_count
       , @physical_memory_kb = physical_memory_kb
       , @committed_target_kb = committed_target_kb
       , @committed_kb = committed_kb
FROM sys.dm_os_sys_info dosi
'
ELSE
SET @cmd = @cmd + '
SELECT @cpu_count = cpu_count
       , @physical_memory_kb = physical_memory_in_bytes / 1024
       , @committed_target_kb = bpool_commit_target * 8
       , @committed_kb = bpool_committed * 8
FROM sys.dm_os_sys_info dosi
';

SET @cmd = @cmd + '
;WITH c AS (
   SELECT ServerName = @@SERVERNAME
       , pc.object_name
       , pc.counter_name
       , pc.instance_name
       , pc.cntr_value
       , BootTime = @BootTime
       , SecondsSinceRestart = @SecondsSinceRestart
       , cpu_count = @cpu_count
       , physical_memory_kb = @physical_memory_kb
       , committed_target_kb = @committed_target_kb
       , committed_kb = @committed_kb
   FROM sys.dm_os_performance_counters pc
   WHERE pc.object_name = ''MSSQL$'' + CONVERT(VARCHAR(255), SERVERPROPERTY(''InstanceName'')) + '':Buffer Node                                                                                                        ''
   AND pc.counter_name = ''Page life expectancy                                                                                                            ''
   AND pc.instance_name IS NOT NULL
)
SELECT RunNum = 7 
   , c.ServerName
   , c.counter_name
   , c.object_name
   , c.instance_name
   , c.cntr_value
   , c.SecondsSinceRestart
   , c.BootTime
   , Ratio = c.cntr_value / CONVERT(DECIMAL(10,0), c.SecondsSinceRestart)
   , cpu_count
   , physical_memory_kb
   , committed_target_kb
   , committed_kb
   , ServerVersion = SERVERPROPERTY(''ProductVersion'')
FROM c;';
   EXEC sys.sp_executesql @cmd;
END

您可以使用我最近在SQLServerScience.com上寫的一篇部落格文章中顯示的程式碼查看哪些對象正在佔用記憶體。

引用自：https://dba.stackexchange.com/questions/244739