Does increasing indexes inclease the amount of memory that SQL server uses?

I was surprised by the above statement as I had assumed that SQL Server
actually performed better if indexes were used on those colums in tables
that are a part of a join, where, or aggregate?
I also assumed that Sql Server would use these indexes which actually
translated to better performance and memory utilization as the index does
not need to be created from scratch and discarded for serving a query?
Am I wrong?
Thanks
SanjayHello Sanjay Pais" spaisatnospammarketlinksolutions.com,
The short answer is no. Since SQL never frees memory unless it has to you
aren't going to be using any more with or without the indexes. You may even
use less if it means you don't have to load the leaf pages of the data and
can instead use the index.
You can also answer this yes if it means that by adding the index to memory
it leaves less space for other things to be put in memory. But this is a
bad argument as if you can use the index correctly it will far outweight
any amount of increased memory.
Aaron Weiker
http://aaronweiker.com/
http://sqlprogrammer.org/

> I was surprised by the above statement as I had assumed that SQL
> Server actually performed better if indexes were used on those colums
> in tables that are a part of a join, where, or aggregate?
> I also assumed that Sql Server would use these indexes which actually
> translated to better performance and memory utilization as the index
> does not need to be created from scratch and discarded for serving a
> query?
> Am I wrong?
> Thanks
> Sanjay
>|||Hee hee.
I knew this would be a yes AND no answer, but I couldn't think of a good way
to say it.
I thin a better question is do indexes allow SQL Server to more efficiently
utilize memory? Properly designed, yes. Badly designed, no.
Darn. Back to yes AND no!
Bob Castleman
DBA Poseur
"Aaron Weiker" <aaron@.sqlprogrammer.org> wrote in message
news:150578632423353497158125@.news.microsoft.com...
> Hello Sanjay Pais" spaisatnospammarketlinksolutions.com,
> The short answer is no. Since SQL never frees memory unless it has to you
> aren't going to be using any more with or without the indexes. You may
> even use less if it means you don't have to load the leaf pages of the
> data and can instead use the index.
> You can also answer this yes if it means that by adding the index to
> memory it leaves less space for other things to be put in memory. But this
> is a bad argument as if you can use the index correctly it will far
> outweight any amount of increased memory.
> --
> Aaron Weiker
> http://aaronweiker.com/
> http://sqlprogrammer.org/
>
>|||Hello Bob,
Think we should bring up the added disk I/O because of the index that needs
to stay in sync?
So anyway. If you wrecklessly add indexes you could significantly increase
the time that it takes to insert/update a record because not only does it
have to modify the leaf table page but it also has to go through and update
all indexes that this row is involved in if the field has changed. This coul
d
then lead to additional page splits.
However if you follow proper indexing guidelines you shouldn't have a proble
m
with this.
Aaron Weiker
http://aaronweiker.com/
http://sqlprogrammer.org/
> Hee hee.
> I knew this would be a yes AND no answer, but I couldn't think of a
> good way to say it.
> I thin a better question is do indexes allow SQL Server to more
> efficiently utilize memory? Properly designed, yes. Badly designed,
> no.
> Darn. Back to yes AND no!
> Bob Castleman
> DBA Poseur
> "Aaron Weiker" <aaron@.sqlprogrammer.org> wrote in message
> news:150578632423353497158125@.news.microsoft.com...
>