+ Post New Thread
Page 2 of 2 FirstFirst 12
Results 16 to 22 of 22
MIS Systems Thread, [SIMS / SQL2008] Show us your Perfmon! in Technical; LEA are passing this onto capita support, so fingers crossed will get a phonecall soon....
  1. #16

    Join Date
    Jul 2009
    Posts
    272
    Thank Post
    6
    Thanked 43 Times in 37 Posts
    Rep Power
    16
    LEA are passing this onto capita support, so fingers crossed will get a phonecall soon.

  2. #17

    Join Date
    Jul 2009
    Posts
    272
    Thank Post
    6
    Thanked 43 Times in 37 Posts
    Rep Power
    16
    you are going to think im mad but i have been watching SQL actmon all day, and running the script to show who's taking upall the CPU.
    I have been ringing users straight away and ask what they where doing in SIMS at the time and they all say the same thing :- "sims is just open and i havn't touched it for a while" ... so is it possible there some kind of sleep function in sims making my CPU max out?
    see pic for latest result
    sims waiting.jpg

  3. #18
    pubgrub277's Avatar
    Join Date
    Jul 2010
    Location
    Newcastle
    Posts
    84
    Thank Post
    4
    Thanked 17 Times in 10 Posts
    Rep Power
    17
    TaskMGr.png

    We've notice a small improvement since we replaced our old dell box with a new machine. the 12gig of Ram has certainly helped.

  4. #19

    jinnantonnixx's Avatar
    Join Date
    Mar 2011
    Location
    In the Calamatorium.
    Posts
    1,974
    Thank Post
    113
    Thanked 493 Times in 337 Posts
    Blog Entries
    2
    Rep Power
    284
    I had a look at why some columns show negative CPU results and it's foxing me.
    I've modified the code, as in the first version I made a bit of an error by joining on the SPID. The SPID can contain a different process when it dumps the running command so this was a mistake.
    Also, I'm ignoring the system databases (so it will filter out the CPU hog query too).

    Now, the code joins only on the dataabse name, the login name and the machine host name. This should, in theory, give us accurate results, but I'm still getting some weird results with negative figures. I thought it was an outer join giving us a new result in table B which wasn't in table A, thus giving us a negative value, but this can't be the case now.

    Just to check, I did a bit of debugging and found that table B (the later one) had a lower value in the CPU ticks column! Very strange.

    So, I still think it's useful, but take the results with a pinch of salt.

    Code:
    -- SQL script to find CPU hogs in SQL server
    -- version 2, but still shows negative values :(
    -- somethings funny with the CPU tick column. oh well.
    
    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
    where name not in ('master', 'msdb', 'model')
    order by pr1.spid
    
    -- wait for a period of time
    print 'Waiting for a while...'
    waitfor delay '00:05: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
    where name not in ('master', 'msdb', 'model')
    order by pr2.spid
    
    -- debug - check the two tables, later one sometimes shows lower CPU values - weird :(
    -- select * from ##cpuhog1
    -- select * from ##cpuhog2
    
    
    -- 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
    		 join ##cpuhog2 t2
    		on 
    			t1.DBName = t2.DBName
    			and t1.loginame = t2.loginame
    			and t1.hostname = t2.hostname
    		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

  5. #20

    jinnantonnixx's Avatar
    Join Date
    Mar 2011
    Location
    In the Calamatorium.
    Posts
    1,974
    Thank Post
    113
    Thanked 493 Times in 337 Posts
    Blog Entries
    2
    Rep Power
    284
    Andy, is your server fully patched and up-to-date?

    Things like this can be fixed by updates.

    This isn't applicable, but it's an example of the problems you get if your system isn't patched.
    You experience low CPU spikes at set intervals even when there is no user activity in SQL Server 2005

  6. #21

    Join Date
    Jul 2009
    Posts
    272
    Thank Post
    6
    Thanked 43 Times in 37 Posts
    Rep Power
    16
    jinnan, its sql 2008, however there is no way its fully patched becuase i tried to run SQL 2008 best practice analyser on it, then it asked for best practice framework as a prerequitsie, then it asked for powershell , the server is *supposed* to be fully managed by the LEA . So i do not dare do much with it TBH. ( last week i got such a telling off for rebooting the cisco pix , thats fully managed by the LEA) . Awkward.

  7. #22

    jinnantonnixx's Avatar
    Join Date
    Mar 2011
    Location
    In the Calamatorium.
    Posts
    1,974
    Thank Post
    113
    Thanked 493 Times in 337 Posts
    Blog Entries
    2
    Rep Power
    284
    I'd ask them to run in the updates on the server. There's a fair chance this will improve things.

    Microsoft SQL Server Release Services - Site Home - MSDN Blogs

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

Similar Threads

  1. Would you like to help and show us your tech?
    By Ric_ in forum General EduGeek News/Announcements
    Replies: 28
    Last Post: 23rd March 2010, 10:51 AM
  2. Show us your office space(/s)
    By p858snake in forum General Chat
    Replies: 19
    Last Post: 3rd July 2009, 09:03 PM
  3. Want a job? Then give us your passwords!
    By adameye in forum IT News
    Replies: 15
    Last Post: 25th June 2009, 09:01 AM
  4. SHOW OFF YOUR VLE HERE
    By thegrassisgreener in forum Virtual Learning Platforms
    Replies: 13
    Last Post: 19th October 2007, 10:12 PM

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
  •