Query memory, wait time and timeout errors
-
Hi,
I found the below text in SQL Server 2005 performance course manual.
I am just wondering if there is a way to find out the amount of memory a query needs (estimated and/or real) and the time the optimizer thinks it would take to run the query, so to try to predict a potential timeout?
Also is there a way to make a specific query to wait longer than the default period of time?
Thanks.
When a query plan is optimized, the optimizer determines two pieces of information about memory use for that query. First, it picks a minimum amount of memory that the query needs in order to run effectively, and that value is stored with the query plan. The optimizer also determines a maximum amount of memory that the query could benefit from. For example, it doesn't help to give a sort 2 GB of memory if the entire table you're sorting fits into 100 MB. You really just want the 100 MB, and so that would be the value for the maximum amount of useful memory that is stored with the query plan.
When SQL Server goes to execute a plan, the plan is passed to a routine called the memory grant scheduler. The grant scheduler does a few interesting things. First, if the query the grant scheduler is looking at doesn't have a sort or a hash operation as part of the plan, SQL Server knows it doesn't use large amounts of memory. In this case, there is no waiting in the memory grant scheduler at all. The plan can be run immediately, so a typical transaction processing request will bypass this mechanism completely.
If there are very large queries, it's best to run a few of them at a time and let them get more of the memory they need. If at least half of the maximum memory requirement of the query is available, the query will run immediately, with whatever memory it can get. Otherwise, the query will wait in the memory grant queue until half of the maximum memory requirement is available. At the same time, the SQL Server will never give out more than half of the remaining query memory to any one query. If you find you are getting lots of memory timeout errors (error number 8645), this means your queries were waiting too long in the memory grant scheduler. By default, a query will wait only for an amount of time that is proportional to its estimated cost to run; at this time, that value is about 25 times the estimated number in seconds. With the default value, no query will wait less then 25 seconds because the query cost is rounded up to 1, if it is less then 1 second. If the time limit is exceeded, error 8645 is generated.
-
Roust_m (3/14/2010)
I am just wondering if there is a way to find out the amount of memory a query needs (estimated and/or real) and the time the optimizer thinks it would take to run the query, so to try to predict a potential timeout?The time-out value, required memory, requested memory, granted memory, and estimated cost can be obtained from sys.dm_exec_query_memory_grants.
The total memory grant for the whole query is also reported by the left-most node in the graphical execution plan from a real execution.
The estimated time to run the query (on a particular reference hardware platform) is the estimated cost of the whole plan, in seconds. This value is reported from both actual and estimated graphical plans.
Roust_m (3/14/2010)
Also is there a way to make a specific query to wait longer than the default period of time?No.
-
The total memory grant for the whole query is also reported by the left-most node in the graphical execution plan from a real execution.
It looks like the plan only includes the memory to cache the plan itself, not the memory needed to run the query. See the attached picture.
-
Roust_m (3/15/2010)
The total memory grant for the whole query is also reported by the left-most node in the graphical execution plan from a real execution.
It looks like the plan only includes the memory to cache the plan itself, not the memory needed to run the query. See the attached picture.
An actual plan reports the total memory grant, if memory-grant-requiring operations occurred:
-
I've tried to get the actual plan for this query:
SELECT * FROM [MyDB].[MySchema].[GetHashTable] ('SomeValue'
,1)
Which calculates hash values for 11k records from a table, but did not get the "memory_grant" line in the plan. However from sys.dm_exec_query_memory_grants it showed that the "requested_memory_kb" and "granted_memory_kb" for the query were both 5168.
So it looks like the plan does not always show the "memory_grant" unless it is a strait forward "select" from a table.
-
Roust_m (3/15/2010)
So it looks like the plan does not always show the "memory_grant" unless it is a straight-forward "select" from a table.No, that is not the case. Your statement references a table-valued user-defined function. I presume it is of the multi-statement variety, as opposed to in-line. Scalar functions and multi-statement TVFs do not appear in full in the query plan displayed by SSMS. A graphical plan captured using Profiler would show the memory grant.
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply