Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    Join Date
    Apr 2006
    Posts
    2

    Question Unanswered: SQL 2000 Server memory issue! CRITICAL!

    Good day everyone!

    The issue I'm going to pose here has to do with SQL Server allocated memory BEING EXHAUSTED by an application (a simple data receiver from GPS units) when the number of units connecting to it is considerably increasing.
    This happens to an extent that no more connections from the units are valid and therefore lots of data (Afterwards) is being lost.
    This causes a real problem with the installations and the projects which are running the system.

    I will hereby post all of the info which might be useful for any Database Experts among you, so you can perhaps help me figure out the source(s) of the mentioned problem, and possible solution(s) of the issue.

    1- The Operating System used:
    Windows 2003 Server (Ent. Edition)

    2- The Database Server used:
    Microsoft SQL 2000 Server

    3- The amount of memory:
    1 GB (but on some sites there are 4 GB and the same problem
    is faced)

    4- The conditions where the issue happens
    When the amount of the received data - which gets written to
    the SQL Server tables - exceeds a certain limit.

    5- Number of applications used:
    We are using two applications:
    a- The receiver, which is a simple application that only
    receives the data from the GPS units installed at the cars
    of the clients' fleet(s), and writes them to the database
    (Day and Night - running all the time), so the memory used
    gets bigger and bigger till it floats the memory used by
    MS SQL Server 2000. As a result of this the connections
    made from the units furtherly to this point (to the receiver)
    are not being received and written to the database.

    b- the other application is quite heavy.. it does data
    management on SQL Server 2000 and exhausts the CPU.
    It reads from tables and makes analysis for GPS data...
    This is done while "vehicle tracking".. so it makes
    calculations on the received data and stores them into
    output tables.

    6- The programming language used in writing these 2 applications:
    Delphi 7

    7- What are we doing right now to handle this problem?
    We need to flush the memory on periodical basis.
    This is being done by stopping the SQL Server, or by
    restarting the PC.
    And it is not solving the problem entirely, especially that
    there must be a way to handle this.
    BTW, the memory is not being flushed when we turn the
    application off.

    8- What do we need to know?

    a- We've found, after research, that there are "Temporary tables" or
    objects in SQL Server 2000, that the DB system creates.. also page
    files, and temp tables.
    What are these for? And does handling them in a certain manner help
    solve the problem?
    b- Is there a way to monitor our applications to see where they are
    increasing the usage of memory and how this is happening?
    c- Is there a script that writes to the database that we can use to see if
    the problem is from our applications? And in case it is, then is there a
    command we should write or a function or script we should run in order
    to flush the MS SQL 2000 Server periodically, so that it doesn't flood?


    What comments can you have on this issue?
    Are there solutions at hand?


    I thank everyone for trying to help!

    All the best
    TD.


    P.S. you can write to me at: database1980 (AT) hotmail (DOT) com

  2. #2
    Join Date
    Dec 2002
    Posts
    1,245
    It's always hard to tune/troubleshoot remotely, but here are a few suggestions/thoughts.

    It's not enitrely clear to me from your description whether you are MEMORY bound or STORAGE bound).

    1. You're using Windows 2003 Enterprise Edition and SQL 2000
    a. What SP for Windows?
    b. What SP for SQL Server?
    c. You're only using 1 GB RAM; W2K3EE and SQL Server 2000 support much more than that. Can you add RAM to the server (then remember to set /PAE in boot.ini and AWE in SQL)?

    2. What's the drive topology?
    a. Do you have data files separated from log files?
    b. Do you have your temp db data file isolated to a separate disk?
    c. Is the OS isolated to it's own disk (what about the paging files?)
    d. Do you have a separate disk for Indexes?

    3. You say that the problem exists when the amount of data written to the server exceeds a certain limit; what's that limit?

    4. Check your recovery model. Are you using Full Recovery, Bulk Recovery or Simply Recovery?

    a. If you are using Full Recovery, are you taking frequent transaction log backups (to clean up the log files)?
    b. If you are using Simple Recovery (and you should not be, if this is a production database), what is the size/status of the log file?

    5. Are your applications disconnecting from the database completely (remember to destroy the connection object); or are you using connection pooling?

    6. Are you properly sizing the data file for the database? If the application is constantly writing to the database and the data file fills up, SQL will usually (unless you have overwritten the default setting for the datafile) extend the file automatically. Expanding (extending) the file is an expensive I/O operation and will lock out other transactions while the expansion is being performed.

    It is best to manually expand the data file(s) during pre-designated maintenance windows.

    7. Can you run SQL Profiler on your database?
    a. Check for errors
    b. Runing the index tuning wizard; look for indexes that might/will improve performance. DO NOT APPLY THE INDEXES WITHOUT CAREFULLY CHECKING THEM FOR SYNTAX, NAMING CONVENTIONS AND COMMON SENSE CHECKS.

    8. You mention "temporary tables", page files and other db objects? Yes, SQL will do it's best to optimize whatever you throw at it and in some cases it will create temporary objects to do so. But make sure that if your application is creating temporary tables (tables prefixed with @ or @@) that the application is destroying these. Normally they will be destroyed when the application disconnects, but if you are using connection pooling, that may be a different matter.

    9. Check the SQL Server Error log file; are there errors in there that indicate a problem?

    10. Check the Windows Application and System log files, are there errors there that indicate a problem?

    11. What's the underlying disk subsystem? SCSI? SAN?

    12. What's your network connection speed for the db server (10MB, 100MB, 1GB)?

    13. Run DBCC SHOWCONTIG WITH TABLERESULTS and analyze your database for fragmentation.

    14. It sounds like you have one application that is busy writing to the database while another application is busy querying it. This can lead to contention because the app that is reading is attempting to acquire a lock, which can prevent the app that is writing from updating the DB.

    a. Consider log shipping to a standby instance and using the standby instance for the reporting application.

    b. Consider replication (transactional or merge) to another server and using that second server for the reporting application.

    I would recommend starting with the fundamentals of a well laid out server; then look at error logs for clues to the problem; then run trace files to look for "low hanging fruit" (easy fixes such as indexes); then have another look at the application design and put it to the common sense test.

    Good luck.

    Regards,

    hmscott
    Have you hugged your backup today?

  3. #3
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    It almost sounds like you have all three applications (SQL, and your two applications) all installed on one box. Have you tried separating the three?

    On item 5, which application's memory expands without bound?

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Gosh I feel dirty writing this after such an awesome post.
    Quote Originally Posted by hmscott
    But make sure that if your application is creating temporary tables (tables prefixed with @ or @@) that the application is destroying these.
    Just in case we don't all know you meant "# or ##"
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    massive +1 for shifting the apps onto another machine. 1GB of memory is not much for a dedicated SQL box let alone one running other apps.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    Dec 2002
    Posts
    1,245
    Quote Originally Posted by pootle flump
    Gosh I feel dirty writing this after such an awesome post.
    Just in case we don't all know you meant "# or ##"
    Aaaaaaaaaaagh. Caught with me knickers round my knees!!!

    Can't believe I did that!

    Thanks for the catch.

    Regards,

    hmscott

    Have you hugged your backup today?

  7. #7
    Join Date
    Feb 2004
    Location
    Burbank & Santa Cruz de la Sierra
    Posts
    3,716
    Quote Originally Posted by hmscott
    Aaaaaaaaaaagh. Caught with me knickers round my knees!!!
    OMG, My Mind's Eye, My Mind's Eye...I think it's blinded!!!
    aka "Paul"
    Non est ei similis.

    I just read "100 Things To Do Before You Die". I was surprised that "Yell for help!!" wasn't one of them

  8. #8
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    Quote Originally Posted by database1980
    a- The receiver, which is a simple application that only
    receives the data from the GPS units installed at the cars
    of the clients' fleet(s), and writes them to the database
    (Day and Night - running all the time), so the memory used
    gets bigger and bigger till it floats the memory used by
    MS SQL Server 2000. As a result of this the connections
    made from the units furtherly to this point (to the receiver)
    are not being received and written to the database.
    this receiver app might be leaking memory. sounds like it is from your description anyway.

  9. #9
    Join Date
    Dec 2002
    Posts
    1,245
    I love these threads. CRITICAL!!! I NEED HELP!!

    Then,
    .
    .
    .
    <crickets />



    Regards,

    hmscott
    Have you hugged your backup today?

  10. #10
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by hmscott
    I love these threads. CRITICAL!!! I NEED HELP!!
    I thought just the same. I googled the guy to see if he had cross posted on other boards - he is very active on systems rather than db boards. Couldn't find another thread like this.

    Nevermind - I really appreciated your post
    Testimonial:
    pootle flump
    ur codings are working excelent.

  11. #11
    Join Date
    Feb 2004
    Location
    Burbank & Santa Cruz de la Sierra
    Posts
    3,716
    I just assume that since the issue was so critical, the person that posted was not able to fix it in time and got fired.
    aka "Paul"
    Non est ei similis.

    I just read "100 Things To Do Before You Die". I was surprised that "Yell for help!!" wasn't one of them

  12. #12
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    ...or had a heart-attack over the problem. Poor schmuck is probably slumped
    over the keyboard - as we speak.
    Inspiration Through Fermentation

  13. #13
    Join Date
    Oct 2006
    Location
    CA
    Posts
    210
    Hey, hmscott. Great checklist! Thanks!

    In particular:
    But make sure that if your application is creating temporary tables (tables prefixed with @ or @@) that the application is destroying these.

    What do you mean? Doing a "drop"? I use a few temp tables, where the scoping advantages make sense, but I've never "cleaned them up". Sooooo, have at me. What damage am I causing and what can I do about it?

    Oh; I use SQL Server 2000 Standard Edition. Although my log files are on a separate physical volume, I've never done anything special about the TEMPDB.

    Thanks!

  14. #14
    Join Date
    Feb 2004
    Location
    Burbank & Santa Cruz de la Sierra
    Posts
    3,716
    It depends on the scope of the temporary tables. If they are #temp tables (one hash, meaning local temp tables) they are automagically destroyed when you leave the scope in which they were created (leave that stored proc, for example). If they are ## temp tables (global temp tables, which reside in tempdb), you gotta clean up after yourself.

    Actually, the database in which they are created is the difference, if I recall....so you might have to clean up BOTH after yourself. Let me see if I can find that info in BOL.
    aka "Paul"
    Non est ei similis.

    I just read "100 Things To Do Before You Die". I was surprised that "Yell for help!!" wasn't one of them

  15. #15
    Join Date
    Feb 2004
    Location
    Burbank & Santa Cruz de la Sierra
    Posts
    3,716
    So yep...actually it looks like both get cleaned up eventually & automagically without the coder having to do a d@mn thing, but I usually drop the temp tables as soon as I am done with them in case I want to rerun my script(s) again and just because I like to clean stuff up when I leave (blame mama for that one...)
    Quote Originally Posted by BOL CREATE TABLE entry on temporary tables (snippet)
    A local temporary table created in a stored procedure is dropped automatically when the stored procedure completes. The table can be referenced by any nested stored procedures executed by the stored procedure that created the table. The table cannot be referenced by the process which called the stored procedure that created the table.


    All other local temporary tables are dropped automatically at the end of the current session.


    Global temporary tables are automatically dropped when the session that created the table ends and all other tasks have stopped referencing them. The association between a task and a table is maintained only for the life of a single Transact-SQL statement. This means that a global temporary table is dropped at the completion of the last Transact-SQL statement that was actively referencing the table when the creating session ended.
    aka "Paul"
    Non est ei similis.

    I just read "100 Things To Do Before You Die". I was surprised that "Yell for help!!" wasn't one of them

Posting Permissions

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