MSSQL Memory Problem

denisv · Post by **denisv** » Mon Feb 12, 2007 5:57 pm

Can you advise if you have seen this following:

MSSQL 2000 (apparently SP3a) just keeps eating up memory when Aware is used until memory gone. Does not release.

Checked MS resources and references made to SP3a for SQL which apparently is on this machine. Also refer to using sp_cursoropen, and dallocating cursors or closing TSQL statements within the application. Also made reference to sp_OAMethod.

Previously we had problems on this machine using MAX CPU - that appearst to be resolved and was due to BS Design.

Other SQL Apps running on machine but issue not reported before Aware.

aware_support · Post by **aware_support** » Mon Feb 12, 2007 11:27 pm

When does it happen? When Aware IM starts up or after a particular operation? Which version of Aware IM are you using?

denisv · Post by **denisv** » Tue Feb 13, 2007 2:00 pm

THis happens progressively as the day goes on. When Aware is started (Service mode) there will be immediate CPU activity as expected and then settles.

As the end users are creating entries for Business Objects or searching during the day, the memory used by SQL increases. It cab be as much as 40MB per 30 mins and there is no sudden jump to corelate with an actual action.

Version is: 2.2 Build 937 (with a modified awareim.config due to issues with B937 login etc...)

Client is applying SQL SP4 today to see if it assists.

denisv · Post by **denisv** » Tue Feb 13, 2007 2:06 pm

Can you advise if the following would cause a problem:

In a business object I have three referenced links back to the RegularUser. Does this cause an overly complex query for SQL to handle ?

I need to be able to show the Created User, Modified User and Closed by User. However I have since used the following in rules and wonder can I use this and remove the referenced links to Regular User.

CallRecord.UserModified=LoggedInSystemUser.LoginName

denisv · Post by **denisv** » Wed Feb 14, 2007 7:14 am

I have done the following:

a) Client has installed SQL SP4
b) I have removed 3 referenced attributes from the main BO

The SQL Memory increasing problem is continuing. Are you aware of any other issues that could be causing this. It would seem not to have been a problem prior to Aware going live.

aware_support · Post by **aware_support** » Wed Feb 14, 2007 8:43 am

Are you saying that when you were using SQL Server for testing memory consumption was fine and only after you went live the problem started to happen?

If so, it must be a difference in SQL Server settings on the testing and production machines. Check out this info, for example http://p2p.wrox.com/topic.asp?TOPIC_ID=147
and
http://searchwinit.techtarget.com/tip/0 ... 32,00.html

denisv · Post by **denisv** » Wed Feb 14, 2007 9:54 am

The SQL memory use appears to have stabilised at 1.73Gb for a period of 45 mins (of normal - high activity).

It would seem that this is the level SQL needs to be at now.

This is a higher level than before Aware went live. During Test there would not have been the same level of interaction.

The server has the capacity to manage this memory and there seems to be no performance issues with either Aware or their other SQL applications.

I will continue to monitor, but suspect that SQL has settled at this level.

Thank you