Link to home
Start Free TrialLog in
Avatar of Clifton Bardwell
Clifton BardwellFlag for United States of America

asked on

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
Avatar of Scott Pletcher
Scott Pletcher
Flag of United States of America image

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.
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, ...).
Avatar of Clifton Bardwell

ASKER

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

ASKER CERTIFIED SOLUTION
Avatar of Scott Pletcher
Scott Pletcher
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Perfect!

Thanks,
Clif Bardwell