1) for a given table that has around 26869141 rows, a query against a
nonindexed column such as << select * from table where somenonindexedcol = 25 >> seems to take a lot of memory as it runs. it must have to do a table
scan and store the results in memory, correct?
2) sql server never releases that memory to the os i think (even with a
max memory limit set)?
3) plus the query has the ability to go past that max limit as well?
4) the solution is to create a nonclustered index on that col, correct?
5) is it ok if i add more than one column to that nonclustered index?
im a developer, so your patience is appreciated.
thxOn Sat, 17 Dec 2005 00:18:12 GMT, usenetjb
<usenet.20.jimbo-black@.antichef.net> wrote:
>1) for a given table that has around 26869141 rows, a query against a
>nonindexed column such as << select * from table where somenonindexedcol =>25 >> seems to take a lot of memory as it runs. it must have to do a table
>scan and store the results in memory, correct?
Well, if it's *around* 26m, then yes. :)
It doesn't store the results in memory, but as long as it has to read
them into memory to look at momentarily, it uses available memory to
cache them, just in case you ask for them again soon.
>2) sql server never releases that memory to the os i think (even with a
>max memory limit set)?
It will release it depending on competing demands.
>3) plus the query has the ability to go past that max limit as well?
If you mean, can your table be larger than your RAM, certainly yes!
>4) the solution is to create a nonclustered index on that col, correct?
Maybe.
It will help if the statistics on the distribution of values is such
that SQLServer thinks it's going to be cheaper to use the index. This
is generally the case if you're going to return just a few rows. But
if your query is going to return more than about 20% of the table, an
index won't help, scans are generally much faster. If the values
being selected are all contiguous, a clustered index may still be
faster than a complete scan. As always, "it depends" is applicable.
>5) is it ok if i add more than one column to that nonclustered index?
OK by me.
SQLServer only remembers the statistics for the first column, and an
index on two columns is less efficient at fetching values selected by
only one column, but if your query is by two fields, putting an index
on both at once is called a "covering index" and becomes an excellent
idea!
>im a developer, so your patience is appreciated.
I hear that.
J.|||jxstern <jxstern@.nowhere.xyz> wrote in
news:t1o6q19libh9i8cucou27cgce5254e52p3@.4ax.com:
> On Sat, 17 Dec 2005 00:18:12 GMT, usenetjb
> <usenet.20.jimbo-black@.antichef.net> wrote:
thx just to confirm
>>1) for a given table that has around 26869141 rows, a query against a
>>nonindexed column such as << select * from table where
>>somenonindexedcol = 25 >> seems to take a lot of memory as it runs.
>>it must have to do a table scan and store the results in memory,
>>correct?
> Well, if it's *around* 26m, then yes. :)
> It doesn't store the results in memory, but as long as it has to read
> them into memory to look at momentarily, it uses available memory to
> cache them, just in case you ask for them again soon.
yeah i understand. that explains why i saw the memory usage go way up
after that query ran.
>>2) sql server never releases that memory to the os i think (even with
>>a max memory limit set)?
> It will release it depending on competing demands.
well, i saw the mem usage go way up to aroun 1.5 gigs, and never really
went down to the pre-query state around 650M. so maybe i'm
misunderstanding something here. i would think that it would go back down
to around 65om after a bit.
>>4) the solution is to create a nonclustered index on that col,
>>correct?
> Maybe.
> It will help if the statistics on the distribution of values is such
> that SQLServer thinks it's going to be cheaper to use the index. This
> is generally the case if you're going to return just a few rows. But
> if your query is going to return more than about 20% of the table, an
> index won't help, scans are generally much faster. If the values
> being selected are all contiguous, a clustered index may still be
> faster than a complete scan. As always, "it depends" is applicable.
it will return probably around 3 to 90 rows at the max. so the index will
probably help me out most in this situation.
>>5) is it ok if i add more than one column to that nonclustered index?
> OK by me.
> SQLServer only remembers the statistics for the first column, and an
> index on two columns is less efficient at fetching values selected by
> only one column, but if your query is by two fields, putting an index
> on both at once is called a "covering index" and becomes an excellent
> idea!
so if i have 2 queries that use 2 diff cols, then its better to create 2
seperate nonclustered indexes?
thx much|||Some comments inline:
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"usenetjb" <usenet.20.jimbo-black@.antichef.net> wrote in message
news:Xns972EE3850DCF4usenetjb@.207.115.17.102...
> jxstern <jxstern@.nowhere.xyz> wrote in
> news:t1o6q19libh9i8cucou27cgce5254e52p3@.4ax.com:
>> On Sat, 17 Dec 2005 00:18:12 GMT, usenetjb
>> <usenet.20.jimbo-black@.antichef.net> wrote:
> thx just to confirm
>>1) for a given table that has around 26869141 rows, a query against a
>>nonindexed column such as << select * from table where
>>somenonindexedcol = 25 >> seems to take a lot of memory as it runs.
>>it must have to do a table scan and store the results in memory,
>>correct?
>> Well, if it's *around* 26m, then yes. :)
>> It doesn't store the results in memory, but as long as it has to read
>> them into memory to look at momentarily, it uses available memory to
>> cache them, just in case you ask for them again soon.
> yeah i understand. that explains why i saw the memory usage go way up
> after that query ran.
>
>>2) sql server never releases that memory to the os i think (even with
>>a max memory limit set)?
>> It will release it depending on competing demands.
> well, i saw the mem usage go way up to aroun 1.5 gigs, and never really
> went down to the pre-query state around 650M. so maybe i'm
> misunderstanding something here. i would think that it would go back down
> to around 65om after a bit.
Why? How would SQL Server know that you won't refer to those particular pages soon again? The
purpose of caching data is for the data to be in cache next tome someone refers to the page. SQL
Server will not release pages *unless the machine is memory constrained*. See:
http://support.microsoft.com/default.aspx?scid=kb;en-us;q321363
http://www.mssqlserver.com/faq/troubleshooting-memoryleak.asp
>>4) the solution is to create a nonclustered index on that col,
>>correct?
>> Maybe.
>> It will help if the statistics on the distribution of values is such
>> that SQLServer thinks it's going to be cheaper to use the index. This
>> is generally the case if you're going to return just a few rows. But
>> if your query is going to return more than about 20% of the table, an
>> index won't help, scans are generally much faster. If the values
>> being selected are all contiguous, a clustered index may still be
>> faster than a complete scan. As always, "it depends" is applicable.
> it will return probably around 3 to 90 rows at the max. so the index will
> probably help me out most in this situation.
>
>>5) is it ok if i add more than one column to that nonclustered index?
>> OK by me.
>> SQLServer only remembers the statistics for the first column, and an
>> index on two columns is less efficient at fetching values selected by
>> only one column, but if your query is by two fields, putting an index
>> on both at once is called a "covering index" and becomes an excellent
>> idea!
> so if i have 2 queries that use 2 diff cols, then its better to create 2
> seperate nonclustered indexes?
Possibly. You create indexes to support your queries. In order to comment what is best for you we
would know what the queries you want to support look like.
> thx much
. Your text says "any rows with the same key_1 and a