Server Memory usage\Performance problem

28 Dec 2005 3:08 PM

Magnus Österberg

I am experiencing the following problem;

I boot my Windows 2000 sp#4 machine to get a fresh machine. The hardware is
Pentium 4, 3 ghz with 512 mb RAM. There are no other heavy applications
running on the machine.

I check sqlservr.exe's memory usage in Task manager. It is ca 20 mb,
everything is OK. Then I run this query;

select postoffice, count(*) as counter from tblTable
where postoffice is not null
group by postoffice
order by counter DESC

There's no index or constraint on field "postoffice" and tblTable contains
ca 1.500.000 rows. I thought this query would still execute in only a few
seconds, but it takes minutes. And the worst thing is that sqlserver.exe's
memory usage grows to about 300-400 mb when the query runs.

I try to add an index for the postoffice-field. No advance!

Some sources have told me to check the disk utilization of the machine when
running the query. Which is the best way of monitoring this?
The disks are two IDE hard drives. SQL Server is installed on c:\ and the
database file itself lays on d:\. The file system is NTFS.

What am I doing wrong or where is the bottleneck in this case?!

/Magnus

28 Dec 2005 3:34 PM

JT

Is this a local install of MSDE or Developer Edition? 512mb RAM is too small
for querying 1.5 million rows from a local server, especially on a desktop
PC with Outlook, AntiVirus and everything else running.

SQL Server will cache read pages into memory, and this can be configured
using the 'min server memory' and 'max server memory' options.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_sa_1zu4.asp
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_sa_40vt.asp

Show quote

"Magnus Österberg" <magnus.osterb***@abo.fi> wrote in message
news:O3iUfC8CGHA.516@TK2MSFTNGP15.phx.gbl...
>I am experiencing the following problem;
>
> I boot my Windows 2000 sp#4 machine to get a fresh machine. The hardware
> is Pentium 4, 3 ghz with 512 mb RAM. There are no other heavy applications
> running on the machine.
>
> I check sqlservr.exe's memory usage in Task manager. It is ca 20 mb,
> everything is OK. Then I run this query;
>
> select postoffice, count(*) as counter from tblTable
> where postoffice is not null
> group by postoffice
> order by counter DESC
>
> There's no index or constraint on field "postoffice" and tblTable contains
> ca 1.500.000 rows. I thought this query would still execute in only a few
> seconds, but it takes minutes. And the worst thing is that sqlserver.exe's
> memory usage grows to about 300-400 mb when the query runs.
>
> I try to add an index for the postoffice-field. No advance!
>
> Some sources have told me to check the disk utilization of the machine
> when running the query. Which is the best way of monitoring this?
> The disks are two IDE hard drives. SQL Server is installed on c:\ and the
> database file itself lays on d:\. The file system is NTFS.
>
> What am I doing wrong or where is the bottleneck in this case?!
>
> /Magnus
>

28 Dec 2005 10:57 PM

Erland Sommarskog

Magnus Österberg (magnus.osterb***@abo.fi) writes:

> I check sqlservr.exe's memory usage in Task manager. It is ca 20 mb,
> everything is OK. Then I run this query;
>
> select postoffice, count(*) as counter from tblTable
> where postoffice is not null
> group by postoffice
> order by counter DESC
>
> There's no index or constraint on field "postoffice" and tblTable contains
> ca 1.500.000 rows. I thought this query would still execute in only a few
> seconds, but it takes minutes. And the worst thing is that sqlserver.exe's
> memory usage grows to about 300-400 mb when the query runs.

That's not the worst thing. That's the good thing, because next time
you run the query, the data is in cache, and it will execute considerably
faster.

Overall, SQL Server is designed to grab as much memory it can, and the
precise reason for that is cache. If there are other apps competing
for memory, SQL Server will yield memory, but it may not yield fast
enough. In this case you can use Enterprise Manager to constrain
how much memory SQL Server may use.

> I try to add an index for the postoffice-field. No advance!

That's strange, as that index would cover the query, so SQL Server
would only have to read that index and not the entire table. Then
again, if postoffice is the only column in the table, it will not
make any difference.

--
Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

31 Dec 2005 4:34 AM

Leythos

In article <O3iUfC8CGHA.***@TK2MSFTNGP15.phx.gbl>,
magnus.osterb***@abo.fi says...
Show quote

> I am experiencing the following problem;
>
> I boot my Windows 2000 sp#4 machine to get a fresh machine. The hardware is
> Pentium 4, 3 ghz with 512 mb RAM. There are no other heavy applications
> running on the machine.
>
> I check sqlservr.exe's memory usage in Task manager. It is ca 20 mb,
> everything is OK. Then I run this query;
>
> select postoffice, count(*) as counter from tblTable
> where postoffice is not null
> group by postoffice
> order by counter DESC
>
> There's no index or constraint on field "postoffice" and tblTable contains
> ca 1.500.000 rows. I thought this query would still execute in only a few
> seconds, but it takes minutes. And the worst thing is that sqlserver.exe's
> memory usage grows to about 300-400 mb when the query runs.
>
> I try to add an index for the postoffice-field. No advance!
>
> Some sources have told me to check the disk utilization of the machine when
> running the query. Which is the best way of monitoring this?
> The disks are two IDE hard drives. SQL Server is installed on c:\ and the
> database file itself lays on d:\. The file system is NTFS.
>
> What am I doing wrong or where is the bottleneck in this case?!

You don't have enough Memory to run a query like that in a fast manner.
If you're going to run SQL (ent) on a machine, you need to have about
2GB just for SQL. Next, you need to be able to have fast drives,
something like a standard SQL server would have (you know, one array for
the transaction logs, one array for the data files, OS on a different
array, etc...).

--

spam999free@rrohio.com
remove 999 in order to email me