MS SQL Server - Server Memory usage\Performance problem

Author

Server Memory usage\Performance problem

Magnus Österberg

2005-12-28, 9:23 am

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

2005-12-28, 11:23 am

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/d..._ar_sa_1zu4.asp
http://msdn.microsoft.com/library/d..._ar_sa_40vt.asp

"Magnus Österberg" <magnus.osterberg@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
>

Erland Sommarskog

2005-12-28, 8:23 pm

Magnus Österberg (magnus.osterberg@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, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Magnus Österberg

2005-12-29, 3:23 am

It is a MS-SQL 2000 Enterprise Edition with SP#4.

Why doesn't MS-SQL free memory that has been read into RAM? Caching is a
nice thing, but one can't cache everything in 512 mb. Can I tweak it to free
up memory on a more frequent basis?
What about the disk utilization issue, anything can be done there?

How much RAM would you recommend for this machine? There is no Outlook
running, but Anti-Virus is running yes.

"JT" <someone@microsoft.com> wrote in message
news:uJHUzS8CGHA.1124@TK2MSFTNGP10.phx.gbl...
> 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/d..._ar_sa_1zu4.asp
> http://msdn.microsoft.com/library/d..._ar_sa_40vt.asp
>
>
>
> "Magnus Österberg" <magnus.osterberg@abo.fi> wrote in message
> news:O3iUfC8CGHA.516@TK2MSFTNGP15.phx.gbl...
>
>

Leythos

2005-12-31, 3:23 am

In article <O3iUfC8CGHA.516@TK2MSFTNGP15.phx.gbl>,
magnus.osterberg@abo.fi says...
> 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

Sponsored Links