+ Post New Thread
Page 2 of 2 FirstFirst 12
Results 16 to 22 of 22
MIS Systems Thread, SQL Server CPUs at 100% in Technical; Oh, when you say 100% CPU use is that all CPUs? It's normal to see a spike on a single ...
  1. #16

    jinnantonnixx's Avatar
    Join Date
    Mar 2011
    Location
    In the Calamatorium.
    Posts
    1,936
    Thank Post
    109
    Thanked 471 Times in 324 Posts
    Blog Entries
    2
    Rep Power
    266
    Oh, when you say 100% CPU use is that all CPUs? It's normal to see a spike on a single processor graph, as sometimes a long-running, intensive process just can't be threaded.

  2. #17

    localzuk's Avatar
    Join Date
    Dec 2006
    Location
    Minehead
    Posts
    17,529
    Thank Post
    513
    Thanked 2,406 Times in 1,862 Posts
    Blog Entries
    24
    Rep Power
    822
    Quote Originally Posted by jinnantonnixx View Post
    SQL server will use all the RAM it can get, but it will release it on demand (up to pre-defined limits). It's very much like 'balloon' memory. More here.

    Anyway, if you're OK with SQL scripting, this little scriplet will show you a quick and dirty table of the CPU hogs on your system. It's a modification of the memory-hog script. Bear in mind it's a cumulative total - to do it properly, you'll need to dump the results to a table, run it again after a while then dump that to another table and compare the CPU total. Anyway, here it is:

    Code:
    use master
    go
     
    Select db.name as 'Database',
        spid,
        pr.status,
        cmd,
        nt_username,
        pr.loginame,
        hostname,
        program_name,
        cpu
    from sysprocesses pr (nolock)
    join sysdatabases db (nolock)
    on pr.dbid = db.dbid
    order by pr.cpu desc
    Interesting command! It shows that FMS uses a heck of a lot more CPU usage than SIMS users do.

  3. #18

    jinnantonnixx's Avatar
    Join Date
    Mar 2011
    Location
    In the Calamatorium.
    Posts
    1,936
    Thank Post
    109
    Thanked 471 Times in 324 Posts
    Blog Entries
    2
    Rep Power
    266
    Don't forget it's cumulative! If something's ticking over 24/7 it will all add up....

  4. #19

    localzuk's Avatar
    Join Date
    Dec 2006
    Location
    Minehead
    Posts
    17,529
    Thank Post
    513
    Thanked 2,406 Times in 1,862 Posts
    Blog Entries
    24
    Rep Power
    822
    Quote Originally Posted by jinnantonnixx View Post
    Don't forget it's cumulative! If something's ticking over 24/7 it will all add up....
    Indeed, but it still shows that FMS chews through a lot - we have one FMS user in today, and they've used about 20x the CPU compared with all the SIMS users logged in put together.

  5. #20

    jinnantonnixx's Avatar
    Join Date
    Mar 2011
    Location
    In the Calamatorium.
    Posts
    1,936
    Thank Post
    109
    Thanked 471 Times in 324 Posts
    Blog Entries
    2
    Rep Power
    266
    Crikey. We don't use FMS but it seems like a busy character from what you say.

  6. #21

    jinnantonnixx's Avatar
    Join Date
    Mar 2011
    Location
    In the Calamatorium.
    Posts
    1,936
    Thank Post
    109
    Thanked 471 Times in 324 Posts
    Blog Entries
    2
    Rep Power
    266
    I've written some more code which gives a snapshot of CPU-usage, ordered by user.
    It's set to snapshot over 2 minutes (change the value in the 'waitfor' line).

    It sort of works, but there's some bug which shows negative values at the end of the list. Don't know why, but I'm going home now so no time to fix it.

    Educuppa to whoever can find the bug....


    Code:
    -- SQL script to find CPU hogs in SQL server
    use master
    go
    
    -- drop temp tables if they exist
    IF OBJECT_ID('tempdb..##cpuhog1','U') IS NOT NULL DROP TABLE ##cpuhog1
    IF OBJECT_ID('tempdb..##cpuhog2','U') IS NOT NULL DROP TABLE ##cpuhog2
    
    
    -- build first table 
    Select db1.name as 'DBName',
        spid,
        pr1.status,
        cmd,
        nt_username,
        pr1.loginame,
        hostname,
        program_name,
        cpu
    into ##cpuhog1
    from sysprocesses pr1 (nolock)
    join sysdatabases db1 (nolock)
    on pr1.dbid = db1.dbid
    order by pr1.cpu desc
    
    -- wait for a period of time
    print 'Waiting for a while...'
    waitfor delay '00:02:00'	-- 'hh:mm:ss', you choose the delay
    
    
    -- build second table
    Select db2.name as 'DBName',
        spid,
        pr2.status,
        cmd,
        nt_username,
        pr2.loginame,
        hostname,
        program_name,
        cpu
    into ##cpuhog2
    from sysprocesses pr2 (nolock)
    join sysdatabases db2 (nolock)
    on pr2.dbid = db2.dbid
    order by pr2.cpu desc
    
    
    --select t1.DBName from ##cpuhog1 t1
    
    
    -- two tables made, calulate the differences between them and order by CPU
    select t1.DBName as 'Database Name',
    	t1.spid,
    	t1.status,
    	t1.cmd as 'Command',
    	t1.nt_username as 'Windows Username',
    	t1.loginame as 'SQL Login',
    	t1.hostname as 'Machine Name',
    	t1.program_name,
    	t2.cpu - t1.cpu as CPUDiff
    	from ##cpuhog1 t1
    		right outer join ##cpuhog2 t2
    		on t1.spid = t2.spid
    		order by CPUDiff desc
    		
    
    
    -- drop temp tables if they exist
    IF OBJECT_ID('tempdb..##cpuhog1','U') IS NOT NULL DROP TABLE ##cpuhog1
    IF OBJECT_ID('tempdb..##cpuhog2','U') IS NOT NULL DROP TABLE ##cpuhog2

  7. #22

    jinnantonnixx's Avatar
    Join Date
    Mar 2011
    Location
    In the Calamatorium.
    Posts
    1,936
    Thank Post
    109
    Thanked 471 Times in 324 Posts
    Blog Entries
    2
    Rep Power
    266
    Might be a fault with the join if some process die during the snapshot - hence negative values. It should still give the correct results, though.

SHARE:
+ Post New Thread
Page 2 of 2 FirstFirst 12

Similar Threads

  1. SQL Server 2008 - Automating DB Backup using the SQL Mgmt Console
    By bodminman in forum How do you do....it?
    Replies: 8
    Last Post: 24th January 2011, 02:55 PM
  2. Replies: 21
    Last Post: 18th January 2011, 12:20 PM
  3. Server at 100%!!
    By TechSupp in forum Windows
    Replies: 18
    Last Post: 10th October 2008, 09:30 AM
  4. CPU on server at 100%
    By kennysarmy in forum Windows
    Replies: 5
    Last Post: 16th March 2008, 10:55 PM
  5. 100% server side software
    By starscream in forum Windows
    Replies: 9
    Last Post: 6th June 2007, 10:09 AM

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •