High pages/sec.. memory pressure ?

When I see high pages/sec, does SQL go to the pagefile to read or write
pages that need to be flushed out of physical memory or to be retrieved into
physical memory?Yes, that is what the pages/sec counter is measuring.
What numbers are you seeing? If you average 20 pages/sec or less you
are within the design of a standard installation of sql server. If I
was seeing more than 20, I would be adding more memory after some
obvious checking (size of pagefile and its utilization: page file: %
usage) That will give you a good idea of how much benefit extra
memory will give you too
On Jan 1, 9:41=A0pm, "Hassan" <has...@.test.com> wrote:
> When I see high pages/sec, does SQL go to the pagefile to read or write
> pages that need to be flushed out of physical memory or to be retrieved in=[/vbcol
]
to[vbcol=seagreen]
> physical memory?|||It depends on how you have your memory configured. A better counter to
monitor for memory pressure is page life expectancy.
Jason Massie
Web: http://statisticsio.com
RSS: http://feeds.feedburner.com/statisticsio
"Hassan" <hassan@.test.com> wrote in message
news:%23NNZlIQTIHA.280@.TK2MSFTNGP03.phx.gbl...
> When I see high pages/sec, does SQL go to the pagefile to read or write
> pages that need to be flushed out of physical memory or to be retrieved
> into physical memory?|||I'd also veryfy whether pages/sec was caused by the SQL Server process or by
some other apps.
Linchi
"Hassan" wrote:

> When I see high pages/sec, does SQL go to the pagefile to read or write
> pages that need to be flushed out of physical memory or to be retrieved in
to
> physical memory?
>|||How do you verify that Linchi ?
"Linchi Shea" <LinchiShea@.discussions.microsoft.com> wrote in message
news:5C03FF55-90CE-4B88-9D9E-6AC3B562EB34@.microsoft.com...[vbcol=seagreen]
> I'd also veryfy whether pages/sec was caused by the SQL Server process or
> by
> some other apps.
> Linchi
> "Hassan" wrote:
>|||You can check page faults/sec for the SQL Server process either under the
perfmon instance Process\<sql process> or using Task Manager by adding the
page faults column on the Processes tab. Now, page faults are not a hard
paging indicator for the process as Pages/sec is for the OS. That is, you
can't necessarily say that a process is causing hard paging if it has high
page faults. But page faults may give you an indication. So if you see no
other process but the SQL process incurring page faults that nicely correlat
e
with the occurence of hard paging, you have reason to suspect the hard pagin
g
si caused by the SQL process.
Linchi
"Hassan" wrote:

> How do you verify that Linchi ?
> "Linchi Shea" <LinchiShea@.discussions.microsoft.com> wrote in message
> news:5C03FF55-90CE-4B88-9D9E-6AC3B562EB34@.microsoft.com...
>sql

High pages/sec.. memory pressure ?

When I see high pages/sec, does SQL go to the pagefile to read or write
pages that need to be flushed out of physical memory or to be retrieved into
physical memory?Yes, that is what the pages/sec counter is measuring.
What numbers are you seeing? If you average 20 pages/sec or less you
are within the design of a standard installation of sql server. If I
was seeing more than 20, I would be adding more memory after some
obvious checking (size of pagefile and its utilization: page file: %
usage) That will give you a good idea of how much benefit extra
memory will give you too
On Jan 1, 9:41=A0pm, "Hassan" <has...@.test.com> wrote:
> When I see high pages/sec, does SQL go to the pagefile to read or write
> pages that need to be flushed out of physical memory or to be retrieved in=to
> physical memory?|||It depends on how you have your memory configured. A better counter to
monitor for memory pressure is page life expectancy.
--
Jason Massie
Web: http://statisticsio.com
RSS: http://feeds.feedburner.com/statisticsio
"Hassan" <hassan@.test.com> wrote in message
news:%23NNZlIQTIHA.280@.TK2MSFTNGP03.phx.gbl...
> When I see high pages/sec, does SQL go to the pagefile to read or write
> pages that need to be flushed out of physical memory or to be retrieved
> into physical memory?|||I'd also veryfy whether pages/sec was caused by the SQL Server process or by
some other apps.
Linchi
"Hassan" wrote:
> When I see high pages/sec, does SQL go to the pagefile to read or write
> pages that need to be flushed out of physical memory or to be retrieved into
> physical memory?
>|||How do you verify that Linchi ?
"Linchi Shea" <LinchiShea@.discussions.microsoft.com> wrote in message
news:5C03FF55-90CE-4B88-9D9E-6AC3B562EB34@.microsoft.com...
> I'd also veryfy whether pages/sec was caused by the SQL Server process or
> by
> some other apps.
> Linchi
> "Hassan" wrote:
>> When I see high pages/sec, does SQL go to the pagefile to read or write
>> pages that need to be flushed out of physical memory or to be retrieved
>> into
>> physical memory?
>>|||You can check page faults/sec for the SQL Server process either under the
perfmon instance Process\<sql process> or using Task Manager by adding the
page faults column on the Processes tab. Now, page faults are not a hard
paging indicator for the process as Pages/sec is for the OS. That is, you
can't necessarily say that a process is causing hard paging if it has high
page faults. But page faults may give you an indication. So if you see no
other process but the SQL process incurring page faults that nicely correlate
with the occurence of hard paging, you have reason to suspect the hard paging
si caused by the SQL process.
Linchi
"Hassan" wrote:
> How do you verify that Linchi ?
> "Linchi Shea" <LinchiShea@.discussions.microsoft.com> wrote in message
> news:5C03FF55-90CE-4B88-9D9E-6AC3B562EB34@.microsoft.com...
> > I'd also veryfy whether pages/sec was caused by the SQL Server process or
> > by
> > some other apps.
> >
> > Linchi
> >
> > "Hassan" wrote:
> >
> >> When I see high pages/sec, does SQL go to the pagefile to read or write
> >> pages that need to be flushed out of physical memory or to be retrieved
> >> into
> >> physical memory?
> >>
> >>
>

High pages/sec.. memory pressure ?

When I see high pages/sec, does SQL go to the pagefile to read or write
pages that need to be flushed out of physical memory or to be retrieved into
physical memory?
Yes, that is what the pages/sec counter is measuring.
What numbers are you seeing? If you average 20 pages/sec or less you
are within the design of a standard installation of sql server. If I
was seeing more than 20, I would be adding more memory after some
obvious checking (size of pagefile and its utilization: page file: %
usage) That will give you a good idea of how much benefit extra
memory will give you too
On Jan 1, 9:41Xpm, "Hassan" <has...@.test.com> wrote:
> When I see high pages/sec, does SQL go to the pagefile to read or write
> pages that need to be flushed out of physical memory or to be retrieved into
> physical memory?
|||It depends on how you have your memory configured. A better counter to
monitor for memory pressure is page life expectancy.
Jason Massie
Web: http://statisticsio.com
RSS: http://feeds.feedburner.com/statisticsio
"Hassan" <hassan@.test.com> wrote in message
news:%23NNZlIQTIHA.280@.TK2MSFTNGP03.phx.gbl...
> When I see high pages/sec, does SQL go to the pagefile to read or write
> pages that need to be flushed out of physical memory or to be retrieved
> into physical memory?
|||I'd also veryfy whether pages/sec was caused by the SQL Server process or by
some other apps.
Linchi
"Hassan" wrote:

> When I see high pages/sec, does SQL go to the pagefile to read or write
> pages that need to be flushed out of physical memory or to be retrieved into
> physical memory?
>
|||How do you verify that Linchi ?
"Linchi Shea" <LinchiShea@.discussions.microsoft.com> wrote in message
news:5C03FF55-90CE-4B88-9D9E-6AC3B562EB34@.microsoft.com...[vbcol=seagreen]
> I'd also veryfy whether pages/sec was caused by the SQL Server process or
> by
> some other apps.
> Linchi
> "Hassan" wrote:
|||You can check page faults/sec for the SQL Server process either under the
perfmon instance Process\<sql process> or using Task Manager by adding the
page faults column on the Processes tab. Now, page faults are not a hard
paging indicator for the process as Pages/sec is for the OS. That is, you
can't necessarily say that a process is causing hard paging if it has high
page faults. But page faults may give you an indication. So if you see no
other process but the SQL process incurring page faults that nicely correlate
with the occurence of hard paging, you have reason to suspect the hard paging
si caused by the SQL process.
Linchi
"Hassan" wrote:

> How do you verify that Linchi ?
> "Linchi Shea" <LinchiShea@.discussions.microsoft.com> wrote in message
> news:5C03FF55-90CE-4B88-9D9E-6AC3B562EB34@.microsoft.com...
>

high log flush wait

I am seeing what appears to be file contention in the
form of log flush waits (consistently longer than 1/sec).
Other than separating the .ldf and .mdf on different
physical devices, is there anything that can be done to
minimize this?
TIA,
AJSeparating the devices is precisely what you should do. Your problem
clearly demonstrates why the recommendation exists in the first place.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"AJ" <anonymous@.discussions.microsoft.com> wrote in message
news:423501c49030$78833070$a601280a@.phx.gbl...
> I am seeing what appears to be file contention in the
> form of log flush waits (consistently longer than 1/sec).
> Other than separating the .ldf and .mdf on different
> physical devices, is there anything that can be done to
> minimize this?
> TIA,
> AJ|||Is there really nothing else that can be done?
I have PLANS to separate them, but I have to wait for new
hardware to arrive. In the meantime, performance is
seriously suffering. Any additional suggestions would be
MOST appreciated.
AJ
>--Original Message--
>Separating the devices is precisely what you should do.
Your problem
>clearly demonstrates why the recommendation exists in
the first place.
>--
>Geoff N. Hiten
>Microsoft SQL Server MVP
>Senior Database Administrator
>Careerbuilder.com
>I support the Professional Association for SQL Server
>www.sqlpass.org
>"AJ" <anonymous@.discussions.microsoft.com> wrote in
message
>news:423501c49030$78833070$a601280a@.phx.gbl...
1/sec).[vbcol=seagreen]
>
>.
>|||You can try and see if there are other performance limitations on your
system, but high log flush wait times won't get better without faster
hardware.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"AJ" <anonymous@.discussions.microsoft.com> wrote in message
news:0aa401c4903b$c7b032a0$a401280a@.phx.gbl...[vbcol=seagreen]
> Is there really nothing else that can be done?
> I have PLANS to separate them, but I have to wait for new
> hardware to arrive. In the meantime, performance is
> seriously suffering. Any additional suggestions would be
> MOST appreciated.
> AJ
> Your problem
> the first place.
> message
> 1/sec).

high log flush wait

I am seeing what appears to be file contention in the
form of log flush waits (consistently longer than 1/sec).
Other than separating the .ldf and .mdf on different
physical devices, is there anything that can be done to
minimize this?
TIA,
AJSeparating the devices is precisely what you should do. Your problem
clearly demonstrates why the recommendation exists in the first place.
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"AJ" <anonymous@.discussions.microsoft.com> wrote in message
news:423501c49030$78833070$a601280a@.phx.gbl...
> I am seeing what appears to be file contention in the
> form of log flush waits (consistently longer than 1/sec).
> Other than separating the .ldf and .mdf on different
> physical devices, is there anything that can be done to
> minimize this?
> TIA,
> AJ|||Is there really nothing else that can be done?
I have PLANS to separate them, but I have to wait for new
hardware to arrive. In the meantime, performance is
seriously suffering. Any additional suggestions would be
MOST appreciated.
AJ
>--Original Message--
>Separating the devices is precisely what you should do.
Your problem
>clearly demonstrates why the recommendation exists in
the first place.
>--
>Geoff N. Hiten
>Microsoft SQL Server MVP
>Senior Database Administrator
>Careerbuilder.com
>I support the Professional Association for SQL Server
>www.sqlpass.org
>"AJ" <anonymous@.discussions.microsoft.com> wrote in
message
>news:423501c49030$78833070$a601280a@.phx.gbl...
>> I am seeing what appears to be file contention in the
>> form of log flush waits (consistently longer than
1/sec).
>> Other than separating the .ldf and .mdf on different
>> physical devices, is there anything that can be done to
>> minimize this?
>> TIA,
>> AJ
>
>.
>|||You can try and see if there are other performance limitations on your
system, but high log flush wait times won't get better without faster
hardware.
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"AJ" <anonymous@.discussions.microsoft.com> wrote in message
news:0aa401c4903b$c7b032a0$a401280a@.phx.gbl...
> Is there really nothing else that can be done?
> I have PLANS to separate them, but I have to wait for new
> hardware to arrive. In the meantime, performance is
> seriously suffering. Any additional suggestions would be
> MOST appreciated.
> AJ
> >--Original Message--
> >Separating the devices is precisely what you should do.
> Your problem
> >clearly demonstrates why the recommendation exists in
> the first place.
> >
> >--
> >Geoff N. Hiten
> >Microsoft SQL Server MVP
> >Senior Database Administrator
> >Careerbuilder.com
> >
> >I support the Professional Association for SQL Server
> >www.sqlpass.org
> >
> >"AJ" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:423501c49030$78833070$a601280a@.phx.gbl...
> >> I am seeing what appears to be file contention in the
> >> form of log flush waits (consistently longer than
> 1/sec).
> >>
> >> Other than separating the .ldf and .mdf on different
> >> physical devices, is there anything that can be done to
> >> minimize this?
> >>
> >> TIA,
> >> AJ
> >
> >
> >.
> >

high log flush wait

I am seeing what appears to be file contention in the
form of log flush waits (consistently longer than 1/sec).
Other than separating the .ldf and .mdf on different
physical devices, is there anything that can be done to
minimize this?
TIA,
AJ
Separating the devices is precisely what you should do. Your problem
clearly demonstrates why the recommendation exists in the first place.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"AJ" <anonymous@.discussions.microsoft.com> wrote in message
news:423501c49030$78833070$a601280a@.phx.gbl...
> I am seeing what appears to be file contention in the
> form of log flush waits (consistently longer than 1/sec).
> Other than separating the .ldf and .mdf on different
> physical devices, is there anything that can be done to
> minimize this?
> TIA,
> AJ
|||Is there really nothing else that can be done?
I have PLANS to separate them, but I have to wait for new
hardware to arrive. In the meantime, performance is
seriously suffering. Any additional suggestions would be
MOST appreciated.
AJ
>--Original Message--
>Separating the devices is precisely what you should do.
Your problem
>clearly demonstrates why the recommendation exists in
the first place.
>--
>Geoff N. Hiten
>Microsoft SQL Server MVP
>Senior Database Administrator
>Careerbuilder.com
>I support the Professional Association for SQL Server
>www.sqlpass.org
>"AJ" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:423501c49030$78833070$a601280a@.phx.gbl...
1/sec).
>
>.
>
|||You can try and see if there are other performance limitations on your
system, but high log flush wait times won't get better without faster
hardware.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"AJ" <anonymous@.discussions.microsoft.com> wrote in message
news:0aa401c4903b$c7b032a0$a401280a@.phx.gbl...[vbcol=seagreen]
> Is there really nothing else that can be done?
> I have PLANS to separate them, but I have to wait for new
> hardware to arrive. In the meantime, performance is
> seriously suffering. Any additional suggestions would be
> MOST appreciated.
> AJ
> Your problem
> the first place.
> message
> 1/sec).

High Context swtiches/sec

We have context switches/sec around 18000 on an 8 way CPU . I am using SQL
2000. Is that good or bad ?
If bad, what can i do further to investigate or reduce it
ThanksIt's not horrible but could probably be better. I would check to see how
your disk queues and cache hit ratios are.
--
Andrew J. Kelly
SQL Server MVP
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:On1JTJjVDHA.2096@.TK2MSFTNGP11.phx.gbl...
> We have context switches/sec around 18000 on an 8 way CPU . I am using SQL
> 2000. Is that good or bad ?
> If bad, what can i do further to investigate or reduce it
> Thanks
>

high Compilations/sec value

Hi,
Would someone please explain what causes a high "Compilations/sec" count?
Is still caued by stored procedures not being cached and not enough memory
alloacted to SQL Server.
ThanksIf you have create SPs that are frequently used, with recompile option might
bring up compilations/sec high.
"mm" <postto@.news.com> wrote in message
news:u1p$2asuEHA.452@.TK2MSFTNGP09.phx.gbl...
> Hi,
> Would someone please explain what causes a high "Compilations/sec" count?
> Is still caued by stored procedures not being cached and not enough memory
> alloacted to SQL Server.
>
> Thanks
>
>|||Generally speaking, if this figure is over 100 compilations per second, then
you may be experiencing unnecessary compilation overhead. A high number such
as this might indicate that you server is just very busy, or it could mean
that unnecessary compilations are being performed. For example, compilations
can be forced by SQL Server if object schema changes, if previously
parallelized execution plans have to run serially, if statistics are
recomputed, or if a number of other things occur.
Also, it depends how your SPs are written. For example, if you have the
following SP:
CREATE PROCEDURE dbo.spTest (@.query bit) AS
IF @.query = 0
SELECT * FROM authors
ELSE
SELECT * FROM publishers
GO
Suppose I make my first call to this procedure with the @.query parameter set
to 0. The query-plan that SQL Server will generate will be optimized for the
first query ("SELECT * FROM authors"), because the path followed on the first
call will result in that query being executed.
Now, if I next call the stored procedure with @.query set to 1, the query
plan that SQL Server has in memory will not be of any use in executing the
second query, since the query-plan is optimized for the authors table, not
the publishers table. Result: SQL Server will have to compile a new query
plan, the one needed for the second query.
Ultimately, you should write the SP as follow:
CREATE PROCEDURE dbo.spTestDelegator (@.query bit) AS
IF @.query = 0
EXEC spTestFromAuthors
ELSE
EXEC spTestFromPublishers
GO
I hope this helps.
--
Sasan Saidi, MSc in CS
"I saw it work in a cartoon once so I am pretty sure I can do it."
"mm" wrote:
> Hi,
> Would someone please explain what causes a high "Compilations/sec" count?
> Is still caued by stored procedures not being cached and not enough memory
> alloacted to SQL Server.
>
> Thanks
>
>

high Compilations/sec value

Hi,
Would someone please explain what causes a high "Compilations/sec" count?
Is still caued by stored procedures not being cached and not enough memory
alloacted to SQL Server.
ThanksIf you have create SPs that are frequently used, with recompile option might
bring up compilations/sec high.
"mm" <postto@.news.com> wrote in message
news:u1p$2asuEHA.452@.TK2MSFTNGP09.phx.gbl...
> Hi,
> Would someone please explain what causes a high "Compilations/sec" count?
> Is still caued by stored procedures not being cached and not enough memory
> alloacted to SQL Server.
>
> Thanks
>
>|||Generally speaking, if this figure is over 100 compilations per second, then
you may be experiencing unnecessary compilation overhead. A high number such
as this might indicate that you server is just very busy, or it could mean
that unnecessary compilations are being performed. For example, compilations
can be forced by SQL Server if object schema changes, if previously
parallelized execution plans have to run serially, if statistics are
recomputed, or if a number of other things occur.
Also, it depends how your SPs are written. For example, if you have the
following SP:
CREATE PROCEDURE dbo.spTest (@.query bit) AS
IF @.query = 0
SELECT * FROM authors
ELSE
SELECT * FROM publishers
GO
Suppose I make my first call to this procedure with the @.query parameter set
to 0. The query-plan that SQL Server will generate will be optimized for the
first query ("SELECT * FROM authors"), because the path followed on the firs
t
call will result in that query being executed.
Now, if I next call the stored procedure with @.query set to 1, the query
plan that SQL Server has in memory will not be of any use in executing the
second query, since the query-plan is optimized for the authors table, not
the publishers table. Result: SQL Server will have to compile a new query
plan, the one needed for the second query.
Ultimately, you should write the SP as follow:
CREATE PROCEDURE dbo.spTestDelegator (@.query bit) AS
IF @.query = 0
EXEC spTestFromAuthors
ELSE
EXEC spTestFromPublishers
GO
I hope this helps.
Sasan Saidi, MSc in CS
"I saw it work in a cartoon once so I am pretty sure I can do it."
"mm" wrote:

> Hi,
> Would someone please explain what causes a high "Compilations/sec" count?
> Is still caued by stored procedures not being cached and not enough memory
> alloacted to SQL Server.
>
> Thanks
>
>

high Compilations/sec value

Hi,
Would someone please explain what causes a high "Compilations/sec" count?
Is still caued by stored procedures not being cached and not enough memory
alloacted to SQL Server.
Thanks
If you have create SPs that are frequently used, with recompile option might
bring up compilations/sec high.
"mm" <postto@.news.com> wrote in message
news:u1p$2asuEHA.452@.TK2MSFTNGP09.phx.gbl...
> Hi,
> Would someone please explain what causes a high "Compilations/sec" count?
> Is still caued by stored procedures not being cached and not enough memory
> alloacted to SQL Server.
>
> Thanks
>
>
|||Generally speaking, if this figure is over 100 compilations per second, then
you may be experiencing unnecessary compilation overhead. A high number such
as this might indicate that you server is just very busy, or it could mean
that unnecessary compilations are being performed. For example, compilations
can be forced by SQL Server if object schema changes, if previously
parallelized execution plans have to run serially, if statistics are
recomputed, or if a number of other things occur.
Also, it depends how your SPs are written. For example, if you have the
following SP:
CREATE PROCEDURE dbo.spTest (@.query bit) AS
IF @.query = 0
SELECT * FROM authors
ELSE
SELECT * FROM publishers
GO
Suppose I make my first call to this procedure with the @.query parameter set
to 0. The query-plan that SQL Server will generate will be optimized for the
first query ("SELECT * FROM authors"), because the path followed on the first
call will result in that query being executed.
Now, if I next call the stored procedure with @.query set to 1, the query
plan that SQL Server has in memory will not be of any use in executing the
second query, since the query-plan is optimized for the authors table, not
the publishers table. Result: SQL Server will have to compile a new query
plan, the one needed for the second query.
Ultimately, you should write the SP as follow:
CREATE PROCEDURE dbo.spTestDelegator (@.query bit) AS
IF @.query = 0
EXEC spTestFromAuthors
ELSE
EXEC spTestFromPublishers
GO
I hope this helps.
Sasan Saidi, MSc in CS
"I saw it work in a cartoon once so I am pretty sure I can do it."
"mm" wrote:

> Hi,
> Would someone please explain what causes a high "Compilations/sec" count?
> Is still caued by stored procedures not being cached and not enough memory
> alloacted to SQL Server.
>
> Thanks
>
>