One of many more perplexing issues to troubleshoot in SQL Server will be those associated to Memory Wave Protocol grants. Some queries want extra memory than others to execute, based mostly on what operations have to be performed (e.g. type, hash). SQL Server’s optimizer estimates how much memory is required, and the query must obtain the memory grant so as to begin executing. It holds that grant throughout question execution - which implies if the optimizer overestimates memory you may run into concurrency issues. If it underestimates memory, then you'll be able to see spills in tempdb. SEMAPHORE waits. There are a number of ways to attack this difficulty, and certainly one of my new favorite strategies is to use Question Retailer. We'll use a duplicate of WideWorldImporters that I inflated utilizing the DataLoadSimulation.DailyProcessToCreateHistory stored procedure. The Gross sales.Orders desk has about 4.6 million rows, and the Gross sales.OrderLines desk has about 9.2 million rows. We are going to restore the backup and allow Query Store, and clear out any previous Question Store data so we do not alter any metrics for this demo.

Store CLEAR; against your manufacturing database unless you wish to take away every little thing from Question Retailer. The first execution returns 1958 rows, the second returns 267,268 rows, and the last returns over 2.2 million rows. Should you look on the date ranges, this isn’t stunning - the larger the date vary the more data being returned. Because this can be a saved process, the enter parameters used initially determine the plan, as effectively 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 type warning. If we glance in Question Store for this stored procedure, we see three executions and the same values for UsedKB memory, whether or not we have a look at Average, Minimal, Most, Final, or Customary Deviation.
jambot.com

Be aware: memory grant data in Query Retailer is reported as the variety of 8KB pages. If we are searching for memory grant points in this state of affairs - the place a plan is cached and re-used - Query Retailer won’t help us. But what if the particular question is compiled on execution, both due to a RECOMPILE trace or as a result of it’s advert-hoc? 5 there is only one execution, and the memory grant numbers match the preliminary execution - so that plan is for the small date vary. The 2 larger date ranges generated the same plan, but there’s important variability in the memory grants - 94,528 for minimal, Memory Wave and 573,568 for maximum. If we have a look at memory grant info utilizing the Query Retailer experiences, this variability shows up a bit in a different way. Opening the top Useful resource Customers report from the database, and then changing the metric to be Memory Consumption (KB) and Avg, our query with the RECOMPILE involves the highest of the listing.

In this window, metrics are aggregated by query, not plan. Right here we are capable of see that the question has two plans, and we can view them each within the plan abstract window, however the metrics are mixed for all plans on this view. The variability in memory grants is apparent when we’re looking instantly on the views. However, we can also search based mostly on the distinction between the minimal and most memory grant, or a share of the difference. These of you working SQL Server 2017 with Columnstore indexes, who've the advantage of Memory Grant suggestions, may use this info in Question Retailer. Remember that the plan in Query Retailer is the one which was executed, but it only comprises estimates. Whereas the plan in the plan cache has memory grant information up to date when memory feedback occurs, this info doesn't get applied to the present plan in Question Retailer. Here’s what I like about utilizing Query Store to have a look at queries with variable memory grants: the data is automatically being collected. If this downside shows up unexpectedly, we don’t have to place anything in place to try and acquire data, Memory Wave Protocol we already have it captured in Query Store. In the case the place a question is parameterized, it may be more durable to find memory grant variability because of the potential for static values due to plan caching. Nevertheless, we may also uncover that, as a consequence of recompilation, the question has multiple plans with extraordinarily completely different memory grant values that we may use to track down the issue. There are a variety of the way to investigate the problem using the information captured in Question Retailer, and it permits you to look at problems proactively in addition to reactively.