Results 1 to 6 of 6
  1. #1
    Join Date
    Sep 2003
    Posts
    6

    Unanswered: SQL Server Memory Usage - Server Locks Up

    Hello,

    Over the past couple of weeks we have been experiencing a problem with SQL Server using up all the memory on our database server and locking it up. We have to connect remotely to the server and restart the SQL Server service to get it running again. We recently increased the total RAM from 4 GB to 8 GB and it still happens. I have the maximum server memory setting in SQL Server set to 5 GB, which I figured was more than enough. There is only a single database on the server that receives requests. The database is not very large. Maybe 50 to 75 tables, with 3 tables being queried more than any others. All 3 tables are indexed. The largest table has about 500,000 records. We recently moved the database from SQL Server 2005, and we never had this problem.

    We are running SQL Server 2008 R2 on a dedicated 64 bit Windows Server 2008 R2 system with 8 GB of RAM.

    I am running the profiler right now to see if there are any queries that are taking an exorbitant amount of time.

    Any help or hints would be much appreciated.

    Thanks,

    Ben

  2. #2
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    How do you know that it's SQL that grabs all the memory? Did you collect perfmon counters to support this? When you're saying "locking it up", do you mean that the server is inaccessible and there are memory-starvation-related errors that appear in system event log?
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  3. #3
    Join Date
    Sep 2003
    Posts
    6
    Right, when I say 'locking it up' the system itself barely responds. It takes quite awhile to just log in and restart services.

    When we had only 4 GB of memory I had SQL Server set to max out at 2 GB. Normally it would show that it was using around 2.1 in the task manager. What I noticed when the system started to slow down is that the 'Free' physical memory would be down under 10 MB, while the 'Available' would be around 500 to 1000 MB. I'm not too familiar with this stuff so my first thought was to increase the amount of memory. We increased the memory last week around Wednesday and the system 'locked up' yesterday. I did not see what happened yesterday so I can't say what processes were using how much memory.

    I don't see anything SQL related in the system event log except for a bunch of errors that read "The SQL Server Browser service terminated unexpectedly. It has done this 1339 time(s). The following corrective action will be taken in 60000 milliseconds: Restart the service." I'm not sure what is happening with that. I cannot start that service manually either. I don't see any memory starvation related errors in the event log. And I haven't used the performance monitor for anything. Again, I apologize, I'm not much of a db admin. I'm a programmer.

    Thanks for your help,

    Ben

  4. #4
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    When the system is slow to login into, and interactive response is sluggish, - it is usually CPU, not memory. CPU usage can be attributed to many things, but on a server it is usually excessive IO requests, which is possible with SQL running, but is not a norm (unless there is wild-wild-west going on [RD: meaning uncontrollable chaotic activities which nobody knows about]). It can also be a version of a Slammer that you might have acquired. And Browser service, responsible for name resolution when communicating to clients, is the one that would be affected first. I think you need to scan for that, before going after doubling the memory.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  5. #5
    Join Date
    Sep 2003
    Posts
    6
    I asked our hosting company to check their virus scans for any activity.

    Is the processor we're running just not beefy enough? We have an Intel Xeon E5410 2.33 GHz quad-core.

    I also ran the SQL Server profiler for 2 hours today and logged all RPC:Completed events under Stored Procedures and all SQL:BatchCompleted events under TSQL. There were about 66,000 events in all. I don't know if that sounds normal or way too high. A few queries took a good amount of time but there wasn't one single query that consistently took a lot of time to run.

  6. #6
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    To check intensity of activity you need to use Batch Requests/sec perfmon counter. This will tell you how many successful requests are made (after logon attempt is successful, and the minimum amount of memory is available and is granted to the client - usually 1024KB).
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

Posting Permissions

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