I have query inside a stored procedure and i am trying to see is there a way to specify memory that required for this query to use.
i know on server level we can change Minimum memory to use Query but i that is for all queries on the server but i am looking for an option at specific query level.
2条答案
按热度按时间7nbnzgx91#
There is no method or option provided to user in SQL Server to reserve memory to particular query. The reservation is done internally by database engine before query is executed. If you are thinking about Resource governor I dont know much about it but you can try
When query is executed it is compiled and then optimizer executes the query. If query is totally new it might need some amount of memory to create multiple plans and then select best plan from that. This is called cost based optimization. A lack of memory can delay the this process. Next comes query execution and assuming you are doing sorts and Hash joins in your query it would require memory perhaps sometimes significant amount of memory depending on rows it has to sort.
When such query executes it calculates two memory parameters
required memoryandadditional memoryrequired memory is minimum memory required to run sort operations and additional memory is memory required to store additional sorts when complete query would execute. This is called as query memory grants. Now in your case I assume stats are either outdated or there is a memory pressure or memory currently is not free. When your query ran it asked for some amount of memory, SQL Server is not going to grant all memory but would grant only minimum memory so that query executon can start and would reserve some memory for query based on sorting it is going to do. But when your query ran either due to skewed stats or due to memory pressure additional memory for storing sorted data could not be obtained and this led to spilling.You can try to update stats and see if query prepares a better plan and would avoid spliing or increase the value of max server memory so as to provide more memory to buffer pool. There can also be chance that you need to change join order in query or may be rewrite a query.
Complete description of how memory is allocated when query runs is in this article
b91juud32#
There are a pair of query hints,
MIN_GRANT_PERCENTandMAX_GRANT_PERCENT, which can be used to set bounds for an individual query. The bounds are as a percentage of the configured server memory rather than in absolute bytes.I'm not sure that this is precisely what you were asking for, but it may be close enough to be useful.
More information: https://learn.microsoft.com/en-US/sql/t-sql/queries/hints-transact-sql-query?view=sql-server-ver16#max_grant_percent--numeric_value