연락처 : MP 이메일 : odellpainter@gmail.com

One of many more perplexing problems to troubleshoot in SQL Server might be these associated to Memory Wave Experience grants. Some queries need extra memory than others to execute, based on what operations need to be performed (e.g. kind, hash). SQL Server’s optimizer estimates how much memory is required, and the question should obtain the memory grant so as to start executing. It holds that grant in the course of question execution - which suggests if the optimizer overestimates memory you'll be able to run into concurrency issues. If it underestimates memory, then you possibly can see spills in tempdb. SEMAPHORE waits. There are a number of methods to attack this concern, and one in all my new favorite strategies is to make use of Question Retailer. We will use a copy of WideWorldImporters that I inflated utilizing the DataLoadSimulation.DailyProcessToCreateHistory stored procedure. The Sales.Orders table 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 allow Question Retailer, and clear out any old Question Store knowledge so we do not alter any metrics for this demo.

Store CLEAR; against your manufacturing database except you need to take away every part from Question Retailer. The primary execution returns 1958 rows, the second returns 267,268 rows, and the last returns over 2.2 million rows. In case you look at the date ranges, this isn’t surprising - the larger the date range the extra data being returned. Because it is a saved procedure, the enter parameters used initially determine the plan, as effectively because 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 identical memory grant, however we get a clue it’s not enough as a result of there’s a kind warning. If we glance in Query Retailer for this saved process, we see three executions and the identical values for UsedKB memory, whether or not we take a look at Average, Minimum, Most, Last, or Standard Deviation.

Word: memory grant information in Query Retailer is reported as the number of 8KB pages. If we are in search of memory grant points on this situation - the place a plan is cached and re-used - Question Store won’t help us. But what if the precise query is compiled on execution, either because of a RECOMPILE hint or as a result of it’s ad-hoc? 5 there is just 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 same plan, however there’s important variability in the memory grants - 94,528 for minimal, and 573,568 for optimum. If we look at memory grant data utilizing the Query Retailer studies, this variability shows up a bit in a different way. Opening the top Resource Customers report from the database, and then altering the metric to be Memory Consumption (KB) and Avg, our question with the RECOMPILE involves the highest of the record.

In this window, metrics are aggregated by question, not plan. Right here we're in a position to see that the query has two plans, and we will view them both within the plan summary window, but the metrics are combined for all plans on this view. The variability in memory grants is obvious when we’re looking immediately on the views. However, we can also search based mostly on the difference between the minimum and most memory grant, or a share of the difference. Those of you running SQL Server 2017 with Columnstore indexes, who have the benefit of Memory Wave Grant feedback, can also use this data in Query Retailer. Keep in mind that the plan in Question Store is the one that was executed, but it solely contains estimates. While the plan in the plan cache has memory grant data up to date when memory suggestions occurs, this information does not get utilized to the present plan in Query Retailer. Here’s what I like about using Query Retailer to have a look at queries with variable memory grants: the data is robotically being collected. If this downside exhibits up unexpectedly, we don’t have to put anything in place to try to collect data, we already have it captured in Query Store. Within the case the place a query is parameterized, it may be more durable to search out memory grant variability because of the potential for static values due to plan caching. However, we might also uncover that, resulting from recompilation, the question has a number of plans with extremely completely different memory grant values that we could use to trace down the issue. There are a selection of the way to research the problem using the information captured in Query Store, and it allows you to take a look at issues proactively in addition to reactively.