Troubleshooting Variable Memory Grants in SQL Server > 자유게시판

One of many extra perplexing problems to troubleshoot in SQL Server will be these associated to memory grants. Some queries need extra memory than others to execute, primarily based on what operations need to be carried out (e.g. sort, hash). SQL Server’s optimizer estimates how a lot memory is required, and the query should receive the memory grant in order to start out executing. It holds that grant in the course of query execution - which suggests if the optimizer overestimates memory you possibly can run into concurrency issues. If it underestimates memory, then you'll be able to see spills in tempdb. SEMAPHORE waits. There are multiple methods to assault this problem, and one of my new favorite methods is to use Query Retailer. We are going to use a replica of WideWorldImporters that I inflated utilizing the DataLoadSimulation.DailyProcessToCreateHistory stored procedure. The Sales.Orders table has about 4.6 million rows, and the Gross sales.OrderLines table has about 9.2 million rows. We are going to restore the backup and enable Question Retailer, and clear out any old Query Store information so we don't alter any metrics for this demo.

Store CLEAR; towards your manufacturing database until you want to take away the whole lot from Question Retailer. The first execution returns 1958 rows, the second returns 267,268 rows, and the final returns over 2.2 million rows. If you happen to look at the date ranges, this isn’t surprising - the larger the date vary the extra knowledge being returned. As a result of it is a stored process, the input parameters used initially decide the plan, Memory Wave as effectively because the memory to be granted. If we look at the actual execution plan for the primary 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, but we get a clue it’s not sufficient because there’s a kind warning. If we glance in Query Retailer for this stored procedure, we see three executions and the identical values for UsedKB memory, whether or not we take a look at Common, Minimum, Maximum, Final, or Commonplace Deviation.

Word: memory grant data in Query Retailer is reported as the number of 8KB pages. If we're in search of memory grant points in this situation - where a plan is cached and re-used - Query Retailer won’t assist us. However what if the precise query 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 initial execution - in order that plan is for the small date range. The two bigger date ranges generated the same plan, however there’s vital variability in the memory grants - 94,528 for minimum, and 573,568 for optimum. If we look at memory grant information utilizing the Query Retailer reviews, this variability shows up a bit in another way. Opening the highest Useful 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 checklist.

On this window, metrics are aggregated by question, not plan. Here we're in a position to see that the question has two plans, and we are able to view them both within the plan summary window, but the metrics are combined for all plans on this view. The variability in Memory Wave grants is obvious when we’re trying instantly at the views. However, we may search based on the distinction between the minimum and most memory grant, or a proportion of the difference. Those of you operating SQL Server 2017 with Columnstore indexes, who've the benefit of Memory Grant suggestions, can even use this information in Question Retailer. Do not forget that the plan in Question Store is the one which was executed, however it only incorporates estimates. While the plan within the plan cache has memory grant data up to date when memory suggestions happens, this info does not get utilized to the prevailing plan in Query Store. Here’s what I like about utilizing Query Store to look at queries with variable memory grants: the information is mechanically being collected. If this drawback shows up unexpectedly, we don’t have to put something in place to try and collect info, we have already got it captured in Question Store. Within the case the place a question is parameterized, MemoryWave it could also be more durable to find memory grant variability due to the potential for static values due to plan caching. Nevertheless, we might also discover that, resulting from recompilation, the query has a number of plans with extremely different memory grant values that we could use to trace down the issue. There are a variety of ways to analyze the issue utilizing the data captured in Query Store, and it permits you to look at problems proactively as well as reactively.