Hi,
I am confused whether database shrinking operation also does reindexing or
rebuilding of indexes in background when it runs because, shrink operation
affects the data page location on disk caused by their movement.
Thanks
ManuManu,
Shrinking a database does not redo the indexes, it just moves pages around.
One result is that the shrink can hurt performance since the data needed for
a search may no longer be contiguous, which increases disk activity.
If this is a concern (and normally it should be), shrink first, then rebuild
the indexes. Rebuilding the indexes will give up some of space savings, but
it will make your users happier with the performance.
RLF
"manu" <manu@.discussions.microsoft.com> wrote in message
news:8DD71F01-C93E-4533-B947-1A14A1BE891D@.microsoft.com...
> Hi,
> I am confused whether database shrinking operation also does reindexing or
> rebuilding of indexes in background when it runs because, shrink operation
> affects the data page location on disk caused by their movement.
> Thanks
> Manu|||manu
Start with
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
"manu" <manu@.discussions.microsoft.com> wrote in message
news:8DD71F01-C93E-4533-B947-1A14A1BE891D@.microsoft.com...
> Hi,
> I am confused whether database shrinking operation also does reindexing or
> rebuilding of indexes in background when it runs because, shrink operation
> affects the data page location on disk caused by their movement.
> Thanks
> Manu
Showing posts with label runs. Show all posts
Showing posts with label runs. Show all posts
Wednesday, March 21, 2012
Database shrink operation
Labels:
background,
database,
indexes,
microsoft,
mysql,
operation,
oracle,
orrebuilding,
reindexing,
runs,
server,
shrink,
shrinking,
sql
Database shrink operation
Hi,
I am confused whether database shrinking operation also does reindexing or
rebuilding of indexes in background when it runs because, shrink operation
affects the data page location on disk caused by their movement.
Thanks
ManuManu,
Shrinking a database does not redo the indexes, it just moves pages around.
One result is that the shrink can hurt performance since the data needed for
a search may no longer be contiguous, which increases disk activity.
If this is a concern (and normally it should be), shrink first, then rebuild
the indexes. Rebuilding the indexes will give up some of space savings, but
it will make your users happier with the performance.
RLF
"manu" <manu@.discussions.microsoft.com> wrote in message
news:8DD71F01-C93E-4533-B947-1A14A1BE891D@.microsoft.com...
> Hi,
> I am confused whether database shrinking operation also does reindexing or
> rebuilding of indexes in background when it runs because, shrink operation
> affects the data page location on disk caused by their movement.
> Thanks
> Manu|||manu
Start with
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
"manu" <manu@.discussions.microsoft.com> wrote in message
news:8DD71F01-C93E-4533-B947-1A14A1BE891D@.microsoft.com...
> Hi,
> I am confused whether database shrinking operation also does reindexing or
> rebuilding of indexes in background when it runs because, shrink operation
> affects the data page location on disk caused by their movement.
> Thanks
> Manu
I am confused whether database shrinking operation also does reindexing or
rebuilding of indexes in background when it runs because, shrink operation
affects the data page location on disk caused by their movement.
Thanks
ManuManu,
Shrinking a database does not redo the indexes, it just moves pages around.
One result is that the shrink can hurt performance since the data needed for
a search may no longer be contiguous, which increases disk activity.
If this is a concern (and normally it should be), shrink first, then rebuild
the indexes. Rebuilding the indexes will give up some of space savings, but
it will make your users happier with the performance.
RLF
"manu" <manu@.discussions.microsoft.com> wrote in message
news:8DD71F01-C93E-4533-B947-1A14A1BE891D@.microsoft.com...
> Hi,
> I am confused whether database shrinking operation also does reindexing or
> rebuilding of indexes in background when it runs because, shrink operation
> affects the data page location on disk caused by their movement.
> Thanks
> Manu|||manu
Start with
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
"manu" <manu@.discussions.microsoft.com> wrote in message
news:8DD71F01-C93E-4533-B947-1A14A1BE891D@.microsoft.com...
> Hi,
> I am confused whether database shrinking operation also does reindexing or
> rebuilding of indexes in background when it runs because, shrink operation
> affects the data page location on disk caused by their movement.
> Thanks
> Manu
Labels:
background,
database,
indexes,
microsoft,
mysql,
operation,
oracle,
rebuilding,
reindexing,
runs,
server,
shrink,
shrinking,
sql
Database shrink operation
Hi,
I am confused whether database shrinking operation also does reindexing or
rebuilding of indexes in background when it runs because, shrink operation
affects the data page location on disk caused by their movement.
Thanks
Manu
Manu,
Shrinking a database does not redo the indexes, it just moves pages around.
One result is that the shrink can hurt performance since the data needed for
a search may no longer be contiguous, which increases disk activity.
If this is a concern (and normally it should be), shrink first, then rebuild
the indexes. Rebuilding the indexes will give up some of space savings, but
it will make your users happier with the performance.
RLF
"manu" <manu@.discussions.microsoft.com> wrote in message
news:8DD71F01-C93E-4533-B947-1A14A1BE891D@.microsoft.com...
> Hi,
> I am confused whether database shrinking operation also does reindexing or
> rebuilding of indexes in background when it runs because, shrink operation
> affects the data page location on disk caused by their movement.
> Thanks
> Manu
|||manu
Start with
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
"manu" <manu@.discussions.microsoft.com> wrote in message
news:8DD71F01-C93E-4533-B947-1A14A1BE891D@.microsoft.com...
> Hi,
> I am confused whether database shrinking operation also does reindexing or
> rebuilding of indexes in background when it runs because, shrink operation
> affects the data page location on disk caused by their movement.
> Thanks
> Manu
sql
I am confused whether database shrinking operation also does reindexing or
rebuilding of indexes in background when it runs because, shrink operation
affects the data page location on disk caused by their movement.
Thanks
Manu
Manu,
Shrinking a database does not redo the indexes, it just moves pages around.
One result is that the shrink can hurt performance since the data needed for
a search may no longer be contiguous, which increases disk activity.
If this is a concern (and normally it should be), shrink first, then rebuild
the indexes. Rebuilding the indexes will give up some of space savings, but
it will make your users happier with the performance.
RLF
"manu" <manu@.discussions.microsoft.com> wrote in message
news:8DD71F01-C93E-4533-B947-1A14A1BE891D@.microsoft.com...
> Hi,
> I am confused whether database shrinking operation also does reindexing or
> rebuilding of indexes in background when it runs because, shrink operation
> affects the data page location on disk caused by their movement.
> Thanks
> Manu
|||manu
Start with
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
"manu" <manu@.discussions.microsoft.com> wrote in message
news:8DD71F01-C93E-4533-B947-1A14A1BE891D@.microsoft.com...
> Hi,
> I am confused whether database shrinking operation also does reindexing or
> rebuilding of indexes in background when it runs because, shrink operation
> affects the data page location on disk caused by their movement.
> Thanks
> Manu
sql
Labels:
background,
database,
indexes,
microsoft,
mysql,
operation,
oracle,
orrebuilding,
reindexing,
runs,
server,
shrink,
shrinking,
sql
Friday, February 17, 2012
database performance management
Hi
I am using SQL server database with asp as front end. I use asp command object to call sql stored procedure. The procedure runs a while loop for say 100,000 records and based on the IF condition calls particular stored procs which process the record.
I am running this app on a p4 IBM pc with win 2000 sever and IIS on same m/c . The cpu utilisation goes upto 98-99% and the process has started running very slow of late. Is this slow processing speed a hardware/OS problem or is it due to calls for stored proc within stored proc? how can i optimise the process. Each stored proc called does have if conditions,table scans etc.
please adviseYou might post your SP for more help, might try recompiling it as well.|||I have attatched the main stored proc(fetchobktotmp_SP_March08.txt) and the proc which is called most often(sot_Sp_March08.txt.. please have a look
regds|||Both SQL & ISS on same machine will have resource crunch and performance would be affected, try to seperate them or add more physical memory to the box. And also assign more memory to SQL itself.
As suggested recompile the SP, update stats the involved tables etc etc.|||Having SQL Server as well as IIS on the same machine leads to a perrformance crunch but inorder to really identify what is the issue you would need to check, RAM & Processor speed. They play a big part.
It is important to identify if this is processor issue or RAM issue, and you would need to check through performance monitor what are the CPU cycle consumption patterns of the IIS and SQL Server.
At the SQL Server level itself, you can check using the trace files if there are any other issues. But i guess, these kind of performance testing has to be done over a period of time and then analyse the results.|||Contribution- http://www.sql-server-performance.com/q&a45.asp|||Hi
currently the machine has 512MB RAM.. we hve observed that as no of records in the table increases (say 600000 or so) thge performance degrades.. for starters we hve decided to hve a serer grade machine for SQL server and iis both.. may be we wud even seperate them ..|||I feel the server is stressed out due to both at one place and SQL is not able to exeucte queries due to less available resources, such as memory which will have major affect for any performance.
I am using SQL server database with asp as front end. I use asp command object to call sql stored procedure. The procedure runs a while loop for say 100,000 records and based on the IF condition calls particular stored procs which process the record.
I am running this app on a p4 IBM pc with win 2000 sever and IIS on same m/c . The cpu utilisation goes upto 98-99% and the process has started running very slow of late. Is this slow processing speed a hardware/OS problem or is it due to calls for stored proc within stored proc? how can i optimise the process. Each stored proc called does have if conditions,table scans etc.
please adviseYou might post your SP for more help, might try recompiling it as well.|||I have attatched the main stored proc(fetchobktotmp_SP_March08.txt) and the proc which is called most often(sot_Sp_March08.txt.. please have a look
regds|||Both SQL & ISS on same machine will have resource crunch and performance would be affected, try to seperate them or add more physical memory to the box. And also assign more memory to SQL itself.
As suggested recompile the SP, update stats the involved tables etc etc.|||Having SQL Server as well as IIS on the same machine leads to a perrformance crunch but inorder to really identify what is the issue you would need to check, RAM & Processor speed. They play a big part.
It is important to identify if this is processor issue or RAM issue, and you would need to check through performance monitor what are the CPU cycle consumption patterns of the IIS and SQL Server.
At the SQL Server level itself, you can check using the trace files if there are any other issues. But i guess, these kind of performance testing has to be done over a period of time and then analyse the results.|||Contribution- http://www.sql-server-performance.com/q&a45.asp|||Hi
currently the machine has 512MB RAM.. we hve observed that as no of records in the table increases (say 600000 or so) thge performance degrades.. for starters we hve decided to hve a serer grade machine for SQL server and iis both.. may be we wud even seperate them ..|||I feel the server is stressed out due to both at one place and SQL is not able to exeucte queries due to less available resources, such as memory which will have major affect for any performance.
Subscribe to:
Posts (Atom)