High performance isn't always an advantage...

Hello
MSSQL 2000 + SP3 on Windows 2000 AS + SP4
I've moved my databases to new server.
IO subsystem now TEN times faster!
I have the system of two jobs performing defragmentation
of my data. One of the jobs issues DBCC INDEXDEFRAG
statements and the second one monitors state of transaction
log and stops first job if log more than 70% full. SQLAgent
can run jobs only with 1 minute intervals. So I can monitor
log status only once in minute. Everything was all right at old
server. Now on new hardware log overfills in couple of seconds
and monitoring job can't catch the moment of overfilling and
correctly stop the defragmentation.
Two questions:
1. Should I increase Tlog size depending on hardware performance
2. Is there any way to monitor log state more ofter than once
in a minute?
Thanks.
Serge ShakhovYou can have a job which uses an endless loop and WAITFOR with, say, 10 seconds.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"Serge Shakhov" <REMOVETHIS_ACETYLENE@.mail.ru> wrote in message
news:83ndmb.5kr.ln@.proxyserver.ctd.mmk.chel.su...
> Hello
> MSSQL 2000 + SP3 on Windows 2000 AS + SP4
> I've moved my databases to new server.
> IO subsystem now TEN times faster!
> I have the system of two jobs performing defragmentation
> of my data. One of the jobs issues DBCC INDEXDEFRAG
> statements and the second one monitors state of transaction
> log and stops first job if log more than 70% full. SQLAgent
> can run jobs only with 1 minute intervals. So I can monitor
> log status only once in minute. Everything was all right at old
> server. Now on new hardware log overfills in couple of seconds
> and monitoring job can't catch the moment of overfilling and
> correctly stop the defragmentation.
> Two questions:
> 1. Should I increase Tlog size depending on hardware performance
> 2. Is there any way to monitor log state more ofter than once
> in a minute?
> Thanks.
> Serge Shakhov
>|||Hello
> You can have a job which uses an endless loop and WAITFOR with, say, 10
seconds.
That's a good idea!
But I'll try to avoid cycling process by executing every minute job wich
will last 58-59 seconds and perform checks say every 5 seconds...
Thank you Tibor.
Serge Shakhovsql

High paging but plenty of memory available

I have a Win2003/SQL2000 SP4 development server with 4GB of memory. It has
been built with two instances of SQL Server and is also running Virtual
Server. The two SQL instances have been configured to take as much memory as
they want.
I've restored a database from our live system which is also Win2003/SQL2000
SP4 but only has 3GB of memor. The problem is that a query which takes less
than 5 mins on the live system takes over 1 hour on the dev box.
I've checked various counters while this query is running and the one
counter which stands out is "Memory: Page Reads/Sec" which is consistently
above 100 on the dev box but remains at 0 on the live server. However, on the
dev server, the "Memory: Available MBytes" = 2842 and the
"MSSQL$INST01:Target SQL Memory (KB)" and "MSSQL$INST01:Total Server memory
(KB)" are both 2724520 which would make me think there is plenty of memory
and no need for excesive paging.
On the live server, "Memory: Available MBytes" = 104 and the
"MSSQL$INST01:Target SQL Memory (KB)" =874128 while "MSSQL$INST01:Total
Server memory (KB)" = 865384.
DBA72 wrote:
> I have a Win2003/SQL2000 SP4 development server with 4GB of memory.
> It has been built with two instances of SQL Server and is also
> running Virtual Server. The two SQL instances have been configured to
> take as much memory as they want.
> I've restored a database from our live system which is also
> Win2003/SQL2000 SP4 but only has 3GB of memor. The problem is that a
> query which takes less than 5 mins on the live system takes over 1
> hour on the dev box.
> I've checked various counters while this query is running and the one
> counter which stands out is "Memory: Page Reads/Sec" which is
> consistently above 100 on the dev box but remains at 0 on the live
> server. However, on the dev server, the "Memory: Available MBytes" =
> 2842 and the "MSSQL$INST01:Target SQL Memory (KB)" and
> "MSSQL$INST01:Total Server memory (KB)" are both 2724520 which would
> make me think there is plenty of memory and no need for excesive
> paging.
> On the live server, "Memory: Available MBytes" = 104 and the
> "MSSQL$INST01:Target SQL Memory (KB)" =874128 while
> "MSSQL$INST01:Total Server memory (KB)" = 865384.
When running multiple instances it is recommended to specify a maximum
amount of memory for each instance so they don't consume too much. You
didn't mention what edition of SQL Server and the OS you are running on
the dev box. Assuming standard edition, you have 4GB for the OS, two
instances, and virtual server. I don't know how much memory Virtual
Server is using. Once you figure out how much it needs and how much the
OS needs (1GB would be a good start), you can split the remainder
between the two SQL instances.
It's very possible you have a lot of contention for memory on the box.
David Gugick
Quest Software
www.imceda.com
www.quest.com

High paging but plenty of memory available

I have a Win2003/SQL2000 SP4 development server with 4GB of memory. It has
been built with two instances of SQL Server and is also running Virtual
Server. The two SQL instances have been configured to take as much memory as
they want.
I've restored a database from our live system which is also Win2003/SQL2000
SP4 but only has 3GB of memor. The problem is that a query which takes less
than 5 mins on the live system takes over 1 hour on the dev box.
I've checked various counters while this query is running and the one
counter which stands out is "Memory: Page Reads/Sec" which is consistently
above 100 on the dev box but remains at 0 on the live server. However, on the
dev server, the "Memory: Available MBytes" = 2842 and the
"MSSQL$INST01:Target SQL Memory (KB)" and "MSSQL$INST01:Total Server memory
(KB)" are both 2724520 which would make me think there is plenty of memory
and no need for excesive paging.
On the live server, "Memory: Available MBytes" = 104 and the
"MSSQL$INST01:Target SQL Memory (KB)" =874128 while "MSSQL$INST01:Total
Server memory (KB)" = 865384.DBA72 wrote:
> I have a Win2003/SQL2000 SP4 development server with 4GB of memory.
> It has been built with two instances of SQL Server and is also
> running Virtual Server. The two SQL instances have been configured to
> take as much memory as they want.
> I've restored a database from our live system which is also
> Win2003/SQL2000 SP4 but only has 3GB of memor. The problem is that a
> query which takes less than 5 mins on the live system takes over 1
> hour on the dev box.
> I've checked various counters while this query is running and the one
> counter which stands out is "Memory: Page Reads/Sec" which is
> consistently above 100 on the dev box but remains at 0 on the live
> server. However, on the dev server, the "Memory: Available MBytes" => 2842 and the "MSSQL$INST01:Target SQL Memory (KB)" and
> "MSSQL$INST01:Total Server memory (KB)" are both 2724520 which would
> make me think there is plenty of memory and no need for excesive
> paging.
> On the live server, "Memory: Available MBytes" = 104 and the
> "MSSQL$INST01:Target SQL Memory (KB)" =874128 while
> "MSSQL$INST01:Total Server memory (KB)" = 865384.
When running multiple instances it is recommended to specify a maximum
amount of memory for each instance so they don't consume too much. You
didn't mention what edition of SQL Server and the OS you are running on
the dev box. Assuming standard edition, you have 4GB for the OS, two
instances, and virtual server. I don't know how much memory Virtual
Server is using. Once you figure out how much it needs and how much the
OS needs (1GB would be a good start), you can split the remainder
between the two SQL instances.
It's very possible you have a lot of contention for memory on the box.
David Gugick
Quest Software
www.imceda.com
www.quest.com

High paging but plenty of memory available

I have a Win2003/SQL2000 SP4 development server with 4GB of memory. It has
been built with two instances of SQL Server and is also running Virtual
Server. The two SQL instances have been configured to take as much memory as
they want.
I've restored a database from our live system which is also Win2003/SQL2000
SP4 but only has 3GB of memor. The problem is that a query which takes less
than 5 mins on the live system takes over 1 hour on the dev box.
I've checked various counters while this query is running and the one
counter which stands out is "Memory: Page Reads/Sec" which is consistently
above 100 on the dev box but remains at 0 on the live server. However, on th
e
dev server, the "Memory: Available MBytes" = 2842 and the
"MSSQL$INST01:Target SQL Memory (KB)" and "MSSQL$INST01:Total Server memory
(KB)" are both 2724520 which would make me think there is plenty of memory
and no need for excesive paging.
On the live server, "Memory: Available MBytes" = 104 and the
"MSSQL$INST01:Target SQL Memory (KB)" =874128 while "MSSQL$INST01:Total
Server memory (KB)" = 865384.DBA72 wrote:
> I have a Win2003/SQL2000 SP4 development server with 4GB of memory.
> It has been built with two instances of SQL Server and is also
> running Virtual Server. The two SQL instances have been configured to
> take as much memory as they want.
> I've restored a database from our live system which is also
> Win2003/SQL2000 SP4 but only has 3GB of memor. The problem is that a
> query which takes less than 5 mins on the live system takes over 1
> hour on the dev box.
> I've checked various counters while this query is running and the one
> counter which stands out is "Memory: Page Reads/Sec" which is
> consistently above 100 on the dev box but remains at 0 on the live
> server. However, on the dev server, the "Memory: Available MBytes" =
> 2842 and the "MSSQL$INST01:Target SQL Memory (KB)" and
> "MSSQL$INST01:Total Server memory (KB)" are both 2724520 which would
> make me think there is plenty of memory and no need for excesive
> paging.
> On the live server, "Memory: Available MBytes" = 104 and the
> "MSSQL$INST01:Target SQL Memory (KB)" =874128 while
> "MSSQL$INST01:Total Server memory (KB)" = 865384.
When running multiple instances it is recommended to specify a maximum
amount of memory for each instance so they don't consume too much. You
didn't mention what edition of SQL Server and the OS you are running on
the dev box. Assuming standard edition, you have 4GB for the OS, two
instances, and virtual server. I don't know how much memory Virtual
Server is using. Once you figure out how much it needs and how much the
OS needs (1GB would be a good start), you can split the remainder
between the two SQL instances.
It's very possible you have a lot of contention for memory on the box.
David Gugick
Quest Software
www.imceda.com
www.quest.com

High file i/o

SQL Server 2000 SP4 running on Windows Server 2003 with two RAID 5 disk
groups, A and B.
I've done some monitoring using fn_virtualfilestats() and determined
that of the five databases on my SQL Server, the "Fred" database's data
file is getting a great majority of the reads and writes of all the
data files on disk group A. During peak periods, disk group A's average
disk queue length is 14 compared with disk group B's average of 2. As
you can see, disk group A, where Fred's data file resides, is getting
hammered!
Now that I know this, I'd like to spread disk group A's i/o out over
these two disks groups by creating another filegroup for the Fred
database on disk group B and moving certain high i/o tables and/or
indexes to it. How can I determine which table(s) and/or index(es)
would be good candidates for this? The best I've determined so far is
to take an educated guess, but I would prefer to see some real i/o
numbers at the table level. Is this possible?
Thanks,
AaronMy first question for you is where are your transaction log files? In my
experience, moving the transaction logs to their own device offers the
biggest performance improvement. Next would be moving your nonclustered
indexes to their own filegroup on a separate device.
"Aaron S" <gcsdba1@.yahoo.com> wrote in message
news:1154308389.902467.305860@.i42g2000cwa.googlegroups.com...
> SQL Server 2000 SP4 running on Windows Server 2003 with two RAID 5 disk
> groups, A and B.
> I've done some monitoring using fn_virtualfilestats() and determined
> that of the five databases on my SQL Server, the "Fred" database's data
> file is getting a great majority of the reads and writes of all the
> data files on disk group A. During peak periods, disk group A's average
> disk queue length is 14 compared with disk group B's average of 2. As
> you can see, disk group A, where Fred's data file resides, is getting
> hammered!
> Now that I know this, I'd like to spread disk group A's i/o out over
> these two disks groups by creating another filegroup for the Fred
> database on disk group B and moving certain high i/o tables and/or
> indexes to it. How can I determine which table(s) and/or index(es)
> would be good candidates for this? The best I've determined so far is
> to take an educated guess, but I would prefer to see some real i/o
> numbers at the table level. Is this possible?
> Thanks,
> Aaron
>|||"Aaron S" <gcsdba1@.yahoo.com> wrote in message
news:1154308389.902467.305860@.i42g2000cwa.googlegroups.com...
> SQL Server 2000 SP4 running on Windows Server 2003 with two RAID 5 disk
> groups, A and B.
> I've done some monitoring using fn_virtualfilestats() and determined
> that of the five databases on my SQL Server, the "Fred" database's data
> file is getting a great majority of the reads and writes of all the
> data files on disk group A. During peak periods, disk group A's average
> disk queue length is 14 compared with disk group B's average of 2. As
> you can see, disk group A, where Fred's data file resides, is getting
> hammered!
> Now that I know this, I'd like to spread disk group A's i/o out over
> these two disks groups by creating another filegroup for the Fred
> database on disk group B and moving certain high i/o tables and/or
> indexes to it. How can I determine which table(s) and/or index(es)
> would be good candidates for this? The best I've determined so far is
> to take an educated guess, but I would prefer to see some real i/o
> numbers at the table level. Is this possible?
>
In 2000 I'm not sure. But it's a bad deal anyway. You'll forever be
tweaking the placement of objects on filegroups. If you place the object on
a file group having one file on each volume, SQL Server will automatically
balance space (and traffic) between the files and thus the volumes.
David|||Aaron
Be aware , that you 'll be benefit from the perfomance issue only if you
move the file to the filegropup that located on another physical disk.

> indexes to it. How can I determine which table(s) and/or index(es)
> would be good candidates for this?
Run SQL Server Profiler to see what is going on.
"Aaron S" <gcsdba1@.yahoo.com> wrote in message
news:1154308389.902467.305860@.i42g2000cwa.googlegroups.com...
> SQL Server 2000 SP4 running on Windows Server 2003 with two RAID 5 disk
> groups, A and B.
> I've done some monitoring using fn_virtualfilestats() and determined
> that of the five databases on my SQL Server, the "Fred" database's data
> file is getting a great majority of the reads and writes of all the
> data files on disk group A. During peak periods, disk group A's average
> disk queue length is 14 compared with disk group B's average of 2. As
> you can see, disk group A, where Fred's data file resides, is getting
> hammered!
> Now that I know this, I'd like to spread disk group A's i/o out over
> these two disks groups by creating another filegroup for the Fred
> database on disk group B and moving certain high i/o tables and/or
> indexes to it. How can I determine which table(s) and/or index(es)
> would be good candidates for this? The best I've determined so far is
> to take an educated guess, but I would prefer to see some real i/o
> numbers at the table level. Is this possible?
> Thanks,
> Aaron
>|||Aaron S wrote:
> SQL Server 2000 SP4 running on Windows Server 2003 with two RAID 5 disk
> groups, A and B.
> I've done some monitoring using fn_virtualfilestats() and determined
> that of the five databases on my SQL Server, the "Fred" database's data
> file is getting a great majority of the reads and writes of all the
> data files on disk group A. During peak periods, disk group A's average
> disk queue length is 14 compared with disk group B's average of 2. As
> you can see, disk group A, where Fred's data file resides, is getting
> hammered!
> Now that I know this, I'd like to spread disk group A's i/o out over
> these two disks groups by creating another filegroup for the Fred
> database on disk group B and moving certain high i/o tables and/or
> indexes to it. How can I determine which table(s) and/or index(es)
> would be good candidates for this? The best I've determined so far is
> to take an educated guess, but I would prefer to see some real i/o
> numbers at the table level. Is this possible?
> Thanks,
> Aaron
>
More than likely, you're seeing the result of missing or inadequate
indexes. Monitor the SQL Server:Access Methods -> Full Scans/sec
counter, and then use Profiler to determine which queries are producing
the most Reads. Pick the worst offender, focus on optimizing that query
(through indexing, rewrites, etc). Rinse, repeat...
Tracy McKibben
MCDBA
http://www.realsqlguy.comsql

High file i/o

SQL Server 2000 SP4 running on Windows Server 2003 with two RAID 5 disk
groups, A and B.
I've done some monitoring using fn_virtualfilestats() and determined
that of the five databases on my SQL Server, the "Fred" database's data
file is getting a great majority of the reads and writes of all the
data files on disk group A. During peak periods, disk group A's average
disk queue length is 14 compared with disk group B's average of 2. As
you can see, disk group A, where Fred's data file resides, is getting
hammered!
Now that I know this, I'd like to spread disk group A's i/o out over
these two disks groups by creating another filegroup for the Fred
database on disk group B and moving certain high i/o tables and/or
indexes to it. How can I determine which table(s) and/or index(es)
would be good candidates for this? The best I've determined so far is
to take an educated guess, but I would prefer to see some real i/o
numbers at the table level. Is this possible?
Thanks,
AaronMy first question for you is where are your transaction log files? In my
experience, moving the transaction logs to their own device offers the
biggest performance improvement. Next would be moving your nonclustered
indexes to their own filegroup on a separate device.
"Aaron S" <gcsdba1@.yahoo.com> wrote in message
news:1154308389.902467.305860@.i42g2000cwa.googlegroups.com...
> SQL Server 2000 SP4 running on Windows Server 2003 with two RAID 5 disk
> groups, A and B.
> I've done some monitoring using fn_virtualfilestats() and determined
> that of the five databases on my SQL Server, the "Fred" database's data
> file is getting a great majority of the reads and writes of all the
> data files on disk group A. During peak periods, disk group A's average
> disk queue length is 14 compared with disk group B's average of 2. As
> you can see, disk group A, where Fred's data file resides, is getting
> hammered!
> Now that I know this, I'd like to spread disk group A's i/o out over
> these two disks groups by creating another filegroup for the Fred
> database on disk group B and moving certain high i/o tables and/or
> indexes to it. How can I determine which table(s) and/or index(es)
> would be good candidates for this? The best I've determined so far is
> to take an educated guess, but I would prefer to see some real i/o
> numbers at the table level. Is this possible?
> Thanks,
> Aaron
>|||"Aaron S" <gcsdba1@.yahoo.com> wrote in message
news:1154308389.902467.305860@.i42g2000cwa.googlegroups.com...
> SQL Server 2000 SP4 running on Windows Server 2003 with two RAID 5 disk
> groups, A and B.
> I've done some monitoring using fn_virtualfilestats() and determined
> that of the five databases on my SQL Server, the "Fred" database's data
> file is getting a great majority of the reads and writes of all the
> data files on disk group A. During peak periods, disk group A's average
> disk queue length is 14 compared with disk group B's average of 2. As
> you can see, disk group A, where Fred's data file resides, is getting
> hammered!
> Now that I know this, I'd like to spread disk group A's i/o out over
> these two disks groups by creating another filegroup for the Fred
> database on disk group B and moving certain high i/o tables and/or
> indexes to it. How can I determine which table(s) and/or index(es)
> would be good candidates for this? The best I've determined so far is
> to take an educated guess, but I would prefer to see some real i/o
> numbers at the table level. Is this possible?
>
In 2000 I'm not sure. But it's a bad deal anyway. You'll forever be
tweaking the placement of objects on filegroups. If you place the object on
a file group having one file on each volume, SQL Server will automatically
balance space (and traffic) between the files and thus the volumes.
David|||Aaron
Be aware , that you 'll be benefit from the perfomance issue only if you
move the file to the filegropup that located on another physical disk.
> indexes to it. How can I determine which table(s) and/or index(es)
> would be good candidates for this?
Run SQL Server Profiler to see what is going on.
"Aaron S" <gcsdba1@.yahoo.com> wrote in message
news:1154308389.902467.305860@.i42g2000cwa.googlegroups.com...
> SQL Server 2000 SP4 running on Windows Server 2003 with two RAID 5 disk
> groups, A and B.
> I've done some monitoring using fn_virtualfilestats() and determined
> that of the five databases on my SQL Server, the "Fred" database's data
> file is getting a great majority of the reads and writes of all the
> data files on disk group A. During peak periods, disk group A's average
> disk queue length is 14 compared with disk group B's average of 2. As
> you can see, disk group A, where Fred's data file resides, is getting
> hammered!
> Now that I know this, I'd like to spread disk group A's i/o out over
> these two disks groups by creating another filegroup for the Fred
> database on disk group B and moving certain high i/o tables and/or
> indexes to it. How can I determine which table(s) and/or index(es)
> would be good candidates for this? The best I've determined so far is
> to take an educated guess, but I would prefer to see some real i/o
> numbers at the table level. Is this possible?
> Thanks,
> Aaron
>|||Aaron S wrote:
> SQL Server 2000 SP4 running on Windows Server 2003 with two RAID 5 disk
> groups, A and B.
> I've done some monitoring using fn_virtualfilestats() and determined
> that of the five databases on my SQL Server, the "Fred" database's data
> file is getting a great majority of the reads and writes of all the
> data files on disk group A. During peak periods, disk group A's average
> disk queue length is 14 compared with disk group B's average of 2. As
> you can see, disk group A, where Fred's data file resides, is getting
> hammered!
> Now that I know this, I'd like to spread disk group A's i/o out over
> these two disks groups by creating another filegroup for the Fred
> database on disk group B and moving certain high i/o tables and/or
> indexes to it. How can I determine which table(s) and/or index(es)
> would be good candidates for this? The best I've determined so far is
> to take an educated guess, but I would prefer to see some real i/o
> numbers at the table level. Is this possible?
> Thanks,
> Aaron
>
More than likely, you're seeing the result of missing or inadequate
indexes. Monitor the SQL Server:Access Methods -> Full Scans/sec
counter, and then use Profiler to determine which queries are producing
the most Reads. Pick the worst offender, focus on optimizing that query
(through indexing, rewrites, etc). Rinse, repeat...
Tracy McKibben
MCDBA
http://www.realsqlguy.com

High CPU over extended time period

Hello,
We are running SQL 2000 sp4 with full text search, recently we are
getting 100% CPU over extended period of time and the only way to
resolve this is by killing the Mssearch.exe process. (we tried to
restart the service but it hang) the Microsoft search service is
running under local system account.
our system topology is active/passive cluster with NAS disks, 4GB RAM
and 4 CPU for each server.
May be some one can help me?
The issue happen randomly with no specific job running at that time or
any thing, and when it happen the SQL queries are running very very
slow.
Thank you
Orna Kahil
Orna,
Does the CPU usage drop at some point or does it stay high at all or most
times? If the latter, please re-confirm that the MSSearch service is using
the "system account" (LocalSystem) and if not, change it and restart the
MSSearch service.
If the former, you can set the MSSearch service's to use one CPU and then
use sp_configure to set the affinity of SQL Server to that cpu to avoid cpu
contention during heavy MSSearch CPU usage via the following method - launch
the Tskmgr.exe from the AT command on the multi-proc server where sql server
resides, as follows:
at <current_time+1min> /interactive taskmgr.exe
when it launches, you can then set "cpu affinity" for the MSSearch service
to a cpu or set of cpu's not being used by SQL Server. Then you would use
sp_configure to set SQL Server's cpu affinity to the other cpu's, thus
preventing the cpu usage of mssearch from affecting your sql server
processing. Note, this only works on multi-processor servers and is a
secured method for achieving this feature.
Additionally, as your server has lots of RAM, you can use
sp_fulltext_service 'resource_usage', 5 to set the MSSearch service to 5
(dedicated), and it will use the max amount of RAM (512Mb), but only if the
RAM is not being used by either the OS or by SQL Server.
Finally, I'd highly recommend that you review all the resources "SQL Server
2000 Full-Text Search Resources and Links" at:
http://spaces.msn.com/members/jtkane/Blog/cns!1pWDBCiDX1uvH5ATJmNCVLPQ!305.entry
Regards,
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"or" <orna@.panam.co.il> wrote in message
news:1131446707.073884.89790@.f14g2000cwb.googlegro ups.com...
> Hello,
> We are running SQL 2000 sp4 with full text search, recently we are
> getting 100% CPU over extended period of time and the only way to
> resolve this is by killing the Mssearch.exe process. (we tried to
> restart the service but it hang) the Microsoft search service is
> running under local system account.
> our system topology is active/passive cluster with NAS disks, 4GB RAM
> and 4 CPU for each server.
> May be some one can help me?
> The issue happen randomly with no specific job running at that time or
> any thing, and when it happen the SQL queries are running very very
> slow.
> Thank you
> Orna Kahil
>
|||Hello John and thank you for your quick response.
The CPU usage does not drop it stack on 100%. and do not release till
we kill the process.
we can change the configuration so the MSsearch will use only 1 CPU but
I think the problem is different since it hang in the same position and
just using lots of CPU usage.
The MSSearch service is using the "system account" (LocalSystem). I
looked at the Resources you point at is there any thing specific there?
I forgot to mention that it all worked fine and suddenly on day (we did
not change a thing) it started to happen.
|||You're welcome, Orna,
What is the exact version of SQL Server that you are using? Could you post
the full output of SELECT @.@.version ?
Depending upon your answer, you may need to upgrade to a new/higher Service
Pack (SP) level to get a fix for a possible known bug related to MSSearch &
high CPU usage. The @.@.version info will provide that information.
Additionally, please review your server's Application event log for
"Microsoft Search" and MssCi source events. Especially, the latter (MssCi)
for ALL informational, warnings and error messages as this source event may
indicate what the true problem is and hopefully provide more detail.
Thanks,
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"or" <orna@.panam.co.il> wrote in message
news:1131523025.784794.210480@.g44g2000cwa.googlegr oups.com...
> Hello John and thank you for your quick response.
> The CPU usage does not drop it stack on 100%. and do not release till
> we kill the process.
> we can change the configuration so the MSsearch will use only 1 CPU but
> I think the problem is different since it hang in the same position and
> just using lots of CPU usage.
> The MSSearch service is using the "system account" (LocalSystem). I
> looked at the Resources you point at is there any thing specific there?
> I forgot to mention that it all worked fine and suddenly on day (we did
> not change a thing) it started to happen.
>
|||Hello John,
Sorry I did not reply sooner .
The data from the SQL @.@.version is:
"Microsoft SQL Server 2000 - 8.00.2039 (Intel X86)
May 3 2005 23:18:38
Copyright (c) 1988-2003 Microsoft Corporation
Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
"
and there are no error from the MssCi only information about merging
once a day.
from the Microsoft search we get all sort of this:"Error: Unable to
complete the requested operation because of either a catastrophic media
failure or a data structure corruption on the disk" and some of "An
error occurred in the resource status monitor for instance <SQL Server
Fulltext>: 800706ba - The RPC server is unavailable. "
The SQL drive is located on NAS disks and recently we have problem with
the Snapshot drive (low disk space) the Snapshot drive is separated
from the SQL drive (S:/ sql and I:/ snapshot)
do you think there is any thing to do with the Mssearch problem?
|||No problem, Or,
The errors "Unable to complete the requested operation because of either a
catastrophic media failure or a data structure corruption on the disk" and
"An error occurred in the resource status monitor for instance <SQL Server>
Fulltext>: 800706ba - The RPC server is unavailable" indicate some serious
problem with the Full Text Catalog.
Is the FT Catalog located on the "Snapshot"drive that you are having low
disk space problems? If so, then either free up disk space on this drive or
drop and re-locate the FT Catalog to another local disk drive, but separate
from your SQL drive (S:\ or I:\) as the MSSearch service requires at least
15% of free disk space be available at all times. Additionally, the above
errors indicate serious problems with the existing FT Catalog, so even if
the FT Catalog is not on the "Snapshot" drive, I'd still recommend dropping
and recreating it.
I'd also recommend that you review the FT Deployment white paper and other
FTS related Kb articles at "SQL Server 2000 Full-Text Search Resources and
Links"
http://spaces.msn.com/members/jtkane/Blog/cns!1pWDBCiDX1uvH5ATJmNCVLPQ!305.entry
Regards,
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"or" <orna@.panam.co.il> wrote in message
news:1131874652.316687.171850@.g14g2000cwa.googlegr oups.com...
> Hello John,
> Sorry I did not reply sooner .
> The data from the SQL @.@.version is:
> "Microsoft SQL Server 2000 - 8.00.2039 (Intel X86)
> May 3 2005 23:18:38
> Copyright (c) 1988-2003 Microsoft Corporation
> Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
> "
> and there are no error from the MssCi only information about merging
> once a day.
> from the Microsoft search we get all sort of this:"Error: Unable to
> complete the requested operation because of either a catastrophic media
> failure or a data structure corruption on the disk" and some of "An
> error occurred in the resource status monitor for instance <SQL Server
> Fulltext>: 800706ba - The RPC server is unavailable. "
> The SQL drive is located on NAS disks and recently we have problem with
> the Snapshot drive (low disk space) the Snapshot drive is separated
> from the SQL drive (S:/ sql and I:/ snapshot)
> do you think there is any thing to do with the Mssearch problem?
>
|||Hi John and Thanks again,
The Catalogs located in the SQL drive which mean there is no space
problem, But I think we will follow your advise and rebuild the
catalog, however this will cause down time to our site so maybe you can
help me with estimating the demage.
We have catalog of about 57,000 rows and each row is 33K (max). how
long will it take to rebuild this catalog?
Thanks
Orna
|||You're welcome, Orna,
Depending upon your server's configuration and if you have the FT Catalog
located on a separate drive from the database files, it should take between
1 and 1.5 hours to run to completion...
Hope that helps!
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"or" <orna@.panam.co.il> wrote in message
news:1131966255.893904.253210@.o13g2000cwo.googlegr oups.com...
> Hi John and Thanks again,
> The Catalogs located in the SQL drive which mean there is no space
> problem, But I think we will follow your advise and rebuild the
> catalog, however this will cause down time to our site so maybe you can
> help me with estimating the demage.
> We have catalog of about 57,000 rows and each row is 33K (max). how
> long will it take to rebuild this catalog?
> Thanks
> Orna
>
|||Thank you John for all your help we will rebuild the catalogs and
continue increase our knowledge via the KB you pointed at.
appreciate it
Orna Kahil
|||Hello John,
Well after 3 weeks of quite it happen again, I will summaries our actions
1. Rebuild all catalogs
2. Changed population mode to be change tracking with background index
3. Separate the resources SQL is working on 1 CPU and Mssearch on the other.
4. Fixed the Netapp configuration
5. Looked for errors in all the available logs
And nothing helped suddenly one day , again nothing was done by us the CPU
stabilized and was low this situation continued for aprox 3 week and on this
Sunday again it went high to 100% it did not harm the site functioning
because it is on different CPU but it require manual interference .
the occurrences are not on the same time each day. and we have no claw what
is going on here do you have any suggestion
Thank you
Orna
"John Kane" wrote:

> You're welcome, Orna,
> Depending upon your server's configuration and if you have the FT Catalog
> located on a separate drive from the database files, it should take between
> 1 and 1.5 hours to run to completion...
> Hope that helps!
> John
> --
> SQL Full Text Search Blog
> http://spaces.msn.com/members/jtkane/
>
> "or" <orna@.panam.co.il> wrote in message
> news:1131966255.893904.253210@.o13g2000cwo.googlegr oups.com...
>
>

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...
>
>