Buffer Cache Hit Ratio

Please advice.
I ran an OLTP Database on a SQL 2K on WIN 2K Advaced
Server in an Active/Passive Cluster configuration. I have
noticed the buffer cache hit ratio dropping down to about
1% sometimes below 1%. Waht can I do to ascertain the
Server needs more memory.
(i.e What memory is available to SQL ,what memory is SQL
requesting and what memory is given.)
I have also noticed the server running out of locks.
Please help urgent.Look at the SQL Server:Memory Manager:Target Server Memory(KB) counter in
Performance Monitor. That is the amount of memory SQL Server would like to
have. Also look at the SQL Server:Memory Manager:Target Server Memory(KB)
counter, which is the total memory SQL Server actually uses.
You can check in Enterprise Manager in the server properties if there are
any limits imposed on the amount of memeory SQL Server can use.
--
Jacco Schalkwijk
SQL Server MVP
"Olutimi" <anonymous@.discussions.microsoft.com> wrote in message
news:07f901c3c47e$e30f25d0$a401280a@.phx.gbl...
> Please advice.
> I ran an OLTP Database on a SQL 2K on WIN 2K Advaced
> Server in an Active/Passive Cluster configuration. I have
> noticed the buffer cache hit ratio dropping down to about
> 1% sometimes below 1%. Waht can I do to ascertain the
> Server needs more memory.
> (i.e What memory is available to SQL ,what memory is SQL
> requesting and what memory is given.)
> I have also noticed the server running out of locks.
>
> Please help urgent.

Breakdown database by percent

I am in the Casino industry and would like to break down our database by
percent of win. For example:
lets say we have 100,000 rated players. I want to find the win the top 1%
of players contribute followed by
2%
3%
4%
.
.
100%
My fields are Player_ID, Win.
I need help writing a procdure to group/calculate this for me. The database
grows automatically so the procudre needs to automatically adjust based on
the growth.
ThanksI don't think I understand your question fully but on it's surface
it sounds like you can benefit from the TOP keyword in your
SELECT.
SELECT TOP n [PERCENT]
See BOL for more details
<brian.shannon@.diamondjo.com> wrote in message
news:OaRnZCgFFHA.2156@.TK2MSFTNGP09.phx.gbl...
> I am in the Casino industry and would like to break down our database by
> percent of win. For example:
> lets say we have 100,000 rated players. I want to find the win the top 1%
> of players contribute followed by
> 2%
> 3%
> 4%
> .
> .
> 100%
> My fields are Player_ID, Win.
> I need help writing a procdure to group/calculate this for me. The
database
> grows automatically so the procudre needs to automatically adjust based on
> the growth.
> Thanks
>|||>> I need help writing a procdure to group/calculate this for me.
Sure, it is easy, but many here won't have the inclination to create sample
tables, insert sample data & write up a query for you when you did not
bother to post required information for others to repro the problem. Read
www.aspfaq.com/5006 & provide table structures, sample data & expected
results so that others can suggest a tested solution.
Anith

Box name and default SQL Server name

I had created SQL 2000 on Win 2000 with the default box
name. What happens to SQL 2000's default name if the
box's name gets changed in future ? Will it also take up
the new name of the box?
ThanksHi,
No, You have drop and create the server name.
Look into sp_dropserver ,sp_addserver in Bol.
Thanks
Hari
MCDBA
"kanthi" <anonymous@.discussions.microsoft.com> wrote in message
news:023701c3b103$22e83f50$a001280a@.phx.gbl...
> I had created SQL 2000 on Win 2000 with the default box
> name. What happens to SQL 2000's default name if the
> box's name gets changed in future ? Will it also take up
> the new name of the box?
> Thanks|||I had a problem because of this and got help off some forum It caused me
problems with an existing replication subscription here is what had to do
to fix it.
In QA use SELECT @.@.ServerName
This will show you the stored SQL servername (It will be your old one)
Then use sp_dropserver 'YourOldServerName', 'droplogins'
Stop and re-start SQL Server
Then do SELECT @.@.ServerNAme again in QA
This should return a null
Then use sp_addserver 'NewServerName', 'local'
Make sure the local parameter is there !
Stop and re-start SQL Server again
Then use SELECT @.@.ServerName again in QA
This should now be your new server name
Hope this helps
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:ODplQtQsDHA.572@.TK2MSFTNGP11.phx.gbl...
> Hi,
> No, You have drop and create the server name.
> Look into sp_dropserver ,sp_addserver in Bol.
> Thanks
> Hari
> MCDBA
>
> "kanthi" <anonymous@.discussions.microsoft.com> wrote in message
> news:023701c3b103$22e83f50$a001280a@.phx.gbl...
> > I had created SQL 2000 on Win 2000 with the default box
> > name. What happens to SQL 2000's default name if the
> > box's name gets changed in future ? Will it also take up
> > the new name of the box?
> >
> > Thanks
>

boot.ini /3GB problem

Hello,
I have win 2k3 standard edition, 4 GB of memory, SQL 2000 enterprise
edition. I have tryed to get more than 2 GB for sql. I have tryed /3GB swich
with awe enabled on sql 2000 without any good result. I could said even
situation is wors because now sql allocate maximum 1,327 GB.
Does anyone hnow what can be wrong '
I have read many of support pages from MS for allocate more than 2G and
nothing is working :(
regards
Pawel MylkaHi
Task manager is not the correct tool to evaluate if memory over 2 GB is
being used as it is not capable of it.
Use Windows Performance Monitor (%SystemRoot%\system32\perfmon.msc /s) to
see how much "Target Server Memory" is. It should be close to 2700 when SQL
Server is configured correctly.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Pawel Mylka" <pawel.mylka@.itcontrol.pl> wrote in message
news:eEnOZij4FHA.252@.TK2MSFTNGP15.phx.gbl...
> Hello,
> I have win 2k3 standard edition, 4 GB of memory, SQL 2000 enterprise
> edition. I have tryed to get more than 2 GB for sql. I have tryed /3GB
> swich
> with awe enabled on sql 2000 without any good result. I could said even
> situation is wors because now sql allocate maximum 1,327 GB.
> Does anyone hnow what can be wrong '
> I have read many of support pages from MS for allocate more than 2G and
> nothing is working :(
> regards
> Pawel Mylka
>|||Hi,
As Mike pointed out use Performance monitor to view the memory usage. As
well as make the memory static (set min and max meory)
once you enable AWE to SQL Server.
Thanks
Hari
SQL Server MVP
"Pawel Mylka" <pawel.mylka@.itcontrol.pl> wrote in message
news:eEnOZij4FHA.252@.TK2MSFTNGP15.phx.gbl...
> Hello,
> I have win 2k3 standard edition, 4 GB of memory, SQL 2000 enterprise
> edition. I have tryed to get more than 2 GB for sql. I have tryed /3GB
> swich
> with awe enabled on sql 2000 without any good result. I could said even
> situation is wors because now sql allocate maximum 1,327 GB.
> Does anyone hnow what can be wrong '
> I have read many of support pages from MS for allocate more than 2G and
> nothing is working :(
> regards
> Pawel Mylka
>|||Don't enable AWE unless you intend to go beyond 3GB.
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:OoI%23Nzt4FHA.3876@.TK2MSFTNGP09.phx.gbl...
> Hi,
> As Mike pointed out use Performance monitor to view the memory usage. As
> well as make the memory static (set min and max meory)
> once you enable AWE to SQL Server.
> Thanks
> Hari
> SQL Server MVP
> "Pawel Mylka" <pawel.mylka@.itcontrol.pl> wrote in message
> news:eEnOZij4FHA.252@.TK2MSFTNGP15.phx.gbl...
>> Hello,
>> I have win 2k3 standard edition, 4 GB of memory, SQL 2000 enterprise
>> edition. I have tryed to get more than 2 GB for sql. I have tryed /3GB
>> swich
>> with awe enabled on sql 2000 without any good result. I could said even
>> situation is wors because now sql allocate maximum 1,327 GB.
>> Does anyone hnow what can be wrong '
>> I have read many of support pages from MS for allocate more than 2G and
>> nothing is working :(
>> regards
>> Pawel Mylka
>>
>

boot.ini /3GB problem

Hello,
I have win 2k3 standard edition, 4 GB of memory, SQL 2000 enterprise
edition. I have tryed to get more than 2 GB for sql. I have tryed /3GB swich
with awe enabled on sql 2000 without any good result. I could said even
situation is wors because now sql allocate maximum 1,327 GB.
Does anyone hnow what can be wrong ?
I have read many of support pages from MS for allocate more than 2G and
nothing is working
regards
Pawel Mylka
Hi
Task manager is not the correct tool to evaluate if memory over 2 GB is
being used as it is not capable of it.
Use Windows Performance Monitor (%SystemRoot%\system32\perfmon.msc /s) to
see how much "Target Server Memory" is. It should be close to 2700 when SQL
Server is configured correctly.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Pawel Mylka" <pawel.mylka@.itcontrol.pl> wrote in message
news:eEnOZij4FHA.252@.TK2MSFTNGP15.phx.gbl...
> Hello,
> I have win 2k3 standard edition, 4 GB of memory, SQL 2000 enterprise
> edition. I have tryed to get more than 2 GB for sql. I have tryed /3GB
> swich
> with awe enabled on sql 2000 without any good result. I could said even
> situation is wors because now sql allocate maximum 1,327 GB.
> Does anyone hnow what can be wrong ?
> I have read many of support pages from MS for allocate more than 2G and
> nothing is working
> regards
> Pawel Mylka
>
|||Hi,
As Mike pointed out use Performance monitor to view the memory usage. As
well as make the memory static (set min and max meory)
once you enable AWE to SQL Server.
Thanks
Hari
SQL Server MVP
"Pawel Mylka" <pawel.mylka@.itcontrol.pl> wrote in message
news:eEnOZij4FHA.252@.TK2MSFTNGP15.phx.gbl...
> Hello,
> I have win 2k3 standard edition, 4 GB of memory, SQL 2000 enterprise
> edition. I have tryed to get more than 2 GB for sql. I have tryed /3GB
> swich
> with awe enabled on sql 2000 without any good result. I could said even
> situation is wors because now sql allocate maximum 1,327 GB.
> Does anyone hnow what can be wrong ?
> I have read many of support pages from MS for allocate more than 2G and
> nothing is working
> regards
> Pawel Mylka
>
|||Don't enable AWE unless you intend to go beyond 3GB.
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:OoI%23Nzt4FHA.3876@.TK2MSFTNGP09.phx.gbl...
> Hi,
> As Mike pointed out use Performance monitor to view the memory usage. As
> well as make the memory static (set min and max meory)
> once you enable AWE to SQL Server.
> Thanks
> Hari
> SQL Server MVP
> "Pawel Mylka" <pawel.mylka@.itcontrol.pl> wrote in message
> news:eEnOZij4FHA.252@.TK2MSFTNGP15.phx.gbl...
>

boot.ini /3GB problem

Hello,
I have win 2k3 standard edition, 4 GB of memory, SQL 2000 enterprise
edition. I have tryed to get more than 2 GB for sql. I have tryed /3GB swich
with awe enabled on sql 2000 without any good result. I could said even
situation is wors because now sql allocate maximum 1,327 GB.
Does anyone hnow what can be wrong '
I have read many of support pages from MS for allocate more than 2G and
nothing is working
regards
Pawel MylkaHi
Task manager is not the correct tool to evaluate if memory over 2 GB is
being used as it is not capable of it.
Use Windows Performance Monitor (%SystemRoot%\system32\perfmon.msc /s) to
see how much "Target Server Memory" is. It should be close to 2700 when SQL
Server is configured correctly.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Pawel Mylka" <pawel.mylka@.itcontrol.pl> wrote in message
news:eEnOZij4FHA.252@.TK2MSFTNGP15.phx.gbl...
> Hello,
> I have win 2k3 standard edition, 4 GB of memory, SQL 2000 enterprise
> edition. I have tryed to get more than 2 GB for sql. I have tryed /3GB
> swich
> with awe enabled on sql 2000 without any good result. I could said even
> situation is wors because now sql allocate maximum 1,327 GB.
> Does anyone hnow what can be wrong '
> I have read many of support pages from MS for allocate more than 2G and
> nothing is working
> regards
> Pawel Mylka
>|||Hi,
As Mike pointed out use Performance monitor to view the memory usage. As
well as make the memory static (set min and max meory)
once you enable AWE to SQL Server.
Thanks
Hari
SQL Server MVP
"Pawel Mylka" <pawel.mylka@.itcontrol.pl> wrote in message
news:eEnOZij4FHA.252@.TK2MSFTNGP15.phx.gbl...
> Hello,
> I have win 2k3 standard edition, 4 GB of memory, SQL 2000 enterprise
> edition. I have tryed to get more than 2 GB for sql. I have tryed /3GB
> swich
> with awe enabled on sql 2000 without any good result. I could said even
> situation is wors because now sql allocate maximum 1,327 GB.
> Does anyone hnow what can be wrong '
> I have read many of support pages from MS for allocate more than 2G and
> nothing is working
> regards
> Pawel Mylka
>|||Don't enable AWE unless you intend to go beyond 3GB.
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:OoI%23Nzt4FHA.3876@.TK2MSFTNGP09.phx.gbl...
> Hi,
> As Mike pointed out use Performance monitor to view the memory usage. As
> well as make the memory static (set min and max meory)
> once you enable AWE to SQL Server.
> Thanks
> Hari
> SQL Server MVP
> "Pawel Mylka" <pawel.mylka@.itcontrol.pl> wrote in message
> news:eEnOZij4FHA.252@.TK2MSFTNGP15.phx.gbl...
>

Boosting SQL Server Priority on Windows

If I dedicate a Win 2003 box to only SQL Server, does it make sense to boost
sql server priority on the box?
--
JohnIf it is the only thing on the box then you shouldn't need it. I have seen
this setting hurt more than help in most cases. I would leave it at the
default unless you know for sure you need it.
--
Andrew J. Kelly SQL MVP
"JT" <Jthayer@.online.nospam> wrote in message
news:1FDFA76C-D8C8-4C48-BDD3-C8165DC0752D@.microsoft.com...
> If I dedicate a Win 2003 box to only SQL Server, does it make sense to
> boost
> sql server priority on the box?
> --
> John

Boosting SQL Server Priority on Windows

If I dedicate a Win 2003 box to only SQL Server, does it make sense to boost
sql server priority on the box?
John
If it is the only thing on the box then you shouldn't need it. I have seen
this setting hurt more than help in most cases. I would leave it at the
default unless you know for sure you need it.
Andrew J. Kelly SQL MVP
"JT" <Jthayer@.online.nospam> wrote in message
news:1FDFA76C-D8C8-4C48-BDD3-C8165DC0752D@.microsoft.com...
> If I dedicate a Win 2003 box to only SQL Server, does it make sense to
> boost
> sql server priority on the box?
> --
> John

Boosting SQL Server Priority on Windows

If I dedicate a Win 2003 box to only SQL Server, does it make sense to boost
sql server priority on the box?
--
JohnIf it is the only thing on the box then you shouldn't need it. I have seen
this setting hurt more than help in most cases. I would leave it at the
default unless you know for sure you need it.
Andrew J. Kelly SQL MVP
"JT" <Jthayer@.online.nospam> wrote in message
news:1FDFA76C-D8C8-4C48-BDD3-C8165DC0752D@.microsoft.com...
> If I dedicate a Win 2003 box to only SQL Server, does it make sense to
> boost
> sql server priority on the box?
> --
> John