Sql-server – query processor ran out of internal resources and could not produce a query plan

There are three DMVs you can use to track tempdb usage:

• sys.dm_db_task_space_usage
• sys.dm_db_session_space_usage
• sys.dm_db_file_space_usage

The first two will allow you to track allocations at a query & session level. The third tracks allocations across version store, user and internal objects.

The following example query will give you allocations per session:

SELECT
  sys.dm_exec_sessions.session_id AS [SESSION ID]
  ,DB_NAME(database_id) AS [DATABASE Name]
  ,HOST_NAME AS [System Name]
  ,program_name AS [Program Name]
  ,login_name AS [USER Name]
  ,status
  ,cpu_time AS [CPU TIME (in milisec)]
  ,total_scheduled_time AS [Total Scheduled TIME (in milisec)]
  ,total_elapsed_time AS    [Elapsed TIME (in milisec)]
  ,(memory_usage * 8)      AS [Memory USAGE (in KB)]
  ,(user_objects_alloc_page_count * 8) AS [SPACE Allocated FOR USER Objects (in KB)]
  ,(user_objects_dealloc_page_count * 8) AS [SPACE Deallocated FOR USER Objects (in KB)]
  ,(internal_objects_alloc_page_count * 8) AS [SPACE Allocated FOR Internal Objects (in KB)]
  ,(internal_objects_dealloc_page_count * 8) AS [SPACE Deallocated FOR Internal Objects (in KB)]
  ,CASE is_user_process
             WHEN 1      THEN 'user session'
             WHEN 0      THEN 'system session'
  END         AS [SESSION Type], row_count AS [ROW COUNT]
FROM 
  sys.dm_db_session_space_usage
INNER join
  sys.dm_exec_sessions
ON  sys.dm_db_session_space_usage.session_id = sys.dm_exec_sessions.session_id

If you want to track usage over a period of time, consider collecting data with sp_whoisactive, as demonstrated by Kendra Little.

Thanks to mrdenny! Your commment pointed me in the right direction!

I've pasted the query into SQL Management Studio

Then used the option Display Estimated Execution Plan

This gave me an output that an index in one of the tables belonging to the view needed to be recreated, so I deleted it and create it from scratch using the suggested output.

CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]
ON [dbo].[MemberOrders] ([Client_Id],[Project_Id],[Person_Id])

Now the query takes only 2 seconds and not the 1:30min that it used to take!

Thanks a lot guys