Poor performance - Large memory consumption by app.

I have a SQL Server 2003sp3 running on a Windows 2003 server. The
users are experiencing performance problems on their apps that seem to
be related to large memory consumption in the sqlserver process.
We have two instances of SQL running. One running the old accounting
system, one running the new accounting software. Both are vertical
market proprietary apps, and I don't have much access the the inner
workings.
The problems seem to start when the service running the new app
starts to consume a lot of memory. While the old app will climb to
about 600Mg and stay there (read from Task Man), the new app will climb
to over 1.7 Gig. That's when things start to crawl. I reboot and
things return to normal, but the new app's memory usage continues to
creep up.
The software has been installed since February, but this just started
happening a few weeks ago. I'm not sure what could be causing this
(other than problems with the software itself, I've asked their tech
support about it, but haven't heard much back).
Everything else seems normal (all performance monitors are nominal).
The only other thing I've noticed that's strange are some errors in
SQLDIAG.txt that state: "This database optimized for 8 processes , this
has been exceeded by 2" I understand this is an error related to MSDE,
but I'm not running MSDE and have never run it on this machine. It's
always been SQLServer 2000.
I'm not really well educated on SQL Server, so I'm not sure where to
turn next. Any advice would be apreciated.
(The two apps in question are "Wind 2" and the problem child
"Vision", both AEC industry accouting/project management apps).
Thanks
JIM HELFER | COMPUTER SYSTEMS ADMINISTRATOR | 412-321-0551 x330 |
JAH222@.WTWARCH.COM
WTW ARCHITECTS | TIMBER COURT | 127 ANDERSON STREET | PITTSBURGH, PA 15212Read about sp_configure ands the "max server memory" setting. Also, you do have MSDE or Personal
Edition, else you wouldn't get that warning. These editions had a performance throttling mechanism
when > 8 concurrently executing queries.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Jim Helfer" <jhelfer@.wtwarch.com> wrote in message news:Ov7eVpVmHHA.4872@.TK2MSFTNGP03.phx.gbl...
> I have a SQL Server 2003sp3 running on a Windows 2003 server. The users are experiencing
> performance problems on their apps that seem to be related to large memory consumption in the
> sqlserver process.
> We have two instances of SQL running. One running the old accounting system, one running the new
> accounting software. Both are vertical market proprietary apps, and I don't have much access the
> the inner workings.
> The problems seem to start when the service running the new app starts to consume a lot of
> memory. While the old app will climb to about 600Mg and stay there (read from Task Man), the new
> app will climb to over 1.7 Gig. That's when things start to crawl. I reboot and things return to
> normal, but the new app's memory usage continues to creep up.
> The software has been installed since February, but this just started happening a few weeks ago.
> I'm not sure what could be causing this (other than problems with the software itself, I've asked
> their tech support about it, but haven't heard much back).
> Everything else seems normal (all performance monitors are nominal). The only other thing I've
> noticed that's strange are some errors in SQLDIAG.txt that state: "This database optimized for 8
> processes , this has been exceeded by 2" I understand this is an error related to MSDE, but I'm
> not running MSDE and have never run it on this machine. It's always been SQLServer 2000.
> I'm not really well educated on SQL Server, so I'm not sure where to turn next. Any advice would
> be apreciated.
> (The two apps in question are "Wind 2" and the problem child "Vision", both AEC industry
> accouting/project management apps).
> Thanks
> JIM HELFER | COMPUTER SYSTEMS ADMINISTRATOR | 412-321-0551 x330 | JAH222@.WTWARCH.COM
> WTW ARCHITECTS | TIMBER COURT | 127 ANDERSON STREET | PITTSBURGH, PA 15212
>
>|||Tibor Karaszi wrote:
> Read about sp_configure ands the "max server memory" setting. Also, you
> do have MSDE or Personal Edition, else you wouldn't get that warning.
> These editions had a performance throttling mechanism when > 8
> concurrently executing queries.
>
Huh. You're right, I have SQL Server Desktop Engine installed. No idea
why. It's an Proliant box, so maybe Compaq Insight Manager installed it.
With Max server memory, are you telling me to set this to limit the
amount of Ram this process uses? or to let it use it all? There are 4
Gig in the machine, and this is the most important program on this
machine, so I want to give it as many resources as I can.
Thanks
JIM HELFER | SYSTEMS ADMINISTRATOR
WTW ARCHITECTS|||In your earlier post, you talk about "the old app" and "the new app", one stayed at 600MB and when
the other grew up to 1.7GB things got slow. Perhaps cap the "big" one at 1.5 GB? Or so...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Jim Helfer" <jhelfer@.wtwarch.com> wrote in message news:OYSU5FWmHHA.3704@.TK2MSFTNGP02.phx.gbl...
> Tibor Karaszi wrote:
>> Read about sp_configure ands the "max server memory" setting. Also, you do have MSDE or Personal
>> Edition, else you wouldn't get that warning. These editions had a performance throttling
>> mechanism when > 8 concurrently executing queries.
> Huh. You're right, I have SQL Server Desktop Engine installed. No idea why. It's an Proliant
> box, so maybe Compaq Insight Manager installed it.
> With Max server memory, are you telling me to set this to limit the amount of Ram this process
> uses? or to let it use it all? There are 4 Gig in the machine, and this is the most important
> program on this machine, so I want to give it as many resources as I can.
> Thanks
> JIM HELFER | SYSTEMS ADMINISTRATOR
> WTW ARCHITECTS|||Tibor Karaszi wrote:
> In your earlier post, you talk about "the old app" and "the new app",
> one stayed at 600MB and when the other grew up to 1.7GB things got slow.
> Perhaps cap the "big" one at 1.5 GB? Or so...
>
OK, I'll look into it. Thanks.
Jim Helfer