locked
Processor RRS feed

  • Question

  • How should I know if I need to add new processor to my Server?

     

    During Submission of our Records every 24th day of the month the cpu usage of the server is steady 100% can you please help me what alternative can I do? or how can i check if need to add new processor.

     

    Please help me guys.

    thanks

    Tuesday, July 24, 2007 11:01 AM

Answers

  • Any sustained CPU usage exceeding 90% indicates a need for more 'processing' power.

     

    If this ONLY occurs on the 24th day of the month, and the remainder of the time CPU utilization is lower, you have a couple of options.

     

    Change the workload on the 24th, spread it out into several smaller 'batches'

    OR

    Get additional processor power (add one or more CPU(s)).

    Tuesday, July 24, 2007 5:58 PM
  • You should also try to investigate what part of your workload is using the most CPU and see if you can do something about it. For example, you might be missing an index for a frequently run query that is causing more CPU pressure (among other things).

     

    -- Get the most CPU intensive queries

    SET NOCOUNT ON;

    DECLARE @SpID smallint

    DECLARE spID_Cursor CURSOR

    FORWARD_ONLY READ_ONLY FOR

    SELECT TOP 25 spid

    FROM master..sysprocesses

    WHERE status = 'runnable'

    AND spid > 50 -- Eliminate system SPIDs

    AND spid <> 102 -- Replace with your SPID

    ORDER BY CPU DESC

    OPEN spID_Cursor

    FETCH NEXT FROM spID_Cursor

    INTO @spID

    WHILE @@FETCH_STATUS = 0

    BEGIN

    PRINT 'Spid #: ' + STR(@spID)

    EXEC ('DBCC INPUTBUFFER (' + @spID + ')')

    FETCH NEXT FROM spID_Cursor

    INTO @spID

    END

    -- Close and deallocate the cursor

    CLOSE spID_Cursor

    DEALLOCATE spID_Cursor

     

    -- Get Top 50 executed SP's ordered by avg worker time

    SELECT TOP 50 qt.text AS 'SP Name', qs.execution_count AS 'Execution Count', ISNULL(qs.total_elapsed_time/qs.execution_count, 0) AS 'AvgElapsedTime',

    qs.total_worker_time/qs.execution_count AS 'AvgWorkerTime',

    qs.total_worker_time AS 'TotalWorkerTime',

    qs.max_logical_reads, qs.max_logical_writes, qs.creation_time,

    DATEDIFF(Minute, qs.creation_time, GetDate()) AS Age,

    ISNULL(qs.execution_count/DATEDIFF(Second, qs.creation_time, GetDate()), 0) AS 'Calls/Second'

    FROM sys.dm_exec_query_stats AS qs

    CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt

    --WHERE qt.dbid = 5 -- Filter by database

    ORDER BY qs.total_worker_time/qs.execution_count DESC

     

    -- Missing Indexes

    SELECT user_seeks * avg_total_user_cost * (avg_user_impact * 0.01) AS index_advantage,

    migs.*, mid.*

    FROM sys.dm_db_missing_index_group_stats AS migs

    INNER JOIN sys.dm_db_missing_index_groups AS mig

    ON migs.group_handle = mig.index_group_handle

    INNER JOIN sys.dm_db_missing_index_details AS mid

    ON mig.index_handle = mid.index_handle

    --WHERE statement = '[ngservices].[dbo].[UserFeedUnreadCountRollup]' -- Specify one table

    ORDER BY index_advantage DESC;

    Wednesday, July 25, 2007 7:50 PM

All replies

  • Any sustained CPU usage exceeding 90% indicates a need for more 'processing' power.

     

    If this ONLY occurs on the 24th day of the month, and the remainder of the time CPU utilization is lower, you have a couple of options.

     

    Change the workload on the 24th, spread it out into several smaller 'batches'

    OR

    Get additional processor power (add one or more CPU(s)).

    Tuesday, July 24, 2007 5:58 PM
  • You should also try to investigate what part of your workload is using the most CPU and see if you can do something about it. For example, you might be missing an index for a frequently run query that is causing more CPU pressure (among other things).

     

    -- Get the most CPU intensive queries

    SET NOCOUNT ON;

    DECLARE @SpID smallint

    DECLARE spID_Cursor CURSOR

    FORWARD_ONLY READ_ONLY FOR

    SELECT TOP 25 spid

    FROM master..sysprocesses

    WHERE status = 'runnable'

    AND spid > 50 -- Eliminate system SPIDs

    AND spid <> 102 -- Replace with your SPID

    ORDER BY CPU DESC

    OPEN spID_Cursor

    FETCH NEXT FROM spID_Cursor

    INTO @spID

    WHILE @@FETCH_STATUS = 0

    BEGIN

    PRINT 'Spid #: ' + STR(@spID)

    EXEC ('DBCC INPUTBUFFER (' + @spID + ')')

    FETCH NEXT FROM spID_Cursor

    INTO @spID

    END

    -- Close and deallocate the cursor

    CLOSE spID_Cursor

    DEALLOCATE spID_Cursor

     

    -- Get Top 50 executed SP's ordered by avg worker time

    SELECT TOP 50 qt.text AS 'SP Name', qs.execution_count AS 'Execution Count', ISNULL(qs.total_elapsed_time/qs.execution_count, 0) AS 'AvgElapsedTime',

    qs.total_worker_time/qs.execution_count AS 'AvgWorkerTime',

    qs.total_worker_time AS 'TotalWorkerTime',

    qs.max_logical_reads, qs.max_logical_writes, qs.creation_time,

    DATEDIFF(Minute, qs.creation_time, GetDate()) AS Age,

    ISNULL(qs.execution_count/DATEDIFF(Second, qs.creation_time, GetDate()), 0) AS 'Calls/Second'

    FROM sys.dm_exec_query_stats AS qs

    CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt

    --WHERE qt.dbid = 5 -- Filter by database

    ORDER BY qs.total_worker_time/qs.execution_count DESC

     

    -- Missing Indexes

    SELECT user_seeks * avg_total_user_cost * (avg_user_impact * 0.01) AS index_advantage,

    migs.*, mid.*

    FROM sys.dm_db_missing_index_group_stats AS migs

    INNER JOIN sys.dm_db_missing_index_groups AS mig

    ON migs.group_handle = mig.index_group_handle

    INNER JOIN sys.dm_db_missing_index_details AS mid

    ON mig.index_handle = mid.index_handle

    --WHERE statement = '[ngservices].[dbo].[UserFeedUnreadCountRollup]' -- Specify one table

    ORDER BY index_advantage DESC;

    Wednesday, July 25, 2007 7:50 PM
  • Yeah, I guest we need a  new server after all . . . this is really makes me difficult . . thanks for the INFO Smile

    Thursday, August 2, 2007 2:53 PM
  •  

    Hey, I might try to use this script . . . I'll keep you update . . . thanks a lot
    Thursday, August 2, 2007 2:54 PM