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?
