High usage of RAM on SQL2k

Hi

I have an sql server with very high usage. The web which is connected to the sql server has approx. 1000 unique visitors every time.
The database is optimized and the site goes really fast now.

But a problem we've had since the beginning is that SQL Server constantly uses 1.8 GB of RAM. Never less, never more.

Do you have any idea on how to decrease the usage of RAM. I am sure that this high usage is necessary.check your logs,if they are more truncate them,
may be it helps you.|||But arent the log files truncated during the backup?|||Logs?

run sp_configure and tell me what the results are for min server memory.|||min server memory (MB) 0 2147483647 0 0

High PF Usage

The system is a Clustered x64 SQL Server 2005 SP2 Standard 32G of RAM. The task manager show the PF Usage as high as 27G. Any ideas why the usage would be so high?

Did you give the "Lock Pages in Memory" right to the SQL Server Service account? This is extremely important for 64-bit SQL Server 2005. Otherwise, the OS controls paging instead of SQL Server. I have some more details below:

http://glennberrysqlperformance.spaces.live.com/blog/cns!45041418ECCAA960!115.entry

High Log flush Wait Time

Hello,
We have a Cluster in Windows 2000 AS, with HP Proliant DL580 G2 connected
to an HP EVA 3000.
4 CPU with HyperTh enabled. 8 GB of RAM
Sqlserver 2000 (SP3a) with 180 Gb of DB.
Since 2 weeks, we have slow performance but CPU are not stressed.
We have High Log flush Wait time (>2000 ms) and high latch wait time (800
ms)
Anyone as an idea ?
THX
--
Message posted via http://www.sqlmonster.comYou probably also have a high disk queue length on the disk(s) where yout
LOG device lives. Make sure yout LOG files are on a RAID 1 or 1+0 device
with no other data files on those disks. This must be a physical disk set,
not just a logical partition of an underlying shared RAID set.
What is happening is that your SQL server cannot write log files to the disk
in a timely manner. SQL will not process a transaction unless the
write-ahead transaction log has the start transaction marker committed to
disk. Thus, your CPUs are stuck waiting on this particular disk function.
Therefore, you make log writes function as fast as possible. Since logs are
written sequentially in relatively small chunks, RAID 1 or1+ 0 is ideal.
RAID 5 will cause performance bottlenecks on log devices.
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"John via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:61f18ba8c87443539797e121a8dd7f14@.SQLMonster.com...
> Hello,
> We have a Cluster in Windows 2000 AS, with HP Proliant DL580 G2 connected
> to an HP EVA 3000.
> 4 CPU with HyperTh enabled. 8 GB of RAM
> Sqlserver 2000 (SP3a) with 180 Gb of DB.
> Since 2 weeks, we have slow performance but CPU are not stressed.
> We have High Log flush Wait time (>2000 ms) and high latch wait time (800
> ms)
> Anyone as an idea ?
> THX
> --
> Message posted via http://www.sqlmonster.com

high CPU time and sql 2000 getting slow performance

Hi,
I admin a server, dual 3,6 xeon with 4GB ram.
Windows 2000 server + SQL 2000 with SP3a + DNS + IIS
The server have around 800 dynamic sites.
SQL 2000 have 1Gb memory for it.
the problem is:
the CPU time after a SQL restart of service keeps getting higher and higher,
i say in a 24h its aroung 24 also memory starts to grow and stays at 1Gb,
wich is the reserved memory for SQL.
Is it normal the CPU time keeping getting higher? or should refresh? like
dllhost.exe in the webserver.
Is it normal the memory stays at 1Gb or also should refresh?
The problem is with some hours/days sites start to open slow and when i
restart the SQL service, sites start to open in few seconds, but after some
day/days start to be slow again.
Its a very busy server with some portals, a few, others sites dynamic also
but with few hits. But almost 100% of sites uses, except the portals, read
for one of the portals database.
Im not the programmer of the sites, and also i dont make the SQL statements
need for each site, or design the databases.
Tkx in advance,
PVThe first red flag is to have a SQL Server sharing resources with another
application in the same server. It is not the optimal planning. Having said
that, it gets worse if the application is IIS and it is a very busy server
due to the busy portals.
SQL Server behaves somehow like an only child. It wants all CPU and all
possible memory. If you tell SQL Server that it could use from 0 to 1GB, it
will eventually use 1GB and it will keep it just in case.
According with what you describe, you have two demanding applications that
are competeng for the hardware resources on the same machine. This could get
worse if the IIS load increases.
I would look into the shared load between IIS and the SQL Server, meaning
that if the SQL Server is only 25% of the load, DNS users 15%, and IIS takes
the 60% remaining, it may be wise to start planning about getting another
machine to move either the SQL Server or get a bigger IIS Server to handle a
n
increasing load.
Let me know if this helps..
"PV" wrote:

> Hi,
> I admin a server, dual 3,6 xeon with 4GB ram.
> Windows 2000 server + SQL 2000 with SP3a + DNS + IIS
> The server have around 800 dynamic sites.
> SQL 2000 have 1Gb memory for it.
>
> the problem is:
> the CPU time after a SQL restart of service keeps getting higher and highe
r,
> i say in a 24h its aroung 24 also memory starts to grow and stays at 1Gb,
> wich is the reserved memory for SQL.
> Is it normal the CPU time keeping getting higher? or should refresh? like
> dllhost.exe in the webserver.
> Is it normal the memory stays at 1Gb or also should refresh?
> The problem is with some hours/days sites start to open slow and when i
> restart the SQL service, sites start to open in few seconds, but after som
e
> day/days start to be slow again.
> Its a very busy server with some portals, a few, others sites dynamic also
> but with few hits. But almost 100% of sites uses, except the portals, read
> for one of the portals database.
> Im not the programmer of the sites, and also i dont make the SQL statement
s
> need for each site, or design the databases.
> Tkx in advance,
> PV|||Hi,
Tkx for the directions needed to be made.
SQL needs a dedicated server for it.
Its SQL 2000 that "decreases server performance" after some hours/days. And,
as you say, starts the "competition" of resources between SQL and IIS mostly
.
dispite all that sites, IIS doesnt take much amount of processor, except the
portals, one specially, but not all the time like SQL.
Since its SQL that decreases the performance i wanted to know what i asked,
if high cpu time is normal? and use of memory. the answer is positve i
presume.
Is there a way to "recycle" SQL uses of resources? like there is one tool to
IIS 5.0, but not need by the time being at this server since IIS is ok.
Or its prudent to "force" restart once a day/2days to the SQL? i think this
isnt the right way to resolve things.
PS:
we had one server with 1Gb memory with a xeon 2.0 with 500-600 dynamic sites
and only one BD in SQL(512Mb reserved and CPU utilization restrain to 50%
only), a forum and DNS. And the server worked EXCELLENT with no problems for
months and months without no problem always online and keeping increasing
amount of sites (10) per month. the server never reached the 1Gb use of
memory.
but no PORTALS... neither SQL to the portals.
Tkx again for the answer,
PV
"Edgardo Valdez, MCSD, MCDBA" wrote:
[vbcol=seagreen]
> The first red flag is to have a SQL Server sharing resources with another
> application in the same server. It is not the optimal planning. Having sai
d
> that, it gets worse if the application is IIS and it is a very busy server
> due to the busy portals.
> SQL Server behaves somehow like an only child. It wants all CPU and all
> possible memory. If you tell SQL Server that it could use from 0 to 1GB, i
t
> will eventually use 1GB and it will keep it just in case.
> According with what you describe, you have two demanding applications that
> are competeng for the hardware resources on the same machine. This could g
et
> worse if the IIS load increases.
> I would look into the shared load between IIS and the SQL Server, meaning
> that if the SQL Server is only 25% of the load, DNS users 15%, and IIS tak
es
> the 60% remaining, it may be wise to start planning about getting another
> machine to move either the SQL Server or get a bigger IIS Server to handle
an
> increasing load.
> Let me know if this helps..
> "PV" wrote:
>|||Using 1 GB of memory isn't unusual...SQL uses as much memory as it can
get ahold of. It's also not unusual to have high CPU times.
Shut down all unecessary services (including IIS, FTP, etc.), enable
the /3GB switch and monitor your I/O & CPU usage. If you still run
into problems, run Profiler to determine what queries, SPs or other
operations are utilitizing your CPU.
Above all, run SQL Server on its own server. Don't mix in IIS
On Tue, 4 Apr 2006 06:42:05 -0700, PV <PV@.discussions.microsoft.com>
wrote:

>Hi,
>I admin a server, dual 3,6 xeon with 4GB ram.
>Windows 2000 server + SQL 2000 with SP3a + DNS + IIS
>The server have around 800 dynamic sites.
>SQL 2000 have 1Gb memory for it.
>
>the problem is:
>the CPU time after a SQL restart of service keeps getting higher and higher
,
>i say in a 24h its aroung 24 also memory starts to grow and stays at 1Gb,
>wich is the reserved memory for SQL.
>Is it normal the CPU time keeping getting higher? or should refresh? like
>dllhost.exe in the webserver.
>Is it normal the memory stays at 1Gb or also should refresh?
>The problem is with some hours/days sites start to open slow and when i
>restart the SQL service, sites start to open in few seconds, but after some
>day/days start to be slow again.
>Its a very busy server with some portals, a few, others sites dynamic also
>but with few hits. But almost 100% of sites uses, except the portals, read
>for one of the portals database.
>Im not the programmer of the sites, and also i dont make the SQL statements
>need for each site, or design the databases.
>Tkx in advance,
>PV

high CPU time and sql 2000 getting slow performance

Hi,
I admin a server, dual 3,6 xeon with 4GB ram.
Windows 2000 server + SQL 2000 with SP3a + DNS + IIS
The server have around 800 dynamic sites.
SQL 2000 have 1Gb memory for it.
the problem is:
the CPU time after a SQL restart of service keeps getting higher and higher,
i say in a 24h its aroung 24 also memory starts to grow and stays at 1Gb,
wich is the reserved memory for SQL.
Is it normal the CPU time keeping getting higher? or should refresh? like
dllhost.exe in the webserver.
Is it normal the memory stays at 1Gb or also should refresh?
The problem is with some hours/days sites start to open slow and when i
restart the SQL service, sites start to open in few seconds, but after some
day/days start to be slow again.
Its a very busy server with some portals, a few, others sites dynamic also
but with few hits. But almost 100% of sites uses, except the portals, read
for one of the portals database.
Im not the programmer of the sites, and also i dont make the SQL statements
need for each site, or design the databases.
Tkx in advance,
PVIt sounds to me that the server is overloaded.
Normally if it is a live environment, you have a dedicated server just
for MSSQL.
Normally it is not advisable to change the default memory setting. For
optimum performance, SQL Server should be allowed to take as much as
RAM as it wants for its own use without having to compete for RAM with
other applications.
You can turn on some performance monitors to see how busy your system
is and justify to the owner of the machine it is time to upgrade the
hardware.
Here are some monitors you can put on (for memory only):
Memory Object: Pages/Sec
Memory Object: Available Bytes
SQLServer: Memory Manager: Total Server Memory
SQLServer: Memory Manager: Target Server Memory
Alternatively, you can turn on SQL Profiler if you are more interested
what MSSQL doing all day longs. It has CPU, I/O and memory eventlog
you can trace.
Mel|||The first red flag is to have a SQL Server sharing resources with another
application in the same server. It is not the optimal planning. Having said
that, it gets worse if the application is IIS and it is a very busy server
due to the busy portals.
SQL Server behaves somehow like an only child. It wants all CPU and all
possible memory. If you tell SQL Server that it could use from 0 to 1GB, it
will eventually use 1GB and it will keep it just in case.
According with what you describe, you have two demanding applications that
are competeng for the hardware resources on the same machine. This could get
worse if the IIS load increases.
I would look into the shared load between IIS and the SQL Server, meaning
that if the SQL Server is only 25% of the load, DNS users 15%, and IIS takes
the 60% remaining, it may be wise to start planning about getting another
machine to move either the SQL Server or get a bigger IIS Server to handle an
increasing load.
Let me know if this helps..
"PV" wrote:
> Hi,
> I admin a server, dual 3,6 xeon with 4GB ram.
> Windows 2000 server + SQL 2000 with SP3a + DNS + IIS
> The server have around 800 dynamic sites.
> SQL 2000 have 1Gb memory for it.
>
> the problem is:
> the CPU time after a SQL restart of service keeps getting higher and higher,
> i say in a 24h its aroung 24 also memory starts to grow and stays at 1Gb,
> wich is the reserved memory for SQL.
> Is it normal the CPU time keeping getting higher? or should refresh? like
> dllhost.exe in the webserver.
> Is it normal the memory stays at 1Gb or also should refresh?
> The problem is with some hours/days sites start to open slow and when i
> restart the SQL service, sites start to open in few seconds, but after some
> day/days start to be slow again.
> Its a very busy server with some portals, a few, others sites dynamic also
> but with few hits. But almost 100% of sites uses, except the portals, read
> for one of the portals database.
> Im not the programmer of the sites, and also i dont make the SQL statements
> need for each site, or design the databases.
> Tkx in advance,
> PV|||Hi,
Tkx for the directions needed to be made.
SQL needs a dedicated server for it.
Its SQL 2000 that "decreases server performance" after some hours/days. And,
as you say, starts the "competition" of resources between SQL and IIS mostly.
dispite all that sites, IIS doesnt take much amount of processor, except the
portals, one specially, but not all the time like SQL.
Since its SQL that decreases the performance i wanted to know what i asked,
if high cpu time is normal? and use of memory. the answer is positve i
presume.
Is there a way to "recycle" SQL uses of resources? like there is one tool to
IIS 5.0, but not need by the time being at this server since IIS is ok.
Or its prudent to "force" restart once a day/2days to the SQL? i think this
isnt the right way to resolve things.
PS:
we had one server with 1Gb memory with a xeon 2.0 with 500-600 dynamic sites
and only one BD in SQL(512Mb reserved and CPU utilization restrain to 50%
only), a forum and DNS. And the server worked EXCELLENT with no problems for
months and months without no problem always online and keeping increasing
amount of sites (10) per month. the server never reached the 1Gb use of
memory.
but no PORTALS... neither SQL to the portals.
Tkx again for the answer,
PV
"Edgardo Valdez, MCSD, MCDBA" wrote:
> The first red flag is to have a SQL Server sharing resources with another
> application in the same server. It is not the optimal planning. Having said
> that, it gets worse if the application is IIS and it is a very busy server
> due to the busy portals.
> SQL Server behaves somehow like an only child. It wants all CPU and all
> possible memory. If you tell SQL Server that it could use from 0 to 1GB, it
> will eventually use 1GB and it will keep it just in case.
> According with what you describe, you have two demanding applications that
> are competeng for the hardware resources on the same machine. This could get
> worse if the IIS load increases.
> I would look into the shared load between IIS and the SQL Server, meaning
> that if the SQL Server is only 25% of the load, DNS users 15%, and IIS takes
> the 60% remaining, it may be wise to start planning about getting another
> machine to move either the SQL Server or get a bigger IIS Server to handle an
> increasing load.
> Let me know if this helps..
> "PV" wrote:
> > Hi,
> >
> > I admin a server, dual 3,6 xeon with 4GB ram.
> >
> > Windows 2000 server + SQL 2000 with SP3a + DNS + IIS
> >
> > The server have around 800 dynamic sites.
> >
> > SQL 2000 have 1Gb memory for it.
> >
> >
> > the problem is:
> > the CPU time after a SQL restart of service keeps getting higher and higher,
> > i say in a 24h its aroung 24 also memory starts to grow and stays at 1Gb,
> > wich is the reserved memory for SQL.
> >
> > Is it normal the CPU time keeping getting higher? or should refresh? like
> > dllhost.exe in the webserver.
> >
> > Is it normal the memory stays at 1Gb or also should refresh?
> >
> > The problem is with some hours/days sites start to open slow and when i
> > restart the SQL service, sites start to open in few seconds, but after some
> > day/days start to be slow again.
> >
> > Its a very busy server with some portals, a few, others sites dynamic also
> > but with few hits. But almost 100% of sites uses, except the portals, read
> > for one of the portals database.
> >
> > Im not the programmer of the sites, and also i dont make the SQL statements
> > need for each site, or design the databases.
> >
> > Tkx in advance,
> > PV|||You will need to use SQL Profiler to capture the CPU and memory usage.
As the monitor will also cause some system resources, so you normally
turn it on for a period of time that you think it is a typical business
workload.
After you have the trace, you can either just open in Profiler or even
better import into as a trace table (built-in feature of Profiler -
Save as trace table). From the trace you can then see what processes
have highest CPU/memory usage, using select sql.
As you quoted another server running just fine, it could well be some
poorly written sql scripts/stored procedures are running on the server.
With the trace, you can then identify the root of the problem (if
any). E.g. a stored procedure that keep re-compile for no good reason,
session no closed down properly.
Mel|||Using 1 GB of memory isn't unusual...SQL uses as much memory as it can
get ahold of. It's also not unusual to have high CPU times.
Shut down all unecessary services (including IIS, FTP, etc.), enable
the /3GB switch and monitor your I/O & CPU usage. If you still run
into problems, run Profiler to determine what queries, SPs or other
operations are utilitizing your CPU.
Above all, run SQL Server on its own server. Don't mix in IIS
On Tue, 4 Apr 2006 06:42:05 -0700, PV <PV@.discussions.microsoft.com>
wrote:
>Hi,
>I admin a server, dual 3,6 xeon with 4GB ram.
>Windows 2000 server + SQL 2000 with SP3a + DNS + IIS
>The server have around 800 dynamic sites.
>SQL 2000 have 1Gb memory for it.
>
>the problem is:
>the CPU time after a SQL restart of service keeps getting higher and higher,
>i say in a 24h its aroung 24 also memory starts to grow and stays at 1Gb,
>wich is the reserved memory for SQL.
>Is it normal the CPU time keeping getting higher? or should refresh? like
>dllhost.exe in the webserver.
>Is it normal the memory stays at 1Gb or also should refresh?
>The problem is with some hours/days sites start to open slow and when i
>restart the SQL service, sites start to open in few seconds, but after some
>day/days start to be slow again.
>Its a very busy server with some portals, a few, others sites dynamic also
>but with few hits. But almost 100% of sites uses, except the portals, read
>for one of the portals database.
>Im not the programmer of the sites, and also i dont make the SQL statements
>need for each site, or design the databases.
>Tkx in advance,
>PV

high CPU time and sql 2000 getting slow performance

Hi,
I admin a server, dual 3,6 xeon with 4GB ram.
Windows 2000 server + SQL 2000 with SP3a + DNS + IIS
The server have around 800 dynamic sites.
SQL 2000 have 1Gb memory for it.
the problem is:
the CPU time after a SQL restart of service keeps getting higher and higher,
i say in a 24h its aroung 24 also memory starts to grow and stays at 1Gb,
wich is the reserved memory for SQL.
Is it normal the CPU time keeping getting higher? or should refresh? like
dllhost.exe in the webserver.
Is it normal the memory stays at 1Gb or also should refresh?
The problem is with some hours/days sites start to open slow and when i
restart the SQL service, sites start to open in few seconds, but after some
day/days start to be slow again.
Its a very busy server with some portals, a few, others sites dynamic also
but with few hits. But almost 100% of sites uses, except the portals, read
for one of the portals database.
Im not the programmer of the sites, and also i dont make the SQL statements
need for each site, or design the databases.
Tkx in advance,
PV
The first red flag is to have a SQL Server sharing resources with another
application in the same server. It is not the optimal planning. Having said
that, it gets worse if the application is IIS and it is a very busy server
due to the busy portals.
SQL Server behaves somehow like an only child. It wants all CPU and all
possible memory. If you tell SQL Server that it could use from 0 to 1GB, it
will eventually use 1GB and it will keep it just in case.
According with what you describe, you have two demanding applications that
are competeng for the hardware resources on the same machine. This could get
worse if the IIS load increases.
I would look into the shared load between IIS and the SQL Server, meaning
that if the SQL Server is only 25% of the load, DNS users 15%, and IIS takes
the 60% remaining, it may be wise to start planning about getting another
machine to move either the SQL Server or get a bigger IIS Server to handle an
increasing load.
Let me know if this helps..
"PV" wrote:

> Hi,
> I admin a server, dual 3,6 xeon with 4GB ram.
> Windows 2000 server + SQL 2000 with SP3a + DNS + IIS
> The server have around 800 dynamic sites.
> SQL 2000 have 1Gb memory for it.
>
> the problem is:
> the CPU time after a SQL restart of service keeps getting higher and higher,
> i say in a 24h its aroung 24 also memory starts to grow and stays at 1Gb,
> wich is the reserved memory for SQL.
> Is it normal the CPU time keeping getting higher? or should refresh? like
> dllhost.exe in the webserver.
> Is it normal the memory stays at 1Gb or also should refresh?
> The problem is with some hours/days sites start to open slow and when i
> restart the SQL service, sites start to open in few seconds, but after some
> day/days start to be slow again.
> Its a very busy server with some portals, a few, others sites dynamic also
> but with few hits. But almost 100% of sites uses, except the portals, read
> for one of the portals database.
> Im not the programmer of the sites, and also i dont make the SQL statements
> need for each site, or design the databases.
> Tkx in advance,
> PV
|||Hi,
Tkx for the directions needed to be made.
SQL needs a dedicated server for it.
Its SQL 2000 that "decreases server performance" after some hours/days. And,
as you say, starts the "competition" of resources between SQL and IIS mostly.
dispite all that sites, IIS doesnt take much amount of processor, except the
portals, one specially, but not all the time like SQL.
Since its SQL that decreases the performance i wanted to know what i asked,
if high cpu time is normal? and use of memory. the answer is positve i
presume.
Is there a way to "recycle" SQL uses of resources? like there is one tool to
IIS 5.0, but not need by the time being at this server since IIS is ok.
Or its prudent to "force" restart once a day/2days to the SQL? i think this
isnt the right way to resolve things.
PS:
we had one server with 1Gb memory with a xeon 2.0 with 500-600 dynamic sites
and only one BD in SQL(512Mb reserved and CPU utilization restrain to 50%
only), a forum and DNS. And the server worked EXCELLENT with no problems for
months and months without no problem always online and keeping increasing
amount of sites (10) per month. the server never reached the 1Gb use of
memory.
but no PORTALS... neither SQL to the portals.
Tkx again for the answer,
PV
"Edgardo Valdez, MCSD, MCDBA" wrote:
[vbcol=seagreen]
> The first red flag is to have a SQL Server sharing resources with another
> application in the same server. It is not the optimal planning. Having said
> that, it gets worse if the application is IIS and it is a very busy server
> due to the busy portals.
> SQL Server behaves somehow like an only child. It wants all CPU and all
> possible memory. If you tell SQL Server that it could use from 0 to 1GB, it
> will eventually use 1GB and it will keep it just in case.
> According with what you describe, you have two demanding applications that
> are competeng for the hardware resources on the same machine. This could get
> worse if the IIS load increases.
> I would look into the shared load between IIS and the SQL Server, meaning
> that if the SQL Server is only 25% of the load, DNS users 15%, and IIS takes
> the 60% remaining, it may be wise to start planning about getting another
> machine to move either the SQL Server or get a bigger IIS Server to handle an
> increasing load.
> Let me know if this helps..
> "PV" wrote:

High CPU - low IO, low RAM

Hi,
I did this experiment with an MS SQL Server 2000 SP4, Windows 2000 Server SP4:
- set SQL server buffer cache at 16 MB, restarted;
- ran a read-intensive query (in terms of SQL Profiler data).
While the query was running CPU usage remained at or above 80%. But the disk
activity was not so high. Most of the time it remained under 30% despite the
minimal amount of RAM I allocated to the buffer cache. Disk queues were also
short, that is, way below 1 (by a few magnitudes). I'm not talking about
occasional spikes here.
So why do I get 10s of millions of (logical) reads in SQL Profiler but don't
see 100% load on a hard disk even with the tiny buffer cache?
-- Many thanks, Oskar.
What version of windows are you on Oskar and what disk performance counters
are you using, some of the counters in win 2000+ are a bit iffy to say the
least... ...also are you monitoring all disk arrays or just the array with
sql on?
"Oskar" wrote:

> Hi,
> I did this experiment with an MS SQL Server 2000 SP4, Windows 2000 Server SP4:
> - set SQL server buffer cache at 16 MB, restarted;
> - ran a read-intensive query (in terms of SQL Profiler data).
> While the query was running CPU usage remained at or above 80%. But the disk
> activity was not so high. Most of the time it remained under 30% despite the
> minimal amount of RAM I allocated to the buffer cache. Disk queues were also
> short, that is, way below 1 (by a few magnitudes). I'm not talking about
> occasional spikes here.
> So why do I get 10s of millions of (logical) reads in SQL Profiler but don't
> see 100% load on a hard disk even with the tiny buffer cache?
> -- Many thanks, Oskar.
>
|||"Ben UK" wrote:

> What version of windows are you on Oskar and what disk performance counters
> are you using, some of the counters in win 2000+ are a bit iffy to say the
> least... ...also are you monitoring all disk arrays or just the array with
> sql on?
1) > > ... Windows 2000 Server SP4
2) Those are single disks and, yes, I looked at the disks with SQL Server
databases. Mind you, the activity was there, but it was less than I expected.
The database files are on a dedicated disk and tempdb is on the OS disk.
[vbcol=seagreen]
> "Oskar" wrote:
|||On Mon, 8 Jan 2007 07:22:00 -0800, Oskar
<Oskar@.discussions.microsoft.com> wrote:
>So why do I get 10s of millions of (logical) reads in SQL Profiler but don't
>see 100% load on a hard disk even with the tiny buffer cache?
If the data is tiny, you can get a zillion logical reads while its
kept in cache, that's why they are LOGICAL reads!
In fact, if the data is well-indexed and just the *index* is cached,
and especially if the data actually fetched is limited, you might get
the pattern you see.
J.
|||If I understood it correctly, your guess is that the data my query is working
on is actually only a little bit bigger than the buffer cache size I
specified. I find this unlikely.
"JXStern" wrote:

> On Mon, 8 Jan 2007 07:22:00 -0800, Oskar
> <Oskar@.discussions.microsoft.com> wrote:
> If the data is tiny, you can get a zillion logical reads while its
> kept in cache, that's why they are LOGICAL reads!
> In fact, if the data is well-indexed and just the *index* is cached,
> and especially if the data actually fetched is limited, you might get
> the pattern you see.
> J.
>
|||On Mon, 8 Jan 2007 09:54:01 -0800, Oskar
<Oskar@.discussions.microsoft.com> wrote:

>If I understood it correctly, your guess is that the data my query is working
>on is actually only a little bit bigger than the buffer cache size I
>specified. I find this unlikely.
I'm just guessing, you should be able to tell how much data you're
fetching.
J.
[vbcol=seagreen]
>"JXStern" wrote:
|||This is actually what I would expect in this case. SQL Server does a lot of
things to optimize disk io. When you are doing pretty sequential reads the
IO subsystem will do read-ahead so a lot of data can be pulled into memory
with a single IO. The issue with low memory then becomes one of
continuously moving data around in the buffer pool to make room for new data
arriving from the disk and building the result set. This requires a lot of
processor cycles. I think what you're missing here (if your description is
correct) is that a lot of buffer space doesn't help you the first time you
run a query. When you scan a table the first time you have to read all the
sectors from disk no matter how much buffer memory you have. Where the
difference comes in is that if you have a lot of memory, the SECOND time you
scan a table, most of the data will be in buffer memory so it won't have to
be read from the disk.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Oskar" <Oskar@.discussions.microsoft.com> wrote in message
news:06F4686F-A738-49E5-8DEB-A7EBDA156843@.microsoft.com...
> Hi,
> I did this experiment with an MS SQL Server 2000 SP4, Windows 2000 Server
> SP4:
> - set SQL server buffer cache at 16 MB, restarted;
> - ran a read-intensive query (in terms of SQL Profiler data).
> While the query was running CPU usage remained at or above 80%. But the
> disk
> activity was not so high. Most of the time it remained under 30% despite
> the
> minimal amount of RAM I allocated to the buffer cache. Disk queues were
> also
> short, that is, way below 1 (by a few magnitudes). I'm not talking about
> occasional spikes here.
> So why do I get 10s of millions of (logical) reads in SQL Profiler but
> don't
> see 100% load on a hard disk even with the tiny buffer cache?
> -- Many thanks, Oskar.
>
|||For Physical Disk monitoring use Disk Reads/ Sec & Disk Writes/ Sec coupled
with % Idle (100- %Idle = % Busy) for seeing how busy the disks are.. ..you
can then use Avg Disk Sec/ Read & Avg Disk Sec/ Write to measure the latency
and see if the disk is keeping up, and Avg Read Bytes/Sec & Avg Write
Bytes/Sec to understand the throughput.
You could also monitor Pages/Sec, Pages Input/Sec, Page Reads/Sec, Page
Output/Sec, Page Writes/Sec to see what is happening with regards to memory
and it's relation to the disk..
Check sql counters like Buffer Cache Hit Ratio, Lazy Writes/Sec, Avg Page
Life Expectency, Latch Waits/Sec, Avg Latch Wait Time to see how what your
doing is impacting sql.
Hope this helps
Ben
"Oskar" wrote:
[vbcol=seagreen]
>
> "Ben UK" wrote:
>
> 1) > > ... Windows 2000 Server SP4
> 2) Those are single disks and, yes, I looked at the disks with SQL Server
> databases. Mind you, the activity was there, but it was less than I expected.
> The database files are on a dedicated disk and tempdb is on the OS disk.
>
|||This is an interesting point. Because, if I'm right, it implies that it's
better to minimize the amount of the buffer cache allocated to a query that
does a large scan. The query would just waste precious CPU resources for
bringing data into the cache just to toss it out in a short while due to RAM
shortage.
As to the low disk activity: maybe this explains it all:
FROM
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlops6.mspx
<<
Note on Disk Queue Length
This counter no longer yields meaningful results in interpreting disk I/O
problems with SQL Server 2000. This is because the SQL Server engines
dynamically manage disk I/O, which is why the Max Async I/O setting no longer
exists. A simplified explanation is that SQL Server issues an order for disk
I/O, but does not wait for that process to complete before issuing another
request. When the I/O request has completed its work, the database engine is
notified and processes the results. SQL Server monitors the throughput on
these requests, and will manage the amount of I/O that the disks can
effectively handle.[vbcol=seagreen]
-- Many thanks, Oskar.
"Roger Wolter[MSFT]" wrote:

> This is actually what I would expect in this case. SQL Server does a lot of
> things to optimize disk io. When you are doing pretty sequential reads the
> IO subsystem will do read-ahead so a lot of data can be pulled into memory
> with a single IO. The issue with low memory then becomes one of
> continuously moving data around in the buffer pool to make room for new data
> arriving from the disk and building the result set. This requires a lot of
> processor cycles. I think what you're missing here (if your description is
> correct) is that a lot of buffer space doesn't help you the first time you
> run a query. When you scan a table the first time you have to read all the
> sectors from disk no matter how much buffer memory you have. Where the
> difference comes in is that if you have a lot of memory, the SECOND time you
> scan a table, most of the data will be in buffer memory so it won't have to
> be read from the disk.
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> "Oskar" <Oskar@.discussions.microsoft.com> wrote in message
> news:06F4686F-A738-49E5-8DEB-A7EBDA156843@.microsoft.com...
>
>
|||"Greg Linwood" wrote:

> That disk queues are short only indicates that the disks are keeping up with
> requests.
> This shouldn't be surprising if either:
> (a) Disk requests are being managed by SQL's UMS
Not sure what do you mean...

> or
> (b) The disk/s are simply keeping up with the requests..
> Also, for a "read intensive query", do you mean a large table scan? If so,
> it's possible that fewer disk requests might be performed than you're
> expecting due to disk IO heuristics such as extent reads..
I didn't look at the execution plan - just the Reads column for
SQL:BatchCompleted and RPC:Completed events, so probably you're right that
some of the reads were read-ahead reads (maybe even a majority of them).
-- Thanks, Oskar.

> Regards,
> Greg Linwood
> SQL Server MVP
> http://blogs.sqlserver.org.au/blogs/greg_linwood
> "Oskar" <Oskar@.discussions.microsoft.com> wrote in message
> news:06F4686F-A738-49E5-8DEB-A7EBDA156843@.microsoft.com...
>
>

High CPU - low IO, low RAM

Hi,
I did this experiment with an MS SQL Server 2000 SP4, Windows 2000 Server SP
4:
- set SQL server buffer cache at 16 MB, restarted;
- ran a read-intensive query (in terms of SQL Profiler data).
While the query was running CPU usage remained at or above 80%. But the disk
activity was not so high. Most of the time it remained under 30% despite the
minimal amount of RAM I allocated to the buffer cache. Disk queues were also
short, that is, way below 1 (by a few magnitudes). I'm not talking about
occasional spikes here.
So why do I get 10s of millions of (logical) reads in SQL Profiler but don't
see 100% load on a hard disk even with the tiny buffer cache?
-- Many thanks, Oskar.What version of windows are you on Oskar and what disk performance counters
are you using, some of the counters in win 2000+ are a bit iffy to say the
least... ...also are you monitoring all disk arrays or just the array with
sql on?
"Oskar" wrote:

> Hi,
> I did this experiment with an MS SQL Server 2000 SP4, Windows 2000 Server
SP4:
> - set SQL server buffer cache at 16 MB, restarted;
> - ran a read-intensive query (in terms of SQL Profiler data).
> While the query was running CPU usage remained at or above 80%. But the di
sk
> activity was not so high. Most of the time it remained under 30% despite t
he
> minimal amount of RAM I allocated to the buffer cache. Disk queues were al
so
> short, that is, way below 1 (by a few magnitudes). I'm not talking about
> occasional spikes here.
> So why do I get 10s of millions of (logical) reads in SQL Profiler but don
't
> see 100% load on a hard disk even with the tiny buffer cache?
> -- Many thanks, Oskar.
>|||"Ben UK" wrote:

> What version of windows are you on Oskar and what disk performance counter
s
> are you using, some of the counters in win 2000+ are a bit iffy to say the
> least... ...also are you monitoring all disk arrays or just the array with
> sql on?
1) > > ... Windows 2000 Server SP4
2) Those are single disks and, yes, I looked at the disks with SQL Server
databases. Mind you, the activity was there, but it was less than I expected
.
The database files are on a dedicated disk and tempdb is on the OS disk.
[vbcol=seagreen]
> "Oskar" wrote:
>|||On Mon, 8 Jan 2007 07:22:00 -0800, Oskar
<Oskar@.discussions.microsoft.com> wrote:
>So why do I get 10s of millions of (logical) reads in SQL Profiler but don'
t
>see 100% load on a hard disk even with the tiny buffer cache?
If the data is tiny, you can get a zillion logical reads while its
kept in cache, that's why they are LOGICAL reads!
In fact, if the data is well-indexed and just the *index* is cached,
and especially if the data actually fetched is limited, you might get
the pattern you see.
J.|||If I understood it correctly, your guess is that the data my query is workin
g
on is actually only a little bit bigger than the buffer cache size I
specified. I find this unlikely.
"JXStern" wrote:

> On Mon, 8 Jan 2007 07:22:00 -0800, Oskar
> <Oskar@.discussions.microsoft.com> wrote:
> If the data is tiny, you can get a zillion logical reads while its
> kept in cache, that's why they are LOGICAL reads!
> In fact, if the data is well-indexed and just the *index* is cached,
> and especially if the data actually fetched is limited, you might get
> the pattern you see.
> J.
>|||That disk queues are short only indicates that the disks are keeping up with
requests.
This shouldn't be surprising if either:
(a) Disk requests are being managed by SQL's UMS
or
(b) The disk/s are simply keeping up with the requests..
Also, for a "read intensive query", do you mean a large table scan? If so,
it's possible that fewer disk requests might be performed than you're
expecting due to disk IO heuristics such as extent reads..
Regards,
Greg Linwood
SQL Server MVP
http://blogs.sqlserver.org.au/blogs/greg_linwood
"Oskar" <Oskar@.discussions.microsoft.com> wrote in message
news:06F4686F-A738-49E5-8DEB-A7EBDA156843@.microsoft.com...
> Hi,
> I did this experiment with an MS SQL Server 2000 SP4, Windows 2000 Server
> SP4:
> - set SQL server buffer cache at 16 MB, restarted;
> - ran a read-intensive query (in terms of SQL Profiler data).
> While the query was running CPU usage remained at or above 80%. But the
> disk
> activity was not so high. Most of the time it remained under 30% despite
> the
> minimal amount of RAM I allocated to the buffer cache. Disk queues were
> also
> short, that is, way below 1 (by a few magnitudes). I'm not talking about
> occasional spikes here.
> So why do I get 10s of millions of (logical) reads in SQL Profiler but
> don't
> see 100% load on a hard disk even with the tiny buffer cache?
> -- Many thanks, Oskar.
>|||On Mon, 8 Jan 2007 09:54:01 -0800, Oskar
<Oskar@.discussions.microsoft.com> wrote:

>If I understood it correctly, your guess is that the data my query is worki
ng
>on is actually only a little bit bigger than the buffer cache size I
>specified. I find this unlikely.
I'm just guessing, you should be able to tell how much data you're
fetching.
J.
[vbcol=seagreen]
>"JXStern" wrote:
>|||This is actually what I would expect in this case. SQL Server does a lot of
things to optimize disk io. When you are doing pretty sequential reads the
IO subsystem will do read-ahead so a lot of data can be pulled into memory
with a single IO. The issue with low memory then becomes one of
continuously moving data around in the buffer pool to make room for new data
arriving from the disk and building the result set. This requires a lot of
processor cycles. I think what you're missing here (if your description is
correct) is that a lot of buffer space doesn't help you the first time you
run a query. When you scan a table the first time you have to read all the
sectors from disk no matter how much buffer memory you have. Where the
difference comes in is that if you have a lot of memory, the SECOND time you
scan a table, most of the data will be in buffer memory so it won't have to
be read from the disk.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Oskar" <Oskar@.discussions.microsoft.com> wrote in message
news:06F4686F-A738-49E5-8DEB-A7EBDA156843@.microsoft.com...
> Hi,
> I did this experiment with an MS SQL Server 2000 SP4, Windows 2000 Server
> SP4:
> - set SQL server buffer cache at 16 MB, restarted;
> - ran a read-intensive query (in terms of SQL Profiler data).
> While the query was running CPU usage remained at or above 80%. But the
> disk
> activity was not so high. Most of the time it remained under 30% despite
> the
> minimal amount of RAM I allocated to the buffer cache. Disk queues were
> also
> short, that is, way below 1 (by a few magnitudes). I'm not talking about
> occasional spikes here.
> So why do I get 10s of millions of (logical) reads in SQL Profiler but
> don't
> see 100% load on a hard disk even with the tiny buffer cache?
> -- Many thanks, Oskar.
>|||For Physical Disk monitoring use Disk Reads/ Sec & Disk Writes/ Sec coupled
with % Idle (100- %Idle = % Busy) for seeing how busy the disks are.. ..you
can then use Avg Disk Sec/ Read & Avg Disk Sec/ Write to measure the latency
and see if the disk is keeping up, and Avg Read Bytes/Sec & Avg Write
Bytes/Sec to understand the throughput.
You could also monitor Pages/Sec, Pages Input/Sec, Page Reads/Sec, Page
Output/Sec, Page Writes/Sec to see what is happening with regards to memory
and it's relation to the disk..
Check sql counters like Buffer Cache Hit Ratio, Lazy Writes/Sec, Avg Page
Life Expectency, Latch Waits/Sec, Avg Latch Wait Time to see how what your
doing is impacting sql.
Hope this helps
Ben
"Oskar" wrote:
[vbcol=seagreen]
>
> "Ben UK" wrote:
>
> 1) > > ... Windows 2000 Server SP4
> 2) Those are single disks and, yes, I looked at the disks with SQL Server
> databases. Mind you, the activity was there, but it was less than I expect
ed.
> The database files are on a dedicated disk and tempdb is on the OS disk.
>|||This is an interesting point. Because, if I'm right, it implies that it's
better to minimize the amount of the buffer cache allocated to a query that
does a large scan. The query would just waste precious CPU resources for
bringing data into the cache just to toss it out in a short while due to RAM
shortage.
As to the low disk activity: maybe this explains it all:
FROM
http://www.microsoft.com/technet/pr...in/sqlops6.mspx
<<
Note on Disk Queue Length
This counter no longer yields meaningful results in interpreting disk I/O
problems with SQL Server 2000. This is because the SQL Server engines
dynamically manage disk I/O, which is why the Max Async I/O setting no longe
r
exists. A simplified explanation is that SQL Server issues an order for disk
I/O, but does not wait for that process to complete before issuing another
request. When the I/O request has completed its work, the database engine is
notified and processes the results. SQL Server monitors the throughput on
these requests, and will manage the amount of I/O that the disks can
effectively handle.
-- Many thanks, Oskar.
"Roger Wolter[MSFT]" wrote:
[vbcol=seagreen]
> This is actually what I would expect in this case. SQL Server does a lot
of
> things to optimize disk io. When you are doing pretty sequential reads th
e
> IO subsystem will do read-ahead so a lot of data can be pulled into memory
> with a single IO. The issue with low memory then becomes one of
> continuously moving data around in the buffer pool to make room for new da
ta
> arriving from the disk and building the result set. This requires a lot o
f
> processor cycles. I think what you're missing here (if your description i
s
> correct) is that a lot of buffer space doesn't help you the first time you
> run a query. When you scan a table the first time you have to read all th
e
> sectors from disk no matter how much buffer memory you have. Where the
> difference comes in is that if you have a lot of memory, the SECOND time y
ou
> scan a table, most of the data will be in buffer memory so it won't have t
o
> be read from the disk.
> --
> This posting is provided "AS IS" with no warranties, and confers no rights
.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> "Oskar" <Oskar@.discussions.microsoft.com> wrote in message
> news:06F4686F-A738-49E5-8DEB-A7EBDA156843@.microsoft.com...
>
>