|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Understanding best way to optimize...these operations to increase performance to do lots of READ operations and few UPDATE/INSERT operations. 1) Defrag file system when SQL Server is stopped. 2) dbcc shrinkdatabase 3) dbreindex <all_table> ... Any help much appraciated. Best regards. -- Fabri "Eat just one little baby and everyone remembers your name!" - CELKO Hi
If the database is sufficiently large enough already then you should not need to defrage the file system unless something else is using it. I would only shrink the data files when there is a reason to do so, which should be a rare. You may want to shrink the log file after some unusual activity, but I would not expect from your description that this would be the case. Reindexing the database should be done periodically, you may want to make this dependent on how fragmented the indexes are and there is an example in Books Online on how to use DBCC SHOWCONTIG to do this. If you have to do this then you may want to only do 1 and 3 (in that order). If you really need to do 2 then do it first as it will leave more free space for the defragger to work with. HTH John Show quote "Fabri" wrote: > I guess in which order I havt to (and also if I'm missing someone) make > these operations to increase performance to do lots of READ operations > and few UPDATE/INSERT operations. > > 1) Defrag file system when SQL Server is stopped. > 2) dbcc shrinkdatabase > 3) dbreindex <all_table> > > > ... > > > Any help much appraciated. > > Best regards. > > > -- > Fabri > "Eat just one little baby and everyone remembers your name!" > - CELKO > In addition to John's comment, I strongly advice you spend half an hour to go through these:
http://www.karaszi.com/SQLServer/info_dont_shrink.asp http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx -- Show quoteTibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://www.solidqualitylearning.com/ Blog: http://solidqualitylearning.com/blogs/tibor/ "Fabri" <n*@sp.am> wrote in message news:XZ9Le.51621$2U1.2787586@news3.tin.it... >I guess in which order I havt to (and also if I'm missing someone) make > these operations to increase performance to do lots of READ operations > and few UPDATE/INSERT operations. > > 1) Defrag file system when SQL Server is stopped. > 2) dbcc shrinkdatabase > 3) dbreindex <all_table> > > > .. > > > Any help much appraciated. > > Best regards. > > > -- > Fabri > "Eat just one little baby and everyone remembers your name!" > - CELKO Tibor Karaszi wrote:
> In addition to John's comment, I strongly advice you spend half an hour Thx to Tibor and John.> to go through these: What do you think about diskeeper? Can it really defrag sql server files also when they are in use? Regards. -- Fabri "Eat just one little baby and everyone remembers your name!" - CELKO Hi
If you find your file system often becomming fragmented then diskeeper is worth considering, although you may want also want to investigate why it becomes fragmented. If you have a dedicated SQL Server and fixed and manually expanded data/log files there should be less need for it, although diskeeper may prove to be more efficient at defragging the disc when required. John Show quote "Fabri" wrote: > Tibor Karaszi wrote: > > In addition to John's comment, I strongly advice you spend half an hour > > to go through these: > > > Thx to Tibor and John. > > What do you think about diskeeper? > > Can it really defrag sql server files also when they are in use? > > Regards. > > > -- > Fabri > "Eat just one little baby and everyone remembers your name!" > - CELKO > Hi. I was thinking of archiving some data from our application and then
shrinking the DB. Sounds like I want to think twice about the shrinking part. Our data comes in nightly batches with almost no data addition during the day; so we don't worry about transaction logs; we take a full backup each morning. Can you say generally wheather we'll reap a little performance benefit from simply archiving some of the old data away without shrinking? Thanks, Ken Trock Verizon Wireless Show quote "Tibor Karaszi" wrote: > In addition to John's comment, I strongly advice you spend half an hour to go through these: > > http://www.karaszi.com/SQLServer/info_dont_shrink.asp > http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx > > -- > Tibor Karaszi, SQL Server MVP > http://www.karaszi.com/sqlserver/default.asp > http://www.solidqualitylearning.com/ > Blog: http://solidqualitylearning.com/blogs/tibor/ > > > "Fabri" <n*@sp.am> wrote in message news:XZ9Le.51621$2U1.2787586@news3.tin.it... > >I guess in which order I havt to (and also if I'm missing someone) make > > these operations to increase performance to do lots of READ operations > > and few UPDATE/INSERT operations. > > > > 1) Defrag file system when SQL Server is stopped. > > 2) dbcc shrinkdatabase > > 3) dbreindex <all_table> > > > > > > .. > > > > > > Any help much appraciated. > > > > Best regards. > > > > > > -- > > Fabri > > "Eat just one little baby and everyone remembers your name!" > > - CELKO > Sure. Fewer records means fewer pages for each
query to review and smaller indexes. Show quote "ktrock" <ktr***@discussions.microsoft.com> wrote in message news:2DF8AEB9-1C60-4886-8851-A922AA873B90@microsoft.com... > Hi. I was thinking of archiving some data from our application and then > shrinking the DB. Sounds like I want to think twice about the shrinking > part. > Our data comes in nightly batches with almost no data addition during the > day; so we don't worry about transaction logs; we take a full backup each > morning. Can you say generally wheather we'll reap a little performance > benefit from simply archiving some of the old data away without shrinking? > > Thanks, > Ken Trock > Verizon Wireless > > "Tibor Karaszi" wrote: > >> In addition to John's comment, I strongly advice you spend half an hour >> to go through these: >> >> http://www.karaszi.com/SQLServer/info_dont_shrink.asp >> http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx >> >> -- >> Tibor Karaszi, SQL Server MVP >> http://www.karaszi.com/sqlserver/default.asp >> http://www.solidqualitylearning.com/ >> Blog: http://solidqualitylearning.com/blogs/tibor/ >> >> >> "Fabri" <n*@sp.am> wrote in message >> news:XZ9Le.51621$2U1.2787586@news3.tin.it... >> >I guess in which order I havt to (and also if I'm missing someone) make >> > these operations to increase performance to do lots of READ operations >> > and few UPDATE/INSERT operations. >> > >> > 1) Defrag file system when SQL Server is stopped. >> > 2) dbcc shrinkdatabase >> > 3) dbreindex <all_table> >> > >> > >> > .. >> > >> > >> > Any help much appraciated. >> > >> > Best regards. >> > >> > >> > -- >> > Fabri >> > "Eat just one little baby and everyone remembers your name!" >> > - CELKO >> On Fri, 12 Aug 2005 23:08:39 GMT, Fabri <n*@sp.am> wrote:
>I guess in which order I havt to (and also if I'm missing someone) make More RAM?>these operations to increase performance to do lots of READ operations >and few UPDATE/INSERT operations. > >1) Defrag file system when SQL Server is stopped. >2) dbcc shrinkdatabase >3) dbreindex <all_table> Better indexing? Better query tuning? J. Hi
For a very fragmented system, I saw a 33% speed increase for queries after a disc defragmentation, so it is a worthwhile exercise if you do have a fragmented disc. John Show quote "JXStern" wrote: > On Fri, 12 Aug 2005 23:08:39 GMT, Fabri <n*@sp.am> wrote: > >I guess in which order I havt to (and also if I'm missing someone) make > >these operations to increase performance to do lots of READ operations > >and few UPDATE/INSERT operations. > > > >1) Defrag file system when SQL Server is stopped. > >2) dbcc shrinkdatabase > >3) dbreindex <all_table> > > More RAM? > > Better indexing? > > Better query tuning? > > J. > > JXStern wrote:
> More RAM? I don't think your advices are really interesting.> > Better indexing? > > Better query tuning? > > J. -- Fabri "Eat just one little baby and everyone remembers your name!" - CELKO  |
|||||||||||||||||||||||
Other interesting topics