Post reply

Any Help Appreciated with Processor Usage 100% SQL

  • May 5, 2004 at 8:25 am

    #85321

    Our server is a little on the old side, its P4 2.0Ghz 1Gb RAM and RAID ATA100 80Gb Hard drives mirrowed.

    I am not all that confident with SQL server as we have only really in the last 3 months started using it. Our Accounts software has just been updated and in doing so we have had to install SQL server, unfortunetly at this moment in time our company are not ready to purchase the full version of SQL server, and we are running the flimsy free edition for up to 5 users. This is OK i guess as we only really need 5 licences for our accounts package.

     

    We went ahead and installed it and it ran ok for a few hours and then the processor stuck on 100% and stayed like that jamming any access to the computer and making it imopossible for users to do any work .

    I can restart the computer and it will be fine for a while, sometimes even days.... and then it will just begin to clog up and come to a standstill.

    I am faced with two options:

    1. Buy the full version of SQL server so i can have more control over the resources it uses.

    2. Buy a dedicated server for purely running SQL server, because currently we use the server as a domain controller and a file server.

    I know in an ideal solution we would do both of the above but funds are limited....

    Can anyone point me in the right direction as i am a little unsure what to recommend as a firm choice to my board of directors.

     

    Cheers Adrian

  • May 5, 2004 at 1:10 pm

    #505359

    Adrian,

    Can you run the following query from the query analyzer and see what is taking 100% of CPU

    Open Query Analyzer and run the following

    select * from sysprocesses where spid > 50 order by cpu desc.

    Try to see what program is executing it. If that doesn't help, try running DBCC INPUTBUFFER(spid) where spid is the spid of the process running at 100% CPU. Again, be aware that your computer would be very very slow during this periods and you will have to wait for a while before you get any outputs.

  • May 5, 2004 at 10:08 pm

    #505400

    If you're using the MSDE, you're probably running up against the "governor" that "dissuades" more than 5 users from using it, although you can theoretically go higher, it just gets slower and slower until it might just even get to 100%. 

     

    Your server config isn't too awful considering the number of users, etc.

     

    If you're running Great Plains, there might be some report process or posting/recovering process hung.

     

    Also, you want to make sure the Auto Close and Auto Shrink options aren't checked.  These can cause pretty severe performance issues, especially when they run during business hours.

    You can check these by either loading an Enterprise Manager client tool install (don't know about how this affects licensing) or by using an Access ADP to view the database and run a script to view these values. 

  • May 6, 2004 at 3:40 am

    #505433

    Thanks you all for your help, i think i may have found the problem. It turns out there was a process running which appeared after the accounts company installed sql and their accounts admin package.

    It is the Distributed File System, for the time being i have stoppped the service and told it never to start automatically, it appears to have stopped the 100%cpu! What i would like to know, where did this service come from and why did it begin to start up after we installed sql server.

    Thanks again

  • May 6, 2004 at 9:51 am

    #505508

    Straight from MS's site:

    Distributed File System (DFS) is a strategic storage management solution in Windows Server 2003 that enables you to group shared folders located on different servers logically by connecting them to one or more hierarchical namespaces.

    With DFS, users no longer need to know the actual physical location of files in order to access them. For example, if you have marketing files on multiple servers in a domain, you can use DFS to make it appear as though all of the marketing files are on a single server. This ability eliminates the need for users to go to multiple locations on the network to find the information they need. DFS also provides many other benefits, including fault tolerance and load-sharing capabilities, making it ideal for all types of organizations.

    I believe what that means is that you can create shares that span multiple drives and directories.

    I think this only comes with Windows 2003, but I tend to be wrong about things I'm nearly completely ignorant.  It probably loads by default, since it's a neat feature and MS LOVES to load their neat features by default.

     

    More info and troubleshooting from MS

     

    http://www.microsoft.com/windowsserver2003/techinfo/overview/dfsfaq.mspx

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply