On a SQL Server 2014 SP1 Enterprise Instance, I'm performing an outer join between two partitioned tables on a numeric(16,0) + char(1) composite key.

100 GB is the max server memory setting on a 128 GB host. Per the execution plan, the outer table is accessed with a non-clustered index of 47 GB and 2.9 billion rows, and the inner table is accessed with a non-clustered index of 36 GB and 3.9 billion rows.

I'm observing with Quest Spotlight a huge stolen page value of about 30 GB while proc cache is only about 3 GB. Is the nature of this join contributing to the stolen pages and will they age-out of the buffer cache following the same rules for least-recently-used pages or a different algorithm?

  • 3
    What is the memory grant for that query? That is likely your stolen pages...but yes, the join is probably the contributing factor – Chad Mattox May 25 '16 at 22:57
  • 2
    since you are on 2014, did you test using the old CE ? Also, sql server might pick up a bad plan due to large memory. – Kin Shah May 26 '16 at 0:20
  • Can you use resource governor to limit query memory like one shown in this article. – Shanky May 26 '16 at 3:57
  • @ChadMattox, the memory grant is 19,468,824 KB, fairly close to the stolen page number.The next largest one from an unrelated process is about 6,800,000 KB. – MattyZDBA May 26 '16 at 22:36
  • @Kin, I'm trying now with trace flag 9481 to use the old CE. Slight difference in a low-impact index recommendation (impact dropped from 8.9 to 6.7) but the plan is still dominated with the Hash Match. – MattyZDBA May 26 '16 at 22:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.