Processor

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

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)).

|||

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;

|||

Yeah, I guest we need a new server after all . . . this is really makes me difficult . . thanks for the INFO

|||

Hey, I might try to use this script . . . I'll keep you update . . . thanks a lot