sqlserver内存占用查询

最新推荐文章于 2026-04-04 15:29:36 发布

原创最新推荐文章于 2026-04-04 15:29:36 发布 · 366 阅读

0 ·

本内容遵循CC 4.0 BY-SA版权协议

标签

#sqlserver #数据库 #sql

收录于

内存占用：
SELECT 
    COUNT(*) * 8 / 1024.0 AS BufferPoolMemoryUsageMB
FROM 
    sys.dm_os_buffer_descriptors;
    
    SELECT 
    DB_NAME(database_id) AS DatabaseName,
    COUNT(*) * 8 / 1024.0 AS BufferPoolMemoryUsageMB
FROM 
    sys.dm_os_buffer_descriptors
GROUP BY 
    database_id
ORDER BY 
    BufferPoolMemoryUsageMB DESC;

查询某个表数据占用内存排行：
-- 定义要查询的表名
DECLARE @TableName NVARCHAR(128) = 'web_content';

-- 动态生成查询语句
DECLARE @Query NVARCHAR(MAX);
SET @Query = 'SELECT ';

-- 遍历表的列，计算每列的长度
SELECT 
    @Query = @Query + 
    CASE 
        WHEN DATA_TYPE IN ('varchar', 'nvarchar', 'varbinary') THEN 
            'LEN(CAST(' + COLUMN_NAME + ' AS NVARCHAR(MAX)))'
        WHEN DATA_TYPE IN ('char', 'nchar', 'binary') THEN 
            'LEN(CAST(' + COLUMN_NAME + ' AS NVARCHAR(MAX)))'
        ELSE 
            '0'
    END + ' + '
FROM 
    INFORMATION_SCHEMA.COLUMNS
WHERE 
    TABLE_NAME = @TableName;

-- 去掉最后一个加号
SET @Query = LEFT(@Query, LEN(@Query) - 1);

-- 继续构建查询语句
SET @Query = @Query + ' AS TotalLength, *
FROM ' + QUOTENAME(@TableName) + '
ORDER BY TotalLength DESC';

-- 执行动态查询
EXEC sp_executesql @Query;