2

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?

Community
  • 1
MattyZDBA
  • 1,933
  • 3
  • 19
  • 28
  • 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 00:20
  • Can you use resource governor to limit query memory like one shown in this article. – Shanky May 26 '16 at 03: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
  • @Shanky, I'm thinking of using resource governor to limit end-users in this fashion but I'm wondering more if we should anticipate this effect when deciding how much RAM to deploy. The new host my team is considering should have 768 GB on-board so a 30 GB stolen page situation isn't as taxing (unless similar queries get run also). – MattyZDBA May 26 '16 at 22:47
  • @MattyZDBA, It's possible that adding more memory will only increase the amount of memory granted. If it asked for far more than the 19GB it was given, SQL may have only been able to give 19GB, but if you add a bunch more memory, it might get 190GB. You should also be able to see the amount of memory requested in sys.dm_exec_query_memory_grants – Chad Mattox May 26 '16 at 23:03

0 Answers0