Troubleshooting Variable Memory Grants In SQL Server

One of the more perplexing issues to troubleshoot in SQL Server could be those related to memory grants. Some queries want extra memory than others to execute, based mostly on what operations should be performed (e.g. kind, hash). SQL Server’s optimizer estimates how much memory is required, and the question should get hold of the memory grant in order to begin executing. It holds that grant during query execution - which implies if the optimizer overestimates memory you can run into concurrency points. If it underestimates memory, then you possibly can see spills in tempdb. SEMAPHORE waits. There are a number of ways to attack this challenge, and one among my new favorite methods is to make use of Query Store. We are going to use a copy of WideWorldImporters that I inflated utilizing the DataLoadSimulation.DailyProcessToCreateHistory saved procedure. The Sales.Orders desk has about 4.6 million rows, and the Sales.OrderLines desk has about 9.2 million rows. We will restore the backup and allow Question Retailer, and clear out any outdated Question Store information so we do not alter any metrics for this demo.



Store CLEAR; against your production database unless you wish to remove every little thing from Query Store. The primary execution returns 1958 rows, the second returns 267,268 rows, and the last returns over 2.2 million rows. When you look at the date ranges, this isn’t surprising - the larger the date range the extra data being returned. Because this is a stored process, the enter parameters used initially determine the plan, as properly as the memory to be granted. If we glance at the precise execution plan for the first execution, we see nested loops and a memory grant of 2656 KB. Subsequent executions have the identical 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 Query Retailer for this saved process, we see three executions and the identical values for UsedKB Memory Wave, whether we look at Common, Minimal, Most, Last, or Commonplace Deviation.
data-medics.com


Observe: memory grant info in Query Retailer is reported as the number of 8KB pages. If we're in search of memory grant points in this state of affairs - the place a plan is cached and re-used - Query Store won’t help us. But what if the specific question is compiled on execution, either due to a RECOMPILE trace or because it’s ad-hoc? 5 there is only one execution, and the memory grant numbers match the initial 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 Wave grants - 94,528 for minimal, and 573,568 for maximum. If we look at memory grant info utilizing the Question Store reviews, this variability reveals up a bit in a different way. Opening the highest Useful resource Consumers report from the database, after which altering the metric to be Memory Consumption (KB) and Avg, our query with the RECOMPILE comes to the highest of the listing.



In this window, metrics are aggregated by query, not plan. Right here we are capable of see that the query has two plans, and we can view them each in the plan abstract window, however the metrics are mixed for all plans in this view. The variability in memory grants is obvious when we’re looking instantly at the views. However, we can even search based on the difference between the minimum and most memory grant, or a percentage of the distinction. Those of you operating SQL Server 2017 with Columnstore indexes, who've the advantage of Memory Grant suggestions, can also use this information in Question Store. Do not forget that the plan in Query Retailer is the one which was executed, but it solely comprises estimates. While the plan in the plan cache has Memory Wave Method grant data updated when memory feedback happens, this information does not get utilized to the prevailing plan in Query Store. Here’s what I like about utilizing Question Retailer to look at queries with variable memory grants: the information is automatically being collected. If this problem shows up unexpectedly, we don’t have to put something in place to try and acquire info, we already have it captured in Query Retailer. In the case where a query is parameterized, it could also be tougher to seek out memory grant variability due to the potential for static values because of plan caching. Nevertheless, we might also discover that, resulting from recompilation, the question has a number of plans with extremely totally different memory grant values that we may use to track down the problem. There are a variety of the way to investigate the issue using the data captured in Query Retailer, and it allows you to look at issues proactively as well as reactively.