+ Post New Thread
Results 1 to 10 of 10
MIS Systems Thread, SIMS Server SQL causing 100% in Technical; One of our Servers is performing really poorly, the CPU Utilisation shows SQL running at 100% from around 9am on ...
  1. #1

    Join Date
    Dec 2011
    Posts
    15
    Thank Post
    0
    Thanked 0 Times in 0 Posts
    Rep Power
    0

    SIMS Server SQL causing 100%

    One of our Servers is performing really poorly, the CPU Utilisation shows SQL running at 100% from around 9am on a morning until 3-4pm on an afternoon. (When the teachers are logged in and using SIMS)

    CAPITA are really scratching their heads to try and resolve this one, so I wondered if anyone out there had any thoughts.

    The server follows a pattern on our performance graphs showing the CPU spike daily to 100% and stay that way until mid afternoon, clearly showing this is a load issue somewhere.

    Any help is really appreciated.

    We have tried:
    Reboots,
    Index Patch (Multiple Times)
    Detaching and Re-Attaching Databases
    Increasing RAM from 6GB to 8GB as requested by CAPITA

    Tech Spec of Server
    E6250 @ 2.4GHz Quad Core VM
    8GB of RAM
    Windows 2008 SP2
    64BIT OS

  2. #2

    glennda's Avatar
    Join Date
    Jun 2009
    Location
    Sussex
    Posts
    7,799
    Thank Post
    272
    Thanked 1,134 Times in 1,030 Posts
    Rep Power
    349
    Do you have the graphs on the front page?

  3. #3

    Join Date
    Dec 2011
    Posts
    15
    Thank Post
    0
    Thanked 0 Times in 0 Posts
    Rep Power
    0
    Quote Originally Posted by glennda View Post
    Do you have the graphs on the front page?
    The homepage widgets were set by the school to show quite a bit of information. We removed the setting to push homepage widgets to staff to see if this would be reduce the load and saw no change in performance.

  4. #4

    jinnantonnixx's Avatar
    Join Date
    Mar 2011
    Location
    In the Calamatorium.
    Posts
    1,952
    Thank Post
    109
    Thanked 482 Times in 331 Posts
    Blog Entries
    2
    Rep Power
    281
    This thread covers a lot of possibilities.
    SQL Server CPUs at 100%

    I'd open SQL Activity Monitor and look the the 'expensive queries' list when you get these problems.

    If the cause remains unexplained, data or log file 'auto grow' operations can sometimes cause unexplained performance drops.

  5. #5

    matt40k's Avatar
    Join Date
    Jun 2008
    Location
    Ipswich
    Posts
    4,377
    Thank Post
    368
    Thanked 637 Times in 519 Posts
    Rep Power
    158
    What's your Average Disc queue length

  6. #6

    Join Date
    Dec 2011
    Posts
    15
    Thank Post
    0
    Thanked 0 Times in 0 Posts
    Rep Power
    0
    Quote Originally Posted by matt40k View Post
    What's your Average Disc queue length
    How would I check the Disc Queue Length?



    There is an extensive query running that CAPITA have noted but said it was ok, even though it is running at around 3millions operations a second!!!
    This has been queried multiple times but I have been told by CAPITA Engineers and a CAPITA Dev that this is not the cause of the high CPU.

  7. #7

    glennda's Avatar
    Join Date
    Jun 2009
    Location
    Sussex
    Posts
    7,799
    Thank Post
    272
    Thanked 1,134 Times in 1,030 Posts
    Rep Power
    349
    Disk queue length is you can view in Admin Tools - performance.

    What is the size of the database?

  8. #8

    Michael's Avatar
    Join Date
    Dec 2005
    Location
    Birmingham
    Posts
    9,262
    Thank Post
    242
    Thanked 1,568 Times in 1,250 Posts
    Rep Power
    340
    Have you tried uninstalling and re-installing SQL then re-attaching the database?

    Are you running the lastest Service Pack and patches for SQL?

  9. #9

    Join Date
    Jan 2008
    Location
    Cheshire, UK
    Posts
    340
    Thank Post
    68
    Thanked 60 Times in 42 Posts
    Rep Power
    48
    As the others have said check the disk queue length for the database drive.

    We had a similar issue and it was caused by the OS, database & logfiles all being on the same disk spindles. In their infinite wisdom a predecessor (or LEA) put the database on the D: drive, which was just a partition on the RAID5 containing the OS. The disk queue length was huge when checked. I rebuilt it last summer on our new virtual server platform with the database on the SAN now and working very well. We tried the SIMS diagnostics on the old server and it would take 30-40 minutes, now it takes less than 2.

    Something else we did with FMS rather than SIMS. Detattach and reattach the database was the advice from Capita and it worked! We also found the rebuild indexes patch helped a little. However these were only a sticking plaster on the real problem of the server being badly set up in the first place.

    As an aside I am planning on getting a t-shirt made with something like "it wasn't my fault, I wasn't here when that was bought." written on it as I seem to be saying it lot at the moment. The only problem is that we are changing so much that soon it will all be my fault!
    Last edited by sjatkn; 16th July 2012 at 01:18 PM.

  10. #10

    matt40k's Avatar
    Join Date
    Jun 2008
    Location
    Ipswich
    Posts
    4,377
    Thank Post
    368
    Thanked 637 Times in 519 Posts
    Rep Power
    158
    Re-disc queue:

    Open Performance Monitor from Administrative Tools
    Data Collector Sets > User Defined
    R-click in the right hand window panel > New > Data Collector Set
    Name: IO Performance - Select Create manually (Advanced) - Next
    Create data logs > Performance counter
    Click Add..
    Select counters from computer: <local computer>
    PhysicalDisk - Select each individual drive that SIMS uses - ie S: - Select Current Disk Queue Length, Avg. Disc sec/Read, Avg. Disc sec/Write - click OK
    Sample interval: 1 - Units: Minutes
    Root directory: %systemdrive%\PerfLogs\Admin\IO Performance
    Save and close - Finish
    R-click on the IO Performance > Properties - Stop Condition - Overall duration (tick), 1 - Units: Weeks - click OK
    Start the IO Performance - single click on it, then click on the play button

    Once it's finished - Open Performance Monitor from Administrative Tools
    Reports > User Defined > IO Performance > Double-click the report

    Ideally Current Disc Queue Length will be less than 3 and ideally the Avg. Disc sec/Read and Avg. Disc sec/Write will be 10 ms or less however up to 20 ms would be acceptable. These should be only used a target as they will be almost impossible to achieve 100% of the time in a real-world environment.

    For more information - visit: SQL Server Best Practices Article

    Ideally you want proper monitoring, ie the dedicated monitoring server that just polls the server, however this will do in most cases.

SHARE:
+ Post New Thread

Similar Threads

  1. [SIMS] SQL Server CPUs at 100%
    By JustinRoberts in forum MIS Systems
    Replies: 21
    Last Post: 29th June 2011, 05:15 PM
  2. SIMS server - SQL 2008, SSDs, recommendations
    By 3s-gtech in forum Hardware
    Replies: 11
    Last Post: 7th April 2011, 03:50 PM
  3. [SIMS] SIMS.Net / SQL Server 2008 Service Pack 2 (SP2)
    By Minkus in forum MIS Systems
    Replies: 5
    Last Post: 13th December 2010, 02:34 PM
  4. sims.net SQL Server CPU Spike
    By itgeek in forum MIS Systems
    Replies: 6
    Last Post: 26th February 2009, 12:28 PM
  5. sims.net sql server 2005
    By itgeek in forum MIS Systems
    Replies: 2
    Last Post: 14th March 2008, 06:02 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
  •