3

I hope the title of the question is clear. I am a DB developer as one of my job roles. I recently asked a question regarding index statistics and I have spent a lot of time reading about statistics, cardinality estimates, and SQL Server memory management since.

I talked with my team lead and senior team lead about my findings and showed data from tests I had done where queries ran better when a larger sample size was used on certain indices. As our primary role is software development and no one on my team knows much about database administration it was a very enlightening discussion. They took the findings to the CIO who asked that I schedule a meeting with the entire DBA team, himself, and all the IT senior team leads.

Within 5 minutes of scheduling the meeting, the team lead of the DBA team was at my desk and not in a good mood. He was semi hostile and I explained to him that this was meant to only be a discussion and I didn't want to step on toes. During the course of this conversation he made a comment that Memory Grant sizes get reused from one execution to the next for executions that followed a cached plan. I had not read anything about that in the 20 or so different websites I read about memory grant.

I wondered if it was in fact true and I am starting to have doubts in the abilities of this TL after another two comments in the same conversation. The first comment he made was that his predecessor had created a complex script for handling the thresholds and sample sizes and that he had never made any changes to them. He has been the TL for 2.5 years now. The second comment he made 3 times was that setting IX_myTable with a sample size of 5 or 10 percent would "take forever to run". I finally replied that I had tested that and it only took 2 seconds. He was taken back by my reply and I followed up with by saying that a much larger table's index only took 25 seconds to run a 5% sample size (this table currently has a 0.2% sample size). I showed him my documented tests that showed improved performance and smaller memory grants and even fully disclosed that in my findings certain indices at certain sample sizes no longer yielded improved cardinality estimates.

I have tried searching on his claims about the memory grant size but I have turned up nothing. Can anyone elaborate?

Improve this question

1 Answer 1

Reset to default
4

The team lead is correct in the statement about memory grants given the version of SQL Server you're using (SQL Server 2014, based on the tags here). They are part of the cached execution plan, and the values are re-used across executions as long as the cached plan is in place.

Improvements have been made in this area on newer versions of SQL Server:

These improvements allow the cached plan to be updated based on runtime feedback (how much of the memory grant was used on the previous execution).

I can't find a specific source in the documentation that confirms memory grants are reused as part of the cached plan, but hopefully it's implied well enough from the memory grant feedback improvements that have been introduced in newer versions.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.