Results 1 to 9 of 9
  1. #1
    Join Date
    Mar 2003
    Posts
    11

    Unanswered: SQL Server Memory

    I have an ongoing issue with 2 instances of a web application using SQL server. Each instance of the web app has its own SQL server but queries in one use a huge amount of memory.

    In one a query (uploading content which has a number of database references) uses an additional 120 pages of memory in the 9 connections open from the app server.

    The same code base of the app on a different machine only uses 2 pages of additional memory.

    he instance using the large amount of memory crashes as successive queries use even more pages (200, 400...) until the machine dies

    I have been told that each page is approx 8 k of memory.

    Is there a way to compare the page size in Enterprise Manager? in is so where is this accessible from.

    Thanks,

    Simon

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    That doesn't make to much sense...sql will use as much memory as it needs, and when it needs more it'll go to disk.

    I get a feeling you mean hard disk space...

    How big is the hard disk, what % Free

    How much Memory is on the box

    What version of sql server?

    How big is tempdb? Is set for unrestricted growth?

    What type of load are you doing? bcp? bulk insert? dts? SELECT * INTO?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Feb 2002
    Posts
    2,232
    Are you saying that you have 2 identical setup (a server with web and sql server installed) - on server A for the same query, the memory usage is heavy(and keeps growing) while on server B the memory usage is light ? What version of sql server are you using (including service pack) - you can do a 'select @@version'.

  4. #4
    Join Date
    Mar 2003
    Posts
    11
    Yes, at 2 locations there are 2 identical instances SQL Server 2000 SP3 one is a testing box with 512MB physical memory, the other has 1 GB physical memory.

    There is a query run to insert a reference to a piece of content which is uploaded, in one instance there are an additional 120, then 240, then 400 pages of memory used, in the other the same upload causes only 2 or 3 pages of memory to be used.

    I cannot find any difference in the config, but I don't know if there is a way that the page size could be altered from default size?

    Thanks in advance

    Simon

    Originally posted by rnealejr
    Are you saying that you have 2 identical setup (a server with web and sql server installed) - on server A for the same query, the memory usage is heavy(and keeps growing) while on server B the memory usage is light ? What version of sql server are you using (including service pack) - you can do a 'select @@version'.

  5. #5
    Join Date
    Feb 2002
    Posts
    2,232
    What is the query doing ? Have you tested both servers with no other users using the system to validate ? Also, which sql server versions are you using ?

  6. #6
    Join Date
    Mar 2003
    Posts
    11
    The query is creating a row in 2 tables to reference the new pdf file being uploaded.

    I have tested each system with only 1 user and the page results are from these tests.

    SQL server are both SQL2000 SP3 using MS JDBC drivers

    Thanks,

    Simon

    Originally posted by rnealejr
    What is the query doing ? Have you tested both servers with no other users using the system to validate ? Also, which sql server versions are you using ?

  7. #7
    Join Date
    Feb 2002
    Posts
    2,232
    Which version of jdbc are you using (due to memory leaks) ? Also, have you tried to accessing SQL Server (on server B) from Web (server A) and vice-versa to see if code/jdbc driver might be causing the problem ?

  8. #8
    Join Date
    Mar 2003
    Posts
    11
    I have just installed the updated SP3 MS JDBC driver (11/6/2003), which in testing has improved the issues.

    cannot switch out as one is behind a corp. firewall and data security dept will not allow this access.

    Originally posted by rnealejr
    Which version of jdbc are you using (due to memory leaks) ? Also, have you tried to accessing SQL Server (on server B) from Web (server A) and vice-versa to see if code/jdbc driver might be causing the problem ?

  9. #9
    Join Date
    Feb 2002
    Posts
    2,232
    Normally the driver is to blame for situations like this. The other option is to copy the code from the well-behaved server to the poorly-behaved server and see if you notice a difference. This will eliminate (or discover) the code as the culprit.

Posting Permissions

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