Bug in Report Designer - can anyone reproduce?

I think I've discovered a serious bug with the Report Designer, which
causes devenv.exe to take up 100% CPU and skyrocket in memory usage.
Here's how I got to it:
-Create a report with three parameters: The first two are dates (start
date and end date for a date range) and the third is a String
(employee name).
-Create a query to auto-populate both date fields.
-Create a query to fill the String parameter with values that are
dependant on the first two date parameters (return employees that were
active during the range of the two parameters, for instance).
This works okay when deployed, but in Preview Mode in Visual Studio,
after changing either of the Date parameters from their default value,
VS seems to get stuck in an infinite loop when attempting to
re-populate the third parameter with employee names.
Can anyone reproduce this?
Thanks,
Matthew Brown
Viecore IncYes, I have seen this (and like you saw that it was not a problem with
deployed reports). Once I saw that deployed was OK then I would deploy for
testing (a bit of a pain but I did not have many reports with this issue).
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Matthew Brown" <octavius@.gmail.com> wrote in message
news:lgao41htssrgtq5kqv56d2sernj9fka3eu@.4ax.com...
> I think I've discovered a serious bug with the Report Designer, which
> causes devenv.exe to take up 100% CPU and skyrocket in memory usage.
> Here's how I got to it:
> -Create a report with three parameters: The first two are dates (start
> date and end date for a date range) and the third is a String
> (employee name).
> -Create a query to auto-populate both date fields.
> -Create a query to fill the String parameter with values that are
> dependant on the first two date parameters (return employees that were
> active during the range of the two parameters, for instance).
> This works okay when deployed, but in Preview Mode in Visual Studio,
> after changing either of the Date parameters from their default value,
> VS seems to get stuck in an infinite loop when attempting to
> re-populate the third parameter with employee names.
> Can anyone reproduce this?
> Thanks,
> Matthew Brown
> Viecore Inc
>|||Since I am using calls to a custom assembly to retrieve strings in my
reports, Report Designer Hangs visual studio with this behavior with ALL my
reports. I have to kill it with the task manager. I think this is a very
serious issue.
I would think anyone localizing strings within a report with RS 2000 would
have the same issue as I do. It makes the preview functionality of Report
Designer useless. I would love to have a fix for this in RS 2000.
Thanks for any help or indication of priority, good or bad,
Andrew
"Bruce L-C [MVP]" wrote:
> Yes, I have seen this (and like you saw that it was not a problem with
> deployed reports). Once I saw that deployed was OK then I would deploy for
> testing (a bit of a pain but I did not have many reports with this issue).
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Matthew Brown" <octavius@.gmail.com> wrote in message
> news:lgao41htssrgtq5kqv56d2sernj9fka3eu@.4ax.com...
> > I think I've discovered a serious bug with the Report Designer, which
> > causes devenv.exe to take up 100% CPU and skyrocket in memory usage.
> > Here's how I got to it:
> >
> > -Create a report with three parameters: The first two are dates (start
> > date and end date for a date range) and the third is a String
> > (employee name).
> > -Create a query to auto-populate both date fields.
> > -Create a query to fill the String parameter with values that are
> > dependant on the first two date parameters (return employees that were
> > active during the range of the two parameters, for instance).
> >
> > This works okay when deployed, but in Preview Mode in Visual Studio,
> > after changing either of the Date parameters from their default value,
> > VS seems to get stuck in an infinite loop when attempting to
> > re-populate the third parameter with employee names.
> >
> > Can anyone reproduce this?
> >
> > Thanks,
> > Matthew Brown
> > Viecore Inc
> >
> >
>
>|||Hmmm, the symptom might be the same (hangs Report Designer) but your
description does not seem to have anything to do with what we were talking
about. I have not seen your symptom before (either personally or via a post
in the newsgroup). I use a small amount of custom code but mine is code
behind reports, not a custom assembly. I do know there are definitely
specific steps to be taken when using custom assemblies. I suggest posting
with a subject of "Custom assembly hangs Report Designer) and see if someone
who has used custom assemblies has any ideas.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"skillet" <skillet@.discussions.microsoft.com> wrote in message
news:B2ABA9D3-2934-43BE-AA18-41880E3B6480@.microsoft.com...
> Since I am using calls to a custom assembly to retrieve strings in my
> reports, Report Designer Hangs visual studio with this behavior with ALL
my
> reports. I have to kill it with the task manager. I think this is a very
> serious issue.
> I would think anyone localizing strings within a report with RS 2000 would
> have the same issue as I do. It makes the preview functionality of Report
> Designer useless. I would love to have a fix for this in RS 2000.
> Thanks for any help or indication of priority, good or bad,
> Andrew
> "Bruce L-C [MVP]" wrote:
> > Yes, I have seen this (and like you saw that it was not a problem with
> > deployed reports). Once I saw that deployed was OK then I would deploy
for
> > testing (a bit of a pain but I did not have many reports with this
issue).
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> > "Matthew Brown" <octavius@.gmail.com> wrote in message
> > news:lgao41htssrgtq5kqv56d2sernj9fka3eu@.4ax.com...
> > > I think I've discovered a serious bug with the Report Designer, which
> > > causes devenv.exe to take up 100% CPU and skyrocket in memory usage.
> > > Here's how I got to it:
> > >
> > > -Create a report with three parameters: The first two are dates (start
> > > date and end date for a date range) and the third is a String
> > > (employee name).
> > > -Create a query to auto-populate both date fields.
> > > -Create a query to fill the String parameter with values that are
> > > dependant on the first two date parameters (return employees that were
> > > active during the range of the two parameters, for instance).
> > >
> > > This works okay when deployed, but in Preview Mode in Visual Studio,
> > > after changing either of the Date parameters from their default value,
> > > VS seems to get stuck in an infinite loop when attempting to
> > > re-populate the third parameter with employee names.
> > >
> > > Can anyone reproduce this?
> > >
> > > Thanks,
> > > Matthew Brown
> > > Viecore Inc
> > >
> > >
> >
> >
> >|||That is possible. My situation is fairly simple; I just call a custom code
function which calls right out to a custom assembly, which in turn returns a
string. once I have a few of these in the report the designer no longer
functions, flickering as it tries to refresh (apparently). There are
certainly steps to be taken with respect to referencing and securing a custom
assembly, but I don't think a configuration problem with the custom assembly
would cause this behavior in the Report Designer.
Instead, it seems that the Designer is trying to refresh its fields very
often, and when several fields call out to a custom assembly, the designer's
desired refresh cycle is faster than all of the calls can be completed (?).
Thanks for your help.
"Bruce L-C [MVP]" wrote:
> Hmmm, the symptom might be the same (hangs Report Designer) but your
> description does not seem to have anything to do with what we were talking
> about. I have not seen your symptom before (either personally or via a post
> in the newsgroup). I use a small amount of custom code but mine is code
> behind reports, not a custom assembly. I do know there are definitely
> specific steps to be taken when using custom assemblies. I suggest posting
> with a subject of "Custom assembly hangs Report Designer) and see if someone
> who has used custom assemblies has any ideas.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>

Bug (?) in SQL2005 x64 SP1 CTP

We're testing the same DB (~ 5GB, ~ 500 tables) with
Server32 (Win2003 Standard 32 bit, SQL2005 Developer 32 bit SP1 CTP, 1gb Memory) and with
Server64 (Win2003 x64 Standard 64 bit, SQL2005 Developer 64 bit SP1 CTP, 6gb Memory).

exec sp_MSforeachtable @.command1="SET QUOTED_IDENTIFIER ON DBCC DBREINDEX ('?', '', 90)"

On Server32 the SQL above runs without errors. On Server64 there comes the following error message:
Msg 8621, Level 17, State 1, Line 1
The query processor ran out of stack space during query optimization. Please simplify the query.

A 64bit server with 6gb memory has less stack space than a 32bit server with 1gb memory?

Regards
Peter

PS: I know sp_MSforeachtable is not an official stored procedure, but I don't thinkt there's the problem. I got the same error on Server64, when I run the following cursor:
DECLARE tablename CURSOR
READ_ONLY
FOR select [name] from sys.tables where type = 'U'

DECLARE @.name varchar(80)
OPEN tablename

FETCH NEXT FROM tablename INTO @.name
WHILE (@.@.fetch_status <> -1)
BEGIN
IF (@.@.fetch_status <> -2)
BEGIN
DECLARE @.message varchar(800)
SELECT @.message = 'SET QUOTED_IDENTIFIER ON DBCC DBREINDEX (''' + @.name + ''', '''', 90)'
EXEC (@.message)
END
FETCH NEXT FROM tablename INTO @.name
END

CLOSE tablename
DEALLOCATE tablename
GO
Please file a bug using the MSDN Product Feedback Center. And mention the OS platform, server versions, number of tables in the database etc.|||Bug report created

Thanks
Peter

Buffer Pool vs MemToLeave Area

Whats in the buffer pool and whats in the memtoleave area ? Is this all in
RAM or could it also be virtual memory/pagefile ? Using SQL 2000Whether it is in virtual memory or not depends on how much ram you have and
how you set your system up. As for what is in there I would suggest you
take a look in BooksOnLine under "memory, pool" and it's associated links in
addition to "Inside SQL 2000" which I know you have now<g>.
Andrew J. Kelly
SQL Server MVP
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:%23rFuqtE9DHA.3012@.TK2MSFTNGP09.phx.gbl...
> Whats in the buffer pool and whats in the memtoleave area ? Is this all in
> RAM or could it also be virtual memory/pagefile ? Using SQL 2000
>|||I didn't really talk about the memtoleave value in the book, but I discussed
it here
http://www.sqlmag.com/Articles/Inde...ArticleID=16522
The above article is several years old now, so it is freely accessible.
I also wrote two articles on memory in SQL Server Magazine last March and
April, but those are only available to subscribers, as they are less than a
year old.
http://www.sqlmag.com/Articles/Inde...ArticleID=37890
http://www.sqlmag.com/Articles/Inde...ArticleID=37908
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:#GqikKJ9DHA.1548@.tk2msftngp13.phx.gbl...
> Whether it is in virtual memory or not depends on how much ram you have
and
> how you set your system up. As for what is in there I would suggest you
> take a look in BooksOnLine under "memory, pool" and it's associated links
in
> addition to "Inside SQL 2000" which I know you have now<g>.
> --
> Andrew J. Kelly
> SQL Server MVP
>
> "Hassan" <fatima_ja@.hotmail.com> wrote in message
> news:%23rFuqtE9DHA.3012@.TK2MSFTNGP09.phx.gbl...
in
>|||Probably not but you do discuss the memory pool<g>.
Andrew J. Kelly
SQL Server MVP
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:uE1kiAL9DHA.2832@.tk2msftngp13.phx.gbl...
> I didn't really talk about the memtoleave value in the book, but I
discussed
> it here
> http://www.sqlmag.com/Articles/Inde...ArticleID=16522
> The above article is several years old now, so it is freely accessible.
> I also wrote two articles on memory in SQL Server Magazine last March and
> April, but those are only available to subscribers, as they are less than
a
> year old.
> http://www.sqlmag.com/Articles/Inde...ArticleID=37890
> http://www.sqlmag.com/Articles/Inde...ArticleID=37908
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:#GqikKJ9DHA.1548@.tk2msftngp13.phx.gbl...
> and
links
> in
all
> in
>|||Have a look at
http://msdn.microsoft.com/data/defa...ev_01262004.asp
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:%23rFuqtE9DHA.3012@.TK2MSFTNGP09.phx.gbl...
> Whats in the buffer pool and whats in the memtoleave area ? Is this all in
> RAM or could it also be virtual memory/pagefile ? Using SQL 2000
>|||That's a good one Jasper. How did I miss that?
Andrew J. Kelly
SQL Server MVP
"Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
news:eanijzL9DHA.2064@.TK2MSFTNGP11.phx.gbl...
> Have a look at
>
http://msdn.microsoft.com/data/defa...004.
asp
> --
> HTH
> Jasper Smith (SQL Server MVP)
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
>
> "Hassan" <fatima_ja@.hotmail.com> wrote in message
> news:%23rFuqtE9DHA.3012@.TK2MSFTNGP09.phx.gbl...
in
>

Buffer Pool vs MemToLeave Area

Whats in the buffer pool and whats in the memtoleave area ? Is this all in
RAM or could it also be virtual memory/pagefile ? Using SQL 2000Whether it is in virtual memory or not depends on how much ram you have and
how you set your system up. As for what is in there I would suggest you
take a look in BooksOnLine under "memory, pool" and it's associated links in
addition to "Inside SQL 2000" which I know you have now<g>.
--
Andrew J. Kelly
SQL Server MVP
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:%23rFuqtE9DHA.3012@.TK2MSFTNGP09.phx.gbl...
> Whats in the buffer pool and whats in the memtoleave area ? Is this all in
> RAM or could it also be virtual memory/pagefile ? Using SQL 2000
>|||I didn't really talk about the memtoleave value in the book, but I discussed
it here
http://www.sqlmag.com/Articles/Index.cfm?ArticleID=16522
The above article is several years old now, so it is freely accessible.
I also wrote two articles on memory in SQL Server Magazine last March and
April, but those are only available to subscribers, as they are less than a
year old.
http://www.sqlmag.com/Articles/Index.cfm?ArticleID=37890
http://www.sqlmag.com/Articles/Index.cfm?ArticleID=37908
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:#GqikKJ9DHA.1548@.tk2msftngp13.phx.gbl...
> Whether it is in virtual memory or not depends on how much ram you have
and
> how you set your system up. As for what is in there I would suggest you
> take a look in BooksOnLine under "memory, pool" and it's associated links
in
> addition to "Inside SQL 2000" which I know you have now<g>.
> --
> Andrew J. Kelly
> SQL Server MVP
>
> "Hassan" <fatima_ja@.hotmail.com> wrote in message
> news:%23rFuqtE9DHA.3012@.TK2MSFTNGP09.phx.gbl...
> > Whats in the buffer pool and whats in the memtoleave area ? Is this all
in
> > RAM or could it also be virtual memory/pagefile ? Using SQL 2000
> >
> >
>|||Probably not but you do discuss the memory pool<g>.
--
Andrew J. Kelly
SQL Server MVP
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:uE1kiAL9DHA.2832@.tk2msftngp13.phx.gbl...
> I didn't really talk about the memtoleave value in the book, but I
discussed
> it here
> http://www.sqlmag.com/Articles/Index.cfm?ArticleID=16522
> The above article is several years old now, so it is freely accessible.
> I also wrote two articles on memory in SQL Server Magazine last March and
> April, but those are only available to subscribers, as they are less than
a
> year old.
> http://www.sqlmag.com/Articles/Index.cfm?ArticleID=37890
> http://www.sqlmag.com/Articles/Index.cfm?ArticleID=37908
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:#GqikKJ9DHA.1548@.tk2msftngp13.phx.gbl...
> > Whether it is in virtual memory or not depends on how much ram you have
> and
> > how you set your system up. As for what is in there I would suggest you
> > take a look in BooksOnLine under "memory, pool" and it's associated
links
> in
> > addition to "Inside SQL 2000" which I know you have now<g>.
> >
> > --
> >
> > Andrew J. Kelly
> > SQL Server MVP
> >
> >
> > "Hassan" <fatima_ja@.hotmail.com> wrote in message
> > news:%23rFuqtE9DHA.3012@.TK2MSFTNGP09.phx.gbl...
> > > Whats in the buffer pool and whats in the memtoleave area ? Is this
all
> in
> > > RAM or could it also be virtual memory/pagefile ? Using SQL 2000
> > >
> > >
> >
> >
>|||Have a look at
http://msdn.microsoft.com/data/default.aspx?pull=/library/en-us/dnsqldev/html/sqldev_01262004.asp
--
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:%23rFuqtE9DHA.3012@.TK2MSFTNGP09.phx.gbl...
> Whats in the buffer pool and whats in the memtoleave area ? Is this all in
> RAM or could it also be virtual memory/pagefile ? Using SQL 2000
>|||That's a good one Jasper. How did I miss that?
--
Andrew J. Kelly
SQL Server MVP
"Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
news:eanijzL9DHA.2064@.TK2MSFTNGP11.phx.gbl...
> Have a look at
>
http://msdn.microsoft.com/data/default.aspx?pull=/library/en-us/dnsqldev/html/sqldev_01262004.asp
> --
> HTH
> Jasper Smith (SQL Server MVP)
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
>
> "Hassan" <fatima_ja@.hotmail.com> wrote in message
> news:%23rFuqtE9DHA.3012@.TK2MSFTNGP09.phx.gbl...
> > Whats in the buffer pool and whats in the memtoleave area ? Is this all
in
> > RAM or could it also be virtual memory/pagefile ? Using SQL 2000
> >
> >
>

Buffer Manager on SQl server

I am monitoring my SQL Server. If the server has 2gig of
physical ram and SQL server memory is 1.6G and the SQL
Servver Buffer Manager cache hit ratio is 99, is that
good? If not, what configuration can be done? The server
is very slow at themoment. Thank you.
Jasmine>--Original Message--
>I am monitoring my SQL Server. If the server has 2gig of
>physical ram and SQL server memory is 1.6G and the SQL
>Servver Buffer Manager cache hit ratio is 99, is that
>good? If not, what configuration can be done? The
server
>is very slow at the moment. Thank you.
>Jasmine
Friends,
I just thought I should post this from sp_configure so you
can see how the memory is configured for sql server. It
is set to dynamically configure sql server memory - but
when I run performance monitoring it shows that sql server
consumes too much memory. Please take a look and let me
know if I need to reduce the amount of sql memory or some
key buffer and sort buffer configurations? Thank you very
much.
minimum maximum configure_value run_value
max degree of parallelism 0 32 0 0
max server memory (MB) 4 2147483647 2147483647
2147483647
max text repl size (B) 0 2147483647 65536
65536
max worker threads 32 32767 255 255
media retention 0 365 0 0
min memory per query (KB) 512 2147483647
1024 1024
min server memory (MB) 0 2147483647 0 0
>|||Those numbers are pretty good. SQL is using as much memory as is available
(2GB minus some for OS, overhead, etc. 1.6 is about right). 99% Buffer
cache hit ratio is also good. Until that gets under 95% or so, adding
memory likely won't give a noticable performance boost.
It looks like memory isn't your performance problem. Here is a good
starting point for finding out why your server isn't responding as well as
you need it to.
HOW TO: Troubleshoot Application Performance Issues
http://support.microsoft.com/default.aspx?scid=kb;en-us;298475&Product=sql2k
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Jasmine Quinlan" <anonymous@.discussions.microsoft.com> wrote in message
news:f64701c43dd2$ff0f0f20$a001280a@.phx.gbl...
> I am monitoring my SQL Server. If the server has 2gig of
> physical ram and SQL server memory is 1.6G and the SQL
> Servver Buffer Manager cache hit ratio is 99, is that
> good? If not, what configuration can be done? The server
> is very slow at themoment. Thank you.
> Jasmine
>|||Geoff,
Thank you so much for your help. I appreciate that.
Jasmine
>--Original Message--
>Those numbers are pretty good. SQL is using as much
memory as is available
>(2GB minus some for OS, overhead, etc. 1.6 is about
right). 99% Buffer
>cache hit ratio is also good. Until that gets under 95%
or so, adding
>memory likely won't give a noticable performance boost.
>It looks like memory isn't your performance problem.
Here is a good
>starting point for finding out why your server isn't
responding as well as
>you need it to.
>HOW TO: Troubleshoot Application Performance Issues
>http://support.microsoft.com/default.aspx?scid=kb;en-
us;298475&Product=sql2k
>
>--
>Geoff N. Hiten
>Microsoft SQL Server MVP
>Senior Database Administrator
>Careerbuilder.com
>I support the Professional Association for SQL Server
>www.sqlpass.org
>"Jasmine Quinlan" <anonymous@.discussions.microsoft.com>
wrote in message
>news:f64701c43dd2$ff0f0f20$a001280a@.phx.gbl...
>> I am monitoring my SQL Server. If the server has 2gig
of
>> physical ram and SQL server memory is 1.6G and the SQL
>> Servver Buffer Manager cache hit ratio is 99, is that
>> good? If not, what configuration can be done? The
server
>> is very slow at themoment. Thank you.
>> Jasmine
>
>.
>

Buffer Manager on SQl server

I am monitoring my SQL Server. If the server has 2gig of
physical ram and SQL server memory is 1.6G and the SQL
Servver Buffer Manager cache hit ratio is 99, is that
good? If not, what configuration can be done? The server
is very slow at themoment. Thank you.
Jasmine
Those numbers are pretty good. SQL is using as much memory as is available
(2GB minus some for OS, overhead, etc. 1.6 is about right). 99% Buffer
cache hit ratio is also good. Until that gets under 95% or so, adding
memory likely won't give a noticable performance boost.
It looks like memory isn't your performance problem. Here is a good
starting point for finding out why your server isn't responding as well as
you need it to.
HOW TO: Troubleshoot Application Performance Issues
http://support.microsoft.com/default...&Product=sql2k
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Jasmine Quinlan" <anonymous@.discussions.microsoft.com> wrote in message
news:f64701c43dd2$ff0f0f20$a001280a@.phx.gbl...
> I am monitoring my SQL Server. If the server has 2gig of
> physical ram and SQL server memory is 1.6G and the SQL
> Servver Buffer Manager cache hit ratio is 99, is that
> good? If not, what configuration can be done? The server
> is very slow at themoment. Thank you.
> Jasmine
>
|||Geoff,
Thank you so much for your help. I appreciate that.
Jasmine

>--Original Message--
>Those numbers are pretty good. SQL is using as much
memory as is available
>(2GB minus some for OS, overhead, etc. 1.6 is about
right). 99% Buffer
>cache hit ratio is also good. Until that gets under 95%
or so, adding
>memory likely won't give a noticable performance boost.
>It looks like memory isn't your performance problem.
Here is a good
>starting point for finding out why your server isn't
responding as well as
>you need it to.
>HOW TO: Troubleshoot Application Performance Issues
>http://support.microsoft.com/default.aspx?scid=kb;en-
us;298475&Product=sql2k
>
>--
>Geoff N. Hiten
>Microsoft SQL Server MVP
>Senior Database Administrator
>Careerbuilder.com
>I support the Professional Association for SQL Server
>www.sqlpass.org
>"Jasmine Quinlan" <anonymous@.discussions.microsoft.com>
wrote in message[vbcol=seagreen]
>news:f64701c43dd2$ff0f0f20$a001280a@.phx.gbl...
of[vbcol=seagreen]
server
>
>.
>

Buffer Manager on SQl server

I am monitoring my SQL Server. If the server has 2gig of
physical ram and SQL server memory is 1.6G and the SQL
Servver Buffer Manager cache hit ratio is 99, is that
good? If not, what configuration can be done? The server
is very slow at themoment. Thank you.
JasmineThose numbers are pretty good. SQL is using as much memory as is available
(2GB minus some for OS, overhead, etc. 1.6 is about right). 99% Buffer
cache hit ratio is also good. Until that gets under 95% or so, adding
memory likely won't give a noticable performance boost.
It looks like memory isn't your performance problem. Here is a good
starting point for finding out why your server isn't responding as well as
you need it to.
HOW TO: Troubleshoot Application Performance Issues
http://support.microsoft.com/defaul...5&Product=sql2k
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Jasmine Quinlan" <anonymous@.discussions.microsoft.com> wrote in message
news:f64701c43dd2$ff0f0f20$a001280a@.phx.gbl...
> I am monitoring my SQL Server. If the server has 2gig of
> physical ram and SQL server memory is 1.6G and the SQL
> Servver Buffer Manager cache hit ratio is 99, is that
> good? If not, what configuration can be done? The server
> is very slow at themoment. Thank you.
> Jasmine
>|||Geoff,
Thank you so much for your help. I appreciate that.
Jasmine

>--Original Message--
>Those numbers are pretty good. SQL is using as much
memory as is available
>(2GB minus some for OS, overhead, etc. 1.6 is about
right). 99% Buffer
>cache hit ratio is also good. Until that gets under 95%
or so, adding
>memory likely won't give a noticable performance boost.
>It looks like memory isn't your performance problem.
Here is a good
>starting point for finding out why your server isn't
responding as well as
>you need it to.
>HOW TO: Troubleshoot Application Performance Issues
>http://support.microsoft.com/default.aspx?scid=kb;en-
us;298475&Product=sql2k
>
>--
>Geoff N. Hiten
>Microsoft SQL Server MVP
>Senior Database Administrator
>Careerbuilder.com
>I support the Professional Association for SQL Server
>www.sqlpass.org
>"Jasmine Quinlan" <anonymous@.discussions.microsoft.com>
wrote in message
>news:f64701c43dd2$ff0f0f20$a001280a@.phx.gbl...
of[vbcol=seagreen]
server[vbcol=seagreen]
>
>.
>

Buffer Cache Hit Ratio vs Memory Manager Cache Hit ratio

We have a box that has 8GB of RAM but the OS only lets us see 3GB. We are
trying ascertain whether or not upgrading to a OS that will see the full
monty will help or redploying the assets would be a better option
The Buffer Cache Hit Ratio - 99%
The Memory Manager Cache Hit Ratio = 77%
The Target and actual values for Total Memory and Buffer Pages are the same
The memory pages/sec swings wildly, generally below 1 it goes up into the
thousands.
What is the difference between the two Cache Hit Ratio objects
Note: Vendor supplied DB is horrendous in design.Hi
You would need Enterprise Edition of Windows and SQL Server if you want to
use all 8GB.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Jeffrey K. Ericson" wrote:
> We have a box that has 8GB of RAM but the OS only lets us see 3GB. We are
> trying ascertain whether or not upgrading to a OS that will see the full
> monty will help or redploying the assets would be a better option
> The Buffer Cache Hit Ratio - 99%
> The Memory Manager Cache Hit Ratio = 77%
> The Target and actual values for Total Memory and Buffer Pages are the same
> The memory pages/sec swings wildly, generally below 1 it goes up into the
> thousands.
> What is the difference between the two Cache Hit Ratio objects
> Note: Vendor supplied DB is horrendous in design.|||Yes, we have EE of SQL running on 2k standard. We are contemplaing going to
a 2003 version that can support the 8GB.
"Mike Epprecht (SQL MVP)" wrote:
> Hi
> You would need Enterprise Edition of Windows and SQL Server if you want to
> use all 8GB.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
>
> "Jeffrey K. Ericson" wrote:
> > We have a box that has 8GB of RAM but the OS only lets us see 3GB. We are
> > trying ascertain whether or not upgrading to a OS that will see the full
> > monty will help or redploying the assets would be a better option
> >
> > The Buffer Cache Hit Ratio - 99%
> > The Memory Manager Cache Hit Ratio = 77%
> > The Target and actual values for Total Memory and Buffer Pages are the same
> > The memory pages/sec swings wildly, generally below 1 it goes up into the
> > thousands.
> >
> > What is the difference between the two Cache Hit Ratio objects
> > Note: Vendor supplied DB is horrendous in design.|||Those are good questions; however, you will find that using Win2K AS or
Win2K3 EE not much different, but although AWE will momentarily ease memory
pressures, the added headaches it introduces will make the upgrades
short-lived. By going that route, you could extend the life of your system
for a year or two at best, but then you will be quickly running into the
limitations of the 32-bit architecture.
Since the OS and SQL Server licenses can be redeployed, and upgraded to
their 64-bit counterparts without charge, you could go ahead and upgrade
immediately but consider 64-bit solutions long term, IA64, not that silly
wanna-be, x64, EMT64 garbage.
It kind of depends on the usage. I would look at the SQL Server:Databases,
Transactions per second for each database, or the _Total instance, and the
SQL Server:General Statistics, Batch Requests per second to get an idea of
how busy your box is.
As for the two Cach hit Ratios: the Buffer Manager actually has five (5)
seperate memory managers associated with it...that is, the Buffer Pool. The
Data Buffer, which is just one of the memory managers, stores and manages
the data page cache and is what this counter is referring to. Whenever data
is requested to satisfy a query, if the data pages supporting that request
is currently resident in memory, you have a Data Buffer Cache Hit, otherwise
it is a miss and Disk I/O must be performed to load the requested pages.
You want a high cache hit, anything below 90% and you would be pretty
sluggish. Even at 98%, you are 10 times as inefficient than at 99%.
Now, the other cached items are the procedure plans in the PROCCACHE. The
Cache Manager, Cache Hit Ratio measures this and, actually, has multiple
instances, the _Total and one for each type of execution object: Procedures,
Ad-Hoc, Execution Contexts, Triggers, Cursors, Misc. Normalized Trees, etc.,
etc., etc. You should look at all of the instances as well as the _Total,
which you are quoting a figure of 77%. The instances can clue you in on
whether you have a memory limitation issue or just bad coding.
Now, let this be known, if you go the AWE route, it will be mostly for
additional Data Page buffering, not the Proc Cache. Welcome to the
limitations of the 32-bit arcitecture. No matter what you do, you are
limited to the 2 GB/3 GB lower memory region for Proc Cache storage. Now,
it is possible to offload some of the Data Buffer to the AWE space thereby
leaving more lower memory region for a larger than currently allocated Proc
Cache, but once you go AWE, SQL Server looses some of its flexibility and
dynamicity. Chances are your Proc Cache will not be reallocated much. In
fact, whenever there are memory pressures, the proc cache is usually the
first thing that is flushed or paged out to the swap file.
So, long term, you have two choices: go multi-instanced, either on the same
server, or by installing multiple servers, or took a real good look at IA64,
64-bit platforms. Dell has two low-entry versions currently, their 3250 and
7250 series.
Spend some time with the DBCC MEMORYSTATUS and DBCC PROCCACHE. These will
give you a lot of insight into how your installations is partitioning it
various memory allocations.
INF: Using DBCC MEMORYSTATUS to Monitor SQL Server Memory Usage
http://support.microsoft.com/default.aspx?scid=kb;en-us;271624
Hope this helps.
Sincerely,
Anthony Thomas
"Jeffrey K. Ericson" <JeffreyKEricson@.discussions.microsoft.com> wrote in
message news:CAB56205-326F-41A1-8E42-20985CBE4CFF@.microsoft.com...
We have a box that has 8GB of RAM but the OS only lets us see 3GB. We are
trying ascertain whether or not upgrading to a OS that will see the full
monty will help or redploying the assets would be a better option
The Buffer Cache Hit Ratio - 99%
The Memory Manager Cache Hit Ratio = 77%
The Target and actual values for Total Memory and Buffer Pages are the same
The memory pages/sec swings wildly, generally below 1 it goes up into the
thousands.
What is the difference between the two Cache Hit Ratio objects
Note: Vendor supplied DB is horrendous in design.|||Do u have the PAE switch and AWE switch enabled ?
If you have done the required configuration, I dont see how the OS is not
able to see
beyond 3GB.
Thanks,
Gopi
"Jeffrey K. Ericson" <JeffreyKEricson@.discussions.microsoft.com> wrote in
message news:8FCCB4AB-E082-40AE-B508-EECCB6603033@.microsoft.com...
> Yes, we have EE of SQL running on 2k standard. We are contemplaing going
> to
> a 2003 version that can support the 8GB.
> "Mike Epprecht (SQL MVP)" wrote:
>> Hi
>> You would need Enterprise Edition of Windows and SQL Server if you want
>> to
>> use all 8GB.
>> Regards
>> --
>> Mike Epprecht, Microsoft SQL Server MVP
>> Zurich, Switzerland
>> MVP Program: http://www.microsoft.com/mvp
>> Blog: http://www.msmvps.com/epprecht/
>>
>> "Jeffrey K. Ericson" wrote:
>> > We have a box that has 8GB of RAM but the OS only lets us see 3GB. We
>> > are
>> > trying ascertain whether or not upgrading to a OS that will see the
>> > full
>> > monty will help or redploying the assets would be a better option
>> >
>> > The Buffer Cache Hit Ratio - 99%
>> > The Memory Manager Cache Hit Ratio = 77%
>> > The Target and actual values for Total Memory and Buffer Pages are the
>> > same
>> > The memory pages/sec swings wildly, generally below 1 it goes up into
>> > the
>> > thousands.
>> >
>> > What is the difference between the two Cache Hit Ratio objects
>> > Note: Vendor supplied DB is horrendous in design.|||Found this in another Post. Have you applied SP4 ?
See the link below for the KB article on this issue. Microsoft are still
working on the problem.
--
Nick Colebourn (MCDBA)
DBA
United Coop Ltd
"Dimitar Dimitrov" wrote:
> The configuration is:
> OS: Windows 2003 Enterprise Edition
> RAM: 6GB
> boot.ini switches : /fastdetect /PAE /3GB /USERVA=3030
> SQL 2000 Enterprise Edition SP3a (awe enabled; min server memory (MB) => max server memory (MB) = 5120)
> Before installing SP4 SQL server uses configured amount of memory
> (5120MB).
> After installing SP4 it doesn't utilize more than 3GB of memory the SQL
> Server Logs reports "Warning: unable to allocate 'min server memory' of
> 5120MB."
"Jeffrey K. Ericson" <JeffreyKEricson@.discussions.microsoft.com> wrote in
message news:8FCCB4AB-E082-40AE-B508-EECCB6603033@.microsoft.com...
> Yes, we have EE of SQL running on 2k standard. We are contemplaing going
> to
> a 2003 version that can support the 8GB.
> "Mike Epprecht (SQL MVP)" wrote:
>> Hi
>> You would need Enterprise Edition of Windows and SQL Server if you want
>> to
>> use all 8GB.
>> Regards
>> --
>> Mike Epprecht, Microsoft SQL Server MVP
>> Zurich, Switzerland
>> MVP Program: http://www.microsoft.com/mvp
>> Blog: http://www.msmvps.com/epprecht/
>>
>> "Jeffrey K. Ericson" wrote:
>> > We have a box that has 8GB of RAM but the OS only lets us see 3GB. We
>> > are
>> > trying ascertain whether or not upgrading to a OS that will see the
>> > full
>> > monty will help or redploying the assets would be a better option
>> >
>> > The Buffer Cache Hit Ratio - 99%
>> > The Memory Manager Cache Hit Ratio = 77%
>> > The Target and actual values for Total Memory and Buffer Pages are the
>> > same
>> > The memory pages/sec swings wildly, generally below 1 it goes up into
>> > the
>> > thousands.
>> >
>> > What is the difference between the two Cache Hit Ratio objects
>> > Note: Vendor supplied DB is horrendous in design.|||"Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message
news:OzSLCxEaFHA.1152@.tk2msftngp13.phx.gbl...
> Those are good questions; however, you will find that using Win2K AS or
> Win2K3 EE not much different, but although AWE will momentarily ease
> memory
> pressures, the added headaches it introduces will make the upgrades
> short-lived.
What headaches do you mean? We're using AWE for a few years, and I have not
had any headaches because of that :) The only headache could be hardware
and/or drivers not compatible with AWE.
Best regards
Wojtek|||How many transactions per second and/or batch requests per second does your
installation produce?
Ours pushes nearly 1500 hundred of both/either quite often. This coupled
with extended proc, FOR XML, and LOB usage, constrains the 32-bit memory
space quite heavily.
We've been piloting a 64-bit system for a while now and are getting ready to
make the conversion. Dropping AWE altogether and going back to Dynamic
Memory management for all of the memory managers including the PROC CACHE
was probably the most compelling reason for the push to convert.
Glad to hear that you haven't begun to hit those limits yet and that it is
still working out for your installation(s).
Sincerely,
Anthony Thomas
"Wojtek Garwol" <garwol@.usunto.poczta.fm> wrote in message
news:d7qh4d$7v9$1@.nemesis.news.tpi.pl...
"Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message
news:OzSLCxEaFHA.1152@.tk2msftngp13.phx.gbl...
> Those are good questions; however, you will find that using Win2K AS or
> Win2K3 EE not much different, but although AWE will momentarily ease
> memory
> pressures, the added headaches it introduces will make the upgrades
> short-lived.
What headaches do you mean? We're using AWE for a few years, and I have not
had any headaches because of that :) The only headache could be hardware
and/or drivers not compatible with AWE.
Best regards
Wojtek|||The profile of our system is very different indeed. We have quite a lot of
data, 200+GB, biggest table has about 80mln records, and it is mostly
static. In this scenario, AWE, /pae and /3gb seem to work just fine. But now
I do see what you mean. AFAIK, AWE memory can be used only by data cache, so
it's pretty much limited indeed. Thanks for the info!
Regards
Wojtek
"Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message
news:%23Wl4GcIaFHA.2076@.TK2MSFTNGP15.phx.gbl...
> How many transactions per second and/or batch requests per second does
> your
> installation produce?
> Ours pushes nearly 1500 hundred of both/either quite often. This coupled
> with extended proc, FOR XML, and LOB usage, constrains the 32-bit memory
> space quite heavily.
> We've been piloting a 64-bit system for a while now and are getting ready
> to
> make the conversion. Dropping AWE altogether and going back to Dynamic
> Memory management for all of the memory managers including the PROC CACHE
> was probably the most compelling reason for the push to convert.
> Glad to hear that you haven't begun to hit those limits yet and that it is
> still working out for your installation(s).
> Sincerely,
>
> Anthony Thomas
>
> --
> "Wojtek Garwol" <garwol@.usunto.poczta.fm> wrote in message
> news:d7qh4d$7v9$1@.nemesis.news.tpi.pl...
> "Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message
> news:OzSLCxEaFHA.1152@.tk2msftngp13.phx.gbl...
>> Those are good questions; however, you will find that using Win2K AS or
>> Win2K3 EE not much different, but although AWE will momentarily ease
>> memory
>> pressures, the added headaches it introduces will make the upgrades
>> short-lived.
> What headaches do you mean? We're using AWE for a few years, and I have
> not
> had any headaches because of that :) The only headache could be hardware
> and/or drivers not compatible with AWE.
> Best regards
> Wojtek
>

Buffer Cache Hit Ratio vs Memory Manager Cache Hit ratio

We have a box that has 8GB of RAM but the OS only lets us see 3GB. We are
trying ascertain whether or not upgrading to a OS that will see the full
monty will help or redploying the assets would be a better option
The Buffer Cache Hit Ratio - 99%
The Memory Manager Cache Hit Ratio = 77%
The Target and actual values for Total Memory and Buffer Pages are the same
The memory pages/sec swings wildly, generally below 1 it goes up into the
thousands.
What is the difference between the two Cache Hit Ratio objects
Note: Vendor supplied DB is horrendous in design.
Hi
You would need Enterprise Edition of Windows and SQL Server if you want to
use all 8GB.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Jeffrey K. Ericson" wrote:

> We have a box that has 8GB of RAM but the OS only lets us see 3GB. We are
> trying ascertain whether or not upgrading to a OS that will see the full
> monty will help or redploying the assets would be a better option
> The Buffer Cache Hit Ratio - 99%
> The Memory Manager Cache Hit Ratio = 77%
> The Target and actual values for Total Memory and Buffer Pages are the same
> The memory pages/sec swings wildly, generally below 1 it goes up into the
> thousands.
> What is the difference between the two Cache Hit Ratio objects
> Note: Vendor supplied DB is horrendous in design.
|||Yes, we have EE of SQL running on 2k standard. We are contemplaing going to
a 2003 version that can support the 8GB.
"Mike Epprecht (SQL MVP)" wrote:
[vbcol=seagreen]
> Hi
> You would need Enterprise Edition of Windows and SQL Server if you want to
> use all 8GB.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
>
> "Jeffrey K. Ericson" wrote:
|||Those are good questions; however, you will find that using Win2K AS or
Win2K3 EE not much different, but although AWE will momentarily ease memory
pressures, the added headaches it introduces will make the upgrades
short-lived. By going that route, you could extend the life of your system
for a year or two at best, but then you will be quickly running into the
limitations of the 32-bit architecture.
Since the OS and SQL Server licenses can be redeployed, and upgraded to
their 64-bit counterparts without charge, you could go ahead and upgrade
immediately but consider 64-bit solutions long term, IA64, not that silly
wanna-be, x64, EMT64 garbage.
It kind of depends on the usage. I would look at the SQL Server:Databases,
Transactions per second for each database, or the _Total instance, and the
SQL Server:General Statistics, Batch Requests per second to get an idea of
how busy your box is.
As for the two Cach hit Ratios: the Buffer Manager actually has five (5)
seperate memory managers associated with it...that is, the Buffer Pool. The
Data Buffer, which is just one of the memory managers, stores and manages
the data page cache and is what this counter is referring to. Whenever data
is requested to satisfy a query, if the data pages supporting that request
is currently resident in memory, you have a Data Buffer Cache Hit, otherwise
it is a miss and Disk I/O must be performed to load the requested pages.
You want a high cache hit, anything below 90% and you would be pretty
sluggish. Even at 98%, you are 10 times as inefficient than at 99%.
Now, the other cached items are the procedure plans in the PROCCACHE. The
Cache Manager, Cache Hit Ratio measures this and, actually, has multiple
instances, the _Total and one for each type of execution object: Procedures,
Ad-Hoc, Execution Contexts, Triggers, Cursors, Misc. Normalized Trees, etc.,
etc., etc. You should look at all of the instances as well as the _Total,
which you are quoting a figure of 77%. The instances can clue you in on
whether you have a memory limitation issue or just bad coding.
Now, let this be known, if you go the AWE route, it will be mostly for
additional Data Page buffering, not the Proc Cache. Welcome to the
limitations of the 32-bit arcitecture. No matter what you do, you are
limited to the 2 GB/3 GB lower memory region for Proc Cache storage. Now,
it is possible to offload some of the Data Buffer to the AWE space thereby
leaving more lower memory region for a larger than currently allocated Proc
Cache, but once you go AWE, SQL Server looses some of its flexibility and
dynamicity. Chances are your Proc Cache will not be reallocated much. In
fact, whenever there are memory pressures, the proc cache is usually the
first thing that is flushed or paged out to the swap file.
So, long term, you have two choices: go multi-instanced, either on the same
server, or by installing multiple servers, or took a real good look at IA64,
64-bit platforms. Dell has two low-entry versions currently, their 3250 and
7250 series.
Spend some time with the DBCC MEMORYSTATUS and DBCC PROCCACHE. These will
give you a lot of insight into how your installations is partitioning it
various memory allocations.
INF: Using DBCC MEMORYSTATUS to Monitor SQL Server Memory Usage
http://support.microsoft.com/default...b;en-us;271624
Hope this helps.
Sincerely,
Anthony Thomas

"Jeffrey K. Ericson" <JeffreyKEricson@.discussions.microsoft.com> wrote in
message news:CAB56205-326F-41A1-8E42-20985CBE4CFF@.microsoft.com...
We have a box that has 8GB of RAM but the OS only lets us see 3GB. We are
trying ascertain whether or not upgrading to a OS that will see the full
monty will help or redploying the assets would be a better option
The Buffer Cache Hit Ratio - 99%
The Memory Manager Cache Hit Ratio = 77%
The Target and actual values for Total Memory and Buffer Pages are the same
The memory pages/sec swings wildly, generally below 1 it goes up into the
thousands.
What is the difference between the two Cache Hit Ratio objects
Note: Vendor supplied DB is horrendous in design.
|||Do u have the PAE switch and AWE switch enabled ?
If you have done the required configuration, I dont see how the OS is not
able to see
beyond 3GB.
Thanks,
Gopi
"Jeffrey K. Ericson" <JeffreyKEricson@.discussions.microsoft.com> wrote in
message news:8FCCB4AB-E082-40AE-B508-EECCB6603033@.microsoft.com...[vbcol=seagreen]
> Yes, we have EE of SQL running on 2k standard. We are contemplaing going
> to
> a 2003 version that can support the 8GB.
> "Mike Epprecht (SQL MVP)" wrote:
|||Found this in another Post. Have you applied SP4 ?
See the link below for the KB article on this issue. Microsoft are still
working on the problem.
Nick Colebourn (MCDBA)
DBA
United Coop Ltd
"Dimitar Dimitrov" wrote:

> The configuration is:
> OS: Windows 2003 Enterprise Edition
> RAM: 6GB
> boot.ini switches : /fastdetect /PAE /3GB /USERVA=3030
> SQL 2000 Enterprise Edition SP3a (awe enabled; min server memory (MB) =
> max server memory (MB) = 5120)
> Before installing SP4 SQL server uses configured amount of memory
> (5120MB).
> After installing SP4 it doesn't utilize more than 3GB of memory the SQL
> Server Logs reports "Warning: unable to allocate 'min server memory' of
> 5120MB."
"Jeffrey K. Ericson" <JeffreyKEricson@.discussions.microsoft.com> wrote in
message news:8FCCB4AB-E082-40AE-B508-EECCB6603033@.microsoft.com...[vbcol=seagreen]
> Yes, we have EE of SQL running on 2k standard. We are contemplaing going
> to
> a 2003 version that can support the 8GB.
> "Mike Epprecht (SQL MVP)" wrote:
|||Those are good questions; however, you will find that using Win2K AS or
Win2K3 EE not much different, but although AWE will momentarily ease memory
pressures, the added headaches it introduces will make the upgrades
short-lived. By going that route, you could extend the life of your system
for a year or two at best, but then you will be quickly running into the
limitations of the 32-bit architecture.
Since the OS and SQL Server licenses can be redeployed, and upgraded to
their 64-bit counterparts without charge, you could go ahead and upgrade
immediately but consider 64-bit solutions long term, IA64, not that silly
wanna-be, x64, EMT64 garbage.
It kind of depends on the usage. I would look at the SQL Server:Databases,
Transactions per second for each database, or the _Total instance, and the
SQL Server:General Statistics, Batch Requests per second to get an idea of
how busy your box is.
As for the two Cach hit Ratios: the Buffer Manager actually has five (5)
seperate memory managers associated with it...that is, the Buffer Pool. The
Data Buffer, which is just one of the memory managers, stores and manages
the data page cache and is what this counter is referring to. Whenever data
is requested to satisfy a query, if the data pages supporting that request
is currently resident in memory, you have a Data Buffer Cache Hit, otherwise
it is a miss and Disk I/O must be performed to load the requested pages.
You want a high cache hit, anything below 90% and you would be pretty
sluggish. Even at 98%, you are 10 times as inefficient than at 99%.
Now, the other cached items are the procedure plans in the PROCCACHE. The
Cache Manager, Cache Hit Ratio measures this and, actually, has multiple
instances, the _Total and one for each type of execution object: Procedures,
Ad-Hoc, Execution Contexts, Triggers, Cursors, Misc. Normalized Trees, etc.,
etc., etc. You should look at all of the instances as well as the _Total,
which you are quoting a figure of 77%. The instances can clue you in on
whether you have a memory limitation issue or just bad coding.
Now, let this be known, if you go the AWE route, it will be mostly for
additional Data Page buffering, not the Proc Cache. Welcome to the
limitations of the 32-bit arcitecture. No matter what you do, you are
limited to the 2 GB/3 GB lower memory region for Proc Cache storage. Now,
it is possible to offload some of the Data Buffer to the AWE space thereby
leaving more lower memory region for a larger than currently allocated Proc
Cache, but once you go AWE, SQL Server looses some of its flexibility and
dynamicity. Chances are your Proc Cache will not be reallocated much. In
fact, whenever there are memory pressures, the proc cache is usually the
first thing that is flushed or paged out to the swap file.
So, long term, you have two choices: go multi-instanced, either on the same
server, or by installing multiple servers, or took a real good look at IA64,
64-bit platforms. Dell has two low-entry versions currently, their 3250 and
7250 series.
Spend some time with the DBCC MEMORYSTATUS and DBCC PROCCACHE. These will
give you a lot of insight into how your installations is partitioning it
various memory allocations.
INF: Using DBCC MEMORYSTATUS to Monitor SQL Server Memory Usage
http://support.microsoft.com/default...b;en-us;271624
Hope this helps.
Sincerely,
Anthony Thomas

"Jeffrey K. Ericson" <JeffreyKEricson@.discussions.microsoft.com> wrote in
message news:CAB56205-326F-41A1-8E42-20985CBE4CFF@.microsoft.com...
We have a box that has 8GB of RAM but the OS only lets us see 3GB. We are
trying ascertain whether or not upgrading to a OS that will see the full
monty will help or redploying the assets would be a better option
The Buffer Cache Hit Ratio - 99%
The Memory Manager Cache Hit Ratio = 77%
The Target and actual values for Total Memory and Buffer Pages are the same
The memory pages/sec swings wildly, generally below 1 it goes up into the
thousands.
What is the difference between the two Cache Hit Ratio objects
Note: Vendor supplied DB is horrendous in design.
|||"Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message
news:OzSLCxEaFHA.1152@.tk2msftngp13.phx.gbl...
> Those are good questions; however, you will find that using Win2K AS or
> Win2K3 EE not much different, but although AWE will momentarily ease
> memory
> pressures, the added headaches it introduces will make the upgrades
> short-lived.
What headaches do you mean? We're using AWE for a few years, and I have not
had any headaches because of that The only headache could be hardware
and/or drivers not compatible with AWE.
Best regards
Wojtek
|||How many transactions per second and/or batch requests per second does your
installation produce?
Ours pushes nearly 1500 hundred of both/either quite often. This coupled
with extended proc, FOR XML, and LOB usage, constrains the 32-bit memory
space quite heavily.
We've been piloting a 64-bit system for a while now and are getting ready to
make the conversion. Dropping AWE altogether and going back to Dynamic
Memory management for all of the memory managers including the PROC CACHE
was probably the most compelling reason for the push to convert.
Glad to hear that you haven't begun to hit those limits yet and that it is
still working out for your installation(s).
Sincerely,
Anthony Thomas

"Wojtek Garwol" <garwol@.usunto.poczta.fm> wrote in message
news:d7qh4d$7v9$1@.nemesis.news.tpi.pl...
"Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message
news:OzSLCxEaFHA.1152@.tk2msftngp13.phx.gbl...
> Those are good questions; however, you will find that using Win2K AS or
> Win2K3 EE not much different, but although AWE will momentarily ease
> memory
> pressures, the added headaches it introduces will make the upgrades
> short-lived.
What headaches do you mean? We're using AWE for a few years, and I have not
had any headaches because of that The only headache could be hardware
and/or drivers not compatible with AWE.
Best regards
Wojtek
|||The profile of our system is very different indeed. We have quite a lot of
data, 200+GB, biggest table has about 80mln records, and it is mostly
static. In this scenario, AWE, /pae and /3gb seem to work just fine. But now
I do see what you mean. AFAIK, AWE memory can be used only by data cache, so
it's pretty much limited indeed. Thanks for the info!
Regards
Wojtek
"Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message
news:%23Wl4GcIaFHA.2076@.TK2MSFTNGP15.phx.gbl...
> How many transactions per second and/or batch requests per second does
> your
> installation produce?
> Ours pushes nearly 1500 hundred of both/either quite often. This coupled
> with extended proc, FOR XML, and LOB usage, constrains the 32-bit memory
> space quite heavily.
> We've been piloting a 64-bit system for a while now and are getting ready
> to
> make the conversion. Dropping AWE altogether and going back to Dynamic
> Memory management for all of the memory managers including the PROC CACHE
> was probably the most compelling reason for the push to convert.
> Glad to hear that you haven't begun to hit those limits yet and that it is
> still working out for your installation(s).
> Sincerely,
>
> Anthony Thomas
>
> --
> "Wojtek Garwol" <garwol@.usunto.poczta.fm> wrote in message
> news:d7qh4d$7v9$1@.nemesis.news.tpi.pl...
> "Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message
> news:OzSLCxEaFHA.1152@.tk2msftngp13.phx.gbl...
> What headaches do you mean? We're using AWE for a few years, and I have
> not
> had any headaches because of that The only headache could be hardware
> and/or drivers not compatible with AWE.
> Best regards
> Wojtek
>

Buffer Cache Hit Ratio vs Memory Manager Cache Hit ratio

We have a box that has 8GB of RAM but the OS only lets us see 3GB. We are
trying ascertain whether or not upgrading to a OS that will see the full
monty will help or redploying the assets would be a better option
The Buffer Cache Hit Ratio - 99%
The Memory Manager Cache Hit Ratio = 77%
The Target and actual values for Total Memory and Buffer Pages are the same
The memory pages/sec swings wildly, generally below 1 it goes up into the
thousands.
What is the difference between the two Cache Hit Ratio objects
Note: Vendor supplied DB is horrendous in design.Hi
You would need Enterprise Edition of Windows and SQL Server if you want to
use all 8GB.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Jeffrey K. Ericson" wrote:

> We have a box that has 8GB of RAM but the OS only lets us see 3GB. We are
> trying ascertain whether or not upgrading to a OS that will see the full
> monty will help or redploying the assets would be a better option
> The Buffer Cache Hit Ratio - 99%
> The Memory Manager Cache Hit Ratio = 77%
> The Target and actual values for Total Memory and Buffer Pages are the sam
e
> The memory pages/sec swings wildly, generally below 1 it goes up into the
> thousands.
> What is the difference between the two Cache Hit Ratio objects
> Note: Vendor supplied DB is horrendous in design.|||Yes, we have EE of SQL running on 2k standard. We are contemplaing going to
a 2003 version that can support the 8GB.
"Mike Epprecht (SQL MVP)" wrote:
[vbcol=seagreen]
> Hi
> You would need Enterprise Edition of Windows and SQL Server if you want to
> use all 8GB.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
>
> "Jeffrey K. Ericson" wrote:
>|||Those are good questions; however, you will find that using Win2K AS or
Win2K3 EE not much different, but although AWE will momentarily ease memory
pressures, the added headaches it introduces will make the upgrades
short-lived. By going that route, you could extend the life of your system
for a year or two at best, but then you will be quickly running into the
limitations of the 32-bit architecture.
Since the OS and SQL Server licenses can be redeployed, and upgraded to
their 64-bit counterparts without charge, you could go ahead and upgrade
immediately but consider 64-bit solutions long term, IA64, not that silly
wanna-be, x64, EMT64 garbage.
It kind of depends on the usage. I would look at the SQL Server:Databases,
Transactions per second for each database, or the _Total instance, and the
SQL Server:General Statistics, Batch Requests per second to get an idea of
how busy your box is.
As for the two Cach hit Ratios: the Buffer Manager actually has five (5)
seperate memory managers associated with it...that is, the Buffer Pool. The
Data Buffer, which is just one of the memory managers, stores and manages
the data page cache and is what this counter is referring to. Whenever data
is requested to satisfy a query, if the data pages supporting that request
is currently resident in memory, you have a Data Buffer Cache Hit, otherwise
it is a miss and Disk I/O must be performed to load the requested pages.
You want a high cache hit, anything below 90% and you would be pretty
sluggish. Even at 98%, you are 10 times as inefficient than at 99%.
Now, the other cached items are the procedure plans in the PROCCACHE. The
Cache Manager, Cache Hit Ratio measures this and, actually, has multiple
instances, the _Total and one for each type of execution object: Procedures,
Ad-Hoc, Execution Contexts, Triggers, Cursors, Misc. Normalized Trees, etc.,
etc., etc. You should look at all of the instances as well as the _Total,
which you are quoting a figure of 77%. The instances can clue you in on
whether you have a memory limitation issue or just bad coding.
Now, let this be known, if you go the AWE route, it will be mostly for
additional Data Page buffering, not the Proc Cache. Welcome to the
limitations of the 32-bit arcitecture. No matter what you do, you are
limited to the 2 GB/3 GB lower memory region for Proc Cache storage. Now,
it is possible to offload some of the Data Buffer to the AWE space thereby
leaving more lower memory region for a larger than currently allocated Proc
Cache, but once you go AWE, SQL Server looses some of its flexibility and
dynamicity. Chances are your Proc Cache will not be reallocated much. In
fact, whenever there are memory pressures, the proc cache is usually the
first thing that is flushed or paged out to the swap file.
So, long term, you have two choices: go multi-instanced, either on the same
server, or by installing multiple servers, or took a real good look at IA64,
64-bit platforms. Dell has two low-entry versions currently, their 3250 and
7250 series.
Spend some time with the DBCC MEMORYSTATUS and DBCC PROCCACHE. These will
give you a lot of insight into how your installations is partitioning it
various memory allocations.
INF: Using DBCC MEMORYSTATUS to Monitor SQL Server Memory Usage
http://support.microsoft.com/defaul...kb;en-us;271624
Hope this helps.
Sincerely,
Anthony Thomas
"Jeffrey K. Ericson" <JeffreyKEricson@.discussions.microsoft.com> wrote in
message news:CAB56205-326F-41A1-8E42-20985CBE4CFF@.microsoft.com...
We have a box that has 8GB of RAM but the OS only lets us see 3GB. We are
trying ascertain whether or not upgrading to a OS that will see the full
monty will help or redploying the assets would be a better option
The Buffer Cache Hit Ratio - 99%
The Memory Manager Cache Hit Ratio = 77%
The Target and actual values for Total Memory and Buffer Pages are the same
The memory pages/sec swings wildly, generally below 1 it goes up into the
thousands.
What is the difference between the two Cache Hit Ratio objects
Note: Vendor supplied DB is horrendous in design.|||Do u have the PAE switch and AWE switch enabled ?
If you have done the required configuration, I dont see how the OS is not
able to see
beyond 3GB.
Thanks,
Gopi
"Jeffrey K. Ericson" <JeffreyKEricson@.discussions.microsoft.com> wrote in
message news:8FCCB4AB-E082-40AE-B508-EECCB6603033@.microsoft.com...[vbcol=seagreen]
> Yes, we have EE of SQL running on 2k standard. We are contemplaing going
> to
> a 2003 version that can support the 8GB.
> "Mike Epprecht (SQL MVP)" wrote:
>|||Found this in another Post. Have you applied SP4 ?
See the link below for the KB article on this issue. Microsoft are still
working on the problem.
--
Nick Colebourn (MCDBA)
DBA
United Coop Ltd
"Dimitar Dimitrov" wrote:

> The configuration is:
> OS: Windows 2003 Enterprise Edition
> RAM: 6GB
> boot.ini switches : /fastdetect /PAE /3GB /USERVA=3030
> SQL 2000 Enterprise Edition SP3a (awe enabled; min server memory (MB) =
> max server memory (MB) = 5120)
> Before installing SP4 SQL server uses configured amount of memory
> (5120MB).
> After installing SP4 it doesn't utilize more than 3GB of memory the SQL
> Server Logs reports "Warning: unable to allocate 'min server memory' of
> 5120MB."
"Jeffrey K. Ericson" <JeffreyKEricson@.discussions.microsoft.com> wrote in
message news:8FCCB4AB-E082-40AE-B508-EECCB6603033@.microsoft.com...[vbcol=seagreen]
> Yes, we have EE of SQL running on 2k standard. We are contemplaing going
> to
> a 2003 version that can support the 8GB.
> "Mike Epprecht (SQL MVP)" wrote:
>|||Those are good questions; however, you will find that using Win2K AS or
Win2K3 EE not much different, but although AWE will momentarily ease memory
pressures, the added headaches it introduces will make the upgrades
short-lived. By going that route, you could extend the life of your system
for a year or two at best, but then you will be quickly running into the
limitations of the 32-bit architecture.
Since the OS and SQL Server licenses can be redeployed, and upgraded to
their 64-bit counterparts without charge, you could go ahead and upgrade
immediately but consider 64-bit solutions long term, IA64, not that silly
wanna-be, x64, EMT64 garbage.
It kind of depends on the usage. I would look at the SQL Server:Databases,
Transactions per second for each database, or the _Total instance, and the
SQL Server:General Statistics, Batch Requests per second to get an idea of
how busy your box is.
As for the two Cach hit Ratios: the Buffer Manager actually has five (5)
seperate memory managers associated with it...that is, the Buffer Pool. The
Data Buffer, which is just one of the memory managers, stores and manages
the data page cache and is what this counter is referring to. Whenever data
is requested to satisfy a query, if the data pages supporting that request
is currently resident in memory, you have a Data Buffer Cache Hit, otherwise
it is a miss and Disk I/O must be performed to load the requested pages.
You want a high cache hit, anything below 90% and you would be pretty
sluggish. Even at 98%, you are 10 times as inefficient than at 99%.
Now, the other cached items are the procedure plans in the PROCCACHE. The
Cache Manager, Cache Hit Ratio measures this and, actually, has multiple
instances, the _Total and one for each type of execution object: Procedures,
Ad-Hoc, Execution Contexts, Triggers, Cursors, Misc. Normalized Trees, etc.,
etc., etc. You should look at all of the instances as well as the _Total,
which you are quoting a figure of 77%. The instances can clue you in on
whether you have a memory limitation issue or just bad coding.
Now, let this be known, if you go the AWE route, it will be mostly for
additional Data Page buffering, not the Proc Cache. Welcome to the
limitations of the 32-bit arcitecture. No matter what you do, you are
limited to the 2 GB/3 GB lower memory region for Proc Cache storage. Now,
it is possible to offload some of the Data Buffer to the AWE space thereby
leaving more lower memory region for a larger than currently allocated Proc
Cache, but once you go AWE, SQL Server looses some of its flexibility and
dynamicity. Chances are your Proc Cache will not be reallocated much. In
fact, whenever there are memory pressures, the proc cache is usually the
first thing that is flushed or paged out to the swap file.
So, long term, you have two choices: go multi-instanced, either on the same
server, or by installing multiple servers, or took a real good look at IA64,
64-bit platforms. Dell has two low-entry versions currently, their 3250 and
7250 series.
Spend some time with the DBCC MEMORYSTATUS and DBCC PROCCACHE. These will
give you a lot of insight into how your installations is partitioning it
various memory allocations.
INF: Using DBCC MEMORYSTATUS to Monitor SQL Server Memory Usage
http://support.microsoft.com/defaul...kb;en-us;271624
Hope this helps.
Sincerely,
Anthony Thomas
"Jeffrey K. Ericson" <JeffreyKEricson@.discussions.microsoft.com> wrote in
message news:CAB56205-326F-41A1-8E42-20985CBE4CFF@.microsoft.com...
We have a box that has 8GB of RAM but the OS only lets us see 3GB. We are
trying ascertain whether or not upgrading to a OS that will see the full
monty will help or redploying the assets would be a better option
The Buffer Cache Hit Ratio - 99%
The Memory Manager Cache Hit Ratio = 77%
The Target and actual values for Total Memory and Buffer Pages are the same
The memory pages/sec swings wildly, generally below 1 it goes up into the
thousands.
What is the difference between the two Cache Hit Ratio objects
Note: Vendor supplied DB is horrendous in design.|||"Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message
news:OzSLCxEaFHA.1152@.tk2msftngp13.phx.gbl...
> Those are good questions; however, you will find that using Win2K AS or
> Win2K3 EE not much different, but although AWE will momentarily ease
> memory
> pressures, the added headaches it introduces will make the upgrades
> short-lived.
What headaches do you mean? We're using AWE for a few years, and I have not
had any headaches because of that The only headache could be hardware
and/or drivers not compatible with AWE.
Best regards
Wojtek|||How many transactions per second and/or batch requests per second does your
installation produce?
Ours pushes nearly 1500 hundred of both/either quite often. This coupled
with extended proc, FOR XML, and LOB usage, constrains the 32-bit memory
space quite heavily.
We've been piloting a 64-bit system for a while now and are getting ready to
make the conversion. Dropping AWE altogether and going back to Dynamic
Memory management for all of the memory managers including the PROC CACHE
was probably the most compelling reason for the push to convert.
Glad to hear that you haven't begun to hit those limits yet and that it is
still working out for your installation(s).
Sincerely,
Anthony Thomas
"Wojtek Garwol" <garwol@.usunto.poczta.fm> wrote in message
news:d7qh4d$7v9$1@.nemesis.news.tpi.pl...
"Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message
news:OzSLCxEaFHA.1152@.tk2msftngp13.phx.gbl...
> Those are good questions; however, you will find that using Win2K AS or
> Win2K3 EE not much different, but although AWE will momentarily ease
> memory
> pressures, the added headaches it introduces will make the upgrades
> short-lived.
What headaches do you mean? We're using AWE for a few years, and I have not
had any headaches because of that The only headache could be hardware
and/or drivers not compatible with AWE.
Best regards
Wojtek|||The profile of our system is very different indeed. We have quite a lot of
data, 200+GB, biggest table has about 80mln records, and it is mostly
static. In this scenario, AWE, /pae and /3gb seem to work just fine. But now
I do see what you mean. AFAIK, AWE memory can be used only by data cache, so
it's pretty much limited indeed. Thanks for the info!
Regards
Wojtek
"Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message
news:%23Wl4GcIaFHA.2076@.TK2MSFTNGP15.phx.gbl...
> How many transactions per second and/or batch requests per second does
> your
> installation produce?
> Ours pushes nearly 1500 hundred of both/either quite often. This coupled
> with extended proc, FOR XML, and LOB usage, constrains the 32-bit memory
> space quite heavily.
> We've been piloting a 64-bit system for a while now and are getting ready
> to
> make the conversion. Dropping AWE altogether and going back to Dynamic
> Memory management for all of the memory managers including the PROC CACHE
> was probably the most compelling reason for the push to convert.
> Glad to hear that you haven't begun to hit those limits yet and that it is
> still working out for your installation(s).
> Sincerely,
>
> Anthony Thomas
>
> --
> "Wojtek Garwol" <garwol@.usunto.poczta.fm> wrote in message
> news:d7qh4d$7v9$1@.nemesis.news.tpi.pl...
> "Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message
> news:OzSLCxEaFHA.1152@.tk2msftngp13.phx.gbl...
> What headaches do you mean? We're using AWE for a few years, and I have
> not
> had any headaches because of that The only headache could be hardware
> and/or drivers not compatible with AWE.
> Best regards
> Wojtek
>

Bound Control on Access Form Causing High Memory Usage on SQL Server

I got about 50 Access users every day using the SQL Server
2000 database. At the end of the day, SQL Server always
ran out of the memory. Someone told me this is because of
the bound connection on the Access form causing the memory
leak. Is there a way to fix this problem without changing
my Access form design.not really.
acccess bound controls open numerous connections PER FORM.
this is not good.
only way to fix it would be to code your connectivity manually.
GAJ|||I currently have 2 GB physical memory which could last for
about 2 to 3 days if no one runs a big report. What if I
increase the memory size to 4 GB?

>--Original Message--
>not really.
>acccess bound controls open numerous connections PER FORM.
>this is not good.
>only way to fix it would be to code your connectivity
manually.
>
>GAJ
>
>.
>|||I suspect you will still eventually run out of connections and\or memory.
instead of using bound controls, write the code manually to populate
controls. that way you have explicit control over connection management.
will this work for you ?
GAJ|||Ken,
Are you sure it is really a memory leak? SQL Server will not release memory
once it grabs it unless the OS specifically calls for it. That is by
design. http://www.support.microsoft.com/?id=321363 Do you have things
other than sql server running on the same machine? If so you may want to
set your max memory setting to something less than the default of max and
see if that helps.
Andrew J. Kelly
SQL Server MVP
"Ken" <anonymous@.discussions.microsoft.com> wrote in message
news:bf6601c3ecfa$9f12e2a0$a401280a@.phx.gbl...
> I got about 50 Access users every day using the SQL Server
> 2000 database. At the end of the day, SQL Server always
> ran out of the memory. Someone told me this is because of
> the bound connection on the Access form causing the memory
> leak. Is there a way to fix this problem without changing
> my Access form design.

Bound Control on Access Form Causing High Memory Usage on SQL Server

I got about 50 Access users every day using the SQL Server
2000 database. At the end of the day, SQL Server always
ran out of the memory. Someone told me this is because of
the bound connection on the Access form causing the memory
leak. Is there a way to fix this problem without changing
my Access form design.not really.
acccess bound controls open numerous connections PER FORM.
this is not good.
only way to fix it would be to code your connectivity manually.
GAJ|||I currently have 2 GB physical memory which could last for
about 2 to 3 days if no one runs a big report. What if I
increase the memory size to 4 GB?
>--Original Message--
>not really.
>acccess bound controls open numerous connections PER FORM.
>this is not good.
>only way to fix it would be to code your connectivity
manually.
>
>GAJ
>
>.
>|||I suspect you will still eventually run out of connections and\or memory.
instead of using bound controls, write the code manually to populate
controls. that way you have explicit control over connection management.
will this work for you ?
GAJ|||Ken,
Are you sure it is really a memory leak? SQL Server will not release memory
once it grabs it unless the OS specifically calls for it. That is by
design. http://www.support.microsoft.com/?id=321363 Do you have things
other than sql server running on the same machine? If so you may want to
set your max memory setting to something less than the default of max and
see if that helps.
--
Andrew J. Kelly
SQL Server MVP
"Ken" <anonymous@.discussions.microsoft.com> wrote in message
news:bf6601c3ecfa$9f12e2a0$a401280a@.phx.gbl...
> I got about 50 Access users every day using the SQL Server
> 2000 database. At the end of the day, SQL Server always
> ran out of the memory. Someone told me this is because of
> the bound connection on the Access form causing the memory
> leak. Is there a way to fix this problem without changing
> my Access form design.

BOOT.INI File for 4GB RAM and SQL 2005

I have 3 questions regarding memory configuration on a Windows Server 2003
SP2 Enterprise Edition with SQL2005 SP2.
1. Can someone advise on the correct boot.ini file configuration to utilize
4GB of local memory? I'm not sure if I need the 3GB switch?
multi(0)rdisk(0)partition(1)\WINDOWS="Windows Server 2003,
Enterprise"/3GB/fastdetect/PAE/NoExectute=OptOut
2. Once Boot.ini configured, is the only setting in SQL 2005 to modify is
the sp_configure awe enabled config_value=1 and run_value=1 ?
3. Assuming nothing else is to be done to have SQL 2005 utilize more than
the 2GB memory space, how do you verify or validate SQL 2005 is using more
memory?
Thanks in advancezorro
Is it 64 bit or 32 bit?
> 1. Can someone advise on the correct boot.ini file configuration to
> utilize
> 4GB of local memory? I'm not sure if I need the 3GB switch?
You do need 3GB switch
> 2. Once Boot.ini configured, is the only setting in SQL 2005 to modify is
> the sp_configure awe enabled config_value=1 and run_value=1 ?
AWE is for utilyze more than 4GB
Yes, unless you use 64 bit , then you do not need AWE , make sure that an
account SQL Serverc runs under is added to LockPages in Memory Local Group
Policy
> 3. Assuming nothing else is to be done to have SQL 2005 utilize more than
> the 2GB memory space, how do you verify or validate SQL 2005 is using more
> memory?
Task Manager
"zorro" <zorro@.discussions.microsoft.com> wrote in message
news:C2009B55-7B5D-4F65-8986-3AF00547FA1E@.microsoft.com...
>I have 3 questions regarding memory configuration on a Windows Server 2003
> SP2 Enterprise Edition with SQL2005 SP2.
> 1. Can someone advise on the correct boot.ini file configuration to
> utilize
> 4GB of local memory? I'm not sure if I need the 3GB switch?
> multi(0)rdisk(0)partition(1)\WINDOWS="Windows Server 2003,
> Enterprise"/3GB/fastdetect/PAE/NoExectute=OptOut
> 2. Once Boot.ini configured, is the only setting in SQL 2005 to modify is
> the sp_configure awe enabled config_value=1 and run_value=1 ?
> 3. Assuming nothing else is to be done to have SQL 2005 utilize more than
> the 2GB memory space, how do you verify or validate SQL 2005 is using more
> memory?
> Thanks in advance
>|||>> 3. Assuming nothing else is to be done to have SQL 2005 utilize more than
>> the 2GB memory space, how do you verify or validate SQL 2005 is using
>> more
>> memory?
> Task Manager
Actually, I would use performance monitor. Task manager tends to
under-report what SQL Server is actually using.|||Aaron
I agreee. I assumed the OP wants to utilize 4GB ONLY , so after adding
3GB switch you can actually observe in TM.
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:%23qL67StfIHA.5088@.TK2MSFTNGP02.phx.gbl...
>> 3. Assuming nothing else is to be done to have SQL 2005 utilize more
>> than
>> the 2GB memory space, how do you verify or validate SQL 2005 is using
>> more
>> memory?
>> Task Manager
> Actually, I would use performance monitor. Task manager tends to
> under-report what SQL Server is actually using.|||Thanks Uri,
1. 32 bit Windows Server 2003 version.
2. To verify 3GB switch is needed for my 5GB physically installed RAM?
3. Also good catch on the LockPages in Memory setting within the gpedit.msc
Thanks,
zorro
"Uri Dimant" wrote:
> zorro
> Is it 64 bit or 32 bit?
> > 1. Can someone advise on the correct boot.ini file configuration to
> > utilize
> > 4GB of local memory? I'm not sure if I need the 3GB switch?
> You do need 3GB switch
> > 2. Once Boot.ini configured, is the only setting in SQL 2005 to modify is
> > the sp_configure awe enabled config_value=1 and run_value=1 ?
> AWE is for utilyze more than 4GB
> Yes, unless you use 64 bit , then you do not need AWE , make sure that an
> account SQL Serverc runs under is added to LockPages in Memory Local Group
> Policy
> > 3. Assuming nothing else is to be done to have SQL 2005 utilize more than
> > the 2GB memory space, how do you verify or validate SQL 2005 is using more
> > memory?
> Task Manager
>
>
> "zorro" <zorro@.discussions.microsoft.com> wrote in message
> news:C2009B55-7B5D-4F65-8986-3AF00547FA1E@.microsoft.com...
> >I have 3 questions regarding memory configuration on a Windows Server 2003
> > SP2 Enterprise Edition with SQL2005 SP2.
> >
> > 1. Can someone advise on the correct boot.ini file configuration to
> > utilize
> > 4GB of local memory? I'm not sure if I need the 3GB switch?
> >
> > multi(0)rdisk(0)partition(1)\WINDOWS="Windows Server 2003,
> > Enterprise"/3GB/fastdetect/PAE/NoExectute=OptOut
> >
> > 2. Once Boot.ini configured, is the only setting in SQL 2005 to modify is
> > the sp_configure awe enabled config_value=1 and run_value=1 ?
> >
> > 3. Assuming nothing else is to be done to have SQL 2005 utilize more than
> > the 2GB memory space, how do you verify or validate SQL 2005 is using more
> > memory?
> >
> > Thanks in advance
> >
> >
>
>|||See comments inline.
Linchi
"Uri Dimant" wrote:
> zorro
> Is it 64 bit or 32 bit?
> > 1. Can someone advise on the correct boot.ini file configuration to
> > utilize
> > 4GB of local memory? I'm not sure if I need the 3GB switch?
> You do need 3GB switch
>
Why?
> > 2. Once Boot.ini configured, is the only setting in SQL 2005 to modify is
> > the sp_configure awe enabled config_value=1 and run_value=1 ?
> AWE is for utilyze more than 4GB
I keep hearing that AWE is for utilizing more than 4GB. Not sure where that
comes from. Technically, it's not.
> Yes, unless you use 64 bit , then you do not need AWE , make sure that an
> account SQL Serverc runs under is added to LockPages in Memory Local Group
> Policy
> > 3. Assuming nothing else is to be done to have SQL 2005 utilize more than
> > the 2GB memory space, how do you verify or validate SQL 2005 is using more
> > memory?
> Task Manager
>
>
> "zorro" <zorro@.discussions.microsoft.com> wrote in message
> news:C2009B55-7B5D-4F65-8986-3AF00547FA1E@.microsoft.com...
> >I have 3 questions regarding memory configuration on a Windows Server 2003
> > SP2 Enterprise Edition with SQL2005 SP2.
> >
> > 1. Can someone advise on the correct boot.ini file configuration to
> > utilize
> > 4GB of local memory? I'm not sure if I need the 3GB switch?
> >
> > multi(0)rdisk(0)partition(1)\WINDOWS="Windows Server 2003,
> > Enterprise"/3GB/fastdetect/PAE/NoExectute=OptOut
> >
> > 2. Once Boot.ini configured, is the only setting in SQL 2005 to modify is
> > the sp_configure awe enabled config_value=1 and run_value=1 ?
> >
> > 3. Assuming nothing else is to be done to have SQL 2005 utilize more than
> > the 2GB memory space, how do you verify or validate SQL 2005 is using more
> > memory?
> >
> > Thanks in advance
> >
> >
>
>|||Hi Linchi
You are right, that technically, it's not. If I have 4GB RAM and want tio
utilize 3GB for SQL Server, so I simple add switch in BOOT.INI and why
bother enabling AWE? What do you think?
"Linchi Shea" <LinchiShea@.discussions.microsoft.com> wrote in message
news:61A4195E-F98E-4AE2-A24F-0BC6AC668D08@.microsoft.com...
> See comments inline.
> Linchi
> "Uri Dimant" wrote:
>> zorro
>> Is it 64 bit or 32 bit?
>> > 1. Can someone advise on the correct boot.ini file configuration to
>> > utilize
>> > 4GB of local memory? I'm not sure if I need the 3GB switch?
>> You do need 3GB switch
> Why?
>> > 2. Once Boot.ini configured, is the only setting in SQL 2005 to modify
>> > is
>> > the sp_configure awe enabled config_value=1 and run_value=1 ?
>> AWE is for utilyze more than 4GB
> I keep hearing that AWE is for utilizing more than 4GB. Not sure where
> that
> comes from. Technically, it's not.
>> Yes, unless you use 64 bit , then you do not need AWE , make sure that
>> an
>> account SQL Serverc runs under is added to LockPages in Memory Local
>> Group
>> Policy
>> > 3. Assuming nothing else is to be done to have SQL 2005 utilize more
>> > than
>> > the 2GB memory space, how do you verify or validate SQL 2005 is using
>> > more
>> > memory?
>> Task Manager
>>
>>
>> "zorro" <zorro@.discussions.microsoft.com> wrote in message
>> news:C2009B55-7B5D-4F65-8986-3AF00547FA1E@.microsoft.com...
>> >I have 3 questions regarding memory configuration on a Windows Server
>> >2003
>> > SP2 Enterprise Edition with SQL2005 SP2.
>> >
>> > 1. Can someone advise on the correct boot.ini file configuration to
>> > utilize
>> > 4GB of local memory? I'm not sure if I need the 3GB switch?
>> >
>> > multi(0)rdisk(0)partition(1)\WINDOWS="Windows Server 2003,
>> > Enterprise"/3GB/fastdetect/PAE/NoExectute=OptOut
>> >
>> > 2. Once Boot.ini configured, is the only setting in SQL 2005 to modify
>> > is
>> > the sp_configure awe enabled config_value=1 and run_value=1 ?
>> >
>> > 3. Assuming nothing else is to be done to have SQL 2005 utilize more
>> > than
>> > the 2GB memory space, how do you verify or validate SQL 2005 is using
>> > more
>> > memory?
>> >
>> > Thanks in advance
>> >
>> >
>>

BOOT.INI File for 4GB RAM and SQL 2005

I have 3 questions regarding memory configuration on a Windows Server 2003
SP2 Enterprise Edition with SQL2005 SP2.
1. Can someone advise on the correct boot.ini file configuration to utilize
4GB of local memory? I'm not sure if I need the 3GB switch?
multi(0)rdisk(0)partition(1)\WINDOWS="Windows Server 2003,
Enterprise"/3GB/fastdetect/PAE/NoExectute=OptOut
2. Once Boot.ini configured, is the only setting in SQL 2005 to modify is
the sp_configure awe enabled config_value=1 and run_value=1 ?
3. Assuming nothing else is to be done to have SQL 2005 utilize more than
the 2GB memory space, how do you verify or validate SQL 2005 is using more
memory?
Thanks in advance
zorro
Is it 64 bit or 32 bit?
> 1. Can someone advise on the correct boot.ini file configuration to
> utilize
> 4GB of local memory? I'm not sure if I need the 3GB switch?
You do need 3GB switch

> 2. Once Boot.ini configured, is the only setting in SQL 2005 to modify is
> the sp_configure awe enabled config_value=1 and run_value=1 ?
AWE is for utilyze more than 4GB
Yes, unless you use 64 bit , then you do not need AWE , make sure that an
account SQL Serverc runs under is added to LockPages in Memory Local Group
Policy

> 3. Assuming nothing else is to be done to have SQL 2005 utilize more than
> the 2GB memory space, how do you verify or validate SQL 2005 is using more
> memory?
Task Manager
"zorro" <zorro@.discussions.microsoft.com> wrote in message
news:C2009B55-7B5D-4F65-8986-3AF00547FA1E@.microsoft.com...
>I have 3 questions regarding memory configuration on a Windows Server 2003
> SP2 Enterprise Edition with SQL2005 SP2.
> 1. Can someone advise on the correct boot.ini file configuration to
> utilize
> 4GB of local memory? I'm not sure if I need the 3GB switch?
> multi(0)rdisk(0)partition(1)\WINDOWS="Windows Server 2003,
> Enterprise"/3GB/fastdetect/PAE/NoExectute=OptOut
> 2. Once Boot.ini configured, is the only setting in SQL 2005 to modify is
> the sp_configure awe enabled config_value=1 and run_value=1 ?
> 3. Assuming nothing else is to be done to have SQL 2005 utilize more than
> the 2GB memory space, how do you verify or validate SQL 2005 is using more
> memory?
> Thanks in advance
>
|||>> 3. Assuming nothing else is to be done to have SQL 2005 utilize more than
> Task Manager
Actually, I would use performance monitor. Task manager tends to
under-report what SQL Server is actually using.
|||Aaron
I agreee. I assumed the OP wants to utilize 4GB ONLY , so after adding
3GB switch you can actually observe in TM.
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:%23qL67StfIHA.5088@.TK2MSFTNGP02.phx.gbl...
> Actually, I would use performance monitor. Task manager tends to
> under-report what SQL Server is actually using.
|||Thanks Uri,
1. 32 bit Windows Server 2003 version.
2. To verify 3GB switch is needed for my 5GB physically installed RAM?
3. Also good catch on the LockPages in Memory setting within the gpedit.msc
Thanks,
zorro
"Uri Dimant" wrote:

> zorro
> Is it 64 bit or 32 bit?
> You do need 3GB switch
>
> AWE is for utilyze more than 4GB
> Yes, unless you use 64 bit , then you do not need AWE , make sure that an
> account SQL Serverc runs under is added to LockPages in Memory Local Group
> Policy
>
> Task Manager
>
>
> "zorro" <zorro@.discussions.microsoft.com> wrote in message
> news:C2009B55-7B5D-4F65-8986-3AF00547FA1E@.microsoft.com...
>
>
|||See comments inline.
Linchi
"Uri Dimant" wrote:

> zorro
> Is it 64 bit or 32 bit?
> You do need 3GB switch
>
Why?

> AWE is for utilyze more than 4GB
I keep hearing that AWE is for utilizing more than 4GB. Not sure where that
comes from. Technically, it's not.

> Yes, unless you use 64 bit , then you do not need AWE , make sure that an
> account SQL Serverc runs under is added to LockPages in Memory Local Group
> Policy
>
> Task Manager
>
>
> "zorro" <zorro@.discussions.microsoft.com> wrote in message
> news:C2009B55-7B5D-4F65-8986-3AF00547FA1E@.microsoft.com...
>
>
|||Hi Linchi
You are right, that technically, it's not. If I have 4GB RAM and want tio
utilize 3GB for SQL Server, so I simple add switch in BOOT.INI and why
bother enabling AWE? What do you think?
"Linchi Shea" <LinchiShea@.discussions.microsoft.com> wrote in message
news:61A4195E-F98E-4AE2-A24F-0BC6AC668D08@.microsoft.com...[vbcol=seagreen]
> See comments inline.
> Linchi
> "Uri Dimant" wrote:
>
> Why?
>
> I keep hearing that AWE is for utilizing more than 4GB. Not sure where
> that
> comes from. Technically, it's not.