|
|
Posted by Magnus Φsterberg on 08/29/05 15:10
"Erland Sommarskog" <esquel@sommarskog.se> wrote in message
news:Xns96C183A0F9A0DYazorman@127.0.0.1...
> Magnus Φsterberg (magnus.osterberg@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, esquel@sommarskog.se
>
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techinfo/productdoc/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
[Back to original message]
|