Troubleshooting Variable Memory Grants In SQL Server

De Transcription | Bibliothèque patrimoniale numérique Mines ParisTech
Révision datée du 11 novembre 2025 à 19:45 par PriscillaL72 (discussion | contributions) (Page créée avec « <br>One of many extra perplexing problems to troubleshoot in SQL Server can be those related to memory grants. Some queries want extra memory than others to execute, based... »)
(diff) ← Version précédente | Voir la version actuelle (diff) | Version suivante → (diff)
Aller à : navigation, rechercher


One of many extra perplexing problems to troubleshoot in SQL Server can be those related to memory grants. Some queries want extra memory than others to execute, based on what operations should be carried out (e.g. type, hash). SQL Server’s optimizer estimates how much memory is required, and the question must acquire the memory grant so as to begin executing. It holds that grant throughout query execution - which suggests if the optimizer overestimates memory you can run into concurrency issues. If it underestimates memory, then you may see spills in tempdb. SEMAPHORE waits. There are multiple methods to attack this challenge, and one in all my new favorite strategies is to make use of Query Retailer. We are going to use a copy of WideWorldImporters that I inflated using the DataLoadSimulation.DailyProcessToCreateHistory saved procedure. The Gross sales.Orders desk has about 4.6 million rows, and the Sales.OrderLines table has about 9.2 million rows. We are going to restore the backup and enable Question Retailer, and clear out any previous Question Retailer information so we do not alter any metrics for this demo.



Store CLEAR; against your manufacturing database until you wish to take away all the pieces from Query Store. The first execution returns 1958 rows, the second returns 267,268 rows, and the final returns over 2.2 million rows. If you look on the date ranges, this isn’t stunning - the bigger the date vary the extra information being returned. As a result of this is a stored procedure, the enter parameters used initially decide the plan, as nicely because the memory to be granted. If we glance at the actual execution plan for the first execution, we see nested loops and a memory grant of 2656 KB. Subsequent executions have the same plan (as that’s what was cached) and the same memory grant, however we get a clue it’s not sufficient as a result of there’s a sort warning. If we glance in Question Store for this stored process, we see three executions and the identical values for UsedKB memory, whether we have a look at Common, Minimum, Maximum, Last, or Customary Deviation.
ask.com


Note: memory grant info in Query Store is reported because the number of 8KB pages. If we're looking for memory grant points on this situation - the place a plan is cached and re-used - Query Store won’t help us. However what if the precise question is compiled on execution, either because of a RECOMPILE hint or as a result of it’s advert-hoc? 5 there is just one execution, and the memory grant numbers match the preliminary execution - so that plan is for the small date vary. The two bigger date ranges generated the identical plan, but there’s important variability within the memory grants - 94,528 for minimal, and 573,568 for optimum. If we have a look at memory grant info using the Query Retailer studies, this variability exhibits up a bit in a different way. Opening the highest Resource Customers report from the database, MemoryWave and then altering the metric to be Memory Consumption (KB) and Avg, our query with the RECOMPILE comes to the top of the list.



On this window, metrics are aggregated by query, not plan. Right here we are capable of see that the query has two plans, and we are able to view them both in the plan summary window, but the metrics are combined for all plans in this view. The variability in memory grants is apparent when we’re wanting instantly at the views. But, we may also search based mostly on the distinction between the minimal and maximum memory grant, or a percentage of the distinction. Those of you running SQL Server 2017 with Columnstore indexes, Memory Wave who've the advantage of Memory Grant feedback, also can use this information in Query Retailer. Keep in mind that the plan in Query Store is the one that was executed, but it surely only incorporates estimates. Whereas the plan within the plan cache has memory grant info up to date when memory suggestions happens, MemoryWave this information does not get applied to the prevailing plan in Question Retailer. Here’s what I like about utilizing Question Retailer to look at queries with variable memory grants: the data is automatically being collected. If this problem shows up unexpectedly, we don’t have to place something in place to try and gather info, we have already got it captured in Query Retailer. Within the case the place a query is parameterized, it may be tougher to find memory grant variability because of the potential for static values because of plan caching. However, Memory Wave we might also discover that, as a consequence of recompilation, the question has a number of plans with extraordinarily different memory grant values that we may use to track down the issue. There are a selection of ways to analyze the issue using the data captured in Question Store, and it allows you to look at problems proactively as well as reactively.