Top 10 memory usage queries

Configurare noua (How To)

Situatie

Mai jos este o procedura ce listeaza top 10 cele mai costisitoare query-uri dpdv al memoriei utilizate, pe un server MSSQL Server.

Solutie

create procedure TopMemoryUsageQueries

 

as

declare @spid int

select @spid = @@spid

SELECT

top 10

mg.session_id,

mg.granted_memory_kb,

mg.requested_memory_kb,

mg.ideal_memory_kb,

mg.request_time,

mg.grant_time,

mg.query_cost,

mg.dop,st.[TEXT],

qp.query_plan

FROM sys.dm_exec_query_memory_grants AS mg

CROSS APPLY sys.dm_exec_sql_text(mg.plan_handle) AS st

CROSS APPLY sys.dm_exec_query_plan(mg.plan_handle) AS qp

where mg.session_id <> @spid

ORDER BY mg.required_memory_kb DESC

Tip solutie

Permanent

Voteaza

(5 din 15 persoane apreciaza acest articol)

Despre Autor

Leave A Comment?