SQL Server Performance

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

Discussion in 'SQL Server 2005 General DBA Questions' started by zxed, Jul 8, 2009.

  1. zxed New Member

    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
  2. satya Moderator

    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?
  3. zxed New Member

    [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.
  4. zxed New Member

    [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..
  5. ndinakar Member

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

  7. satya Moderator

  8. zxed New Member

    [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.,
  9. ndinakar Member

    Did you change the compatibility mode to 90 after you re-attached the databases?
  10. zxed New Member

    [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 :)
  11. zxed New Member

    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?
  12. zxed New Member

    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.
  13. zxed New Member

    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

Share This Page