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?