When a query is parsed and compiled initially, it will consume compile or optimizer memory. Once the query is compiled that memory is released and the resulting query plan must be stored in cache. For that, the plan will consume plan cache memory and will stay in the cache until Microsoft SQL Azure Database Server is restarted or memory pressure occurs. Once a plan is cached, the query is ready for execution. If the query happens to be performing any sort operations or hash match (join or aggregates), then it will first reserve and later be granted to use part, or all, of the reserved memory for sort results or hash buckets. These memory operations during the execution of a query are collectively termed as Memory Grants, QE Reservations, Execution Memory, Workspace memory and Memory Reservations.
When the Microsoft SQL Azure Database Server grants the requested memory to an executing query it is said that a memory grant has occurred. If any delays are noticed in granting the requested memory, then the execution time of the query too may increase considerably. To avoid such unnecessary delays and to proactively detect such delays, it is essential for the administrators to keep a constant vigil on the memory grants of the database server. The SQL Azure Memory Grants test helps administrators in this regard!
This test helps administrators figure out the maximum wait time for which the query should wait for the requested memory and the maximum amount of memory consumed by a query for execution. Besides, this test also throws light on the count of queries that are still waiting for the requested memory for execution. The detailed diagnostics lists the queries that are taking longer than usual time for execution.
Target of the test : A Microsoft SQL Azure database server
Agent deploying the test : An external/remote agent
Outputs of the test : One set of results for the target Microsoft SQL Azure database server being monitored.
|
Measurement | Description | Measurement Unit | Interpretation |
---|---|---|---|
Maximum memory consumed by query |
Indicates the maximum amount of memory consumed by a query executing on this database. |
KB |
|
Queries waiting for memory |
Indicates the number of queries waiting for memory in this database. |
Number |
|
Maximum query cost by memory |
Indicates the maximum query cost based on memory of this database. |
Seconds |
A high value for this measure is a cause of concern. |
Maximum wait duration for memory |
Indicates the maximum time for which the query had to wait for memory in this database. |
Seconds |
A high value for this measure is a cause of concern as this may sometimes indicate that the database is inaccessible. |