SQL Server Performance

CPU Usage high

Discussion in 'SQL Server 2005 Performance Tuning for DBAs' started by Trev256, Jan 14, 2010.

  1. Trev256 New Member

    I have spotted the CPU usage is high and sql server is using this. I have noticed there are 1000 processes in activity monitor but only 100 users (10 processes per user). What does this indicate?
    Also, if the CPU column in activity monitor reads 2079 what does this mean? Does it mean it is using 2079 Hz or 2079 MHz?
    And, what other tips do you have to troubleshoot this?
    Many thanks
  2. rohit2900 Member

    Hi.....
    [quote user="Trev256"]
    I have spotted the CPU usage is high and sql server is using this. I have noticed there are 1000 processes in activity monitor but only 100 users (10 processes per user). What does this indicate?
    [/quote]
    Are you seeing same spid 10 times or different spid.
    [quote user="Trev256"]
    Also, if the CPU column in activity monitor reads 2079 what does this mean? Does it mean it is using 2079 Hz or 2079 MHz?
    [/quote]
    The cpu field shows the cpu time.
  3. Trev256 New Member

    I'm seeing the same spid 10 times. Therefore 100 spids times 10 shows 1000 processes.
    Unfortunately this database is in compatibility mode 80. So I can't use 2005 perfomance monitoring scripts.
    Any ideas on how to troubleshoot a CPU issue?
  4. Adriaan New Member

    Same SPID showing on multiple processes? If I remember correctly, this means that these processes are running on multiple processors.
    You may be suffering from unwanted parallellism. Check the Max degree of parallellism settings (Server properties > Advanced).
  5. satya Moderator

    Multiple rows per SPID indicates parallel query operation, as it is referred above on MAXDOP. Coming to actual problem I believe this as a self-blocking display is a new feature of SP4. Before SP4, you had no way easy way to see this information even though it was happening. If you have excessive self-blocking you may want to tweak some parallelism settings.
  6. Adriaan New Member

    "There is only one processor on the server."
    How old is that server? [;)]
    Note that a multi-core processor in fact contains multiple processors. So unless you have set SQL Server to use only a single processor ...
  7. michaellutz New Member

    Yes this is correct, lowering MAXDOP for the server will likely help.
    The other and probably more important best practice is to tune your quries. It's possible you're dealing with a system that isn't properly indexed and possibly queries have not been tuned. You should identify worst performance SQL and look into establishing proper indexes and tuning your quries.
    It's easier to just reduce MAXDOP but in the log run this will only help so much. The proper thing to do is to fix indexes and correct bad SQL. SQL Server will often resot to parallel scans if no proper indexes are in place. It all depends on the nature of your queries and system.
  8. Trev256 New Member

    There is only one processor on the server. Does this mean MAXDOP is irrelevant?
    Why would there be multiple processes for the same user? Is the application programmed badly? How can this be resolved?
    For example - there are 100 users but 1000 processes in activity monitor.
  9. michaellutz New Member

    Interesting. Thanks for this additional information.
    Yes since you have 1 CPU this would make MAXDOP irrelevant (from http://msdn.microsoft.com/en-us/library/ms181007.aspx we read "If the computer has only one processor, the max degree of parallelism value is ignored."). So yes you're right this doesn't matter.
    Back to your question, "why would there be multiple proceses for the same user". I'll admit, I'm surprised you only have 1 CPU on your server given what you're seeing. That being said, it is possible (and not uncommon) for SQL Server to spawn multiple threads per query even if MAXDOP is set to 1 (or in your case Unfortnately you'll see a lot of incorrect information posted on the web about this, and even Microsoft's documentation is confusing. Take a UNION query for example. Even with MAXDOP = 1, each SELECT in the UNION can run in parallel and you'll see multiple ecid's for the spid. MAXDOP only means the degree of parallelism for a single operator within an execution plan. It doesn't control the overall parallelism of processing the query.
    If you really want me to help you solve this, I'm going to need to ask 1 favor bc/ I don't have enough information yet to give you a clear answer. 2 questions, what version of SQL Server are you running? Second thing I need is, a dump from sys.sysprocesses. I just want to take a look at exactly what you're looking at. When the problem is occurring, just run a select * from sys.processes and post it on the web if you don't mind (or feel free to email me).
    I think you're going to have to identify the SQL that is the culprit and I strongly encourage you to dig into dm_exec_query_stats. This will show you the worst performing queries with cached plans and it's possible to get the exact SQL causing the slowness. Then you can post the SQL and also you can look at the execution plan to determine what is soaking up so much CPU time.
  10. Trev256b Member

    ok - thanks - sorry for late reply - been busy! i recall sorting this by finding a bad query - although still unclear on why sql displays like this - i guess it is down to the self blocking stuff that is now displayed in SP4 - but still a little unsure! :)
  11. Jahanzaib Member

    For Disk Delays
    ===============
    select DB_NAME(database_id) DB_NAME, di.file_id,df.name,io_stall_read_ms ,num_of_reads
    ,cast(io_stall_read_ms/(1.0+num_of_reads) as numeric(10,1)) as 'avg_read_stall_ms'
    ,io_stall_write_ms,num_of_writes
    ,cast(io_stall_write_ms/(1.0+num_of_writes) as numeric(10,1)) as 'avg_write_stall_ms'
    ,io_stall_read_ms + io_stall_write_ms as io_stalls
    ,num_of_reads + num_of_writes as total_io
    ,cast((io_stall_read_ms+io_stall_write_ms)/(1.0+num_of_reads + num_of_writes) as numeric(10,1)) as 'avg_io_stall_ms'
    from sys.dm_io_virtual_file_stats(null,null) di inner join sys.database_files df on df.file_id = di.file_id
    where DB_NAME(database_id) = 'your database name'
    order by avg_io_stall_ms desc

    IO pendings
    ===========
    select
    database_id,
    file_id,
    io_stall,
    io_pending_ms_ticks,
    scheduler_address
    from sys.dm_io_virtual_file_stats(NULL, NULL)t1,
    sys.dm_io_pending_io_requests as t2
    where t1.file_handle = t2.io_handle

    For Memory Presure
    ==================
    select * from sys.dm_os_performance_counters
    where counter_name like 'page life%'

    For Queries Utilizations
    ========================
    SELECT ST.TEXT,SP.SPID,WAITTIME,LASTWAITTYPE,CPU,PHYSICAL_IO,STATUS,HOSTNAME,PROGRAM_NAME,CMD,LOGINAME FROM SYS.SYSPROCESSES SP
    CROSS APPLY SYS.DM_EXEC_SQL_TEXT(SP.SQL_HANDLE) ST
    WHERE STATUS !='SLEEPING'
    ORDER BY CPU DESC

    For CPU Usage
    =============
    select
    scheduler_id,
    current_tasks_count,
    runnable_tasks_count
    from
    sys.dm_os_schedulers
    where
    scheduler_id < 255

    For System Info
    =============
    Select * from sys.dm_os_sys_info

    provide result of these queries

Share This Page