The Query Processor Ran Out Of Internal Resources...

This is for MS SQL Server 2005

The stored procedure has been running fine for over a year (it was written by my predecessor), but suddently today I get the following error when trying to run it:

Msg 8623, Level 16, State 1, Line 1
The query processor ran out of internal resources and could not produce a query plan. This is a rare event and only expected for extremely complex queries or queries that reference a very large number of tables or partitions. Please simplify the query. If you believe you have received this message in error, contact Customer Support Services for more information.

It certainly is a complex query.  It's five separate main queries unioned together.  Each of those queries contains 12 subqueries.  (As I said, I didn't write it)

I can run each of the main queries separately, I can even run four of the five together with no issue, but running all five throws the error.

As I said, it has been running fine since I have been here.  I even modified it a few months ago and it was running when I released it (duh!).

So, what do I look at that might have changed over the weekend that is now causing it to fail?

TIA
Clifton BardwellProgrammerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott PletcherSenior DBACommented:
Who knows why that error is showing up now.  But if the query is that bad, you should likely review and re-write the parts of the query anyway.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Looks like is time to review the query as ScottPletcher mentioned and also review your server configuration. Maybe an upgrade is needed (new Server, new Windows version, new SQL Server version, new storage, new architecture, ...).
0
Clifton BardwellProgrammerAuthor Commented:
Can't upgrade the database.

As far as reviewing the query, I really don't know where to begin...

Here's the code for one row (there are four more similar that are JOINed)  How would I simplify this:
SELECT 
	'Freshman' AS 'EnrollmentStatus',
	(	SELECT 
			COUNT(DISTINCT StudentID) 
		FROM 
			CAMS_StudentBYORMerge_View v
			LEFT OUTER JOIN dbo.CAMS_StudentBYORDemog_Rpt_View r 
				ON v.studentuid = r.studentuid
		WHERE 
			ExpectedEntryTerm = @TextTerm 
			AND 
			AddressType = 'Home' 
			AND 
			EnrollmentStatus = 'New Freshman'
	) AS 'Applicants',
	(	SELECT 
			COUNT(DISTINCT StudentID) 
		FROM 
			CAMS_StudentBYORMerge_View v
			LEFT OUTER JOIN dbo.CAMS_StudentBYORDemog_Rpt_View r 
				ON v.studentuid = r.studentuid
		WHERE 
			ExpectedEntryTerm = @TextTerm 
			AND 
			AddressType = 'Home' 
			AND 
			EnrollmentStatus = 'New Freshman' 
--			AND 
--			StudentType = 'ADMITTED' 
			AND 
			ProspectStatus IN (	'Conditional Admit',
								'Conditional Admit - Cleared',
								'Regular Admit',
								'Regular Admit - Cleared',
								'Tentative Admit',
								'Provisional',
								'Provisional',
								'Regular Provisional')
	) AS 'Accepted',
	(	SELECT 
			COUNT(DISTINCT StudentID) 
		FROM 
			CAMS_StudentBYORMerge_View v
			LEFT OUTER JOIN dbo.CAMS_StudentBYORDemog_Rpt_View r 
				ON v.studentuid = r.studentuid
		WHERE 
			ExpectedEntryTerm = @TextTerm 
			AND 
			AddressType = 'Home' 
			AND 
			EnrollmentStatus = 'New Freshman' 
			and 
			ProspectStatus IN (	'Will NOT attend')
	) AS 'Will Not Attend',
	(	SELECT 
			COUNT(DISTINCT StudentID) 
		FROM 
			CAMS_StudentBYORMerge_View v
			LEFT OUTER JOIN dbo.CAMS_StudentBYORDemog_Rpt_View r 
				ON v.studentuid = r.studentuid
		WHERE 
			ExpectedEntryTerm = @TextTerm 
			AND 
			AddressType = 'Home' 
			AND 
			EnrollmentStatus = 'New Freshman' 
			and 
			ProspectStatus IN (	'Denied')
	) AS 'Denied',
	(	SELECT 
			COUNT(DISTINCT StudentID) 
		FROM 
			CAMS_StudentBYORMerge_View v
			LEFT OUTER JOIN dbo.CAMS_StudentBYORDemog_Rpt_View r 
				ON v.studentuid = r.studentuid
		WHERE 
			ExpectedEntryTerm = @TextTerm 
			AND 
			AddressType = 'Home' 
			AND 
			EnrollmentStatus = 'New Freshman' 
			and 
			ProspectStatus IN (	'Confirmed')
	) AS 'Confirmed',
	(	SELECT 
			COUNT(DISTINCT StudentID) 
		FROM 
			CAMS_StudentBYORMerge_View v
			LEFT OUTER JOIN dbo.CAMS_StudentBYORDemog_Rpt_View r 
			ON v.studentuid = r.studentuid
		WHERE 
			ExpectedEntryTerm = @TextTerm 
			AND 
			AddressType = 'Home' 
			AND 
			EnrollmentStatus = 'New Freshman' 
			and 
			ProspectStatus IN (	'Pending')
--			and 
--			StudentType = 'APPLICANT'
	) AS 'Pending',
	(	SELECT 
			COUNT(DISTINCT StudentID) 
		FROM 
			CAMS_StudentBYORMerge_View v
			LEFT OUTER JOIN dbo.CAMS_StudentBYORDemog_Rpt_View r 
				ON v.studentuid = r.studentuid
		WHERE 
			ExpectedEntryTerm = @TextTerm 
			AND 
			AddressType = 'Home' 
			AND 
			EnrollmentStatus = 'New Freshman' 
			and 
			ProspectStatus IN (	'Regular Admit',
								'Regular Admit - Cleared')
	) AS 'RegAdmit',
	(	SELECT 
			COUNT(DISTINCT StudentID) 
		FROM 
			CAMS_StudentBYORMerge_View v
			LEFT OUTER JOIN dbo.CAMS_StudentBYORDemog_Rpt_View r 
			ON v.studentuid = r.studentuid
		WHERE 
			ExpectedEntryTerm = @TextTerm 
			AND 
			AddressType = 'Home' 
			AND 
			EnrollmentStatus = 'New Freshman' 
			and 
			ProspectStatus IN (	'Regular Provisional')
	) AS 'Regular Provisional',
	(	SELECT 
			COUNT(DISTINCT StudentID) 
		FROM 
			CAMS_StudentBYORMerge_View v
			LEFT OUTER JOIN dbo.CAMS_StudentBYORDemog_Rpt_View r 
			ON v.studentuid = r.studentuid
		WHERE 
			ExpectedEntryTerm = @TextTerm 
			AND 
			AddressType = 'Home' 
			AND 
			EnrollmentStatus = 'New Freshman' 
			and 
			ProspectStatus IN (	'Provisional')
	) AS 'Provisional',
	(	SELECT 
			COUNT(DISTINCT StudentID) 
		FROM 
			CAMS_StudentBYORMerge_View v
			LEFT OUTER JOIN dbo.CAMS_StudentBYORDemog_Rpt_View r 
				ON v.studentuid = r.studentuid
		WHERE 
			ExpectedEntryTerm = @TextTerm 
			AND 
			AddressType = 'Home' 
			AND 
			EnrollmentStatus = 'New Freshman' 
			and 
			ProspectStatus IN (	'Tentative Admit')
	) AS 'TentativeAdmit',
	(	SELECT 
			COUNT(DISTINCT StudentID) 
		FROM 
			CAMS_StudentBYORMerge_View v
			LEFT OUTER JOIN dbo.CAMS_StudentBYORDemog_Rpt_View r 
				ON v.studentuid = r.studentuid
		WHERE 
			ExpectedEntryTerm = @TextTerm 
			AND 
			AddressType = 'Home' 
			AND EnrollmentStatus = 'New Freshman' 
			and 
			ProspectStatus IN (	'Conditional Admit',
								'Conditional Admit - Cleared')
	) AS 'ConditionalAdmit',
	(	SELECT 
			COUNT(DISTINCT StudentID) 
		FROM 
			CAMS_StudentBYORMerge_View v
			LEFT OUTER JOIN dbo.CAMS_StudentBYORDemog_Rpt_View r 
				ON v.studentuid = r.studentuid
		WHERE 
			ExpectedEntryTerm = @TextTerm 
			AND 
			AddressType = 'Home' 
			AND 
			EnrollmentStatus = 'New Freshman' 
			and 
			ProspectStatus IN (	'Waitlist Hold')
	) AS 'WaitlistHold'

Open in new window

0
Scott PletcherSenior DBACommented:
This should reduce the compile load, and the run time, considerably, as its only a single pass of the joined views instead of many:

SELECT
      'Freshman' AS 'EnrollmentStatus',
      COUNT(DISTINCT StudentID) AS 'Applicants',
      COUNT(DISTINCT CASE WHEN
              StudentType = 'ADMITTED'
                  AND
                  ProspectStatus IN (      'Conditional Admit',
                                                'Conditional Admit - Cleared',
                                                'Regular Admit',
                                                'Regular Admit - Cleared',
                                                'Tentative Admit',
                                                'Provisional',
                                                'Provisional',
                                                'Regular Provisional' )
                  THEN StudentID END) AS 'Accepted',
      COUNT(DISTINCT CASE WHEN
                  ProspectStatus IN ( 'Will NOT attend' )
                  THEN StudentID END) AS 'Will Not Attend',
      COUNT(DISTINCT CASE WHEN
              ProspectStatus IN (      'Denied' )
              THEN StudentID END) AS 'Denied',
      COUNT(DISTINCT CASE WHEN             
                  ProspectStatus IN (      'Confirmed' )
              THEN StudentID END) AS 'Confirmed',
      COUNT(DISTINCT CASE WHEN
                  ProspectStatus IN (      'Pending' )
--                  and
--                  StudentType = 'APPLICANT'
            THEN StudentID END) AS 'Pending',
      COUNT(DISTINCT CASE WHEN
                  ProspectStatus IN (      'Regular Admit',
                                                'Regular Admit - Cleared' )
                  THEN StudentID END) AS 'RegAdmit',
      COUNT(DISTINCT CASE WHEN
                  ProspectStatus IN (      'Regular Provisional' )
                  THEN StudentID END) AS 'Regular Provisional',
      COUNT(DISTINCT CASE WHEN
                  ProspectStatus IN (      'Provisional' )
                  THEN StudentID END) AS 'Provisional',
      COUNT(DISTINCT CASE WHEN
                  ProspectStatus IN (      'Tentative Admit' )
                  THEN StudentID END) AS 'TentativeAdmit',
      COUNT(DISTINCT CASE WHEN
                  ProspectStatus IN (      'Conditional Admit',
                                                'Conditional Admit - Cleared' )
                THEN StudentID END) AS 'ConditionalAdmit',
      COUNT(DISTINCT CASE WHEN
                  ProspectStatus IN (      'Waitlist Hold' )
                  THEN StudentID END) AS 'WaitlistHold'
FROM
      CAMS_StudentBYORMerge_View v
      LEFT OUTER JOIN dbo.CAMS_StudentBYORDemog_Rpt_View r
            ON v.studentuid = r.studentuid
WHERE
      ExpectedEntryTerm = @TextTerm
      AND
      AddressType = 'Home'
      AND
      EnrollmentStatus = 'New Freshman'
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Clifton BardwellProgrammerAuthor Commented:
Perfect!

Thanks,
Clif Bardwell
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.