How do CTE memory space work? When you have complex SQL statements executing with the CTE and you get unusually large result set, can it increase the logical reads of the query?

I have a CTE that is executing 32 trillion reads, and other times it runs with 200K

closed as too localized by Mark Storey-Smith, RolandoMySQLDBA, Paul White, Mat, dezso May 29 '13 at 9:50

This question is unlikely to help any future visitors; it is only relevant to a small geographic area, a specific moment in time, or an extraordinarily narrow situation that is not generally applicable to the worldwide audience of the internet. For help making this question more broadly applicable, visit the help center. If this question can be reworded to fit the rules in the help center, please edit the question.

  • 2
    This is a recursive CTE I'm guessing? – JNK May 16 '13 at 16:51
  • 5
    Show us a sample query, please... – Jon Seigel May 17 '13 at 1:57

Here's my stab in the dark at what may be happening. With a small resultset and a sufficiently simple query plan, the query memory stolen pages (that portion of the query memory grant actually used) is under the grant size, so no spill to tempdb occurs. In this case, the logical reads are what you would expect - the query thread references to user object pages in the buffer pool. But. what if the query stolen pages reaches the query memory grant size, and there's still lots of query work left to do? Once all granted memory has been stolen for the query, the remaining "query memory" contents spill to tempdb. These bypass the usual buffer pool LRUQ and get written to tempdb right away. If the query needs them again later to finish its work, it'll read the temdb pages into the buffer pool and the query threads will incur logical reads when accessing these intermediate results. So, work similar to query memory stolen pages references (which I don't believe get counted in logical reads) becomes logical reads of tempdb contents.

Looking through sys.dm_exec_query_stats (for query read and write stats), sys.dm_exec_query_memory_grants (for query memory grant request size and used query memory), sys.dm_db_session_space_usage (cumulative tempdb for completed tasks in the session), and sys.dm_db_task_usage (tempdb usage for in-progress tasks) should make it clear whether this "maxed out stolen pages so adding tempdb logical reads to finish the query" is occurring.

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