Can dbcc shrinkdatabase be performed anytime and will this lead to
blocking,etc. ?There's absolutely no need to run this in single user mode or backup your
master database. The whole point of shrink is that you can run it online.
Shrink will take short term page X locks and table IX locks while it is
moving pages but should not cause any long-term blocking. It is setup so as
to always be picked as the deadlock victim, if a deadlock occurs. What you
may see is shrink itself being blocked for a long period as it will always
wait for the page locks it needs.
Regards,
Paul.
--
Paul Randal
DBCC Technical Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Steve Thompson" <SteveThompson@.nomail.please> wrote in message
news:uhMAa2rWDHA.1004@.TK2MSFTNGP12.phx.gbl...
> "Hassan" <fatima_ja@.hotmail.com> wrote in message
> news:eJ9m0srWDHA.1632@.TK2MSFTNGP11.phx.gbl...
> > Can dbcc shrinkdatabase be performed anytime and will this lead to
> > blocking,etc. ?
> >
> It might -- this operation is one I'd recommend running in single user
mode.
> Be sure to back up master and your application databases when complete.
> Steve
>
Showing posts with label performed. Show all posts
Showing posts with label performed. Show all posts
Thursday, March 22, 2012
Sunday, March 11, 2012
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...
>
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...
>
Subscribe to:
Posts (Atom)