--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