Log Cache Hit Ratio + Buffer Cache Hit Ratio

Thanks in advance for any info.
SQL Server Standard/ Windows 2000 AS, Dual Proc (1.4's I think), 1.5GB RAM.
RAID 5.
What does one do when the Buffer Cache is at (and stays at) 99% and the Log
Cache is at 99%? Faster processors, more memory, change the RAID config?
Note: this is not daily activity on the box, but a monthly process that has
gone from 18 hours to 28 hours {and counting} with no changes.
Thanks,
MorganIf you're talking about the hit ratios, those are both
excellent numbers. Anything over 95% is optimal. When
those numbers start to drop, in general more memory is
the key, but be sure to check into it more closely before
purchasing hardware.
>--Original Message--
>Thanks in advance for any info.
>SQL Server Standard/ Windows 2000 AS, Dual Proc (1.4's I
think), 1.5GB RAM.
>RAID 5.
>What does one do when the Buffer Cache is at (and stays
at) 99% and the Log
>Cache is at 99%? Faster processors, more memory, change
the RAID config?
>Note: this is not daily activity on the box, but a
monthly process that has
>gone from 18 hours to 28 hours {and counting} with no
changes.
>
>Thanks,
>Morgan
>
>.
>|||Morgan,
> What does one do when the Buffer Cache is at (and stays at) 99% and the
Log
> Cache is at 99%? Faster processors, more memory, change the RAID config?
> Note: this is not daily activity on the box, but a monthly process that
has
> gone from 18 hours to 28 hours {and counting} with no changes.
99% cache hit ratios are optimal.
This means that the server is practically always getting data from the
caches, rather than going to the disk subsystem.
It sounds more like the work needs to be done in the monthly process, as far
as optimizing it goes. Your hardware seems to be performing optimally.
When did it go from 18 to 28 hours? Suddenly? Over time? I would start
suspecting one of the following:
That perhaps either:
A.) The data set has grown
B.) Someone changed one of the queries
C.) Someone dropped a key index
D.) One or more of the above. :-)
James Hokes|||In addition to the other comments the log can be a bottle neck if on a Raid
5 and especially with the data. The Log cache ration doesn't mean much for
writes so you may want to check your disk queues on the Raid that houses the
log file anyway and make sure it has no issues. TempDB can be another place
to look. Usually month end type process does a lot of activity that uses
temp tables and you can have bottlenecks there as well. Essentially you
need to monitor the cpu and disk counters while this process is happening to
see where the bottleneck is coming from. If the data cache ratio is 99%
chances are more memory won't help much. If your procedures are optimized
then it will boil down to disk or cpu. But how sure are you that they are
that optimized?
--
Andrew J. Kelly
SQL Server MVP
"Morgan" <mfears@.spamcop.net> wrote in message
news:OJWUJA%23yDHA.2540@.tk2msftngp13.phx.gbl...
> Thanks in advance for any info.
> SQL Server Standard/ Windows 2000 AS, Dual Proc (1.4's I think), 1.5GB
RAM.
> RAID 5.
> What does one do when the Buffer Cache is at (and stays at) 99% and the
Log
> Cache is at 99%? Faster processors, more memory, change the RAID config?
> Note: this is not daily activity on the box, but a monthly process that
has
> gone from 18 hours to 28 hours {and counting} with no changes.
>
> Thanks,
> Morgan
>|||On Fri, 26 Dec 2003 13:42:08 -0500, "Morgan" wrote:
>SQL Server Standard/ Windows 2000 AS, Dual Proc (1.4's I think), 1.5GB RAM.
>RAID 5.
>What does one do when the Buffer Cache is at (and stays at) 99% and the Log
>Cache is at 99%? Faster processors, more memory, change the RAID config?
>Note: this is not daily activity on the box, but a monthly process that has
>gone from 18 hours to 28 hours {and counting} with no changes.
In addition to what James, James and Andrew said, look at your
statistics. As your database grows over time, the distribution of values
across columns can change substantially. Out of date stats can
contribute to bad query plans, leading to long runs.
Are you able to pinpoint which part(s) of your monthly process chews the
most time?
cheers,
Ross
--
Ross McKay, WebAware Pty Ltd
"The lawn could stand another mowing; funny, I don't even care"
- Elvis Costello|||Thanks to everyone for their insight.
I was somewhat under the impression that if they're both max'd out, then
it's time to start looking at HW. I know for a fact the procedures need
"help", but it's an inherited process that for the first time this month,
took much longer than usual, which is why I knee-jerked my post. To the best
of my knowledge, none of the underlying objects has changed, but upon
further review (below), several tables are missing indicies where they are
required. I also saw quite a bit of parallelism happening via SP_WHO, and
have begun to question the multi-proc machine, which for better or worse, is
most likely fine.
I ran Profiler off-and-on (didn't want to make it any worse than it was),
and found a single procedure that appeared to be somewhat of a bottleneck,
lots of extensive reads for a single query, minimal (single, actually)
writes. Fortunately, a predecessor had the forethought to not use temp
tables, however, the tables in use are not properly indexed. Upon review of
the primary table in the afore mentioned procedure, it turns out there are
no indexes or unique constraints; along with a truncate table statement
against the table at the start of the procedure. I know the cost of a
properly placed index will fix the amount of reads against the suspect table
I saw in Profiler. Top all this off with cursors galore (note: not the
author, but the maintainer), and you can easily appreciate the mess I'm
dealing with. I'm reading this as the beginning of the end for the current
processes... ;)
Thanks again,
Morgan
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:uOYObWMzDHA.2408@.tk2msftngp13.phx.gbl...
> In addition to the other comments the log can be a bottle neck if on a
Raid
> 5 and especially with the data. The Log cache ration doesn't mean much for
> writes so you may want to check your disk queues on the Raid that houses
the
> log file anyway and make sure it has no issues. TempDB can be another
place
> to look. Usually month end type process does a lot of activity that uses
> temp tables and you can have bottlenecks there as well. Essentially you
> need to monitor the cpu and disk counters while this process is happening
to
> see where the bottleneck is coming from. If the data cache ratio is 99%
> chances are more memory won't help much. If your procedures are optimized
> then it will boil down to disk or cpu. But how sure are you that they are
> that optimized?
> --
> Andrew J. Kelly
> SQL Server MVP
>
> "Morgan" <mfears@.spamcop.net> wrote in message
> news:OJWUJA%23yDHA.2540@.tk2msftngp13.phx.gbl...
> > Thanks in advance for any info.
> >
> > SQL Server Standard/ Windows 2000 AS, Dual Proc (1.4's I think), 1.5GB
> RAM.
> > RAID 5.
> >
> > What does one do when the Buffer Cache is at (and stays at) 99% and the
> Log
> > Cache is at 99%? Faster processors, more memory, change the RAID config?
> > Note: this is not daily activity on the box, but a monthly process that
> has
> > gone from 18 hours to 28 hours {and counting} with no changes.
> >
> >
> >
> > Thanks,
> > Morgan
> >
> >
>

Locks in tempdb

I am runninga stored procedure on a test box (1GB CPU and 500MB RAM) and it
takes about 20 minutes. The locks max out at about 2400 (sp_lock). I run it
on a production box and it is taking hours and in some cases not even coming
close to finishing. The locks on the production box keep going up. The last
time I issued a kill command there were 20000 locks for this bad spid.
The test box does not have anything running on it besides anti virus
software. The production box does have some very low intensive stuff running
on it.
- The procedure in question is using dynamic sql
- I set SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
Does anyone have any obvious rabbit holes I could go down?
Thanks,
Mike
are u using some syntax such as
select xxx into #abc from xyz ?
that will definitely create lots of lock in tempdb.
If so, try avoid it.
Kan
"Mike S" <sagaeta@.yahoo.com> wrote in message
news:edvn8ILKEHA.1156@.TK2MSFTNGP09.phx.gbl...
> I am runninga stored procedure on a test box (1GB CPU and 500MB RAM) and
it
> takes about 20 minutes. The locks max out at about 2400 (sp_lock). I run
it
> on a production box and it is taking hours and in some cases not even
coming
> close to finishing. The locks on the production box keep going up. The
last
> time I issued a kill command there were 20000 locks for this bad spid.
> The test box does not have anything running on it besides anti virus
> software. The production box does have some very low intensive stuff
running
> on it.
> - The procedure in question is using dynamic sql
> - I set SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
> Does anyone have any obvious rabbit holes I could go down?
> Thanks,
> Mike
>
|||Could you post the proc in question? It could be any number of things.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
"Mike S" <sagaeta@.yahoo.com> wrote in message
news:edvn8ILKEHA.1156@.TK2MSFTNGP09.phx.gbl...
> I am runninga stored procedure on a test box (1GB CPU and 500MB RAM) and
it
> takes about 20 minutes. The locks max out at about 2400 (sp_lock). I run
it
> on a production box and it is taking hours and in some cases not even
coming
> close to finishing. The locks on the production box keep going up. The
last
> time I issued a kill command there were 20000 locks for this bad spid.
> The test box does not have anything running on it besides anti virus
> software. The production box does have some very low intensive stuff
running
> on it.
> - The procedure in question is using dynamic sql
> - I set SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
> Does anyone have any obvious rabbit holes I could go down?
> Thanks,
> Mike
>

Locks in tempdb

I am runninga stored procedure on a test box (1GB CPU and 500MB RAM) and it
takes about 20 minutes. The locks max out at about 2400 (sp_lock). I run it
on a production box and it is taking hours and in some cases not even coming
close to finishing. The locks on the production box keep going up. The last
time I issued a kill command there were 20000 locks for this bad spid.
The test box does not have anything running on it besides anti virus
software. The production box does have some very low intensive stuff running
on it.
- The procedure in question is using dynamic sql
- I set SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
Does anyone have any obvious rabbit holes I could go down?
Thanks,
Mikeare u using some syntax such as
select xxx into #abc from xyz '
that will definitely create lots of lock in tempdb.
If so, try avoid it.
Kan
"Mike S" <sagaeta@.yahoo.com> wrote in message
news:edvn8ILKEHA.1156@.TK2MSFTNGP09.phx.gbl...
> I am runninga stored procedure on a test box (1GB CPU and 500MB RAM) and
it
> takes about 20 minutes. The locks max out at about 2400 (sp_lock). I run
it
> on a production box and it is taking hours and in some cases not even
coming
> close to finishing. The locks on the production box keep going up. The
last
> time I issued a kill command there were 20000 locks for this bad spid.
> The test box does not have anything running on it besides anti virus
> software. The production box does have some very low intensive stuff
running
> on it.
> - The procedure in question is using dynamic sql
> - I set SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
> Does anyone have any obvious rabbit holes I could go down?
> Thanks,
> Mike
>|||Could you post the proc in question? It could be any number of things.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
"Mike S" <sagaeta@.yahoo.com> wrote in message
news:edvn8ILKEHA.1156@.TK2MSFTNGP09.phx.gbl...
> I am runninga stored procedure on a test box (1GB CPU and 500MB RAM) and
it
> takes about 20 minutes. The locks max out at about 2400 (sp_lock). I run
it
> on a production box and it is taking hours and in some cases not even
coming
> close to finishing. The locks on the production box keep going up. The
last
> time I issued a kill command there were 20000 locks for this bad spid.
> The test box does not have anything running on it besides anti virus
> software. The production box does have some very low intensive stuff
running
> on it.
> - The procedure in question is using dynamic sql
> - I set SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
> Does anyone have any obvious rabbit holes I could go down?
> Thanks,
> Mike
>

Locks in tempdb

I am runninga stored procedure on a test box (1GB CPU and 500MB RAM) and it
takes about 20 minutes. The locks max out at about 2400 (sp_lock). I run it
on a production box and it is taking hours and in some cases not even coming
close to finishing. The locks on the production box keep going up. The last
time I issued a kill command there were 20000 locks for this bad spid.
The test box does not have anything running on it besides anti virus
software. The production box does have some very low intensive stuff running
on it.
- The procedure in question is using dynamic sql
- I set SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
Does anyone have any obvious rabbit holes I could go down?
Thanks,
Mikeare u using some syntax such as
select xxx into #abc from xyz '
that will definitely create lots of lock in tempdb.
If so, try avoid it.
Kan
"Mike S" <sagaeta@.yahoo.com> wrote in message
news:edvn8ILKEHA.1156@.TK2MSFTNGP09.phx.gbl...
> I am runninga stored procedure on a test box (1GB CPU and 500MB RAM) and
it
> takes about 20 minutes. The locks max out at about 2400 (sp_lock). I run
it
> on a production box and it is taking hours and in some cases not even
coming
> close to finishing. The locks on the production box keep going up. The
last
> time I issued a kill command there were 20000 locks for this bad spid.
> The test box does not have anything running on it besides anti virus
> software. The production box does have some very low intensive stuff
running
> on it.
> - The procedure in question is using dynamic sql
> - I set SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
> Does anyone have any obvious rabbit holes I could go down?
> Thanks,
> Mike
>|||Could you post the proc in question? It could be any number of things.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
"Mike S" <sagaeta@.yahoo.com> wrote in message
news:edvn8ILKEHA.1156@.TK2MSFTNGP09.phx.gbl...
> I am runninga stored procedure on a test box (1GB CPU and 500MB RAM) and
it
> takes about 20 minutes. The locks max out at about 2400 (sp_lock). I run
it
> on a production box and it is taking hours and in some cases not even
coming
> close to finishing. The locks on the production box keep going up. The
last
> time I issued a kill command there were 20000 locks for this bad spid.
> The test box does not have anything running on it besides anti virus
> software. The production box does have some very low intensive stuff
running
> on it.
> - The procedure in question is using dynamic sql
> - I set SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
> Does anyone have any obvious rabbit holes I could go down?
> Thanks,
> Mike
>

Locks and Blocking

I am working on solving performance problems for a client experiencing
frequent blocks on their server which has 4 CPUs and 4GB or RAM. Their
application is a self-developed VB6 application that is very resource
intensive. With about 150 simultaneous users CPU utilization frequently
exceeds 90%. Yesterday their server froze at 100% CPU utilization, requiring
all users to exit and the server to be rebooted.
Their programming model uses ADO recordsets. Once a recordset is fetched to
the application and displayed on the form, the rsX variable is set to nothing
and the user can update the form. When the OK button is pressed on the form,
the application code does the following:
strSQL = "Select * From Contact Where 1=2"
Set rsX = Openrecordset(strSQL)
Rsx.Field1 = Form!Field1
...
...
Rsx.Fieldn = Form!Fieldn
Rsx.Update
Question: Does the grabbing of a recordset in this manner lock the whole
table since there is no individual record or page to lock? An if 20 users did
this simultaneously, would that lead to a blocking issue?
Any insight would be appreciated!
Larry Menzin
American Techsystems Corp.
These links may help. The first link is a VB link about locking -
looks like you definitely might have some locking issues.
http://msdn.microsoft.com/library/de...oidlocking.asp
http://www.sql-server-performance.co...cing_locks.asp
http://msdn.microsoft.com/library/de...on_7a_1hf7.asp
|||Do each of the tables have a valid PK Constraint defined on them? Is the
Update issued by ADO using the PK to do the update? If not it is more
likely the Update is causing the problems. That is a pretty lame way to
update rows anyway. They should create a stored procedure to do the update
and call that from the front end instead.
Andrew J. Kelly SQL MVP
"Larry Menzin" <LarryMenzin@.discussions.microsoft.com> wrote in message
news:0DAF194C-3019-4E7C-AB0F-691B95911D0E@.microsoft.com...
>I am working on solving performance problems for a client experiencing
> frequent blocks on their server which has 4 CPUs and 4GB or RAM. Their
> application is a self-developed VB6 application that is very resource
> intensive. With about 150 simultaneous users CPU utilization frequently
> exceeds 90%. Yesterday their server froze at 100% CPU utilization,
> requiring
> all users to exit and the server to be rebooted.
> Their programming model uses ADO recordsets. Once a recordset is fetched
> to
> the application and displayed on the form, the rsX variable is set to
> nothing
> and the user can update the form. When the OK button is pressed on the
> form,
> the application code does the following:
> strSQL = "Select * From Contact Where 1=2"
> Set rsX = Openrecordset(strSQL)
> Rsx.Field1 = Form!Field1
> ...
> ...
> Rsx.Fieldn = Form!Fieldn
> Rsx.Update
> Question: Does the grabbing of a recordset in this manner lock the whole
> table since there is no individual record or page to lock? An if 20 users
> did
> this simultaneously, would that lead to a blocking issue?
> Any insight would be appreciated!
> --
> Larry Menzin
> American Techsystems Corp.
|||Hi,
When u execute a select statement it will fetech all the columns and
rows now imagine if the table has 40 or 45 coulmns then how much
resource it will use .
u should field name and ur where clause.
Secondly when the recodset work is done close the recordset.
hope this help
from
killer
Andrew J. Kelly wrote:[vbcol=seagreen]
> Do each of the tables have a valid PK Constraint defined on them? Is the
> Update issued by ADO using the PK to do the update? If not it is more
> likely the Update is causing the problems. That is a pretty lame way to
> update rows anyway. They should create a stored procedure to do the update
> and call that from the front end instead.
> --
> Andrew J. Kelly SQL MVP
>
> "Larry Menzin" <LarryMenzin@.discussions.microsoft.com> wrote in message
> news:0DAF194C-3019-4E7C-AB0F-691B95911D0E@.microsoft.com...
|||Hi
Run profiler and see what/where this is happening. You may want to look for
items in the locking event class and use the read/write/duration values to
find queries that take a long time and do a large number of I/O. You will
then be able to analyse query plans and indexes, or maybe even want to pass
the output of a trace into the index tuning wizard and see what it comes up
with.
John
"Larry Menzin" wrote:

> I am working on solving performance problems for a client experiencing
> frequent blocks on their server which has 4 CPUs and 4GB or RAM. Their
> application is a self-developed VB6 application that is very resource
> intensive. With about 150 simultaneous users CPU utilization frequently
> exceeds 90%. Yesterday their server froze at 100% CPU utilization, requiring
> all users to exit and the server to be rebooted.
> Their programming model uses ADO recordsets. Once a recordset is fetched to
> the application and displayed on the form, the rsX variable is set to nothing
> and the user can update the form. When the OK button is pressed on the form,
> the application code does the following:
> strSQL = "Select * From Contact Where 1=2"
> Set rsX = Openrecordset(strSQL)
> Rsx.Field1 = Form!Field1
> ...
> ...
> Rsx.Fieldn = Form!Fieldn
> Rsx.Update
> Question: Does the grabbing of a recordset in this manner lock the whole
> table since there is no individual record or page to lock? An if 20 users did
> this simultaneously, would that lead to a blocking issue?
> Any insight would be appreciated!
> --
> Larry Menzin
> American Techsystems Corp.

Locks and Blocking

I am working on solving performance problems for a client experiencing
frequent blocks on their server which has 4 CPUs and 4GB or RAM. Their
application is a self-developed VB6 application that is very resource
intensive. With about 150 simultaneous users CPU utilization frequently
exceeds 90%. Yesterday their server froze at 100% CPU utilization, requiring
all users to exit and the server to be rebooted.
Their programming model uses ADO recordsets. Once a recordset is fetched to
the application and displayed on the form, the rsX variable is set to nothin
g
and the user can update the form. When the OK button is pressed on the form,
the application code does the following:
strSQL = "Select * From Contact Where 1=2"
Set rsX = Openrecordset(strSQL)
Rsx.Field1 = Form!Field1
...
...
Rsx.Fieldn = Form!Fieldn
Rsx.Update
Question: Does the grabbing of a recordset in this manner lock the whole
table since there is no individual record or page to lock? An if 20 users di
d
this simultaneously, would that lead to a blocking issue?
Any insight would be appreciated!
Larry Menzin
American Techsystems Corp.These links may help. The first link is a VB link about locking -
looks like you definitely might have some locking issues.
http://msdn.microsoft.com/library/d...
idlocking.asp
http://www.sql-server-performance.c...ucing_locks.asp
http://msdn.microsoft.com/library/d... />
a_1hf7.asp|||Do each of the tables have a valid PK Constraint defined on them? Is the
Update issued by ADO using the PK to do the update? If not it is more
likely the Update is causing the problems. That is a pretty lame way to
update rows anyway. They should create a stored procedure to do the update
and call that from the front end instead.
Andrew J. Kelly SQL MVP
"Larry Menzin" <LarryMenzin@.discussions.microsoft.com> wrote in message
news:0DAF194C-3019-4E7C-AB0F-691B95911D0E@.microsoft.com...
>I am working on solving performance problems for a client experiencing
> frequent blocks on their server which has 4 CPUs and 4GB or RAM. Their
> application is a self-developed VB6 application that is very resource
> intensive. With about 150 simultaneous users CPU utilization frequently
> exceeds 90%. Yesterday their server froze at 100% CPU utilization,
> requiring
> all users to exit and the server to be rebooted.
> Their programming model uses ADO recordsets. Once a recordset is fetched
> to
> the application and displayed on the form, the rsX variable is set to
> nothing
> and the user can update the form. When the OK button is pressed on the
> form,
> the application code does the following:
> strSQL = "Select * From Contact Where 1=2"
> Set rsX = Openrecordset(strSQL)
> Rsx.Field1 = Form!Field1
> ...
> ...
> Rsx.Fieldn = Form!Fieldn
> Rsx.Update
> Question: Does the grabbing of a recordset in this manner lock the whole
> table since there is no individual record or page to lock? An if 20 users
> did
> this simultaneously, would that lead to a blocking issue?
> Any insight would be appreciated!
> --
> Larry Menzin
> American Techsystems Corp.|||Hi,
When u execute a select statement it will fetech all the columns and
rows now imagine if the table has 40 or 45 coulmns then how much
resource it will use .
u should field name and ur where clause.
Secondly when the recodset work is done close the recordset.
hope this help
from
killer
Andrew J. Kelly wrote:[vbcol=seagreen]
> Do each of the tables have a valid PK Constraint defined on them? Is the
> Update issued by ADO using the PK to do the update? If not it is more
> likely the Update is causing the problems. That is a pretty lame way to
> update rows anyway. They should create a stored procedure to do the updat
e
> and call that from the front end instead.
> --
> Andrew J. Kelly SQL MVP
>
> "Larry Menzin" <LarryMenzin@.discussions.microsoft.com> wrote in message
> news:0DAF194C-3019-4E7C-AB0F-691B95911D0E@.microsoft.com...|||Hi
Run profiler and see what/where this is happening. You may want to look for
items in the locking event class and use the read/write/duration values to
find queries that take a long time and do a large number of I/O. You will
then be able to analyse query plans and indexes, or maybe even want to pass
the output of a trace into the index tuning wizard and see what it comes up
with.
John
"Larry Menzin" wrote:

> I am working on solving performance problems for a client experiencing
> frequent blocks on their server which has 4 CPUs and 4GB or RAM. Their
> application is a self-developed VB6 application that is very resource
> intensive. With about 150 simultaneous users CPU utilization frequently
> exceeds 90%. Yesterday their server froze at 100% CPU utilization, requiri
ng
> all users to exit and the server to be rebooted.
> Their programming model uses ADO recordsets. Once a recordset is fetched t
o
> the application and displayed on the form, the rsX variable is set to noth
ing
> and the user can update the form. When the OK button is pressed on the for
m,
> the application code does the following:
> strSQL = "Select * From Contact Where 1=2"
> Set rsX = Openrecordset(strSQL)
> Rsx.Field1 = Form!Field1
> ...
> ...
> Rsx.Fieldn = Form!Fieldn
> Rsx.Update
> Question: Does the grabbing of a recordset in this manner lock the whole
> table since there is no individual record or page to lock? An if 20 users
did
> this simultaneously, would that lead to a blocking issue?
> Any insight would be appreciated!
> --
> Larry Menzin
> American Techsystems Corp.