SQL Server 占用内存太高，查找占用内存高以及影响其性能的sql语句及解决方法

柔光的暖阳◎ 2023-10-04 16:26 1阅读 0赞

--SQL Server 占用内存太高，查找占用内存高以及影响其性能的sql语句
    ：
    --方法一：
    SELECT s2.dbid, 
    s1.sql_handle, 
    (SELECT TOP 1 SUBSTRING(s2.text,statement_start_offset / 2+1 , 
    ( (CASE WHEN statement_end_offset = -1 
    THEN (LEN(CONVERT(nvarchar(max),s2.text)) * 2) 
    ELSE statement_end_offset END) - statement_start_offset) / 2+1)) AS sql_statement,
    execution_count, 
    plan_generation_num, 
    last_execution_time, 
    total_worker_time, 
    last_worker_time, 
    min_worker_time, 
    max_worker_time,
    total_physical_reads, 
    last_physical_reads, 
    min_physical_reads, 
    max_physical_reads, 
    total_logical_writes, 
    last_logical_writes, 
    min_logical_writes, 
    max_logical_writes 
    FROM sys.dm_exec_query_stats AS s1 
    CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS s2 
    WHERE s2.objectid is null 
    ORDER BY last_worker_time desc,s1.sql_handle, s1.statement_start_offset, s1.statement_end_offset;
    
    --方法二：
    
    --使用以下语句查找出什么语句占内存最高，针对占内存高的语句进行优化
    
    SELECT SS.SUM_EXECUTION_COUNT,
    T.TEXT,
    SS.SUM_TOTAL_ELAPSED_TIME,
    SS.SUM_TOTAL_WORKER_TIME,
    SS.SUM_TOTAL_LOGICAL_READS,
    SS.SUM_TOTAL_LOGICAL_WRITES
    FROM (SELECT S.PLAN_HANDLE,
    SUM(S.EXECUTION_COUNT)SUM_EXECUTION_COUNT,
    SUM(S.TOTAL_ELAPSED_TIME)SUM_TOTAL_ELAPSED_TIME,
    SUM(S.TOTAL_WORKER_TIME)SUM_TOTAL_WORKER_TIME,
    SUM(S.TOTAL_LOGICAL_READS)SUM_TOTAL_LOGICAL_READS,
    SUM(S.TOTAL_LOGICAL_WRITES)SUM_TOTAL_LOGICAL_WRITES
    FROM SYS.DM_EXEC_QUERY_STATS S
    GROUP BY S.PLAN_HANDLE
    ) AS SS
    CROSS APPLY SYS.dm_exec_sql_text(SS.PLAN_HANDLE)T
    ORDER BY SUM_TOTAL_LOGICAL_READS DESC
    
    --方法三：
    --大的运算，一般是报表之类的， 找一下最近运行超过3秒的sql，按消耗秒数降序看下就差不多了
    SELECT TOP 10 OBJECT_NAME(qt.objectid, qt.dbId)  AS procName,
           DB_NAME(qt.dbId)                   AS [db_name],
           qt.text                            AS SQL_Full,
           SUBSTRING(
               qt.text,
               (qs.statement_start_offset / 2) + 1,
               (
                   (
                       CASE statement_end_offset
                            WHEN -1 THEN DATALENGTH(qt.text)
                            ELSE qs.statement_end_offset
                       END 
                       - qs.statement_start_offset
                   ) / 2
               ) + 1
           )                                  AS SQL_Part --统计对应的部分语句
           ,
           qs.creation_time,
           qs.last_execution_time,
           qs.execution_count,
           qs.last_elapsed_time / 1000000     AS lastElapsedSeconds,
           qs.last_worker_time / 1000000      AS lastCpuSeconds,
           CAST(
               qs.total_elapsed_time / 1000000.0 / (
                   CASE 
                        WHEN qs.execution_count = 0 THEN -1
                        ELSE qs.execution_count
                   END
               ) AS DECIMAL(28, 2)
           )                                  AS avgDurationSeconds,
           CAST(qs.last_logical_reads AS BIGINT) * 1.0 / (1024 * 1024) * 8060 AS 
           lastLogicReadsMB,
           qs.last_logical_reads,
           qs.plan_handle
    FROM   sys.dm_exec_query_stats qs
           CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
    CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS p
    WHERE  qs.last_execution_time >= CONVERT(CHAR(10),GETDATE(),120)+' 08:00'	--今天8点之后的慢SQL
           AND qs.last_elapsed_time >= 3 * 1000 * 1000			                --只取执行时间大于 3 秒的记录
           AND qt.[text] NOT LIKE '%Proc_DBA%'
    ORDER BY
           qs.last_worker_time DESC

SQL Server 2008 R2如果程序设计不合理，运行越久，占用内存会越来越大 。以下提供两种解决方式。

第一种：  
使用任务计划定期执行下边批处理重启SQL服务：

net stop sqlserveragent
    net stop mssqlserver
    net start mssqlserver
    net start sqlserveragent

第二种：  
进入Sql server 企业管理器，在数据库服务器名称上点击【右键】，选择【属性】，然后，找到【内存】选项，在右边的【使用A WE分配内存】（sqlServer64的应该不用勾 ） 左边把对勾打上。在最大服务器内存（MB）上填入适当的大小（具体填多大，肯定不能超过计算机的物理内存，当然，也可以在任务管理器中查一下，sqlserver.exe占有多大时，系统会变慢作为参考），记得是以M（兆）为单位，点确定，重启一下Sql服务器！OK！ 一般情况下顶多设置为物理内存的4分之一即可。