OLTP SQL Memory problem

My OLTP SQL server usually serves ~2700 connections. Twice in the past we've
had memory problem when there is more traffic. In that situation, the number
of connections goes up to 4000. The connections are legitimate and it's been
proved that there are not leaking connections.
I see flood of errors such as the following in the error log. The server has
4GB of memory and /3GB switch is on. SQL server is configured to use the max
memory.
Where do I being to troubleshoot this problem?
Thanks
---
2003-11-04 13:59:44.12 spid3100 Query Memory Manager: Grants=0 Waiting=0
Maximum=245613 Available=245613
2003-11-04 13:59:44.14 spid3167 Buffer Distribution: Stolen=10410 Free=85
Procedures=843
Inram=0 Dirty=23719 Kept=0
I/O=0, Latched=6, Other=303289
2003-11-04 13:59:44.14 spid3167 Buffer Counts: Commited=338352
Target=338352 Hashed=327015
InternalReservation=516 ExternalReservation=0 Min Free=172
2003-11-04 13:59:44.14 spid3167 Procedure Cache: TotalProcs=283
TotalPages=843 InUsePages=389
2003-11-04 13:59:44.14 spid3167 Dynamic Memory Manager: Stolen=11252 OS
Reserved=21680
OS Committed=21667
OS In Use=21665
Query Plan=1080 Optimizer=0
General=2578
Utilities=7 Connection=29191
2003-11-04 13:59:44.14 spid3167 Global Memory Objects: Resource=2303
Locks=59
SQLCache=121 Replication=2
LockBytes=2 ServerGlobal=45
Xact=74Kam,
4GB for a system serving 4000 users seems a little low. I don't know how
large your DB is or how much other parts of the memory are being used but
each connection takes up 12 KB + (3 * Network Packet Size). Then you have
all the other objects and the OS that need memory as well. You don't give
any clues as to what kind of memory issues you are having so it's hard to
say if your just low on ram or there is something else going on. I would
suggest you give MS PSS a call and have them work with you directly and they
can suggest some traces and diags to help pinpoint the issue. Without any
other info I would have to say you are probably just short on ram.
http://support.microsoft.com/default.aspx?scid=fh;EN-US;sql SQL Support
http://www.mssqlserver.com/faq/general-pss.asp MS PSS
--
Andrew J. Kelly
SQL Server MVP
"Kam" <krajabi@.hotmail.com> wrote in message
news:3faacbff$1@.news.microsoft.com...
> My OLTP SQL server usually serves ~2700 connections. Twice in the past
we've
> had memory problem when there is more traffic. In that situation, the
number
> of connections goes up to 4000. The connections are legitimate and it's
been
> proved that there are not leaking connections.
>
> I see flood of errors such as the following in the error log. The server
has
> 4GB of memory and /3GB switch is on. SQL server is configured to use the
max
> memory.
>
> Where do I being to troubleshoot this problem?
>
> Thanks
>
>
> ---
> 2003-11-04 13:59:44.12 spid3100 Query Memory Manager: Grants=0 Waiting=0
> Maximum=245613 Available=245613
> 2003-11-04 13:59:44.14 spid3167 Buffer Distribution: Stolen=10410
Free=85
> Procedures=843
> Inram=0 Dirty=23719 Kept=0
> I/O=0, Latched=6, Other=303289
> 2003-11-04 13:59:44.14 spid3167 Buffer Counts: Commited=338352
> Target=338352 Hashed=327015
> InternalReservation=516 ExternalReservation=0 Min Free=172
> 2003-11-04 13:59:44.14 spid3167 Procedure Cache: TotalProcs=283
> TotalPages=843 InUsePages=389
> 2003-11-04 13:59:44.14 spid3167 Dynamic Memory Manager: Stolen=11252 OS
> Reserved=21680
> OS Committed=21667
> OS In Use=21665
> Query Plan=1080 Optimizer=0
> General=2578
> Utilities=7 Connection=29191
> 2003-11-04 13:59:44.14 spid3167 Global Memory Objects: Resource=2303
> Locks=59
> SQLCache=121 Replication=2
> LockBytes=2 ServerGlobal=45
> Xact=74
>|||Andrew,
Some more information based on my observation
- Memory usuage is at 95%+ range
- CPU usage however seems normal ~ 45%
- There is no other application except SQL server
- SQL does accept any more connection when this issue occurs
- When I tried to log into the profiler, I got a message saying Memory is
Low
- There was a rolling blocking senario on the server too
4000 * [12,288 + (3 * 4,096)] = 98,304,000
Andrew, ~93 MB of memory doesn't look that much to me considering SQL
controls 3GB on that server.
Thanks,
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:uNFR#LMpDHA.1656@.tk2msftngp13.phx.gbl...
> Kam,
> 4GB for a system serving 4000 users seems a little low. I don't know how
> large your DB is or how much other parts of the memory are being used but
> each connection takes up 12 KB + (3 * Network Packet Size). Then you have
> all the other objects and the OS that need memory as well. You don't give
> any clues as to what kind of memory issues you are having so it's hard to
> say if your just low on ram or there is something else going on. I would
> suggest you give MS PSS a call and have them work with you directly and
they
> can suggest some traces and diags to help pinpoint the issue. Without any
> other info I would have to say you are probably just short on ram.
> http://support.microsoft.com/default.aspx?scid=fh;EN-US;sql SQL Support
> http://www.mssqlserver.com/faq/general-pss.asp MS PSS
> --
> Andrew J. Kelly
> SQL Server MVP
>
> "Kam" <krajabi@.hotmail.com> wrote in message
> news:3faacbff$1@.news.microsoft.com...
> > My OLTP SQL server usually serves ~2700 connections. Twice in the past
> we've
> > had memory problem when there is more traffic. In that situation, the
> number
> > of connections goes up to 4000. The connections are legitimate and it's
> been
> > proved that there are not leaking connections.
> >
> >
> >
> > I see flood of errors such as the following in the error log. The server
> has
> > 4GB of memory and /3GB switch is on. SQL server is configured to use the
> max
> > memory.
> >
> >
> >
> > Where do I being to troubleshoot this problem?
> >
> >
> >
> > Thanks
> >
> >
> >
> >
> >
> > ---
> >
> > 2003-11-04 13:59:44.12 spid3100 Query Memory Manager: Grants=0
Waiting=0
> > Maximum=245613 Available=245613
> >
> > 2003-11-04 13:59:44.14 spid3167 Buffer Distribution: Stolen=10410
> Free=85
> > Procedures=843
> >
> > Inram=0 Dirty=23719 Kept=0
> >
> > I/O=0, Latched=6, Other=303289
> >
> > 2003-11-04 13:59:44.14 spid3167 Buffer Counts: Commited=338352
> > Target=338352 Hashed=327015
> >
> > InternalReservation=516 ExternalReservation=0 Min Free=172
> >
> > 2003-11-04 13:59:44.14 spid3167 Procedure Cache: TotalProcs=283
> > TotalPages=843 InUsePages=389
> >
> > 2003-11-04 13:59:44.14 spid3167 Dynamic Memory Manager: Stolen=11252
OS
> > Reserved=21680
> >
> > OS Committed=21667
> >
> > OS In Use=21665
> >
> > Query Plan=1080 Optimizer=0
> >
> > General=2578
> >
> > Utilities=7 Connection=29191
> >
> > 2003-11-04 13:59:44.14 spid3167 Global Memory Objects: Resource=2303
> > Locks=59
> >
> > SQLCache=121 Replication=2
> >
> > LockBytes=2 ServerGlobal=45
> >
> > Xact=74
> >
> >
>|||It may not look like much by itself but it does have to share that with
everything else that takes up memory. If you had blocking issues then you
could also have a ton of locks being held which also use up memory. It may
be the locks that are starting the trouble so you might want to find out why
the blocking happened. But it still sounds like you are right at the edge
of using all the memory effectively for the load and it is likely to happen
again and again.
--
Andrew J. Kelly
SQL Server MVP
"Kam" <krajabi@.hotmail.com> wrote in message
news:3faaeda8$1@.news.microsoft.com...
> Andrew,
> Some more information based on my observation
> - Memory usuage is at 95%+ range
> - CPU usage however seems normal ~ 45%
> - There is no other application except SQL server
> - SQL does accept any more connection when this issue occurs
> - When I tried to log into the profiler, I got a message saying Memory is
> Low
> - There was a rolling blocking senario on the server too
> 4000 * [12,288 + (3 * 4,096)] = 98,304,000
> Andrew, ~93 MB of memory doesn't look that much to me considering SQL
> controls 3GB on that server.
> Thanks,
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:uNFR#LMpDHA.1656@.tk2msftngp13.phx.gbl...
> > Kam,
> >
> > 4GB for a system serving 4000 users seems a little low. I don't know
how
> > large your DB is or how much other parts of the memory are being used
but
> > each connection takes up 12 KB + (3 * Network Packet Size). Then you
have
> > all the other objects and the OS that need memory as well. You don't
give
> > any clues as to what kind of memory issues you are having so it's hard
to
> > say if your just low on ram or there is something else going on. I
would
> > suggest you give MS PSS a call and have them work with you directly and
> they
> > can suggest some traces and diags to help pinpoint the issue. Without
any
> > other info I would have to say you are probably just short on ram.
> >
> > http://support.microsoft.com/default.aspx?scid=fh;EN-US;sql SQL
Support
> > http://www.mssqlserver.com/faq/general-pss.asp MS PSS
> >
> > --
> >
> > Andrew J. Kelly
> > SQL Server MVP
> >
> >
> > "Kam" <krajabi@.hotmail.com> wrote in message
> > news:3faacbff$1@.news.microsoft.com...
> > > My OLTP SQL server usually serves ~2700 connections. Twice in the past
> > we've
> > > had memory problem when there is more traffic. In that situation, the
> > number
> > > of connections goes up to 4000. The connections are legitimate and
it's
> > been
> > > proved that there are not leaking connections.
> > >
> > >
> > >
> > > I see flood of errors such as the following in the error log. The
server
> > has
> > > 4GB of memory and /3GB switch is on. SQL server is configured to use
the
> > max
> > > memory.
> > >
> > >
> > >
> > > Where do I being to troubleshoot this problem?
> > >
> > >
> > >
> > > Thanks
> > >
> > >
> > >
> > >
> > >
> >
> ---
> > >
> > > 2003-11-04 13:59:44.12 spid3100 Query Memory Manager: Grants=0
> Waiting=0
> > > Maximum=245613 Available=245613
> > >
> > > 2003-11-04 13:59:44.14 spid3167 Buffer Distribution: Stolen=10410
> > Free=85
> > > Procedures=843
> > >
> > > Inram=0 Dirty=23719 Kept=0
> > >
> > > I/O=0, Latched=6, Other=303289
> > >
> > > 2003-11-04 13:59:44.14 spid3167 Buffer Counts: Commited=338352
> > > Target=338352 Hashed=327015
> > >
> > > InternalReservation=516 ExternalReservation=0 Min Free=172
> > >
> > > 2003-11-04 13:59:44.14 spid3167 Procedure Cache: TotalProcs=283
> > > TotalPages=843 InUsePages=389
> > >
> > > 2003-11-04 13:59:44.14 spid3167 Dynamic Memory Manager: Stolen=11252
> OS
> > > Reserved=21680
> > >
> > > OS Committed=21667
> > >
> > > OS In Use=21665
> > >
> > > Query Plan=1080 Optimizer=0
> > >
> > > General=2578
> > >
> > > Utilities=7 Connection=29191
> > >
> > > 2003-11-04 13:59:44.14 spid3167 Global Memory Objects: Resource=2303
> > > Locks=59
> > >
> > > SQLCache=121 Replication=2
> > >
> > > LockBytes=2 ServerGlobal=45
> > >
> > > Xact=74
> > >
> > >
> >
> >
>|||under perfmon, process object, sqlservr instance, what is
the Virtual Bytes, Working Set,
also get the Virtual Memory size from task manager for the
SqlServr.exe process.
under perfmon SQLServer Buffer Manager, what is the total
pages?
One common problem when SQL Server use 2 or 3GB of memory
is that too much memory and virtual address space gets
allocated to buffers, leaving inadequate address space for
other data structures.
While SQL Server will release buffers to free up memory
for other applications, it does not appear to free up
buffers to make more address space available for other SQL
Server internal requests
By default, SQL reserves 256M of address space for other
than buffers, that's why you frequently see 1.75GB
physical memory used for standard and 2.75GB for /3GB mode,
almost all of the address space is used for data buffers,
and most of the reserved 256M is unused.
in your case, it could be that you need more than 275M but
SQL has already allocated 2.75GB for buffers, and there no
more address space for other uses
provide the above info, but you can also try the -gxxx
startup parameter with say -g384 leaving 384MB for other
stuff
>--Original Message--
>Andrew,
>Some more information based on my observation
>- Memory usuage is at 95%+ range
>- CPU usage however seems normal ~ 45%
>- There is no other application except SQL server
>- SQL does accept any more connection when this issue
occurs
>- When I tried to log into the profiler, I got a message
saying Memory is
>Low
>- There was a rolling blocking senario on the server too
>4000 * [12,288 + (3 * 4,096)] = 98,304,000
>Andrew, ~93 MB of memory doesn't look that much to me
considering SQL
>controls 3GB on that server.
>Thanks,
>
>"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in
message
>news:uNFR#LMpDHA.1656@.tk2msftngp13.phx.gbl...
>> Kam,
>> 4GB for a system serving 4000 users seems a little
low. I don't know how
>> large your DB is or how much other parts of the memory
are being used but
>> each connection takes up 12 KB + (3 * Network Packet
Size). Then you have
>> all the other objects and the OS that need memory as
well. You don't give
>> any clues as to what kind of memory issues you are
having so it's hard to
>> say if your just low on ram or there is something else
going on. I would
>> suggest you give MS PSS a call and have them work with
you directly and
>they
>> can suggest some traces and diags to help pinpoint the
issue. Without any
>> other info I would have to say you are probably just
short on ram.
>> http://support.microsoft.com/default.aspx?scid=fh;EN-
US;sql SQL Support
>> http://www.mssqlserver.com/faq/general-pss.asp MS PSS
>> --
>> Andrew J. Kelly
>> SQL Server MVP
>>
>> "Kam" <krajabi@.hotmail.com> wrote in message
>> news:3faacbff$1@.news.microsoft.com...
>> > My OLTP SQL server usually serves ~2700 connections.
Twice in the past
>> we've
>> > had memory problem when there is more traffic. In
that situation, the
>> number
>> > of connections goes up to 4000. The connections are
legitimate and it's
>> been
>> > proved that there are not leaking connections.
>> >
>> >
>> >
>> > I see flood of errors such as the following in the
error log. The server
>> has
>> > 4GB of memory and /3GB switch is on. SQL server is
configured to use the
>> max
>> > memory.
>> >
>> >
>> >
>> > Where do I being to troubleshoot this problem?
>> >
>> >
>> >
>> > Thanks
>> >
>> >
>> >
>> >
>> >
>> > ---
--
>> >
>> > 2003-11-04 13:59:44.12 spid3100 Query Memory
Manager: Grants=0
>Waiting=0
>> > Maximum=245613 Available=245613
>> >
>> > 2003-11-04 13:59:44.14 spid3167 Buffer
Distribution: Stolen=10410
>> Free=85
>> > Procedures=843
>> >
>> > Inram=0 Dirty=23719 Kept=0
>> >
>> > I/O=0, Latched=6, Other=303289
>> >
>> > 2003-11-04 13:59:44.14 spid3167 Buffer Counts:
Commited=338352
>> > Target=338352 Hashed=327015
>> >
>> > InternalReservation=516
ExternalReservation=0 Min Free=172
>> >
>> > 2003-11-04 13:59:44.14 spid3167 Procedure Cache:
TotalProcs=283
>> > TotalPages=843 InUsePages=389
>> >
>> > 2003-11-04 13:59:44.14 spid3167 Dynamic Memory
Manager: Stolen=11252
>OS
>> > Reserved=21680
>> >
>> > OS Committed=21667
>> >
>> > OS In Use=21665
>> >
>> > Query Plan=1080 Optimizer=0
>> >
>> > General=2578
>> >
>> > Utilities=7 Connection=29191
>> >
>> > 2003-11-04 13:59:44.14 spid3167 Global Memory
Objects: Resource=2303
>> > Locks=59
>> >
>> > SQLCache=121 Replication=2
>> >
>> > LockBytes=2 ServerGlobal=45
>> >
>> > Xact=74
>> >
>> >
>>
>
>.
>

OleDB Connection Class in Custom Task?

I have OLE DB Connections set up in my connection manager (Native OLE DB\Microsoft OLE DB Provider for SQL Server). I would like to reference and query these connections from a custom task, written in C#. I currently reference it as follows:

using System.Data.OleDb;

...................................

OleDbConnection connection = (OleDbConnection) connections["MyConnection"].AcquireConnection(null);

What may be obvious to some (though wasn't to me, as I am new at this), when I run the task, I get an error saying that I cannot make this cast. After perusing the boards, I understand that this is because I am not making a cast to the right connection type. Well, that is where I am lost. What connection type (and corresponding library) do I need to reference? I want to continue to use the "Native OLE DB..." connection.

Thanks!

The OleDb connection manager is for tasks and data flow components that use unmanaged OleDb API.
Since you want managed connection object, use ADO.NET connection manager, select the same provider (Native OLE DB\Microsoft OLE DB Provider for SQL Server).

|||Makes sense. I don't know much about connection managers, but this helps. I'll give it a shot. Thanks!

OLE DB provider "MSDAORA" for linked server "DBNAME" returned message "

Hi All,

I have a strange problem with one of my Linked server connections. I am connecting from

SQL Server 2005 Standard Edition (9.00.3042.00) to and Oracle 10g database using the Microsoft OLE DB Provider for Oracle. The connection works and I am able to select from all the Oracle tables except for 1 table. When selecting from this table I get the following error:

OLE DB provider "MSDAORA" for linked server "DBNAME" returned message "ORA-01476: divisor is equal to zero".

Msg 7330, Level 16, State 2, Line 1

Cannot fetch a row from OLE DB provider "MSDAORA" for linked server "DBNAME".

This probem happens if the table has rows or has no rows (empty table).

I have tried SELECT * and count(*) along with selecting a specific column from the table.

I can use SQL*plus from the system that the SQL database server running on it and select from the table using the same login/password that the linked server uses to rule out a permissions problem.

Any suggestions on what I can try next?

Thanks.

Did you try using Oracle's own oledb provider? MSDAORA is a legacy provider and not officially supported for Oracle 10g.|||

Yes I had tried that. Found the solution that worked for me:

Provider: used the Oracle Provider

Product name: Oracle

Data source: The database name in TNSNames.ora

And here is the important part. Under the Provider Options for the Oracle provider enable (check) the "Allow inprocess" provider option.

Regards,

Scott

OLE DB provider "MSDAORA" for linked server "DBNAME" returned message "

Hi All,

I have a strange problem with one of my Linked server connections. I am connecting from

SQL Server 2005 Standard Edition (9.00.3042.00) to and Oracle 10g database using the Microsoft OLE DB Provider for Oracle. The connection works and I am able to select from all the Oracle tables except for 1 table. When selecting from this table I get the following error:

OLE DB provider "MSDAORA" for linked server "DBNAME" returned message "ORA-01476: divisor is equal to zero".

Msg 7330, Level 16, State 2, Line 1

Cannot fetch a row from OLE DB provider "MSDAORA" for linked server "DBNAME".

This probem happens if the table has rows or has no rows (empty table).

I have tried SELECT * and count(*) along with selecting a specific column from the table.

I can use SQL*plus from the system that the SQL database server running on it and select from the table using the same login/password that the linked server uses to rule out a permissions problem.

Any suggestions on what I can try next?

Thanks.

Did you try using Oracle's own oledb provider? MSDAORA is a legacy provider and not officially supported for Oracle 10g.|||

Yes I had tried that. Found the solution that worked for me:

Provider: used the Oracle Provider

Product name: Oracle

Data source: The database name in TNSNames.ora

And here is the important part. Under the Provider Options for the Oracle provider enable (check) the "Allow inprocess" provider option.

Regards,

Scott

OLE DB for ODBC connections in BIDS/AS project

Hi,

I try to configure a new data source in my Analysis Services Project in BIDS and I would like to test the OLE DB for ODBC Provider, but I cannot find it, although it is definitely installed on the box.

Is this kind of connection not allowed or not supported for AS?

Norbert

I'm pretty certain that it's not supported for AS2005.

Sorry,

Chris

|||

Indeed the OLEDB for ODBC provider is not supported by Analysis Services 2005.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

OLE DB connections

I have .net 2.0 framework and SQL V 8.0 on my web server.

I am trying to create a DSN and I am not given the OLEDB provider for SQL as an option for my list of providers. Is there a download that will add this ? What do I do to get it?

Hi,

If you use Start > Control Panel > Administrative Tools > Data Sources (ODBC) Choose "File DSN" and Choose "Add" and scroll all the way to the bottom, do you not see "Sql Server" ?

|||Alternatively you could check out all these connection strings; http://www.connectionstrings.com/?carrier=sqlserver

old connections

All,
Sql 2000 sp3, Server 2000 sp4
I have a cold fusion front end that hooks to my db and it
never seems to close a connection. The problem has only
gotten worse in the last 2 weeks. I go through and kill
everything older than a day, going by last batch date. Is
there a clean up proc available? Anyone else have this
problem, resolutions?
Thanks,
Gary
When you say close a connection , do you mean a transaction? What's the
behaviour you see on the SQL side?
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.

old connections

All,
Sql 2000 sp3, Server 2000 sp4
I have a cold fusion front end that hooks to my db and it
never seems to close a connection. The problem has only
gotten worse in the last 2 weeks. I go through and kill
everything older than a day, going by last batch date. Is
there a clean up proc available? Anyone else have this
problem, resolutions?
Thanks,
GaryWhen you say close a connection , do you mean a transaction? What's the
behaviour you see on the SQL side?
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.