Excel Import: excessive

Showing posts with label excessive. Show all posts

Monday, March 12, 2012

Excessive stored procedure [COMPILE] lock

Hello!
I am trying to investigate strange problem with particular stored
procedure. It runs OK for several days and suddenly we start getting and lot
of locks. The reason being [COMPILE] lock placed on this procedure. As a
result, we have 40-50 other connections waiting, then next connection using
this procedure has [COMPILE] lock etc. Client is fully qualifying stored
procedure by database/owner name and it doesn't start with sp_. I know
these are the reasons for [COMPILE] lock being placed. Is there something
else that might trigger this lock? When troubleshooting this issue, I
noticed there was no plan for this procedure in syscacheobjects. The stored
procedure is very simple (I know it could be rewritten/optimized but our
developer wrote it):
CREATE PROCEDURE [dbo].[vsp_mail_select]
@.user_id int,
@.folder_id int,
@.is_read bit = 1, --IF 1, pull everything, else just pull unread mail
@.start_index int = null, --unused for now, we return everything
@.total_count int = null output, -- count of all mail in specified folder
@.unread_count int = null output -- count of unread mail in specified folder
AS
SET NOCOUNT ON
select m1.* from mail m1(nolock) where m1.user_id=@.user_id and
folder_id=@.folder_id and ((@.is_read=0 and is_read=0) or (@.is_read=1)) order
by date_sent desc
select @.total_count = count(mail_id) from mail m1(nolock) where
m1.user_id=@.user_id and folder_id=@.folder_id and ((is_read=0 and @.is_read=0)
or (@.is_read=1))
select @.unread_count = count(mail_id) from mail m1(nolock) where
m1.user_id=@.user_id and folder_id=@.folder_id and is_read=0
GO
I was monitoring server for a couple of day before and I am not sure why
this happens every 3-4 days only!
Any help on this matter would be greately appreciated!
Thanks,
igor
This looks like a Parameter Sniffing problem to me.
take a look at this link:
http://www.microsoft.com/technet/pro...05/recomp.mspx
regards,
sarav...
"Igor Marchenko" <igormarchenko@.hotmail.com> wrote in message
news:OZRXncnWFHA.3464@.TK2MSFTNGP10.phx.gbl...
> Hello!
> I am trying to investigate strange problem with particular stored
> procedure. It runs OK for several days and suddenly we start getting and
> lot of locks. The reason being [COMPILE] lock placed on this procedure. As
> a result, we have 40-50 other connections waiting, then next connection
> using this procedure has [COMPILE] lock etc. Client is fully qualifying
> stored procedure by database/owner name and it doesn't start with sp_. I
> know these are the reasons for [COMPILE] lock being placed. Is there
> something else that might trigger this lock? When troubleshooting this
> issue, I noticed there was no plan for this procedure in syscacheobjects.
> The stored procedure is very simple (I know it could be
> rewritten/optimized but our developer wrote it):
>
> CREATE PROCEDURE [dbo].[vsp_mail_select]
> @.user_id int,
> @.folder_id int,
> @.is_read bit = 1, --IF 1, pull everything, else just pull unread mail
> @.start_index int = null, --unused for now, we return everything
> @.total_count int = null output, -- count of all mail in specified folder
> @.unread_count int = null output -- count of unread mail in specified
> folder
> AS
> SET NOCOUNT ON
> select m1.* from mail m1(nolock) where m1.user_id=@.user_id and
> folder_id=@.folder_id and ((@.is_read=0 and is_read=0) or (@.is_read=1))
> order by date_sent desc
> select @.total_count = count(mail_id) from mail m1(nolock) where
> m1.user_id=@.user_id and folder_id=@.folder_id and ((is_read=0 and
> @.is_read=0) or (@.is_read=1))
> select @.unread_count = count(mail_id) from mail m1(nolock) where
> m1.user_id=@.user_id and folder_id=@.folder_id and is_read=0
> GO
> I was monitoring server for a couple of day before and I am not sure why
> this happens every 3-4 days only!
> Any help on this matter would be greately appreciated!
> Thanks,
> igor
>
|||I think recompilations may be caused by modifcations done in base tables.
Developer confirmed that we have an hourly job running that can potentially
modify alot of data in base table. I am thinking of using
KEEPFIXED PLAN option to alleviate this problem.
Igor
"Sarav" <sarav@.sqlservertips.com> wrote in message
news:u8upDynWFHA.2420@.TK2MSFTNGP12.phx.gbl...
> This looks like a Parameter Sniffing problem to me.
> take a look at this link:
> http://www.microsoft.com/technet/pro...05/recomp.mspx
> regards,
> sarav...
> "Igor Marchenko" <igormarchenko@.hotmail.com> wrote in message
> news:OZRXncnWFHA.3464@.TK2MSFTNGP10.phx.gbl...
>

Excessive stored procedure [COMPILE] lock

Hello!
I am trying to investigate strange problem with particular stored
procedure. It runs OK for several days and suddenly we start getting and lot
of locks. The reason being [COMPILE] lock placed on this procedure. As a
result, we have 40-50 other connections waiting, then next connection using
this procedure has [COMPILE] lock etc. Client is fully qualifying stored
procedure by database/owner name and it doesn't start with sp_. I know
these are the reasons for [COMPILE] lock being placed. Is there somethin
g
else that might trigger this lock? When troubleshooting this issue, I
noticed there was no plan for this procedure in syscacheobjects. The stored
procedure is very simple (I know it could be rewritten/optimized but our
developer wrote it):
CREATE PROCEDURE [dbo].[vsp_mail_select]
@.user_id int,
@.folder_id int,
@.is_read bit = 1, --IF 1, pull everything, else just pull unread mail
@.start_index int = null, --unused for now, we return everything
@.total_count int = null output, -- count of all mail in specified folder
@.unread_count int = null output -- count of unread mail in specified folder
AS
SET NOCOUNT ON
select m1.* from mail m1(nolock) where m1.user_id=@.user_id and
folder_id=@.folder_id and ((@.is_read=0 and is_read=0) or (@.is_read=1)) order
by date_sent desc
select @.total_count = count(mail_id) from mail m1(nolock) where
m1.user_id=@.user_id and folder_id=@.folder_id and ((is_read=0 and @.is_read=0)
or (@.is_read=1))
select @.unread_count = count(mail_id) from mail m1(nolock) where
m1.user_id=@.user_id and folder_id=@.folder_id and is_read=0
GO
I was monitoring server for a couple of day before and I am not sure why
this happens every 3-4 days only!
Any help on this matter would be greately appreciated!
Thanks,
igorThis looks like a Parameter Sniffing problem to me.
take a look at this link:
http://www.microsoft.com/technet/pr...005/recomp.mspx
regards,
sarav...
"Igor Marchenko" <igormarchenko@.hotmail.com> wrote in message
news:OZRXncnWFHA.3464@.TK2MSFTNGP10.phx.gbl...
> Hello!
> I am trying to investigate strange problem with particular stored
> procedure. It runs OK for several days and suddenly we start getting and
> lot of locks. The reason being [COMPILE] lock placed on this procedure
. As
> a result, we have 40-50 other connections waiting, then next connection
> using this procedure has [COMPILE] lock etc. Client is fully qualifyin
g
> stored procedure by database/owner name and it doesn't start with sp_. I
> know these are the reasons for [COMPILE] lock being placed. Is there
> something else that might trigger this lock? When troubleshooting this
> issue, I noticed there was no plan for this procedure in syscacheobjects.
> The stored procedure is very simple (I know it could be
> rewritten/optimized but our developer wrote it):
>
> CREATE PROCEDURE [dbo].[vsp_mail_select]
> @.user_id int,
> @.folder_id int,
> @.is_read bit = 1, --IF 1, pull everything, else just pull unread mail
> @.start_index int = null, --unused for now, we return everything
> @.total_count int = null output, -- count of all mail in specified folder
> @.unread_count int = null output -- count of unread mail in specified
> folder
> AS
> SET NOCOUNT ON
> select m1.* from mail m1(nolock) where m1.user_id=@.user_id and
> folder_id=@.folder_id and ((@.is_read=0 and is_read=0) or (@.is_read=1))
> order by date_sent desc
> select @.total_count = count(mail_id) from mail m1(nolock) where
> m1.user_id=@.user_id and folder_id=@.folder_id and ((is_read=0 and
> @.is_read=0) or (@.is_read=1))
> select @.unread_count = count(mail_id) from mail m1(nolock) where
> m1.user_id=@.user_id and folder_id=@.folder_id and is_read=0
> GO
> I was monitoring server for a couple of day before and I am not sure why
> this happens every 3-4 days only!
> Any help on this matter would be greately appreciated!
> Thanks,
> igor
>|||I think recompilations may be caused by modifcations done in base tables.
Developer confirmed that we have an hourly job running that can potentially
modify alot of data in base table. I am thinking of using
KEEPFIXED PLAN option to alleviate this problem.
Igor
"Sarav" <sarav@.sqlservertips.com> wrote in message
news:u8upDynWFHA.2420@.TK2MSFTNGP12.phx.gbl...
> This looks like a Parameter Sniffing problem to me.
> take a look at this link:
> http://www.microsoft.com/technet/pr...005/recomp.mspx
> regards,
> sarav...
> "Igor Marchenko" <igormarchenko@.hotmail.com> wrote in message
> news:OZRXncnWFHA.3464@.TK2MSFTNGP10.phx.gbl...
>

Excessive stored procedure [COMPILE] lock

Hello!

I am trying to investigate strange problem with particular stored
procedure. It runs OK for several days and suddenly we start getting
and lot
of locks. The reason being [COMPILE] lock placed on this procedure. As
a
result, we have 40-50 other connections waiting, then next connection
using
this procedure has [COMPILE] lock etc. Client is fully qualifying
stored
procedure by database/owner name and it doesn't start with sp_. I know

these are the reasons for [COMPILE] lock being placed. Is there
something
else that might trigger this lock? When troubleshooting this issue, I
noticed there was no plan for this procedure in syscacheobjects. The
stored
procedure is very simple (I know it could be rewritten/optimized but
our
developer wrote it):

CREATE PROCEDURE [dbo].[vsp_mail_select]

@.user_id int,
@.folder_id int,
@.is_read bit = 1, --IF 1, pull everything, else just pull unread mail
@.start_index int = null, --unused for now, we return everything
@.total_count int = null output, -- count of all mail in specified
folder
@.unread_count int = null output -- count of unread mail in specified
folder

AS
SET NOCOUNT ON

select m1.* from mail m1(nolock) where m1.user_id=@.user_id and
folder_id=@.folder_id and ((@.is_read=0 and is_read=0) or (@.is_read=1))
order
by date_sent desc
select @.total_count = count(mail_id) from mail m1(nolock) where
m1.user_id=@.user_id and folder_id=@.folder_id and ((is_read=0 and
@.is_read=0)
or (@.is_read=1))
select @.unread_count = count(mail_id) from mail m1(nolock) where
m1.user_id=@.user_id and folder_id=@.folder_id and is_read=0

I was monitoring server for a couple of day before and I am not sure
why
this happens every 3-4 days only!

Any help on this matter would be greately appreciated!

Thanks,
IgorSee:
http://support.microsoft.com/defaul...B;en-us;q263889
http://support.microsoft.com/?kbid=836136
Maybe one of them (or the related articles) will help.

Razvan

Excessive stored procedure [COMPILE] lock

Hello!
I am trying to investigate strange problem with particular stored
procedure. It runs OK for several days and suddenly we start getting and lot
of locks. The reason being [COMPILE] lock placed on this procedure. As a
result, we have 40-50 other connections waiting, then next connection using
this procedure has [COMPILE] lock etc. Client is fully qualifying stored
procedure by database/owner name and it doesn't start with sp_. I know
these are the reasons for [COMPILE] lock being placed. Is there something
else that might trigger this lock? When troubleshooting this issue, I
noticed there was no plan for this procedure in syscacheobjects. The stored
procedure is very simple (I know it could be rewritten/optimized but our
developer wrote it):
CREATE PROCEDURE [dbo].[vsp_mail_select]
@.user_id int,
@.folder_id int,
@.is_read bit = 1, --IF 1, pull everything, else just pull unread mail
@.start_index int = null, --unused for now, we return everything
@.total_count int = null output, -- count of all mail in specified folder
@.unread_count int = null output -- count of unread mail in specified folder
AS
SET NOCOUNT ON
select m1.* from mail m1(nolock) where m1.user_id=@.user_id and
folder_id=@.folder_id and ((@.is_read=0 and is_read=0) or (@.is_read=1)) order
by date_sent desc
select @.total_count = count(mail_id) from mail m1(nolock) where
m1.user_id=@.user_id and folder_id=@.folder_id and ((is_read=0 and @.is_read=0)
or (@.is_read=1))
select @.unread_count = count(mail_id) from mail m1(nolock) where
m1.user_id=@.user_id and folder_id=@.folder_id and is_read=0
GO
I was monitoring server for a couple of day before and I am not sure why
this happens every 3-4 days only!
Any help on this matter would be greately appreciated!
Thanks,
igorThis looks like a Parameter Sniffing problem to me.
take a look at this link:
http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx
regards,
sarav...
"Igor Marchenko" <igormarchenko@.hotmail.com> wrote in message
news:OZRXncnWFHA.3464@.TK2MSFTNGP10.phx.gbl...
> Hello!
> I am trying to investigate strange problem with particular stored
> procedure. It runs OK for several days and suddenly we start getting and
> lot of locks. The reason being [COMPILE] lock placed on this procedure. As
> a result, we have 40-50 other connections waiting, then next connection
> using this procedure has [COMPILE] lock etc. Client is fully qualifying
> stored procedure by database/owner name and it doesn't start with sp_. I
> know these are the reasons for [COMPILE] lock being placed. Is there
> something else that might trigger this lock? When troubleshooting this
> issue, I noticed there was no plan for this procedure in syscacheobjects.
> The stored procedure is very simple (I know it could be
> rewritten/optimized but our developer wrote it):
>
> CREATE PROCEDURE [dbo].[vsp_mail_select]
> @.user_id int,
> @.folder_id int,
> @.is_read bit = 1, --IF 1, pull everything, else just pull unread mail
> @.start_index int = null, --unused for now, we return everything
> @.total_count int = null output, -- count of all mail in specified folder
> @.unread_count int = null output -- count of unread mail in specified
> folder
> AS
> SET NOCOUNT ON
> select m1.* from mail m1(nolock) where m1.user_id=@.user_id and
> folder_id=@.folder_id and ((@.is_read=0 and is_read=0) or (@.is_read=1))
> order by date_sent desc
> select @.total_count = count(mail_id) from mail m1(nolock) where
> m1.user_id=@.user_id and folder_id=@.folder_id and ((is_read=0 and
> @.is_read=0) or (@.is_read=1))
> select @.unread_count = count(mail_id) from mail m1(nolock) where
> m1.user_id=@.user_id and folder_id=@.folder_id and is_read=0
> GO
> I was monitoring server for a couple of day before and I am not sure why
> this happens every 3-4 days only!
> Any help on this matter would be greately appreciated!
> Thanks,
> igor
>|||I think recompilations may be caused by modifcations done in base tables.
Developer confirmed that we have an hourly job running that can potentially
modify alot of data in base table. I am thinking of using
KEEPFIXED PLAN option to alleviate this problem.
Igor
"Sarav" <sarav@.sqlservertips.com> wrote in message
news:u8upDynWFHA.2420@.TK2MSFTNGP12.phx.gbl...
> This looks like a Parameter Sniffing problem to me.
> take a look at this link:
> http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx
> regards,
> sarav...
> "Igor Marchenko" <igormarchenko@.hotmail.com> wrote in message
> news:OZRXncnWFHA.3464@.TK2MSFTNGP10.phx.gbl...
>> Hello!
>> I am trying to investigate strange problem with particular stored
>> procedure. It runs OK for several days and suddenly we start getting and
>> lot of locks. The reason being [COMPILE] lock placed on this procedure.
>> As a result, we have 40-50 other connections waiting, then next
>> connection using this procedure has [COMPILE] lock etc. Client is fully
>> qualifying stored procedure by database/owner name and it doesn't start
>> with sp_. I know these are the reasons for [COMPILE] lock being placed.
>> Is there something else that might trigger this lock? When
>> troubleshooting this issue, I noticed there was no plan for this
>> procedure in syscacheobjects. The stored procedure is very simple (I know
>> it could be rewritten/optimized but our developer wrote it):
>>
>> CREATE PROCEDURE [dbo].[vsp_mail_select]
>> @.user_id int,
>> @.folder_id int,
>> @.is_read bit = 1, --IF 1, pull everything, else just pull unread mail
>> @.start_index int = null, --unused for now, we return everything
>> @.total_count int = null output, -- count of all mail in specified folder
>> @.unread_count int = null output -- count of unread mail in specified
>> folder
>> AS
>> SET NOCOUNT ON
>> select m1.* from mail m1(nolock) where m1.user_id=@.user_id and
>> folder_id=@.folder_id and ((@.is_read=0 and is_read=0) or (@.is_read=1))
>> order by date_sent desc
>> select @.total_count = count(mail_id) from mail m1(nolock) where
>> m1.user_id=@.user_id and folder_id=@.folder_id and ((is_read=0 and
>> @.is_read=0) or (@.is_read=1))
>> select @.unread_count = count(mail_id) from mail m1(nolock) where
>> m1.user_id=@.user_id and folder_id=@.folder_id and is_read=0
>> GO
>> I was monitoring server for a couple of day before and I am not sure why
>> this happens every 3-4 days only!
>> Any help on this matter would be greately appreciated!
>> Thanks,
>> igor
>

Excessive SQL Compilations

Hello, my sql server 2000 box is doing excessive compilations. There is
no ad-hoc SQL on the system, just stored procedures. In particular there
is really only 5-6 sprocs that run day and night. Still perfmon reports
something like 30-40 SQL Compilations per second (depending on the day).
I have the following questions:
1. What is causing excessive compilations?
2.How do I identify specific sprocs that cause SQL Compilations and
what can I do to alleviate the problem?
Thanks.
Have a look at these:
http://support.microsoft.com/default...;en-us;Q243586
http://www.sql-server-performance.co...recompiles.asp
Andrew J. Kelly SQL MVP
"Frank Rizzo" <none@.none.com> wrote in message
news:uYnoWe14GHA.3604@.TK2MSFTNGP03.phx.gbl...
> Hello, my sql server 2000 box is doing excessive compilations. There is
> no ad-hoc SQL on the system, just stored procedures. In particular there
> is really only 5-6 sprocs that run day and night. Still perfmon reports
> something like 30-40 SQL Compilations per second (depending on the day).
> I have the following questions:
> 1. What is causing excessive compilations?
> 2. How do I identify specific sprocs that cause SQL Compilations and what
> can I do to alleviate the problem?
> Thanks.

Excessive SQL Compilations

Hello, my sql server 2000 box is doing excessive compilations. There is
no ad-hoc SQL on the system, just stored procedures. In particular there
is really only 5-6 sprocs that run day and night. Still perfmon reports
something like 30-40 SQL Compilations per second (depending on the day).
I have the following questions:
1. What is causing excessive compilations?
2. How do I identify specific sprocs that cause SQL Compilations and
what can I do to alleviate the problem?
Thanks.Have a look at these:
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q243586
http://www.sql-server-performance.com/rd_optimizing_sp_recompiles.asp
Andrew J. Kelly SQL MVP
"Frank Rizzo" <none@.none.com> wrote in message
news:uYnoWe14GHA.3604@.TK2MSFTNGP03.phx.gbl...
> Hello, my sql server 2000 box is doing excessive compilations. There is
> no ad-hoc SQL on the system, just stored procedures. In particular there
> is really only 5-6 sprocs that run day and night. Still perfmon reports
> something like 30-40 SQL Compilations per second (depending on the day).
> I have the following questions:
> 1. What is causing excessive compilations?
> 2. How do I identify specific sprocs that cause SQL Compilations and what
> can I do to alleviate the problem?
> Thanks.

Excessive SQL Compilations

Hello, my sql server 2000 box is doing excessive compilations. There is
no ad-hoc SQL on the system, just stored procedures. In particular there
is really only 5-6 sprocs that run day and night. Still perfmon reports
something like 30-40 SQL Compilations per second (depending on the day).
I have the following questions:
1. What is causing excessive compilations?
2. How do I identify specific sprocs that cause SQL Compilations and
what can I do to alleviate the problem?
Thanks.Have a look at these:
http://support.microsoft.com/defaul...b;en-us;Q243586
http://www.sql-server-performance.c..._recompiles.asp
Andrew J. Kelly SQL MVP
"Frank Rizzo" <none@.none.com> wrote in message
news:uYnoWe14GHA.3604@.TK2MSFTNGP03.phx.gbl...
> Hello, my sql server 2000 box is doing excessive compilations. There is
> no ad-hoc SQL on the system, just stored procedures. In particular there
> is really only 5-6 sprocs that run day and night. Still perfmon reports
> something like 30-40 SQL Compilations per second (depending on the day).
> I have the following questions:
> 1. What is causing excessive compilations?
> 2. How do I identify specific sprocs that cause SQL Compilations and what
> can I do to alleviate the problem?
> Thanks.

excessive sending package from the sqlserver

The users reported excessive sending package from the sqlserver, we intented down the services but we can't
The server administrator rebooted the server and the network was stable
What do you think about it?
Sounds suspiciously like a Slammer infection. I'd suggest you take a
look here http://www.microsoft.com/security/incident/slammer.mspx and
make sure your servers are patched correctly.
g.
http://www.sqlskunkworks.com
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!

excessive sending package from the sqlserver

The users reported excessive sending package from the sqlserver, we intented
down the services but we can't
The server administrator rebooted the server and the network was stable
What do you think about it?Sounds suspiciously like a Slammer infection. I'd suggest you take a
look here http://www.microsoft.com/security/incident/slammer.mspx and
make sure your servers are patched correctly.
g.
http://www.sqlskunkworks.com
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!

Excessive paging after enabling AWE in SQL server - memgraph1.jpg (0/1)

After enabling AWE in SQL I am noticing a good bit more paging than
before. The attached jpg file is a graph of memory usage.
The configuration for this server is:
IBM x360 w/ 4 x 1.6ghz processors, 8GB memory
Storage: C: and D: partitions internal drives
Data stored on SAN volumes: 5 x 683GB volumes
# databases on this server: 162
boot.ini file has the /3GB and /PAE switches
OS sees all 8GB of memory.
SQL Server has AWE enabled with memory settings:
Min: 0 Max: 5500
As soon as I enabled AWE in SQL, I noticed the following:
(see jpg of the memory usage graph)
1. MemPhysUsage jumps up to almost 80%
(this is the 5500 mark) (in green)
2. PageFileUsage starts climbing up over time. (in pink)
There was little or no page file usage prior to enabling AWE.
Any ideas?
Thanks in advance
jtw
--
http://www.UsenetRocket.comHi,
if the server is only a MS SQL 2k Server, change the
Server properties - dynamicly configure SQL Server Memory -
Give the Server the maximum of memory.
J.K
>--Original Message--
>After enabling AWE in SQL I am noticing a good bit more
paging than
>before. The attached jpg file is a graph of memory usage.
>The configuration for this server is:
>IBM x360 w/ 4 x 1.6ghz processors, 8GB memory
>Storage: C: and D: partitions internal drives
>Data stored on SAN volumes: 5 x 683GB volumes
># databases on this server: 162
>boot.ini file has the /3GB and /PAE switches
>OS sees all 8GB of memory.
>SQL Server has AWE enabled with memory settings:
> Min: 0 Max: 5500
>As soon as I enabled AWE in SQL, I noticed the following:
>(see jpg of the memory usage graph)
>1. MemPhysUsage jumps up to almost 80%
> (this is the 5500 mark) (in green)
>2. PageFileUsage starts climbing up over time. (in pink)
>There was little or no page file usage prior to enabling
AWE.
>Any ideas?
>
>Thanks in advance
>jtw
>--
>http://www.UsenetRocket.com
>.
>

Excessive Page Faults?

I am noticing that an many of my servers (Server 2003 EE SP2 R2), that the
MsMpEng.exe and MOMHost.exe processes are creating a significant number of
page faults.
For example, on my SQL 2000 SP4 server with 4GB RAM which has been up for 19
days has nearly 19M page faults on MsMpEng and over 16M faults on MOMHost.
Another example is my Exchange 2003 SP2 with 4GB RAM which has also been up
for 19 days has nearly 182M faults on MsMpEng and nearly 16M faults on
MOMHost.
Keep in mind that the "M" with the number is million.
We only recently started using Forefront on our servers, and this appears to
be a relatively new issue, and I would like to know if others have been
experiencing this as well.
RichardThis is not an exchange issue...
But take a look at
http://geekswithblogs.net/Coleman/archive/2007/03/26/109906.aspx
"Richard Perry" <RichardPerry@.newsgroup.nospam> wrote in message
news:3BFD2358-2798-4A29-B54A-EA90AD883293@.microsoft.com...
>I am noticing that an many of my servers (Server 2003 EE SP2 R2), that the
>MsMpEng.exe and MOMHost.exe processes are creating a significant number of
>page faults.
> For example, on my SQL 2000 SP4 server with 4GB RAM which has been up for
> 19 days has nearly 19M page faults on MsMpEng and over 16M faults on
> MOMHost.
> Another example is my Exchange 2003 SP2 with 4GB RAM which has also been
> up for 19 days has nearly 182M faults on MsMpEng and nearly 16M faults on
> MOMHost.
> Keep in mind that the "M" with the number is million.
> We only recently started using Forefront on our servers, and this appears
> to be a relatively new issue, and I would like to know if others have been
> experiencing this as well.
> Richard|||Thanks for the link to the article. This does give some additional
information, however the problem in the article points more to high CPU
utilization rather than high number of page faults. Perhaps since this also
includes the MOM components, I should try posting in that forum as the
Forefront group is fairly new?
Richard
"Betelgeuse" <betelgeuse@.news.postalias> wrote in message
news:OBAYHbCDIHA.3712@.TK2MSFTNGP02.phx.gbl...
> This is not an exchange issue...
> But take a look at
> http://geekswithblogs.net/Coleman/archive/2007/03/26/109906.aspx
>
> "Richard Perry" <RichardPerry@.newsgroup.nospam> wrote in message
> news:3BFD2358-2798-4A29-B54A-EA90AD883293@.microsoft.com...
>>I am noticing that an many of my servers (Server 2003 EE SP2 R2), that the
>>MsMpEng.exe and MOMHost.exe processes are creating a significant number of
>>page faults.
>> For example, on my SQL 2000 SP4 server with 4GB RAM which has been up for
>> 19 days has nearly 19M page faults on MsMpEng and over 16M faults on
>> MOMHost.
>> Another example is my Exchange 2003 SP2 with 4GB RAM which has also been
>> up for 19 days has nearly 182M faults on MsMpEng and nearly 16M faults on
>> MOMHost.
>> Keep in mind that the "M" with the number is million.
>> We only recently started using Forefront on our servers, and this appears
>> to be a relatively new issue, and I would like to know if others have
>> been experiencing this as well.
>> Richard
>|||Dear Richard,
Thank you for posting here.
I understand that the process MsMpEng.exe and MOMHost.exe processes are
creating a significant number of page faults on several servers.
According to your description, it appears that the issue should not on the
SQL Server side. Based on my experience, this behavior can occur due to a
Memory Leak but sometimes it is normal for some processes. In order to
ensure the current status, I would like to confirm the following
information before going further:
1. Has the performance on these severs been affected due to this issue?
2. Please also use the Performance Monitor utility to monitor the stability
of memory pages used by these two processes.
To do so, please refer to the following steps:
--
a. Enter the Control Panel -> Administrative Tools -> Performance.
b. Select "Process" in the "Performance object" selection
c. Add the following counter for the two processes (MsMpEng.exe and
MOMHost.exe) in question:
- Working Set (The Working Set is the set of memory pages touched recently
by the threads in the process)
- Virtual Bytes (Virtual Bytes is the current size, in bytes, of the
virtual address space the process is using)
NOTE: You can also add some main process for this server. E.X: sqlservr.exe
process
d. If the indication goes very smoothly, it indicates that the memory usage
of these processes is stable. In opposition, the memory leak issue may
exist.
At the same time, on the SQL Server side, we can attempt to adjust the
memory to see if it helps. Below are the detail steps:
--
a. Open the SQL Server Management Studio
b. Right click on the SQL Server instance->choose Properties.
c. Click on the Memory tab on the left panel, adjust the memory for your
SQL Server.
Have a nice day!
Best regards,
Adams Qu
MCSE, MCDBA, MCTS
Microsoft Online Support
Microsoft Global Technical Support Center
Get Secure! - www.microsoft.com/security
=====================================================When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================This posting is provided "AS IS" with no warranties, and confers no rights.
--
| From: "Richard Perry" <RichardPerry@.newsgroup.nospam>
| References: <3BFD2358-2798-4A29-B54A-EA90AD883293@.microsoft.com>
<OBAYHbCDIHA.3712@.TK2MSFTNGP02.phx.gbl>
| Subject: Re: Excessive Page Faults?
| Date: Thu, 11 Oct 2007 12:56:40 -0700
| Lines: 1
| Message-ID: <84D91F2A-E4A6-4933-B34B-72879DD28277@.microsoft.com>
| MIME-Version: 1.0
| Content-Type: text/plain;
| format=flowed;
| charset="iso-8859-1";
| reply-type=response
| Content-Transfer-Encoding: 7bit
| X-Priority: 3
| X-MSMail-Priority: Normal
| Importance: Normal
| X-Newsreader: Microsoft Windows Live Mail 12.0.1365
| X-MimeOLE: Produced By Microsoft MimeOLE V12.0.1365
| X-MS-CommunityGroup-PostID: {84D91F2A-E4A6-4933-B34B-72879DD28277}
| X-MS-CommunityGroup-ThreadID: 3BFD2358-2798-4A29-B54A-EA90AD883293
| X-MS-CommunityGroup-ParentID: 47993B93-4F51-47EE-BE41-7DBD1C4B8AF7
| Newsgroups:
microsoft.public.exchange.admin,microsoft.public.security.forefront,microsof
t.public.sqlserver.server
| Path: TK2MSFTNGHUB02.phx.gbl
| Xref: TK2MSFTNGHUB02.phx.gbl microsoft.public.security.forefront:71
microsoft.public.sqlserver.server:27713
microsoft.public.exchange.admin:47476
| NNTP-Posting-Host: TK2MSFTNGHUB02.phx.gbl 127.0.0.1
| X-Tomcat-NG: microsoft.public.sqlserver.server
|
| Thanks for the link to the article. This does give some additional
| information, however the problem in the article points more to high CPU
| utilization rather than high number of page faults. Perhaps since this
also
| includes the MOM components, I should try posting in that forum as the
| Forefront group is fairly new?
|
| Richard
|
| "Betelgeuse" <betelgeuse@.news.postalias> wrote in message
| news:OBAYHbCDIHA.3712@.TK2MSFTNGP02.phx.gbl...
| > This is not an exchange issue...
| >
| > But take a look at
| > http://geekswithblogs.net/Coleman/archive/2007/03/26/109906.aspx
| >
| >
| > "Richard Perry" <RichardPerry@.newsgroup.nospam> wrote in message
| > news:3BFD2358-2798-4A29-B54A-EA90AD883293@.microsoft.com...
| >>I am noticing that an many of my servers (Server 2003 EE SP2 R2), that
the
| >>MsMpEng.exe and MOMHost.exe processes are creating a significant number
of
| >>page faults.
| >>
| >> For example, on my SQL 2000 SP4 server with 4GB RAM which has been up
for
| >> 19 days has nearly 19M page faults on MsMpEng and over 16M faults on
| >> MOMHost.
| >>
| >> Another example is my Exchange 2003 SP2 with 4GB RAM which has also
been
| >> up for 19 days has nearly 182M faults on MsMpEng and nearly 16M faults
on
| >> MOMHost.
| >>
| >> Keep in mind that the "M" with the number is million.
| >>
| >> We only recently started using Forefront on our servers, and this
appears
| >> to be a relatively new issue, and I would like to know if others have
| >> been experiencing this as well.
| >>
| >> Richard
| >
| >
||||Dear Richard,
We just want to say hi and check if there is anything further we can do for
you. If there is, please don't hesitate to let us know.
Regards,
Sophie Tan, MCSE 2000
Microsoft Online Support
Microsoft Global Technical Support Center
Get Secure! - www.microsoft.com/security
====================================================When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
====================================================This posting is provided "AS IS" with no warranties, and confers no rights.
| X-Tomcat-ID: 94083421
| References: <3BFD2358-2798-4A29-B54A-EA90AD883293@.microsoft.com>
<OBAYHbCDIHA.3712@.TK2MSFTNGP02.phx.gbl>
<84D91F2A-E4A6-4933-B34B-72879DD28277@.microsoft.com>
| MIME-Version: 1.0
| Content-Type: text/plain
| Content-Transfer-Encoding: 7bit
| From: v-adamqu@.online.microsoft.com (Adams Qu [MSFT])
| Organization: Microsoft
| Date: Fri, 12 Oct 2007 07:35:19 GMT
| Subject: Re: Excessive Page Faults?
| X-Tomcat-NG: microsoft.public.sqlserver.server
| Message-ID: <8LjAyJKDIHA.4664@.TK2MSFTNGHUB02.phx.gbl>
| Newsgroups: microsoft.public.sqlserver.server
| Lines: 132
| Path: TK2MSFTNGHUB02.phx.gbl
| Xref: TK2MSFTNGHUB02.phx.gbl microsoft.public.sqlserver.server:27758
| NNTP-Posting-Host: tomcatimport2.phx.gbl 10.201.218.182
|
| Dear Richard,
|
| Thank you for posting here.
|
| I understand that the process MsMpEng.exe and MOMHost.exe processes are
| creating a significant number of page faults on several servers.
|
| According to your description, it appears that the issue should not on
the
| SQL Server side. Based on my experience, this behavior can occur due to a
| Memory Leak but sometimes it is normal for some processes. In order to
| ensure the current status, I would like to confirm the following
| information before going further:
|
| 1. Has the performance on these severs been affected due to this issue?
|
| 2. Please also use the Performance Monitor utility to monitor the
stability
| of memory pages used by these two processes.
|
| To do so, please refer to the following steps:
| --
| a. Enter the Control Panel -> Administrative Tools -> Performance.
| b. Select "Process" in the "Performance object" selection
| c. Add the following counter for the two processes (MsMpEng.exe and
| MOMHost.exe) in question:
|
| - Working Set (The Working Set is the set of memory pages touched
recently
| by the threads in the process)
| - Virtual Bytes (Virtual Bytes is the current size, in bytes, of the
| virtual address space the process is using)
|
| NOTE: You can also add some main process for this server. E.X:
sqlservr.exe
| process
|
| d. If the indication goes very smoothly, it indicates that the memory
usage
| of these processes is stable. In opposition, the memory leak issue may
| exist.
|
| At the same time, on the SQL Server side, we can attempt to adjust the
| memory to see if it helps. Below are the detail steps:
| --
| a. Open the SQL Server Management Studio
| b. Right click on the SQL Server instance->choose Properties.
| c. Click on the Memory tab on the left panel, adjust the memory for your
| SQL Server.
|
| Have a nice day!
|
| Best regards,
|
| Adams Qu
| MCSE, MCDBA, MCTS
| Microsoft Online Support
|
| Microsoft Global Technical Support Center
|
| Get Secure! - www.microsoft.com/security
| =====================================================| When responding to posts, please "Reply to Group" via your newsreader so
| that others may learn and benefit from your issue.
| =====================================================| This posting is provided "AS IS" with no warranties, and confers no
rights.
|
| --
| | From: "Richard Perry" <RichardPerry@.newsgroup.nospam>
| | References: <3BFD2358-2798-4A29-B54A-EA90AD883293@.microsoft.com>
| <OBAYHbCDIHA.3712@.TK2MSFTNGP02.phx.gbl>
| | Subject: Re: Excessive Page Faults?
| | Date: Thu, 11 Oct 2007 12:56:40 -0700
| | Lines: 1
| | Message-ID: <84D91F2A-E4A6-4933-B34B-72879DD28277@.microsoft.com>
| | MIME-Version: 1.0
| | Content-Type: text/plain;
| | format=flowed;
| | charset="iso-8859-1";
| | reply-type=response
| | Content-Transfer-Encoding: 7bit
| | X-Priority: 3
| | X-MSMail-Priority: Normal
| | Importance: Normal
| | X-Newsreader: Microsoft Windows Live Mail 12.0.1365
| | X-MimeOLE: Produced By Microsoft MimeOLE V12.0.1365
| | X-MS-CommunityGroup-PostID: {84D91F2A-E4A6-4933-B34B-72879DD28277}
| | X-MS-CommunityGroup-ThreadID: 3BFD2358-2798-4A29-B54A-EA90AD883293
| | X-MS-CommunityGroup-ParentID: 47993B93-4F51-47EE-BE41-7DBD1C4B8AF7
| | Newsgroups:
|
microsoft.public.exchange.admin,microsoft.public.security.forefront,microsof
| t.public.sqlserver.server
| | Path: TK2MSFTNGHUB02.phx.gbl
| | Xref: TK2MSFTNGHUB02.phx.gbl microsoft.public.security.forefront:71
| microsoft.public.sqlserver.server:27713
| microsoft.public.exchange.admin:47476
| | NNTP-Posting-Host: TK2MSFTNGHUB02.phx.gbl 127.0.0.1
| | X-Tomcat-NG: microsoft.public.sqlserver.server
| |
| | Thanks for the link to the article. This does give some additional
| | information, however the problem in the article points more to high CPU
| | utilization rather than high number of page faults. Perhaps since this
| also
| | includes the MOM components, I should try posting in that forum as the
| | Forefront group is fairly new?
| |
| | Richard
| |
| | "Betelgeuse" <betelgeuse@.news.postalias> wrote in message
| | news:OBAYHbCDIHA.3712@.TK2MSFTNGP02.phx.gbl...
| | > This is not an exchange issue...
| | >
| | > But take a look at
| | > http://geekswithblogs.net/Coleman/archive/2007/03/26/109906.aspx
| | >
| | >
| | > "Richard Perry" <RichardPerry@.newsgroup.nospam> wrote in message
| | > news:3BFD2358-2798-4A29-B54A-EA90AD883293@.microsoft.com...
| | >>I am noticing that an many of my servers (Server 2003 EE SP2 R2),
that
| the
| | >>MsMpEng.exe and MOMHost.exe processes are creating a significant
number
| of
| | >>page faults.
| | >>
| | >> For example, on my SQL 2000 SP4 server with 4GB RAM which has been
up
| for
| | >> 19 days has nearly 19M page faults on MsMpEng and over 16M faults on
| | >> MOMHost.
| | >>
| | >> Another example is my Exchange 2003 SP2 with 4GB RAM which has also
| been
| | >> up for 19 days has nearly 182M faults on MsMpEng and nearly 16M
faults
| on
| | >> MOMHost.
| | >>
| | >> Keep in mind that the "M" with the number is million.
| | >>
| | >> We only recently started using Forefront on our servers, and this
| appears
| | >> to be a relatively new issue, and I would like to know if others
have
| | >> been experiencing this as well.
| | >>
| | >> Richard
| | >
| | >
| |
|
||||Sorry for the long delay in replying to this post. With the fires here is
SoCal, things have been fairly hectic.
Following your suggestions, I am verified that there is not a memory leak
with regards to these processes. Therefore, I am left with the simple
concern of the performance of Forefront on the Server platform. I will raise
this issue in the Forefront forums.
I do have a recurring issue with timeouts to the database that I am
troubleshooting, and it was this issue that prompted my looking at the
paging performance. I have consistently believed that the cause if these
timeouts is due to our network configuration, but for the sake of
completeness, I have been verifying our SQL setup. I will post regarding
those timeouts in another thread.
Sophie Tan, if you see this, I wanted to thank you for your follow up post
as well. At this time, I would consider this thread closed.
Richard Perry
"Adams Qu [MSFT]" <v-adamqu@.online.microsoft.com> wrote in message
news:8LjAyJKDIHA.4664@.TK2MSFTNGHUB02.phx.gbl...
> Dear Richard,
> Thank you for posting here.
> I understand that the process MsMpEng.exe and MOMHost.exe processes are
> creating a significant number of page faults on several servers.
> According to your description, it appears that the issue should not on the
> SQL Server side. Based on my experience, this behavior can occur due to a
> Memory Leak but sometimes it is normal for some processes. In order to
> ensure the current status, I would like to confirm the following
> information before going further:
> 1. Has the performance on these severs been affected due to this issue?
> 2. Please also use the Performance Monitor utility to monitor the
> stability
> of memory pages used by these two processes.
> To do so, please refer to the following steps:
> --
> a. Enter the Control Panel -> Administrative Tools -> Performance.
> b. Select "Process" in the "Performance object" selection
> c. Add the following counter for the two processes (MsMpEng.exe and
> MOMHost.exe) in question:
> - Working Set (The Working Set is the set of memory pages touched recently
> by the threads in the process)
> - Virtual Bytes (Virtual Bytes is the current size, in bytes, of the
> virtual address space the process is using)
> NOTE: You can also add some main process for this server. E.X:
> sqlservr.exe
> process
> d. If the indication goes very smoothly, it indicates that the memory
> usage
> of these processes is stable. In opposition, the memory leak issue may
> exist.
> At the same time, on the SQL Server side, we can attempt to adjust the
> memory to see if it helps. Below are the detail steps:
> --
> a. Open the SQL Server Management Studio
> b. Right click on the SQL Server instance->choose Properties.
> c. Click on the Memory tab on the left panel, adjust the memory for your
> SQL Server.
> Have a nice day!
> Best regards,
> Adams Qu
> MCSE, MCDBA, MCTS
> Microsoft Online Support
> Microsoft Global Technical Support Center
> Get Secure! - www.microsoft.com/security
> =====================================================> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> =====================================================> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> --
> | From: "Richard Perry" <RichardPerry@.newsgroup.nospam>
> | References: <3BFD2358-2798-4A29-B54A-EA90AD883293@.microsoft.com>
> <OBAYHbCDIHA.3712@.TK2MSFTNGP02.phx.gbl>
> | Subject: Re: Excessive Page Faults?
> | Date: Thu, 11 Oct 2007 12:56:40 -0700
> | Lines: 1
> | Message-ID: <84D91F2A-E4A6-4933-B34B-72879DD28277@.microsoft.com>
> | MIME-Version: 1.0
> | Content-Type: text/plain;
> | format=flowed;
> | charset="iso-8859-1";
> | reply-type=response
> | Content-Transfer-Encoding: 7bit
> | X-Priority: 3
> | X-MSMail-Priority: Normal
> | Importance: Normal
> | X-Newsreader: Microsoft Windows Live Mail 12.0.1365
> | X-MimeOLE: Produced By Microsoft MimeOLE V12.0.1365
> | X-MS-CommunityGroup-PostID: {84D91F2A-E4A6-4933-B34B-72879DD28277}
> | X-MS-CommunityGroup-ThreadID: 3BFD2358-2798-4A29-B54A-EA90AD883293
> | X-MS-CommunityGroup-ParentID: 47993B93-4F51-47EE-BE41-7DBD1C4B8AF7
> | Newsgroups:
> microsoft.public.exchange.admin,microsoft.public.security.forefront,microsof
> t.public.sqlserver.server
> | Path: TK2MSFTNGHUB02.phx.gbl
> | Xref: TK2MSFTNGHUB02.phx.gbl microsoft.public.security.forefront:71
> microsoft.public.sqlserver.server:27713
> microsoft.public.exchange.admin:47476
> | NNTP-Posting-Host: TK2MSFTNGHUB02.phx.gbl 127.0.0.1
> | X-Tomcat-NG: microsoft.public.sqlserver.server
> |
> | Thanks for the link to the article. This does give some additional
> | information, however the problem in the article points more to high CPU
> | utilization rather than high number of page faults. Perhaps since this
> also
> | includes the MOM components, I should try posting in that forum as the
> | Forefront group is fairly new?
> |
> | Richard
> |
> | "Betelgeuse" <betelgeuse@.news.postalias> wrote in message
> | news:OBAYHbCDIHA.3712@.TK2MSFTNGP02.phx.gbl...
> | > This is not an exchange issue...
> | >
> | > But take a look at
> | > http://geekswithblogs.net/Coleman/archive/2007/03/26/109906.aspx
> | >
> | >
> | > "Richard Perry" <RichardPerry@.newsgroup.nospam> wrote in message
> | > news:3BFD2358-2798-4A29-B54A-EA90AD883293@.microsoft.com...
> | >>I am noticing that an many of my servers (Server 2003 EE SP2 R2), that
> the
> | >>MsMpEng.exe and MOMHost.exe processes are creating a significant
> number
> of
> | >>page faults.
> | >>
> | >> For example, on my SQL 2000 SP4 server with 4GB RAM which has been up
> for
> | >> 19 days has nearly 19M page faults on MsMpEng and over 16M faults on
> | >> MOMHost.
> | >>
> | >> Another example is my Exchange 2003 SP2 with 4GB RAM which has also
> been
> | >> up for 19 days has nearly 182M faults on MsMpEng and nearly 16M
> faults
> on
> | >> MOMHost.
> | >>
> | >> Keep in mind that the "M" with the number is million.
> | >>
> | >> We only recently started using Forefront on our servers, and this
> appears
> | >> to be a relatively new issue, and I would like to know if others have
> | >> been experiencing this as well.
> | >>
> | >> Richard
> | >
> | >
> |
>|||Dear Richard,
Thank you for posting back.
From your last post, we are glad to hear that the memory leak issue does
not exist after we verified manually on the server.
I also understand that another issue with timeouts to the database is
experienced. To help you resolve the issue more efficiently, I also would
like to share with you the following troubleshooting information. You can
try the following points before submitting the new thread.
Reference Information on the second issue
--
Based on my experience, the time-out connection issue can be concluded to
be caused by one of the following two factors commonly:
a. Caused by the network configuration (as you mentioned)
b. The second cause is that the SQL Server is running on a heavy load at
that time.
c. Application or hard driver factors.
Please use SQL Profiler and Perfmon to monitor your SQL Server to see what
happens if the connection timeout occurs.
I also would like to provide the following links for your reference:
HOW TO: Troubleshoot Application Performance with SQL Server
http://support.microsoft.com/kb/224587/en-us
Troubleshooting Performance Problems in SQL Server 2005
http://www.microsoft.com/technet/prodtechnol/sql/2005/tsprfprb.mspx
INF: Understanding and Resolving SQL Server 7.0 or 2000 Blocking Problems
http://support.microsoft.com/kb/224453/en-us
Hope above information is helpful. Once we see your new thread, we will be
glad to follow up and help you. If you have any other questions or
concerns, please do not hesitate to contact us. It is always my pleasure to
be of assistance.
Have a nice day!
Best regards,
Adams Qu
MCSE, MCDBA, MCTS
Microsoft Online Support
Microsoft Global Technical Support Center
Get Secure! - www.microsoft.com/security
=====================================================When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================This posting is provided "AS IS" with no warranties, and confers no rights.
--
| From: "Richard Perry" <RichardPerry@.newsgroup.nospam>
| References: <3BFD2358-2798-4A29-B54A-EA90AD883293@.microsoft.com>
<OBAYHbCDIHA.3712@.TK2MSFTNGP02.phx.gbl>
<84D91F2A-E4A6-4933-B34B-72879DD28277@.microsoft.com>
<8LjAyJKDIHA.4664@.TK2MSFTNGHUB02.phx.gbl>
| Subject: Re: Excessive Page Faults?
| Date: Wed, 31 Oct 2007 16:54:13 -0700
| Lines: 2
| MIME-Version: 1.0
| Content-Type: text/plain;
| format=flowed;
| charset="iso-8859-1";
| reply-type=original
| Content-Transfer-Encoding: 7bit
| X-Priority: 3
| X-MSMail-Priority: Normal
| Importance: Normal
| X-Newsreader: Microsoft Windows Live Mail 12.0.1365
| X-MimeOLE: Produced By Microsoft MimeOLE V12.0.1365
| Message-ID: <#82$XlBHIHA.4768@.TK2MSFTNGP03.phx.gbl>
| Newsgroups: microsoft.public.sqlserver.server
| NNTP-Posting-Host: 209.242.152.130
| Path: TK2MSFTNGHUB02.phx.gbl!TK2MSFTNGP01.phx.gbl!TK2MSFTNGP03.phx.gbl
| Xref: TK2MSFTNGHUB02.phx.gbl microsoft.public.sqlserver.server:29221
| X-Tomcat-NG: microsoft.public.sqlserver.server
|
| Sorry for the long delay in replying to this post. With the fires here is
| SoCal, things have been fairly hectic.
|
| Following your suggestions, I am verified that there is not a memory leak
| with regards to these processes. Therefore, I am left with the simple
| concern of the performance of Forefront on the Server platform. I will
raise
| this issue in the Forefront forums.
|
| I do have a recurring issue with timeouts to the database that I am
| troubleshooting, and it was this issue that prompted my looking at the
| paging performance. I have consistently believed that the cause if these
| timeouts is due to our network configuration, but for the sake of
| completeness, I have been verifying our SQL setup. I will post regarding
| those timeouts in another thread.
|
| Sophie Tan, if you see this, I wanted to thank you for your follow up
post
| as well. At this time, I would consider this thread closed.
|
| Richard Perry
|
|
| "Adams Qu [MSFT]" <v-adamqu@.online.microsoft.com> wrote in message
| news:8LjAyJKDIHA.4664@.TK2MSFTNGHUB02.phx.gbl...
| > Dear Richard,
| >
| > Thank you for posting here.
| >
| > I understand that the process MsMpEng.exe and MOMHost.exe processes are
| > creating a significant number of page faults on several servers.
| >
| > According to your description, it appears that the issue should not on
the
| > SQL Server side. Based on my experience, this behavior can occur due to
a
| > Memory Leak but sometimes it is normal for some processes. In order to
| > ensure the current status, I would like to confirm the following
| > information before going further:
| >
| > 1. Has the performance on these severs been affected due to this issue?
| >
| > 2. Please also use the Performance Monitor utility to monitor the
| > stability
| > of memory pages used by these two processes.
| >
| > To do so, please refer to the following steps:
| > --
| > a. Enter the Control Panel -> Administrative Tools -> Performance.
| > b. Select "Process" in the "Performance object" selection
| > c. Add the following counter for the two processes (MsMpEng.exe and
| > MOMHost.exe) in question:
| >
| > - Working Set (The Working Set is the set of memory pages touched
recently
| > by the threads in the process)
| > - Virtual Bytes (Virtual Bytes is the current size, in bytes, of the
| > virtual address space the process is using)
| >
| > NOTE: You can also add some main process for this server. E.X:
| > sqlservr.exe
| > process
| >
| > d. If the indication goes very smoothly, it indicates that the memory
| > usage
| > of these processes is stable. In opposition, the memory leak issue may
| > exist.
| >
| > At the same time, on the SQL Server side, we can attempt to adjust the
| > memory to see if it helps. Below are the detail steps:
| > --
| > a. Open the SQL Server Management Studio
| > b. Right click on the SQL Server instance->choose Properties.
| > c. Click on the Memory tab on the left panel, adjust the memory for your
| > SQL Server.
| >
| > Have a nice day!
| >
| > Best regards,
| >
| > Adams Qu
| > MCSE, MCDBA, MCTS
| > Microsoft Online Support
| >
| > Microsoft Global Technical Support Center
| >
| > Get Secure! - www.microsoft.com/security
| > =====================================================| > When responding to posts, please "Reply to Group" via your newsreader so
| > that others may learn and benefit from your issue.
| > =====================================================| > This posting is provided "AS IS" with no warranties, and confers no
| > rights.
| >
| > --
| > | From: "Richard Perry" <RichardPerry@.newsgroup.nospam>
| > | References: <3BFD2358-2798-4A29-B54A-EA90AD883293@.microsoft.com>
| > <OBAYHbCDIHA.3712@.TK2MSFTNGP02.phx.gbl>
| > | Subject: Re: Excessive Page Faults?
| > | Date: Thu, 11 Oct 2007 12:56:40 -0700
| > | Lines: 1
| > | Message-ID: <84D91F2A-E4A6-4933-B34B-72879DD28277@.microsoft.com>
| > | MIME-Version: 1.0
| > | Content-Type: text/plain;
| > | format=flowed;
| > | charset="iso-8859-1";
| > | reply-type=response
| > | Content-Transfer-Encoding: 7bit
| > | X-Priority: 3
| > | X-MSMail-Priority: Normal
| > | Importance: Normal
| > | X-Newsreader: Microsoft Windows Live Mail 12.0.1365
| > | X-MimeOLE: Produced By Microsoft MimeOLE V12.0.1365
| > | X-MS-CommunityGroup-PostID: {84D91F2A-E4A6-4933-B34B-72879DD28277}
| > | X-MS-CommunityGroup-ThreadID: 3BFD2358-2798-4A29-B54A-EA90AD883293
| > | X-MS-CommunityGroup-ParentID: 47993B93-4F51-47EE-BE41-7DBD1C4B8AF7
| > | Newsgroups:
| >
microsoft.public.exchange.admin,microsoft.public.security.forefront,microsof
| > t.public.sqlserver.server
| > | Path: TK2MSFTNGHUB02.phx.gbl
| > | Xref: TK2MSFTNGHUB02.phx.gbl microsoft.public.security.forefront:71
| > microsoft.public.sqlserver.server:27713
| > microsoft.public.exchange.admin:47476
| > | NNTP-Posting-Host: TK2MSFTNGHUB02.phx.gbl 127.0.0.1
| > | X-Tomcat-NG: microsoft.public.sqlserver.server
| > |
| > | Thanks for the link to the article. This does give some additional
| > | information, however the problem in the article points more to high
CPU
| > | utilization rather than high number of page faults. Perhaps since this
| > also
| > | includes the MOM components, I should try posting in that forum as the
| > | Forefront group is fairly new?
| > |
| > | Richard
| > |
| > | "Betelgeuse" <betelgeuse@.news.postalias> wrote in message
| > | news:OBAYHbCDIHA.3712@.TK2MSFTNGP02.phx.gbl...
| > | > This is not an exchange issue...
| > | >
| > | > But take a look at
| > | > http://geekswithblogs.net/Coleman/archive/2007/03/26/109906.aspx
| > | >
| > | >
| > | > "Richard Perry" <RichardPerry@.newsgroup.nospam> wrote in message
| > | > news:3BFD2358-2798-4A29-B54A-EA90AD883293@.microsoft.com...
| > | >>I am noticing that an many of my servers (Server 2003 EE SP2 R2),
that
| > the
| > | >>MsMpEng.exe and MOMHost.exe processes are creating a significant
| > number
| > of
| > | >>page faults.
| > | >>
| > | >> For example, on my SQL 2000 SP4 server with 4GB RAM which has been
up
| > for
| > | >> 19 days has nearly 19M page faults on MsMpEng and over 16M faults
on
| > | >> MOMHost.
| > | >>
| > | >> Another example is my Exchange 2003 SP2 with 4GB RAM which has also
| > been
| > | >> up for 19 days has nearly 182M faults on MsMpEng and nearly 16M
| > faults
| > on
| > | >> MOMHost.
| > | >>
| > | >> Keep in mind that the "M" with the number is million.
| > | >>
| > | >> We only recently started using Forefront on our servers, and this
| > appears
| > | >> to be a relatively new issue, and I would like to know if others
have
| > | >> been experiencing this as well.
| > | >>
| > | >> Richard
| > | >
| > | >
| > |
| >
|

Excessive network usage with SQL 2000 to SQL 2005 transactional replication

We have a SQL2000 database (Publisher) replicating inserts and updates across a 10Mb link to a SQL 2005 database (Subscriber). The Publisher has two tables we are interested in, 1 with 50 columns and 1 with 15. Both tables have 6 insert/update triggers that fire when a change is made to update columns on the publisher database.

We have set up a pull transactional replication from the Subscriber to occur against the Publisher every minute. We have limited the subscription/replication configuration to Publsih 6 columns from table 1 and 4 from table 2. Any change occuring on any other columns in the Publisher are of no interest. The SQL 2005 database has a trigger on table 1 and table 2 to insert values into a third table. There are around 7,000 insert/updates on table 1 and 28,000 on table 2 per day. All fields in the tables are text.

We are seeing "excessive" network traffic occuring of approximately 1MB per minute (approx 2GB per 24 hrs). We also see that the Distributor databases are getting very large -- upto around 30GB and growing until they get culled. We have reduced the culling intrval from 72 hrs to 24 hours to reduce the size.

Does anyone have any suggestions as to how this "excessive" network traffic can be minimised and how the distributor database size can be minimised. I think that maybe they are both related?

Thanks,

Geoff

WA POLICE

is this merge or tran?

|||

i will guess it's merge replication. MErge replication does transfer a lot of metadata over the wire, you can see what's being replicated by running a profiler trace.

|||

sorry, i totally misread your thread, you do have transactional replication. You need to understand the data changes you are making at the publisher and what is getting replicated. Every change made to a table that is enabled for replication will get marked as a change that needs to be replicated. These changes, one by one, are inserted into the distribution database. So if your database is growing to 30 GB, you must either be doing a lot of inserts or updates, or you might be touching BLOB columns as well. You should investigate exactly what you're replicating to understand your scenario.

|||Thanks for the information Gerg. Yes we do have transactional and yes we have determined that the traffic seems to be related to the triggers on the publisher. We are looking at ways to limit these at present without impacting the business functions that use that database.

Excessive network usage with SQL 2000 to SQL 2005 transactional replication

Does anyone have any suggestions as to how this "excessive" network traffic can be minimised and how the distributor database size can be minimised. I think that maybe they are both related?

Thanks,

Geoff

WA POLICE

is this merge or tran?

|||

i will guess it's merge replication. MErge replication does transfer a lot of metadata over the wire, you can see what's being replicated by running a profiler trace.

|||

Excessive network usage with SQL 2000 to SQL 2005 transactional replication

Does anyone have any suggestions as to how this "excessive" network traffic can be minimised and how the distributor database size can be minimised. I think that maybe they are both related?

Thanks,

Geoff

WA POLICE

is this merge or tran?

|||

i will guess it's merge replication. MErge replication does transfer a lot of metadata over the wire, you can see what's being replicated by running a profiler trace.

|||

Excessive network usage with SQL 2000 to SQL 2005 transactional replication

Does anyone have any suggestions as to how this "excessive" network traffic can be minimised and how the distributor database size can be minimised. I think that maybe they are both related?

Thanks,

Geoff

WA POLICE

is this merge or tran?

|||

i will guess it's merge replication. MErge replication does transfer a lot of metadata over the wire, you can see what's being replicated by running a profiler trace.

|||

Thanks for the information Gerg. Yes we do have transactional and yes we have determined that the traffic seems to be related to the triggers on the publisher. We are looking at ways to limit these at present without impacting the business functions that use that database.

excessive network traffic

Hi all,
I have a Win2000 server with Exchange2003 and SQL
server2000 SP1.
The system seems to work OK, but after about 1 hour after
startup, the SQL server starts to generate networktraffic
that completely floods the network. It is now impossible
to log into the server, get on the network or access the
internet from any workstation that is connected to the
network. In about one hour more than 50.000.000 network
packets are sent into space. When I disconnect the
internet connection, it just continues to do so. When I
stop the SQL service in the service manager, traffic stops
and everything works again.
Any thoughts?
Piet Koenekoop
"Piet Koenekoop" <pietkoenekoop@.hotmail.com> wrote in message
news:1baf501c42130$42cc0430$a501280a@.phx.gbl...
> Hi all,
> I have a Win2000 server with Exchange2003 and SQL
> server2000 SP1.
> The system seems to work OK, but after about 1 hour after
> startup, the SQL server starts to generate networktraffic
> that completely floods the network. It is now impossible
> to log into the server, get on the network or access the
> internet from any workstation that is connected to the
> network. In about one hour more than 50.000.000 network
> packets are sent into space. When I disconnect the
> internet connection, it just continues to do so. When I
> stop the SQL service in the service manager, traffic stops
> and everything works again.
> Any thoughts?
I'm no expert, but the symtoms looks like Slammer virus.
Steven Ung
"The source of all greatness lies within you" - Anonymous
|||Yep, looks like the slammer virus to me as well...
Update your SQL Server to SP3a, and reboot... and life gets good again...
"Piet Koenekoop" <pietkoenekoop@.hotmail.com> wrote in message
news:1baf501c42130$42cc0430$a501280a@.phx.gbl...
> Hi all,
> I have a Win2000 server with Exchange2003 and SQL
> server2000 SP1.
> The system seems to work OK, but after about 1 hour after
> startup, the SQL server starts to generate networktraffic
> that completely floods the network. It is now impossible
> to log into the server, get on the network or access the
> internet from any workstation that is connected to the
> network. In about one hour more than 50.000.000 network
> packets are sent into space. When I disconnect the
> internet connection, it just continues to do so. When I
> stop the SQL service in the service manager, traffic stops
> and everything works again.
> Any thoughts?
> Piet Koenekoop
|||Thanks a lot for the two suggestions. The Slammer virus was not detected by the AV software, but the installation of SP3 has solved my problem.
Piet Koenekoop

excessive network traffic

Hi all,
I have a Win2000 server with Exchange2003 and SQL
server2000 SP1.
The system seems to work OK, but after about 1 hour after
startup, the SQL server starts to generate networktraffic
that completely floods the network. It is now impossible
to log into the server, get on the network or access the
internet from any workstation that is connected to the
network. In about one hour more than 50.000.000 network
packets are sent into space. When I disconnect the
internet connection, it just continues to do so. When I
stop the SQL service in the service manager, traffic stops
and everything works again.
Any thoughts'
Piet Koenekoop"Piet Koenekoop" <pietkoenekoop@.hotmail.com> wrote in message
news:1baf501c42130$42cc0430$a501280a@.phx
.gbl...
> Hi all,
> I have a Win2000 server with Exchange2003 and SQL
> server2000 SP1.
> The system seems to work OK, but after about 1 hour after
> startup, the SQL server starts to generate networktraffic
> that completely floods the network. It is now impossible
> to log into the server, get on the network or access the
> internet from any workstation that is connected to the
> network. In about one hour more than 50.000.000 network
> packets are sent into space. When I disconnect the
> internet connection, it just continues to do so. When I
> stop the SQL service in the service manager, traffic stops
> and everything works again.
> Any thoughts'
I'm no expert, but the symtoms looks like Slammer virus.
--
Steven Ung
"The source of all greatness lies within you" - Anonymous|||Yep, looks like the slammer virus to me as well...
Update your SQL Server to SP3a, and reboot... and life gets good again...
"Piet Koenekoop" <pietkoenekoop@.hotmail.com> wrote in message
news:1baf501c42130$42cc0430$a501280a@.phx
.gbl...
> Hi all,
> I have a Win2000 server with Exchange2003 and SQL
> server2000 SP1.
> The system seems to work OK, but after about 1 hour after
> startup, the SQL server starts to generate networktraffic
> that completely floods the network. It is now impossible
> to log into the server, get on the network or access the
> internet from any workstation that is connected to the
> network. In about one hour more than 50.000.000 network
> packets are sent into space. When I disconnect the
> internet connection, it just continues to do so. When I
> stop the SQL service in the service manager, traffic stops
> and everything works again.
> Any thoughts'
> Piet Koenekoop|||Thanks a lot for the two suggestions. The Slammer virus was not detected by
the AV software, but the installation of SP3 has solved my problem.
Piet Koenekoop

excessive network traffic

Hi all,
I have a Win2000 server with Exchange2003 and SQL
server2000 SP1.
The system seems to work OK, but after about 1 hour after
startup, the SQL server starts to generate networktraffic
that completely floods the network. It is now impossible
to log into the server, get on the network or access the
internet from any workstation that is connected to the
network. In about one hour more than 50.000.000 network
packets are sent into space. When I disconnect the
internet connection, it just continues to do so. When I
stop the SQL service in the service manager, traffic stops
and everything works again.
Any thoughts'
Piet Koenekoop"Piet Koenekoop" <pietkoenekoop@.hotmail.com> wrote in message
news:1baf501c42130$42cc0430$a501280a@.phx.gbl...
> Hi all,
> I have a Win2000 server with Exchange2003 and SQL
> server2000 SP1.
> The system seems to work OK, but after about 1 hour after
> startup, the SQL server starts to generate networktraffic
> that completely floods the network. It is now impossible
> to log into the server, get on the network or access the
> internet from any workstation that is connected to the
> network. In about one hour more than 50.000.000 network
> packets are sent into space. When I disconnect the
> internet connection, it just continues to do so. When I
> stop the SQL service in the service manager, traffic stops
> and everything works again.
> Any thoughts'
I'm no expert, but the symtoms looks like Slammer virus.
--
Steven Ung
"The source of all greatness lies within you" - Anonymous|||Yep, looks like the slammer virus to me as well...
Update your SQL Server to SP3a, and reboot... and life gets good again...
"Piet Koenekoop" <pietkoenekoop@.hotmail.com> wrote in message
news:1baf501c42130$42cc0430$a501280a@.phx.gbl...
> Hi all,
> I have a Win2000 server with Exchange2003 and SQL
> server2000 SP1.
> The system seems to work OK, but after about 1 hour after
> startup, the SQL server starts to generate networktraffic
> that completely floods the network. It is now impossible
> to log into the server, get on the network or access the
> internet from any workstation that is connected to the
> network. In about one hour more than 50.000.000 network
> packets are sent into space. When I disconnect the
> internet connection, it just continues to do so. When I
> stop the SQL service in the service manager, traffic stops
> and everything works again.
> Any thoughts'
> Piet Koenekoop|||Thanks a lot for the two suggestions. The Slammer virus was not detected by the AV software, but the installation of SP3 has solved my problem
Piet Koenekoop

excessive memory usage problem

We have recently migrated from a Windows Server 2000/Sql Server 2000
Standard Edition machine to Windows Server 2003 Standard Edition/Sql
Server 2000 Standard Edition. We are using the machine as a combined
web and database server running asp and com dll's. The old machine had
about 1GB Ram and sql server used about 700Mb and the machine ran ok.
On the new machine with 2.5GB Ram sql server seems to gradually build
up its memory use to 1.9GB from 300mb after a reeboot. The machine
seems be experiencing some performance problems even though we have
the same amount of website traffic as before, I suspect the memory
usage is a problem, it seems as if sql server it not releasing memory.
Is their a way to diagnose/control this? Is it possible that there is
some difference in windows 2003(with IIS 6) that causes sql server to
retain more memory, perhaps not relasing database connections from asp
as quickly as before'
We have the latest service pack on sql server 2000.
Thanks
ScottIn EM, RClick the server/ props/ memory/ set a max.
>--Original Message--
>We have recently migrated from a Windows Server 2000/Sql
Server 2000
>Standard Edition machine to Windows Server 2003
Standard Edition/Sql
>Server 2000 Standard Edition. We are using the machine as
a combined
>web and database server running asp and com dll's. The
old machine had
>about 1GB Ram and sql server used about 700Mb and the
machine ran ok.
>On the new machine with 2.5GB Ram sql server seems to
gradually build
>up its memory use to 1.9GB from 300mb after a reeboot.
The machine
>seems be experiencing some performance problems even
though we have
>the same amount of website traffic as before, I suspect
the memory
>usage is a problem, it seems as if sql server it not
releasing memory.
>Is their a way to diagnose/control this? Is it possible
that there is
>some difference in windows 2003(with IIS 6) that causes
sql server to
>retain more memory, perhaps not relasing database
connections from asp
>as quickly as before'
>We have the latest service pack on sql server 2000.
>Thanks
>Scott
>.
>|||AFAIK, IIS6/Win2003 should be faster than your previous environment.
If SQL takes 1.9GB, you still have 0.6GB for OS and asp, which is double the
amount you had in your previous configuration, right?
The behaviour you describe is by design I believe - SQLServer will take as
much memory as it can (up to 2GB).
Are you _very_ sure that you have the latest service pack?
I have just very recently been studying maybe a bit similar
ASP/COM/IIS5/SQLServer2000 system which encountered performance problems
(after SP3 installation), and was finally cured when we (re)installed very
latest SP3a to application servers. The effect was really amazing. Our app
was using ODBC.
Just an idea,
pexi
<scott_mcarthur2003@.yahoo.co.uk> wrote in message
news:92c92d63.0311200800.124926ff@.posting.google.com...
> We have recently migrated from a Windows Server 2000/Sql Server 2000
> Standard Edition machine to Windows Server 2003 Standard Edition/Sql
> Server 2000 Standard Edition. We are using the machine as a combined
> web and database server running asp and com dll's. The old machine had
> about 1GB Ram and sql server used about 700Mb and the machine ran ok.
> On the new machine with 2.5GB Ram sql server seems to gradually build
> up its memory use to 1.9GB from 300mb after a reeboot. The machine
> seems be experiencing some performance problems even though we have
> the same amount of website traffic as before, I suspect the memory
> usage is a problem, it seems as if sql server it not releasing memory.
> Is their a way to diagnose/control this? Is it possible that there is
> some difference in windows 2003(with IIS 6) that causes sql server to
> retain more memory, perhaps not relasing database connections from asp
> as quickly as before'
> We have the latest service pack on sql server 2000.
> Thanks
> Scott|||Despite the wording in Q814410, below memoryLeak is fixed in SP3A according
to our tests:
http://support.microsoft.com/default.aspx?scid=kb;en-us;814410
which might explain your experience with SP3A.
/jensk
"Pexi" <pekkadotheimonen@.plenwaredotnospamdotcom> wrote in message
news:ef8Jz05rDHA.560@.TK2MSFTNGP11.phx.gbl...
> AFAIK, IIS6/Win2003 should be faster than your previous environment.
> If SQL takes 1.9GB, you still have 0.6GB for OS and asp, which is double
the
> amount you had in your previous configuration, right?
> The behaviour you describe is by design I believe - SQLServer will take as
> much memory as it can (up to 2GB).
> Are you _very_ sure that you have the latest service pack?
> I have just very recently been studying maybe a bit similar
> ASP/COM/IIS5/SQLServer2000 system which encountered performance problems
> (after SP3 installation), and was finally cured when we (re)installed very
> latest SP3a to application servers. The effect was really amazing. Our app
> was using ODBC.
> Just an idea,
> pexi
> <scott_mcarthur2003@.yahoo.co.uk> wrote in message
> news:92c92d63.0311200800.124926ff@.posting.google.com...
> > We have recently migrated from a Windows Server 2000/Sql Server 2000
> > Standard Edition machine to Windows Server 2003 Standard Edition/Sql
> > Server 2000 Standard Edition. We are using the machine as a combined
> > web and database server running asp and com dll's. The old machine had
> > about 1GB Ram and sql server used about 700Mb and the machine ran ok.
> > On the new machine with 2.5GB Ram sql server seems to gradually build
> > up its memory use to 1.9GB from 300mb after a reeboot. The machine
> > seems be experiencing some performance problems even though we have
> > the same amount of website traffic as before, I suspect the memory
> > usage is a problem, it seems as if sql server it not releasing memory.
> > Is their a way to diagnose/control this? Is it possible that there is
> > some difference in windows 2003(with IIS 6) that causes sql server to
> > retain more memory, perhaps not relasing database connections from asp
> > as quickly as before'
> >
> > We have the latest service pack on sql server 2000.
> >
> > Thanks
> >
> > Scott
>

Excessive memory usage

My sql server apparently makes awful use of the system memory. I have to
restart the server once a day, because use of memory gradually increases to
500-600 mb in one day. What am I doing wrong, can you help me?
Max Muller
Max Muller wrote:
> My sql server apparently makes awful use of the system memory. I have
> to restart the server once a day, because use of memory gradually
> increases to 500-600 mb in one day. What am I doing wrong, can you
> help me?
>
> Max Muller
Nothing to do. That's how SQL works. It uses memory as it needs it and
doesn't give it back unless the OS specifically requests the service
release some memory (which rarely happens).
The fact that SQL Server uses memory shouldn't be a problem for most
servers, since it performs better the more memory it has.
If you're running into a situation where SQL Server is running on a box
with other services and applications, you can set the maximum memory SQL
Server can use in SQL Enterprise Manager.
Can you explain why the memory usage is a concern?
David Gugick
Imceda Software
www.imceda.com
|||Hi All,
I'm experiencing the same memory issue with SQL Server 2000 Standard as Max.
I now understand that it's normal for SQL to hog memory as needed. You
mentioned that if SQL server is running on a box with other services and
apps, we could set the maximum memory usage option. I know you said we
"COULD" but I was wondering if we SHOULD. I have other services and apps on
the box and I was concern about the low memory available. After reading your
comments and http://support.microsoft.com/default...;en-us;q321363
I now know that SQL server will release memory as needed by OS, but I'm
still wondering, is setting the max mem limit a standard practice when
running other services on same box as SQL?
Thanks.
E. Ortega
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:ejkaIx8DFHA.3976@.tk2msftngp13.phx.gbl...
> Max Muller wrote:
> Nothing to do. That's how SQL works. It uses memory as it needs it and
> doesn't give it back unless the OS specifically requests the service
> release some memory (which rarely happens).
> The fact that SQL Server uses memory shouldn't be a problem for most
> servers, since it performs better the more memory it has.
> If you're running into a situation where SQL Server is running on a box
> with other services and applications, you can set the maximum memory SQL
> Server can use in SQL Enterprise Manager.
> Can you explain why the memory usage is a concern?
> --
> David Gugick
> Imceda Software
> www.imceda.com
|||E Ortega wrote:
> Hi All,
> I'm experiencing the same memory issue with SQL Server 2000 Standard
> as Max. I now understand that it's normal for SQL to hog memory as
> needed. You mentioned that if SQL server is running on a box with
> other services and apps, we could set the maximum memory usage
> option. I know you said we "COULD" but I was wondering if we SHOULD. I
> have other services and apps on the box and I was concern about the
> low memory available. After reading your comments and
> http://support.microsoft.com/default...;en-us;q321363 I now
> know that SQL server will release memory as needed by OS, but I'm
> still wondering, is setting the max mem limit a standard practice
> when running other services on same box as SQL?
> Thanks.
> E. Ortega
>
Yes, it is. The best thing to do is add as much memory as you can to the
server. Memory is so cheap that it can have a dramatic improvement in
performance for all services. But if you have IIS or other services or
applications running on the server, you should set a maximum limit to
make sure SQL Server doesn't leave the other services starved for memory
as writing to disk is very slow and will hurt overall server performance
for all services.
Tuning your SQL is a great way to limit SQL Servers memory and CPU
footprints as well.
David Gugick
Imceda Software
www.imceda.com
|||Thanks for your time and info very valuable to me and I'm sure to others out
there. If is not too much trouble, could you please briefly consider my
configuration and give me a suggestion as to what my max mem usage should
be?
SQL 2000 (SP3) Standard
Dual Xeon 3.0 GHz
Memory 2 Gig
Current Task Manager info:
Physical Memory
Total 2096636
Available 155724
System Cache 363272
Totals:
Handles 15422
Threads 731
Processes 56
CPU Usage varies 1% - 7%
sqlservr.exe mem usage is 1,175,992 mem
available mem is 155724, the rest of the mem is used by other
services(docsfusion, veritas storage replicator, etc.).
I have one DB with 50 users.
Thanks in advance for your time and info.
E. Ortega
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:uR6mDWFEFHA.3972@.TK2MSFTNGP15.phx.gbl...
>E Ortega wrote:
> Yes, it is. The best thing to do is add as much memory as you can to the
> server. Memory is so cheap that it can have a dramatic improvement in
> performance for all services. But if you have IIS or other services or
> applications running on the server, you should set a maximum limit to make
> sure SQL Server doesn't leave the other services starved for memory as
> writing to disk is very slow and will hurt overall server performance for
> all services.
> Tuning your SQL is a great way to limit SQL Servers memory and CPU
> footprints as well.
>
> --
> David Gugick
> Imceda Software
> www.imceda.com
|||It really depends on just how much memory these other apps need on a regular
basis. If you determine for instance that they need 300MB to operate
properly you might want to drop the max memory down 300MB from the value you
see Sql Server using in task manager. That would be roughly 1.4GB. That
would leave room for the memtoleave portion of sql server and ~300MB for the
other apps. When you run other apps on the same server as Sql Server and
only have 2GB you always have a trade off of who can best use the memory.
Even though Sql Server will dynamically adjust memory if the other apps
require xxMB's all the time it is usually best to limit how much Sql Server
can use.
Andrew J. Kelly SQL MVP
"E Ortega" <elburu@.hotmail.com> wrote in message
news:eGUritFEFHA.3908@.TK2MSFTNGP12.phx.gbl...
> Thanks for your time and info very valuable to me and I'm sure to others
> out there. If is not too much trouble, could you please briefly consider
> my configuration and give me a suggestion as to what my max mem usage
> should be?
> SQL 2000 (SP3) Standard
> Dual Xeon 3.0 GHz
> Memory 2 Gig
> Current Task Manager info:
> Physical Memory
> Total 2096636
> Available 155724
> System Cache 363272
> Totals:
> Handles 15422
> Threads 731
> Processes 56
> CPU Usage varies 1% - 7%
> sqlservr.exe mem usage is 1,175,992 mem
> available mem is 155724, the rest of the mem is used by other
> services(docsfusion, veritas storage replicator, etc.).
> I have one DB with 50 users.
> Thanks in advance for your time and info.
> E. Ortega
>
> "David Gugick" <davidg-nospam@.imceda.com> wrote in message
> news:uR6mDWFEFHA.3972@.TK2MSFTNGP15.phx.gbl...
>
|||As always, thanks very much for your time and info.
E. Ortega
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:urxDmcGEFHA.2572@.tk2msftngp13.phx.gbl...
> It really depends on just how much memory these other apps need on a
> regular basis. If you determine for instance that they need 300MB to
> operate properly you might want to drop the max memory down 300MB from the
> value you see Sql Server using in task manager. That would be roughly
> 1.4GB. That would leave room for the memtoleave portion of sql server and
> ~300MB for the other apps. When you run other apps on the same server as
> Sql Server and only have 2GB you always have a trade off of who can best
> use the memory. Even though Sql Server will dynamically adjust memory if
> the other apps require xxMB's all the time it is usually best to limit how
> much Sql Server can use.
> --
> Andrew J. Kelly SQL MVP
>
> "E Ortega" <elburu@.hotmail.com> wrote in message
> news:eGUritFEFHA.3908@.TK2MSFTNGP12.phx.gbl...
>
|||Thanks a lot David, you helped me understand what I'm dealing with.
Max
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:ejkaIx8DFHA.3976@.tk2msftngp13.phx.gbl...
> Max Muller wrote:
> Nothing to do. That's how SQL works. It uses memory as it needs it and
> doesn't give it back unless the OS specifically requests the service
> release some memory (which rarely happens).
> The fact that SQL Server uses memory shouldn't be a problem for most
> servers, since it performs better the more memory it has.
> If you're running into a situation where SQL Server is running on a box
> with other services and applications, you can set the maximum memory SQL
> Server can use in SQL Enterprise Manager.
> Can you explain why the memory usage is a concern?
> --
> David Gugick
> Imceda Software
> www.imceda.com

Monday, March 12, 2012

Excessive stored procedure [COMPILE] lock

Excessive stored procedure [COMPILE] lock

Excessive stored procedure [COMPILE] lock

Excessive stored procedure [COMPILE] lock

Excessive SQL Compilations

Excessive SQL Compilations

Excessive SQL Compilations

excessive sending package from the sqlserver

excessive sending package from the sqlserver

Excessive paging after enabling AWE in SQL server - memgraph1.jpg (0/1)

Excessive Page Faults?

Excessive network usage with SQL 2000 to SQL 2005 transactional replication

Excessive network usage with SQL 2000 to SQL 2005 transactional replication

Excessive network usage with SQL 2000 to SQL 2005 transactional replication

Excessive network usage with SQL 2000 to SQL 2005 transactional replication

excessive network traffic

excessive network traffic

excessive network traffic

excessive memory usage problem

Excessive memory usage

Excel Import

Blog Archive

About Me