1

I want to save some memory parameters for every stored procedure executed - requested_memory_kb, granted_memory_kb, max_used_memory_kb.

I plan to find them in sys.dm_exec_query_memory_grants at the start and end of execution by @@SPID.

Is it correct to take this values? Or max_used_memory_kb at the end of execution can be less than during the execution of stored procedure?

We log certain information for procedures (parameters, execution start/end time, etc.), and I want to add to that extra information to see procedures with large amounts of granted memory, but with low usage.

2
  • Can you elaborate on what your objective is first? There are probably better ways to get to where you want to go. Commented May 4, 2023 at 14:04
  • we have logging for procedures (param, execution start/end, etc) and i want to add here info to see proc with large amount of granted memory, but with low usage Commented May 4, 2023 at 14:35

2 Answers 2

3

cache me outside

You can use sp_BlitzCache, sort by unused memory grant, and log it to a table.

EXEC sp_BlitzCache
    @SortOrder = 'Unused Grant',
    @OutputDatabaseName = 'whatever_database',
    @OutputSchemaame = 'probably_dbo',
    @OutputTableName = 'something_memorable';

If you stick that in an agent job that runs every hour or so, you should have a pretty good idea which queries ask for the most memory, and don't end up using it.

1

Extended Events, or XEvents, is probably the best way to go about this.

XEvents is a very performant and versatile logging framework built in to SQL Server.

You want either the query_memory_grant_usage or the query_memory_grants event, depending on whether you want data for every execution, or aggregated query information every 5 minutes, respectively.

CREATE EVENT SESSION [MemoryUsage] ON SERVER 
ADD EVENT sqlserver.query_memory_grant_usage,
ADD TARGET package0.event_file
  (SET filename=N'memusage', max_file_size=(100));

SSMS also has a nice wizard for this.

You can even set filters, and capture global fields such as client_hostname or plan_handle.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.