473,378 Members | 1,346 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,378 software developers and data experts.

Memory usage\Performance problem

I am experiencing the following problem;

I boot my Windows 2000 sp#4 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 916.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. What am I doing
wrong??

Tnx in advance!

/Magnus
Aug 29 '05 #1
7 4978
Magnus Österberg (ma**************@abo.fi) writes:
I am experiencing the following problem;

I boot my Windows 2000 sp#4 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 916.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. What am I doing wrong??


So what is the average row size of this table? Say that is 300 bytes,
then that is 300 MB of data to read. That is not very likely to be done
instantly.

SQL Server's memory consumption will increase, as it will read the entire
table into cache, and the table will stay in the cache as long as no
other data competes about the space. This means that if you resubmit the
query, the response time will be significantly shorter.

SQL Server is designed to grab as much memory it can, as the more data
in can have in cache, the better the response times. If there are other
applications competing for memory on the machine, SQL Server will yield,
but in this case it may be better to configure how much memory you want
SQL Server to use.

Note also that framgmenation could cause extra delay. Use DBCC SHOWCONTIG
to see what shape the table is in. To defragment it, you would have to
create a clustered index on the table, and then drop that index.

The query itself would benefit enormously by a non-clustered index on
postoffice.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

Aug 29 '05 #2

"Erland Sommarskog" <es****@sommarskog.se> wrote in message
news:Xn**********************@127.0.0.1...
Magnus Österberg (ma**************@abo.fi) writes:
I am experiencing the following problem;

I boot my Windows 2000 sp#4 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 916.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. What am I doing wrong??


So what is the average row size of this table? Say that is 300 bytes,
then that is 300 MB of data to read. That is not very likely to be done
instantly.

SQL Server's memory consumption will increase, as it will read the entire
table into cache, and the table will stay in the cache as long as no
other data competes about the space. This means that if you resubmit the
query, the response time will be significantly shorter.

SQL Server is designed to grab as much memory it can, as the more data
in can have in cache, the better the response times. If there are other
applications competing for memory on the machine, SQL Server will yield,
but in this case it may be better to configure how much memory you want
SQL Server to use.

Note also that framgmenation could cause extra delay. Use DBCC SHOWCONTIG
to see what shape the table is in. To defragment it, you would have to
create a clustered index on the table, and then drop that index.

The query itself would benefit enormously by a non-clustered index on
postoffice.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp


Thanks for a well-written answer!

I guess my expectations on selecting based on a non-indexed column were
somewhat high. When I added an index, my query executes in seconds.
One index solved this problem, but my table contains 30-40 similar columns,
and I don't think indexing every column is a good idea.

Isn't there any other ways of speeding up selects? Well, I guess not...

/Magnus
Aug 29 '05 #3
Magnus Österberg (ma**************@abo.fi) writes:
I guess my expectations on selecting based on a non-indexed column were
somewhat high. When I added an index, my query executes in seconds. One
index solved this problem, but my table contains 30-40 similar columns,
and I don't think indexing every column is a good idea.

Isn't there any other ways of speeding up selects? Well, I guess not...


Well, once data is in cache it will be faster. Or at least less slow.

But if you need to do this on every column, it sounds to me like one
of those things Analysis Services is good for.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

Aug 29 '05 #4
Magnus Österberg wrote:
I guess my expectations on selecting based on a non-indexed column
were somewhat high. When I added an index, my query executes in
seconds.
One index solved this problem, but my table contains 30-40 similar
columns, and I don't think indexing every column is a good idea.
Two things come to mind:

1. rethink your table design. If these columns are so similar you might
be better off with a second table which is joined. But that of course
depends on your data - just an idea.

2. Create indexes (possibly composite indexes) for most used queries. You
might even get away with a single (or few) composite index if your queries
only use a leading portion of this index's fields.
Isn't there any other ways of speeding up selects? Well, I guess
not...


Well, there are tons of other options, like having data files on several
disks, putting tx log on a separate disk, adding memory, .... It all
depends. :-)

Kind regards

robert

Aug 29 '05 #5
Thank you Robert, these all are things that I'll keep in mind.

One more thing though; why is the sqlservr.exe process consuming 395 mb RAM
when the entire server is idle?
My "cron job" is inserting a few 1000 rows of data at night, but now it is
late morning here in Finland, and the entire machine is more or less idle.
On my other servers, the RAM consumption on idle is about 20-30 mb only.
Anyone got any ideas?

/Magnus

"Robert Klemme" <bo******@gmx.net> wrote in message
news:3n************@individual.net...
Magnus Österberg wrote:
I guess my expectations on selecting based on a non-indexed column
were somewhat high. When I added an index, my query executes in
seconds.
One index solved this problem, but my table contains 30-40 similar
columns, and I don't think indexing every column is a good idea.


Two things come to mind:

1. rethink your table design. If these columns are so similar you might
be better off with a second table which is joined. But that of course
depends on your data - just an idea.

2. Create indexes (possibly composite indexes) for most used queries. You
might even get away with a single (or few) composite index if your queries
only use a leading portion of this index's fields.
Isn't there any other ways of speeding up selects? Well, I guess
not...


Well, there are tons of other options, like having data files on several
disks, putting tx log on a separate disk, adding memory, .... It all
depends. :-)

Kind regards

robert

Aug 31 '05 #6
Magnus Österberg (ma**************@abo.fi) writes:
One more thing though; why is the sqlservr.exe process consuming 395 mb
RAM when the entire server is idle?


I believe that was in my first reply.

SQL Server is designed to get as much memory as it can, and only yield
if an another application needs it. This is because it keeps data in
cache so that future requests for the same data can be answered without
reading from disk.

Thus, this is perfectly normal behaviour.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

Aug 31 '05 #7
Erland Sommarskog wrote:
Magnus Österberg (ma**************@abo.fi) writes:
One more thing though; why is the sqlservr.exe process consuming 395
mb RAM when the entire server is idle?


I believe that was in my first reply.

SQL Server is designed to get as much memory as it can, and only yield
if an another application needs it. This is because it keeps data in
cache so that future requests for the same data can be answered
without reading from disk.

Thus, this is perfectly normal behaviour.


Adding to that max memory consumption is easily configurable so if 400MB
is too much for you then simply turn that down.

Kind regards

robert

Aug 31 '05 #8

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
by: Andreas Suurkuusk | last post by:
Hi, I just noticed your post in the "C# memory problem: no end for our problem?" thread. In the post you implied that I do not how the garbage collector works and that I mislead people. Since...
4
by: Amadeus | last post by:
Hello Everybody! I have a problem with MySQL servers running RedHat 9 (smp kernel 2.4.20) on Intel and MySQL server 4.0.14 (problem also appears on binary distr 4.0.15 and on 4.0.15 I bilt myself...
32
by: John | last post by:
Hi all: When I run my code, I find that the memory that the code uses keeps increasing. I have a PC with 2G RAM running Debian linux. The code consumes 1.5G memory by the time it finishes...
17
by: José Joye | last post by:
Hi, I have implemented a Service that is responsible for getting messages from a MS MQ located on a remote machine. I'm getting memory leak from time to time (???). In some situation, it is...
16
by: JCauble | last post by:
We have a large Asp.net application that is currently crashing our production servers. What we are seeing is the aspnet_wp eat up a bunch of memory and then stop unexpectedly. Does not recycle. ...
0
by: monika.saxena | last post by:
Hi all, In one of my projects which is a web based application in asp.net, a third party tool - "Frontline Solver DLL" (It is an unmanaged DLL and ..NET is calling it using the PInvoke) is used....
7
by: Salvador | last post by:
Hi, I am using WMI to gather information about different computers (using win2K and win 2K3), checking common classes and also WMI load balance. My application runs every 1 minute and reports...
9
by: Bruno Barberi Gnecco | last post by:
I'm using PHP to run a CLI application. It's a script run by cron that parses some HTML files (with DOM XML), and I ended up using PHP to integrate with the rest of the code that already runs the...
9
by: jeungster | last post by:
Hello, I'm trying to track down a memory issue with a C++ application that I'm working on: In a nutshell, the resident memory usage of my program continues to grow as the program runs. It...
17
by: frederic.pica | last post by:
Greets, I've some troubles getting my memory freed by python, how can I force it to release the memory ? I've tried del and gc.collect() with no success. Here is a code sample, parsing an XML...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: ryjfgjl | last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
0
by: taylorcarr | last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
0
by: aa123db | last post by:
Variable and constants Use var or let for variables and const fror constants. Var foo ='bar'; Let foo ='bar';const baz ='bar'; Functions function $name$ ($parameters$) { } ...
0
by: ryjfgjl | last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
0
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
0
BarryA
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
0
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.