Sql 2000 to 2005 migration results in 100% cpu when some jobs are run.

A legacy 2000 DB server was migrated to 2005.This was not "upgraded" the box was re-imaged, sql 2005 installed with 2 instances, and the databases "re-attached" to 1 of the instances...What has been done.1. Ran sp_recompile for all objects.using http://www.mssqltips.com/tip.asp?tip=1260a script was run to execute sp_recompile. 2. Rebuild indexes and update statistics.using http://www.mssqltips.com/tip.asp?tip=1606executed update statistics for all sql server databases.in order to still have the other instance of the server still usable i have had to modify the "processors" setting for the 2nd instance, all the cpus that have been marked to be used for processor affinity for the 2nd instance are at 100%the business logic for most of the complex stored procedures is no longer known., so converting physical joins to temps., rewrited procedures., etc, etc is not an option... the job that seems to cause the 100% "processes" things... from what it looks like... it checks to see if there is something to processes every x minutes by looking at some tables... currently., a trace is running, there is nothing for the job to processes (it terminates itself after running for x time.. checks that internally.. bad design)... and even though there is nothing to process, the cpu is still at 100%Im wondering if there is some sort of wait/delay/loop that it puts itself into that sql 2005 doesnt like.,but 2000 didnt mind...i can post some tsql code if needed

[quote user="satya"]
Welcome to the forums.
What is the service pack level on SQL Server 2005 instance?
Have you restarted the SQL Server serivces after the reimage?
Also what is the schedule of these optimization jobs?
[/quote]
9.00.1399.06 RTM Standard Edition
thats odd...
i was pretty sure the IT admin installed the SP's
ReleaseSqlservr.exe
RTM2005.90.1399
SQL Server 2005 Service Pack 12005.90.2047
SQL Server 2005 Service Pack 22005.90.3042
SQL Server 2005 Service Pack 32005.90.4035
shouldnt it be 2005.90.4035 ?
Box was reimaged with Windows 2003., No SQL.,
Fresh Sql 2005 install (not recovered from image)
server has been restarted multiple times..
because this is also the production box., the jobs have been stopped.. I work on the server after business hours,. so the optimization is brute force .
1. optimize
2. run job, watch cpu usage

And let me go get the It admin in trouble.. i have a signed work order stateing that SP3 was applied... i updated from "windows update" to "microsoft update" and it clearly shows SP3 needs to be installed.
fingers crossed that this will fix it.

[quote user="satya"]
What is the service pack level on SQL Server 2005 instance?
[/quote]
9.00.4035.00 SP3 Standard Edition
tried job again., still using 100% cpu..

the procedure might be recompiling each time. fire up profiler and set up recompile counters and verify.. perhaps parameter sniffing is happening...

[quote user="ndinakar"]
the procedure might be recompiling each time. fire up profiler and set up recompile counters and verify.. perhaps parameter sniffing is happening...
[/quote]
which event do i need to look at to figure out the recompile counters?
i setup the events specified in fig 3 on http://www.databasejournal.com/article.php/10888_2203601_4/
im going to look at http://www.databasejournal.com/article.php/10888_2203601_3/ and try the perfmon tool

[quote user="ndinakar"]
the procedure might be recompiling each time. fire up profiler and set up recompile counters and verify.. perhaps parameter sniffing is happening...
[/quote]
using perfmon., when the spike in processor appears., the recompile pretty much stays at 0... so recompile is not the problem., i think there is a loop in there.. or something.,

Did you change the compatibility mode to 90 after you re-attached the databases?

[quote user="ndinakar"]
Did you change the compatibility mode to 90 after you re-attached the databases?
[/quote]
compatibility has not been changed to 90... there are probably a decent # of tsql statements that might break in 90 that work in 80... this DB server and its application were created in 2003... and has not been touched since 2005

ALTER Procedure [dbo].[SomeProc]
@p_StopHour INT = 0 -- 0-23
,@p_StopMinute INT = 0 -- 0-59
AS
SET NOCOUNT ON
DECLARE @somestuf...............,
,@_SessionStart DATETIME
,@_SuccessYesNO INT
-- ,@_FlightID INT
,@_StopTime INT
,@_CurrentTime INT
,@_msg VARCHAR(128)

-- Verify Supplied Values
IF (@p_StopHour < 0) SELECT @p_StopHour = 0
IF (@p_StopHour > 23) SELECT @p_StopHour = 23
IF (@p_StopMinute < 0) SELECT @p_StopMinute = 0
IF (@p_StopMinute > 59) SELECT @p_StopMinute = 59
-- Establish the stop time
SELECT @_StopTime = (@p_StopHour*60*60) + (@p_StopMinute*60)
-- *** WAIT FOR UPDATES TO PROCESS ***
ProcessStart:
WHILE ( 1=1 )
BEGIN


-- Initiate
IF EXISTS ( SELECT 1
FROM somestuff))
)
BEGIN
BREAK
END
-- Check Stop or Continue
SELECT @_CurrentTime = (DATEPART(hh, getdate()) * 3600) + (DATEPART(mi, getdate()) * 60)
IF ( @_CurrentTime >= @_StopTime )
BEGIN
SELECT @_msg = 'File Processing complete at ' + CONVERT(varchar(30), getdate(), 100)
PRINT @_msg
RETURN 0
END
-- Wait 1 Minute
WAITFOR DELAY '00:05:00'
END
the above is a snip of the procedure start... Not only is this bad design... but i have a feelingthat its contributing to the 100% cpu?? is that possible?

over the weekend i was able to resolve the issue...
it came down to a nested select top 1 ........ query that had no purpose there... AND it had a special condition which decided if it should include it or not... probably the lamest piece of code i have yet to see... anyway., i was able to test that this was the statement that was causing the 10 minute execution by executing just the select top 1 .... query... then captured that in a trace, use database tuning with the trace and got a suggestion for an index... created the index and it went from 10+ minutes to <1 second...
it was not an issue in sql 2000 but surely was in sql 2005,. anyway,. its fixed., thank you all for your suggestions.

p.s. NOT limiting by TOP N wouldnt cause the 10+ minute processing time...
so if the followin was used
select * from ....... it had no issues...
it was the
select top N * from .... that was causing the issue..
one again an extra index solved the issue with sql 2005