Performance Monitoring â?" Interpretation

Folks,
Objective: Is there enough resources on this server to create another
instance of SQL Server?
SQL Server 2000 Enterprise Edition SP3
Windows 2003 Enterprise Edition SP1 4GB Memory
The server currently has 1 instance.
SQL Server is dynamically managing memory
Total Server Memory: 2.606 GB
Available Mbytes: 380
Pages/Sec: .023
SQLSERV Working Set: 2.648 GB
Processor: 10%
Cache Hit Ratio: 99.8%
SQL Server Free Pages: 1,031 (1.611 MB)
No disk contention.
My interpretation is this:
This instance of SQL Server appears to be utilizing all the memory available
to it. If I scale this instance back via max server memory so that I have
available memory for the second instance, Iâ'm thinking that the good
performance Iâ'm realizing now on this server would be jeopardized.
The book I am referencing (SQL Server 2000 Performance Tuning â' Microsoft
Press) also mentions that if SQL Server: Buffer Manager/Free Pages is
consistently below 5MB (in my case it is) that I am either running low on
physical memory or allocating too much to SQL Server. This has me confused.
How can I determine?
Iâ'd also like to know how SQL Server is able to use more than 2GB of
physical memory. Is there anything special (i.e. Boot.ini flag) that is
required?
Thanks in advance.
Scott H.Adding another instance will mean that you have to allocate memory between
the two instances and they may compete but it is hard to say without knowing
what the other instance needs for resources. Why not simply add the new db
to the existing instance? I would look at the Page Life Expectancy counter
to see how well the memory you have now is used. And you are obviously using
more than 2GB for this instance if you have 2.66GB of total memory. So you
must have already set the /3GB switch in the boot.ini file other wise you
would only be using 2GB or less. And why not add more memory? You have EE
for both SQL and Windows yet only have 4GB of memory. Seems counter
productive.
--
Andrew J. Kelly SQL MVP
"Scott H." <ScottH@.discussions.microsoft.com> wrote in message
news:978AEB2B-21E0-48EA-B8A3-687B2A629476@.microsoft.com...
> Folks,
> Objective: Is there enough resources on this server to create another
> instance of SQL Server?
> SQL Server 2000 Enterprise Edition SP3
> Windows 2003 Enterprise Edition SP1 4GB Memory
> The server currently has 1 instance.
> SQL Server is dynamically managing memory
> Total Server Memory: 2.606 GB
> Available Mbytes: 380
> Pages/Sec: .023
> SQLSERV Working Set: 2.648 GB
> Processor: 10%
> Cache Hit Ratio: 99.8%
> SQL Server Free Pages: 1,031 (1.611 MB)
> No disk contention.
> My interpretation is this:
> This instance of SQL Server appears to be utilizing all the memory
> available
> to it. If I scale this instance back via max server memory so that I have
> available memory for the second instance, Iâ?Tm thinking that the good
> performance Iâ?Tm realizing now on this server would be jeopardized.
> The book I am referencing (SQL Server 2000 Performance Tuning â?"
> Microsoft
> Press) also mentions that if SQL Server: Buffer Manager/Free Pages is
> consistently below 5MB (in my case it is) that I am either running low on
> physical memory or allocating too much to SQL Server. This has me
> confused.
> How can I determine?
> Iâ?Td also like to know how SQL Server is able to use more than 2GB of
> physical memory. Is there anything special (i.e. Boot.ini flag) that is
> required?
> Thanks in advance.
> Scott H.
>|||Andrew,
The reason for the additional instance is that there will be 2 new databases
that will be internet facing. Currently this instance is not, and no plans on
opening up port 1433 through the firewall.
My recommendation is to add more memory. There is some reluctance due to
cost. The server is an older generation HP (HP DL380 G2). I'm being told that
the memory is not available.
So, the /3GB switch in the boot.ini file is still applicable on Windows 2003
EE and SQL Server 2000 EE? How can I find/view this parameter?
Based on the performance counters, would your recommendation be to add more
memory?
Thanks for your help.
--
Thanks,
Scott H.
"Andrew J. Kelly" wrote:
> Adding another instance will mean that you have to allocate memory between
> the two instances and they may compete but it is hard to say without knowing
> what the other instance needs for resources. Why not simply add the new db
> to the existing instance? I would look at the Page Life Expectancy counter
> to see how well the memory you have now is used. And you are obviously using
> more than 2GB for this instance if you have 2.66GB of total memory. So you
> must have already set the /3GB switch in the boot.ini file other wise you
> would only be using 2GB or less. And why not add more memory? You have EE
> for both SQL and Windows yet only have 4GB of memory. Seems counter
> productive.
> --
> Andrew J. Kelly SQL MVP
> "Scott H." <ScottH@.discussions.microsoft.com> wrote in message
> news:978AEB2B-21E0-48EA-B8A3-687B2A629476@.microsoft.com...
> > Folks,
> >
> > Objective: Is there enough resources on this server to create another
> > instance of SQL Server?
> >
> > SQL Server 2000 Enterprise Edition SP3
> > Windows 2003 Enterprise Edition SP1 4GB Memory
> >
> > The server currently has 1 instance.
> > SQL Server is dynamically managing memory
> > Total Server Memory: 2.606 GB
> > Available Mbytes: 380
> > Pages/Sec: .023
> > SQLSERV Working Set: 2.648 GB
> > Processor: 10%
> > Cache Hit Ratio: 99.8%
> > SQL Server Free Pages: 1,031 (1.611 MB)
> > No disk contention.
> >
> > My interpretation is this:
> >
> > This instance of SQL Server appears to be utilizing all the memory
> > available
> > to it. If I scale this instance back via max server memory so that I have
> > available memory for the second instance, Iâ?Tm thinking that the good
> > performance Iâ?Tm realizing now on this server would be jeopardized.
> >
> > The book I am referencing (SQL Server 2000 Performance Tuning â?"
> > Microsoft
> > Press) also mentions that if SQL Server: Buffer Manager/Free Pages is
> > consistently below 5MB (in my case it is) that I am either running low on
> > physical memory or allocating too much to SQL Server. This has me
> > confused.
> > How can I determine?
> >
> > Iâ?Td also like to know how SQL Server is able to use more than 2GB of
> > physical memory. Is there anything special (i.e. Boot.ini flag) that is
> > required?
> >
> > Thanks in advance.
> >
> > Scott H.
> >
>
>|||I should have investigated prior to asking. I found the boot.ini under C:\ -
it was hiddent. And yes, the 3GB/ switch is there.
--
Thanks,
Scott H.
"Scott H." wrote:
> Andrew,
> The reason for the additional instance is that there will be 2 new databases
> that will be internet facing. Currently this instance is not, and no plans on
> opening up port 1433 through the firewall.
> My recommendation is to add more memory. There is some reluctance due to
> cost. The server is an older generation HP (HP DL380 G2). I'm being told that
> the memory is not available.
> So, the /3GB switch in the boot.ini file is still applicable on Windows 2003
> EE and SQL Server 2000 EE? How can I find/view this parameter?
> Based on the performance counters, would your recommendation be to add more
> memory?
> Thanks for your help.
> --
> Thanks,
> Scott H.
>
> "Andrew J. Kelly" wrote:
> > Adding another instance will mean that you have to allocate memory between
> > the two instances and they may compete but it is hard to say without knowing
> > what the other instance needs for resources. Why not simply add the new db
> > to the existing instance? I would look at the Page Life Expectancy counter
> > to see how well the memory you have now is used. And you are obviously using
> > more than 2GB for this instance if you have 2.66GB of total memory. So you
> > must have already set the /3GB switch in the boot.ini file other wise you
> > would only be using 2GB or less. And why not add more memory? You have EE
> > for both SQL and Windows yet only have 4GB of memory. Seems counter
> > productive.
> >
> > --
> > Andrew J. Kelly SQL MVP
> >
> > "Scott H." <ScottH@.discussions.microsoft.com> wrote in message
> > news:978AEB2B-21E0-48EA-B8A3-687B2A629476@.microsoft.com...
> > > Folks,
> > >
> > > Objective: Is there enough resources on this server to create another
> > > instance of SQL Server?
> > >
> > > SQL Server 2000 Enterprise Edition SP3
> > > Windows 2003 Enterprise Edition SP1 4GB Memory
> > >
> > > The server currently has 1 instance.
> > > SQL Server is dynamically managing memory
> > > Total Server Memory: 2.606 GB
> > > Available Mbytes: 380
> > > Pages/Sec: .023
> > > SQLSERV Working Set: 2.648 GB
> > > Processor: 10%
> > > Cache Hit Ratio: 99.8%
> > > SQL Server Free Pages: 1,031 (1.611 MB)
> > > No disk contention.
> > >
> > > My interpretation is this:
> > >
> > > This instance of SQL Server appears to be utilizing all the memory
> > > available
> > > to it. If I scale this instance back via max server memory so that I have
> > > available memory for the second instance, Iâ?Tm thinking that the good
> > > performance Iâ?Tm realizing now on this server would be jeopardized.
> > >
> > > The book I am referencing (SQL Server 2000 Performance Tuning â?"
> > > Microsoft
> > > Press) also mentions that if SQL Server: Buffer Manager/Free Pages is
> > > consistently below 5MB (in my case it is) that I am either running low on
> > > physical memory or allocating too much to SQL Server. This has me
> > > confused.
> > > How can I determine?
> > >
> > > Iâ?Td also like to know how SQL Server is able to use more than 2GB of
> > > physical memory. Is there anything special (i.e. Boot.ini flag) that is
> > > required?
> > >
> > > Thanks in advance.
> > >
> > > Scott H.
> > >
> >
> >
> >|||Well like I said I would look at the Page Life Exptancy counter first and
see how high or low that is. You can't tell from a few counters like this
what is needed. We would really need a lot more info. The existing instance
is using all the available memory but that does not mean it actually needs
it. SQL Server will always use what is there if the db is large enough and
you ask for that much data over time. Having said that memory is one of the
cheapest and easiest ways to grow a SQL box.
--
Andrew J. Kelly SQL MVP
"Scott H." <ScottH@.discussions.microsoft.com> wrote in message
news:FFF35509-CC93-4E35-8AE2-C61F0A436B82@.microsoft.com...
> Andrew,
> The reason for the additional instance is that there will be 2 new
> databases
> that will be internet facing. Currently this instance is not, and no plans
> on
> opening up port 1433 through the firewall.
> My recommendation is to add more memory. There is some reluctance due to
> cost. The server is an older generation HP (HP DL380 G2). I'm being told
> that
> the memory is not available.
> So, the /3GB switch in the boot.ini file is still applicable on Windows
> 2003
> EE and SQL Server 2000 EE? How can I find/view this parameter?
> Based on the performance counters, would your recommendation be to add
> more
> memory?
> Thanks for your help.
> --
> Thanks,
> Scott H.
>
> "Andrew J. Kelly" wrote:
>> Adding another instance will mean that you have to allocate memory
>> between
>> the two instances and they may compete but it is hard to say without
>> knowing
>> what the other instance needs for resources. Why not simply add the new
>> db
>> to the existing instance? I would look at the Page Life Expectancy
>> counter
>> to see how well the memory you have now is used. And you are obviously
>> using
>> more than 2GB for this instance if you have 2.66GB of total memory. So
>> you
>> must have already set the /3GB switch in the boot.ini file other wise you
>> would only be using 2GB or less. And why not add more memory? You have EE
>> for both SQL and Windows yet only have 4GB of memory. Seems counter
>> productive.
>> --
>> Andrew J. Kelly SQL MVP
>> "Scott H." <ScottH@.discussions.microsoft.com> wrote in message
>> news:978AEB2B-21E0-48EA-B8A3-687B2A629476@.microsoft.com...
>> > Folks,
>> >
>> > Objective: Is there enough resources on this server to create another
>> > instance of SQL Server?
>> >
>> > SQL Server 2000 Enterprise Edition SP3
>> > Windows 2003 Enterprise Edition SP1 4GB Memory
>> >
>> > The server currently has 1 instance.
>> > SQL Server is dynamically managing memory
>> > Total Server Memory: 2.606 GB
>> > Available Mbytes: 380
>> > Pages/Sec: .023
>> > SQLSERV Working Set: 2.648 GB
>> > Processor: 10%
>> > Cache Hit Ratio: 99.8%
>> > SQL Server Free Pages: 1,031 (1.611 MB)
>> > No disk contention.
>> >
>> > My interpretation is this:
>> >
>> > This instance of SQL Server appears to be utilizing all the memory
>> > available
>> > to it. If I scale this instance back via max server memory so that I
>> > have
>> > available memory for the second instance, Iâ?Tm thinking that the good
>> > performance Iâ?Tm realizing now on this server would be jeopardized.
>> >
>> > The book I am referencing (SQL Server 2000 Performance Tuning â?"
>> > Microsoft
>> > Press) also mentions that if SQL Server: Buffer Manager/Free Pages is
>> > consistently below 5MB (in my case it is) that I am either running low
>> > on
>> > physical memory or allocating too much to SQL Server. This has me
>> > confused.
>> > How can I determine?
>> >
>> > Iâ?Td also like to know how SQL Server is able to use more than 2GB of
>> > physical memory. Is there anything special (i.e. Boot.ini flag) that is
>> > required?
>> >
>> > Thanks in advance.
>> >
>> > Scott H.
>> >
>>|||Thanks for your help Andrew.
Again, I'm taking my lead from SQL Server 2000 Performance Tuning book -
Microsoft Press. The counters I've included are what they suggest. I'd be
happy to read other performance tuning documents if you have any
recommendations.
I have added the suggested counter - page life expectancy. The first
interval returned 12 hours (after conversion)
I assumed SQL Server would release memory if it did not require it.
What I'm going to suggest, until the powers that be decide to spend some
money on memory, is that we run the instance with max server memory
configured to say 1.5GB for a few days. I'll compare the counters I'm
collecting now, with a few days of running with the memory scaled back.
--
Thanks,
Scott H.
"Andrew J. Kelly" wrote:
> Well like I said I would look at the Page Life Exptancy counter first and
> see how high or low that is. You can't tell from a few counters like this
> what is needed. We would really need a lot more info. The existing instance
> is using all the available memory but that does not mean it actually needs
> it. SQL Server will always use what is there if the db is large enough and
> you ask for that much data over time. Having said that memory is one of the
> cheapest and easiest ways to grow a SQL box.
> --
> Andrew J. Kelly SQL MVP
> "Scott H." <ScottH@.discussions.microsoft.com> wrote in message
> news:FFF35509-CC93-4E35-8AE2-C61F0A436B82@.microsoft.com...
> > Andrew,
> >
> > The reason for the additional instance is that there will be 2 new
> > databases
> > that will be internet facing. Currently this instance is not, and no plans
> > on
> > opening up port 1433 through the firewall.
> >
> > My recommendation is to add more memory. There is some reluctance due to
> > cost. The server is an older generation HP (HP DL380 G2). I'm being told
> > that
> > the memory is not available.
> >
> > So, the /3GB switch in the boot.ini file is still applicable on Windows
> > 2003
> > EE and SQL Server 2000 EE? How can I find/view this parameter?
> >
> > Based on the performance counters, would your recommendation be to add
> > more
> > memory?
> >
> > Thanks for your help.
> > --
> > Thanks,
> >
> > Scott H.
> >
> >
> > "Andrew J. Kelly" wrote:
> >
> >> Adding another instance will mean that you have to allocate memory
> >> between
> >> the two instances and they may compete but it is hard to say without
> >> knowing
> >> what the other instance needs for resources. Why not simply add the new
> >> db
> >> to the existing instance? I would look at the Page Life Expectancy
> >> counter
> >> to see how well the memory you have now is used. And you are obviously
> >> using
> >> more than 2GB for this instance if you have 2.66GB of total memory. So
> >> you
> >> must have already set the /3GB switch in the boot.ini file other wise you
> >> would only be using 2GB or less. And why not add more memory? You have EE
> >> for both SQL and Windows yet only have 4GB of memory. Seems counter
> >> productive.
> >>
> >> --
> >> Andrew J. Kelly SQL MVP
> >>
> >> "Scott H." <ScottH@.discussions.microsoft.com> wrote in message
> >> news:978AEB2B-21E0-48EA-B8A3-687B2A629476@.microsoft.com...
> >> > Folks,
> >> >
> >> > Objective: Is there enough resources on this server to create another
> >> > instance of SQL Server?
> >> >
> >> > SQL Server 2000 Enterprise Edition SP3
> >> > Windows 2003 Enterprise Edition SP1 4GB Memory
> >> >
> >> > The server currently has 1 instance.
> >> > SQL Server is dynamically managing memory
> >> > Total Server Memory: 2.606 GB
> >> > Available Mbytes: 380
> >> > Pages/Sec: .023
> >> > SQLSERV Working Set: 2.648 GB
> >> > Processor: 10%
> >> > Cache Hit Ratio: 99.8%
> >> > SQL Server Free Pages: 1,031 (1.611 MB)
> >> > No disk contention.
> >> >
> >> > My interpretation is this:
> >> >
> >> > This instance of SQL Server appears to be utilizing all the memory
> >> > available
> >> > to it. If I scale this instance back via max server memory so that I
> >> > have
> >> > available memory for the second instance, I�¢?Tm thinking that the good
> >> > performance I�¢?Tm realizing now on this server would be jeopardized.
> >> >
> >> > The book I am referencing (SQL Server 2000 Performance Tuning �¢?"
> >> > Microsoft
> >> > Press) also mentions that if SQL Server: Buffer Manager/Free Pages is
> >> > consistently below 5MB (in my case it is) that I am either running low
> >> > on
> >> > physical memory or allocating too much to SQL Server. This has me
> >> > confused.
> >> > How can I determine?
> >> >
> >> > I�¢?Td also like to know how SQL Server is able to use more than 2GB of
> >> > physical memory. Is there anything special (i.e. Boot.ini flag) that is
> >> > required?
> >> >
> >> > Thanks in advance.
> >> >
> >> > Scott H.
> >> >
> >>
> >>
> >>
>
>|||SQL Server never gives back memory unless the OS specifically asks for it.
So if there is nothing else requiring the memory SQL Server will hold onto
it. IF your PLE counter reads 12 hours chances are you have more memory than
you can get away with. A PLE reading of around 300 seconds or more is
usually enough for decent performance. Over 1000 is considered really good.
I think your idea of dropping the MAX memory and see how it performs is a
good idea. Keep in mind that the Max memory setting is for the buffer pool
only and that on a 32 bit machine SQL Server may use up to ~384MB of
additional memory for the MemToLeave area.
--
Andrew J. Kelly SQL MVP
"Scott H." <ScottH@.discussions.microsoft.com> wrote in message
news:19D9D73E-0A5C-441D-BFDF-24DE162FD36E@.microsoft.com...
> Thanks for your help Andrew.
> Again, I'm taking my lead from SQL Server 2000 Performance Tuning book -
> Microsoft Press. The counters I've included are what they suggest. I'd be
> happy to read other performance tuning documents if you have any
> recommendations.
> I have added the suggested counter - page life expectancy. The first
> interval returned 12 hours (after conversion)
> I assumed SQL Server would release memory if it did not require it.
> What I'm going to suggest, until the powers that be decide to spend some
> money on memory, is that we run the instance with max server memory
> configured to say 1.5GB for a few days. I'll compare the counters I'm
> collecting now, with a few days of running with the memory scaled back.
> --
> Thanks,
> Scott H.
>
> "Andrew J. Kelly" wrote:
>> Well like I said I would look at the Page Life Exptancy counter first and
>> see how high or low that is. You can't tell from a few counters like
>> this
>> what is needed. We would really need a lot more info. The existing
>> instance
>> is using all the available memory but that does not mean it actually
>> needs
>> it. SQL Server will always use what is there if the db is large enough
>> and
>> you ask for that much data over time. Having said that memory is one of
>> the
>> cheapest and easiest ways to grow a SQL box.
>> --
>> Andrew J. Kelly SQL MVP
>> "Scott H." <ScottH@.discussions.microsoft.com> wrote in message
>> news:FFF35509-CC93-4E35-8AE2-C61F0A436B82@.microsoft.com...
>> > Andrew,
>> >
>> > The reason for the additional instance is that there will be 2 new
>> > databases
>> > that will be internet facing. Currently this instance is not, and no
>> > plans
>> > on
>> > opening up port 1433 through the firewall.
>> >
>> > My recommendation is to add more memory. There is some reluctance due
>> > to
>> > cost. The server is an older generation HP (HP DL380 G2). I'm being
>> > told
>> > that
>> > the memory is not available.
>> >
>> > So, the /3GB switch in the boot.ini file is still applicable on Windows
>> > 2003
>> > EE and SQL Server 2000 EE? How can I find/view this parameter?
>> >
>> > Based on the performance counters, would your recommendation be to add
>> > more
>> > memory?
>> >
>> > Thanks for your help.
>> > --
>> > Thanks,
>> >
>> > Scott H.
>> >
>> >
>> > "Andrew J. Kelly" wrote:
>> >
>> >> Adding another instance will mean that you have to allocate memory
>> >> between
>> >> the two instances and they may compete but it is hard to say without
>> >> knowing
>> >> what the other instance needs for resources. Why not simply add the
>> >> new
>> >> db
>> >> to the existing instance? I would look at the Page Life Expectancy
>> >> counter
>> >> to see how well the memory you have now is used. And you are obviously
>> >> using
>> >> more than 2GB for this instance if you have 2.66GB of total memory. So
>> >> you
>> >> must have already set the /3GB switch in the boot.ini file other wise
>> >> you
>> >> would only be using 2GB or less. And why not add more memory? You have
>> >> EE
>> >> for both SQL and Windows yet only have 4GB of memory. Seems counter
>> >> productive.
>> >>
>> >> --
>> >> Andrew J. Kelly SQL MVP
>> >>
>> >> "Scott H." <ScottH@.discussions.microsoft.com> wrote in message
>> >> news:978AEB2B-21E0-48EA-B8A3-687B2A629476@.microsoft.com...
>> >> > Folks,
>> >> >
>> >> > Objective: Is there enough resources on this server to create
>> >> > another
>> >> > instance of SQL Server?
>> >> >
>> >> > SQL Server 2000 Enterprise Edition SP3
>> >> > Windows 2003 Enterprise Edition SP1 4GB Memory
>> >> >
>> >> > The server currently has 1 instance.
>> >> > SQL Server is dynamically managing memory
>> >> > Total Server Memory: 2.606 GB
>> >> > Available Mbytes: 380
>> >> > Pages/Sec: .023
>> >> > SQLSERV Working Set: 2.648 GB
>> >> > Processor: 10%
>> >> > Cache Hit Ratio: 99.8%
>> >> > SQL Server Free Pages: 1,031 (1.611 MB)
>> >> > No disk contention.
>> >> >
>> >> > My interpretation is this:
>> >> >
>> >> > This instance of SQL Server appears to be utilizing all the memory
>> >> > available
>> >> > to it. If I scale this instance back via max server memory so that I
>> >> > have
>> >> > available memory for the second instance, IÃf¢?Tm thinking that the
>> >> > good
>> >> > performance IÃf¢?Tm realizing now on this server would be
>> >> > jeopardized.
>> >> >
>> >> > The book I am referencing (SQL Server 2000 Performance Tuning Ãf¢?"
>> >> > Microsoft
>> >> > Press) also mentions that if SQL Server: Buffer Manager/Free Pages
>> >> > is
>> >> > consistently below 5MB (in my case it is) that I am either running
>> >> > low
>> >> > on
>> >> > physical memory or allocating too much to SQL Server. This has me
>> >> > confused.
>> >> > How can I determine?
>> >> >
>> >> > IÃf¢?Td also like to know how SQL Server is able to use more than
>> >> > 2GB of
>> >> > physical memory. Is there anything special (i.e. Boot.ini flag) that
>> >> > is
>> >> > required?
>> >> >
>> >> > Thanks in advance.
>> >> >
>> >> > Scott H.
>> >> >
>> >>
>> >>
>> >>
>>