+ Post New Thread
Page 1 of 2 12 LastLast
Results 1 to 15 of 22
MIS Systems Thread, SQL Server CPUs at 100% in Technical; Our users are experiencing intermittent disconnects and crashes when using Sims at peak times. The server is only running two ...
  1. #1

    Join Date
    Jun 2011
    Location
    England
    Posts
    3
    Thank Post
    0
    Thanked 0 Times in 0 Posts
    Rep Power
    0

    SQL Server CPUs at 100%

    Our users are experiencing intermittent disconnects and crashes when using Sims at peak times.

    The server is only running two SQL instances, hosting FMS and SIMS databases, a Sims file share and document storage is hosted on another server.

    We have 1000 students, 100 staff users and 3000 external users connecting via a hosted SLG.

    We have a reasonably fast SQL server:

    Database size = 4Gb
    Local Users = ~100

    Processors = 2 x 2.66Ghz Xeon x5550 (Quad hyper threading cpu)
    Memory = 16Gb
    Operating System = 64 bit 2008r2
    SQL System = 64 bit 2008
    Network = 4 x gBit Network

    Document Management Server is installed on another network server running only DMS services.

    Please can anyone provide a more suitable response than Capita's Second Line advice, "If you Google SQL using 100% cpu you will find loads of forums etc and I believe a Microsoft KB." ?!




  2. #2

    localzuk's Avatar
    Join Date
    Dec 2006
    Location
    Minehead
    Posts
    18,529
    Thank Post
    527
    Thanked 2,648 Times in 2,049 Posts
    Blog Entries
    24
    Rep Power
    925
    Is SQL the full edition or express?

  3. #3

    Join Date
    Jun 2011
    Location
    England
    Posts
    3
    Thank Post
    0
    Thanked 0 Times in 0 Posts
    Rep Power
    0
    Quote Originally Posted by localzuk View Post
    Is SQL the full edition or express?
    Thanks for your reply, its the Full 2008 Edition

  4. #4

    Join Date
    Aug 2007
    Location
    Kent
    Posts
    78
    Thank Post
    10
    Thanked 4 Times in 4 Posts
    Rep Power
    15
    We had a slow down on our Sims server last week, we did not get users being disconnected but certain reports were crashing when run, I ended up detaching the Sims database and attaching again, then restarted the server and all was running fine, our Sims database is about 5Gb in size.

  5. #5

    localzuk's Avatar
    Join Date
    Dec 2006
    Location
    Minehead
    Posts
    18,529
    Thank Post
    527
    Thanked 2,648 Times in 2,049 Posts
    Blog Entries
    24
    Rep Power
    925
    I've found our SQL server hits 100% when running certain tasks - such as importing Common Transfer Files, and when people are using Assessment Manager en-masse.

    What activities are your staff doing at these times?

    Have you tried looking in SQL Management Studio at active processes etc... to see what's running?

  6. #6

    Join Date
    Jun 2011
    Location
    England
    Posts
    3
    Thank Post
    0
    Thanked 0 Times in 0 Posts
    Rep Power
    0
    Quote Originally Posted by localzuk View Post
    I've found our SQL server hits 100% when running certain tasks - such as importing Common Transfer Files, and when people are using Assessment Manager en-masse.

    What activities are your staff doing at these times?

    Have you tried looking in SQL Management Studio at active processes etc... to see what's running?
    Hi

    Our CPU is maxing out so often it is difficult to identify any pattern at the moment. I have used the Management suite to see what is happening but I haven't seen anything untoward.
    Is there a particular process fault / pattern I should be looking for?!

  7. #7
    NorthernSands's Avatar
    Join Date
    Mar 2011
    Location
    Qatar
    Posts
    134
    Thank Post
    21
    Thanked 19 Times in 19 Posts
    Rep Power
    11
    @JustinRoberts: Have a look at my post at the end of this page SIMS Server Hardware for a large School and the one on the following page in the same thread. The first post might help determine if the CPU is being used by the SIMS SQL process or something else. The second post gives a description of Hyper-Threading and why I think you should turn it off. If you have the Turbo functionality enabled, I've seen recommendations to turn that off as well for a SQL server.

    Pete

  8. #8

    Join Date
    Jan 2008
    Location
    Cheshire, UK
    Posts
    342
    Thank Post
    69
    Thanked 60 Times in 42 Posts
    Rep Power
    49
    I frequently see our SQL at close to 100% CPU. I have managed to find that it was how our database was initially installed. We have the OS, SIMS DB, temp DB and master DB all on the same drive spindles. Using perfmon it is possible to see massive IO loads on the drive. I plan to replace the server this summer budget permitting! Then I will install it in a more performance minded way. There is a reindex patch which helps a little.

  9. #9
    GeneralDreedle's Avatar
    Join Date
    Nov 2009
    Location
    The North
    Posts
    26
    Thank Post
    5
    Thanked 6 Times in 5 Posts
    Rep Power
    12
    Justin, we are experiencing things slowing down, reports grinding to a halt, people getting thrown off etc. I reckon it started after the spring upgrade and rebooting the server every 2 weeks or so is all that fixes it for us.

    Could a big fat memory leak or some dodgy processor-intensive loop have been introduced in the upgrade?

    (We have 8Gb database, 4Gb RAM, poorer processor than yours, the rest similar)

  10. #10

    vikpaw's Avatar
    Join Date
    Sep 2006
    Location
    Saudi Arabia
    Posts
    5,956
    Thank Post
    775
    Thanked 1,487 Times in 1,234 Posts
    Rep Power
    367
    Although you have relatively few users, maybe a combo of concurrent usage with multiple homepage panels would cripple the system. Although the homepage panels have segregated synch times, the one time they all pop at once is when you first log in. I know that at 8am my server cries like a teething baby, when the whole upper school logs in to do registers.

    Even if you have another underlying issue, many people are recommending disabling homepage panels and if you already have it set up, you'll have to manually go round and remove it from staff accounts. Perhaps it's time to do an audit. Many of my users after an INSET session on the panels, ended up with lots that they didn't need, and didn't remove them.

  11. #11

    Join Date
    Sep 2010
    Posts
    674
    Thank Post
    28
    Thanked 78 Times in 73 Posts
    Rep Power
    23
    Quote Originally Posted by localzuk View Post
    I've found our SQL server hits 100% when running certain tasks - such as importing Common Transfer Files, and when people are using Assessment Manager en-masse.

    What activities are your staff doing at these times?

    Have you tried looking in SQL Management Studio at active processes etc... to see what's running?
    we have had this in the past so have asked that no major imports or exports are carried out during the school day (9am-3pm) to prevent disruption to registration etc.

  12. #12

    localzuk's Avatar
    Join Date
    Dec 2006
    Location
    Minehead
    Posts
    18,529
    Thank Post
    527
    Thanked 2,648 Times in 2,049 Posts
    Blog Entries
    24
    Rep Power
    925
    Quote Originally Posted by JustinRoberts View Post
    Hi

    Our CPU is maxing out so often it is difficult to identify any pattern at the moment. I have used the Management suite to see what is happening but I haven't seen anything untoward.
    Is there a particular process fault / pattern I should be looking for?!
    If you look at the activity monitor for the SQL server in management suite, it will give you a list of connected users etc.. It will give you a rough idea of who is doing what, so you could ask those people what they're doing at that time to see if specific tasks are causing the problems.

  13. #13
    Butuz's Avatar
    Join Date
    Feb 2007
    Location
    Wales, UK
    Posts
    1,579
    Thank Post
    211
    Thanked 220 Times in 176 Posts
    Rep Power
    63
    Wow - with the top spec of your server I wouldn't expect any bottlenecks whatsoever with only a 5gb database and 100 users.

    I would definately be looking at the hosted SLG side of it because SIMS on its own should fly on that hardware for a school three times the size.

    One thing worth checking on the server is that your sqlservr.exe process should be using about the same amount of mem usage as the size of your database so in your case it should be using around 5gb. If it's using much less than there may need to be a setting or two changed to allow it to use more of your 16gb ram.

    Butuz

  14. #14
    zag
    zag is offline
    zag's Avatar
    Join Date
    Mar 2007
    Posts
    4,002
    Thank Post
    983
    Thanked 477 Times in 398 Posts
    Blog Entries
    12
    Rep Power
    98
    We have virtually an identical setup including the SLG and cpu can peak at 90% quite often.

    I guess it is the SLG that causes the problems.

    Have you tried reducing the amount of RAM on the server? SQL server just eats anything it can get which might impact on cpu perfomance in some way.

    This screenshot shows cpu at the height of the day during registration if thats any use.

    simssqlserver.jpg

  15. #15

    jinnantonnixx's Avatar
    Join Date
    Mar 2011
    Location
    In the Calamatorium.
    Posts
    2,086
    Thank Post
    123
    Thanked 533 Times in 357 Posts
    Blog Entries
    2
    Rep Power
    336
    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



SHARE:
+ Post New Thread
Page 1 of 2 12 LastLast

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, 03:55 PM
  2. Replies: 21
    Last Post: 18th January 2011, 01:20 PM
  3. Server at 100%!!
    By TechSupp in forum Windows
    Replies: 18
    Last Post: 10th October 2008, 10:30 AM
  4. CPU on server at 100%
    By kennysarmy in forum Windows
    Replies: 5
    Last Post: 16th March 2008, 11:55 PM
  5. 100% server side software
    By starscream in forum Windows
    Replies: 9
    Last Post: 6th June 2007, 11: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
  •