|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
schedule dbcc indexdefrag every Saturday?How can I set up SQL Server to defragment all indexes on a weekly basis, e.g.
every saturday? Schedule through SQL Agent job(s).
You can iterate through the indexes in the system catalog tables/views and loop through to fire off the defrag, if you do not want to set it up individuallly. -- Show quoteGregory A. Beamer MVP; MCP: +I, SE, SD, DBA *************************** Think Outside the Box! *************************** "Mike" wrote: > How can I set up SQL Server to defragment all indexes on a weekly basis, e.g. > every saturday? Or use the code already written for us, which also doesn't defrag if the index isn't fragmented in
the first place. Code found in Books Online, DBCC SHOWCONTIG. -- Show quoteTibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://www.solidqualitylearning.com/ Blog: http://solidqualitylearning.com/blogs/tibor/ "Cowboy (Gregory A. Beamer) - MVP" <NoSpamMgbworld@comcast.netNoSpamM> wrote in message news:9B1D603C-F198-4977-A6DC-4203CC13AA5E@microsoft.com... > Schedule through SQL Agent job(s). > > You can iterate through the indexes in the system catalog tables/views and > loop through to fire off the defrag, if you do not want to set it up > individuallly. > > -- > Gregory A. Beamer > MVP; MCP: +I, SE, SD, DBA > > *************************** > Think Outside the Box! > *************************** > > > "Mike" wrote: > >> How can I set up SQL Server to defragment all indexes on a weekly basis, e.g. >> every saturday? There you go encouraging the use of cursor based progarmming. ;-)
Show quote "Tibor Karaszi" <tibor_please.no.email_kara***@hotmail.nomail.com> wrote in message news:uu$Y1qujFHA.3316@TK2MSFTNGP14.phx.gbl... > Or use the code already written for us, which also doesn't defrag if the > index isn't fragmented in the first place. Code found in Books Online, > DBCC SHOWCONTIG. > > -- > Tibor Karaszi, SQL Server MVP > http://www.karaszi.com/sqlserver/default.asp > http://www.solidqualitylearning.com/ > Blog: http://solidqualitylearning.com/blogs/tibor/ > > > "Cowboy (Gregory A. Beamer) - MVP" <NoSpamMgbworld@comcast.netNoSpamM> > wrote in message > news:9B1D603C-F198-4977-A6DC-4203CC13AA5E@microsoft.com... >> Schedule through SQL Agent job(s). >> >> You can iterate through the indexes in the system catalog tables/views >> and >> loop through to fire off the defrag, if you do not want to set it up >> individuallly. >> >> -- >> Gregory A. Beamer >> MVP; MCP: +I, SE, SD, DBA >> >> *************************** >> Think Outside the Box! >> *************************** >> >> >> "Mike" wrote: >> >>> How can I set up SQL Server to defragment all indexes on a weekly basis, >>> e.g. >>> every saturday? > LOL. Do you have a set based approach? ;-)
-- Show quoteTibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://www.solidqualitylearning.com/ Blog: http://solidqualitylearning.com/blogs/tibor/ "JT" <some***@microsoft.com> wrote in message news:ubRC11vjFHA.3336@tk2msftngp13.phx.gbl... > There you go encouraging the use of cursor based progarmming. ;-) > > "Tibor Karaszi" <tibor_please.no.email_kara***@hotmail.nomail.com> wrote in > message news:uu$Y1qujFHA.3316@TK2MSFTNGP14.phx.gbl... >> Or use the code already written for us, which also doesn't defrag if the >> index isn't fragmented in the first place. Code found in Books Online, >> DBCC SHOWCONTIG. >> >> -- >> Tibor Karaszi, SQL Server MVP >> http://www.karaszi.com/sqlserver/default.asp >> http://www.solidqualitylearning.com/ >> Blog: http://solidqualitylearning.com/blogs/tibor/ >> >> >> "Cowboy (Gregory A. Beamer) - MVP" <NoSpamMgbworld@comcast.netNoSpamM> >> wrote in message >> news:9B1D603C-F198-4977-A6DC-4203CC13AA5E@microsoft.com... >>> Schedule through SQL Agent job(s). >>> >>> You can iterate through the indexes in the system catalog tables/views >>> and >>> loop through to fire off the defrag, if you do not want to set it up >>> individuallly. >>> >>> -- >>> Gregory A. Beamer >>> MVP; MCP: +I, SE, SD, DBA >>> >>> *************************** >>> Think Outside the Box! >>> *************************** >>> >>> >>> "Mike" wrote: >>> >>>> How can I set up SQL Server to defragment all indexes on a weekly basis, >>>> e.g. >>>> every saturday? >> > > Below is an example of how some people iterate through a temporary table. It
doesn't run any faster, but at least they have the satisfaction of knowing they didn't use a cursor. I was once on a project where the lead developer actually wanted to re-write avoer 20 cursor based stored procedures like so. Fortunately, management pulled the plug on the project, and I was able to move on to more meaningful work. CREATE #temptable ( id int NOT NULL IDENTITY (1, 1), . . . ) .. . . select @id = select min(id) from #temptable select @MAXID = select max(id) from #temptable WHILE @id <= @MAXID BEGIN . . . select @id = @id + 1 END Show quote "Tibor Karaszi" <tibor_please.no.email_kara***@hotmail.nomail.com> wrote in message news:%23ARjZ63jFHA.1504@TK2MSFTNGP10.phx.gbl... > LOL. Do you have a set based approach? ;-) > > -- > Tibor Karaszi, SQL Server MVP > http://www.karaszi.com/sqlserver/default.asp > http://www.solidqualitylearning.com/ > Blog: http://solidqualitylearning.com/blogs/tibor/ > > > "JT" <some***@microsoft.com> wrote in message > news:ubRC11vjFHA.3336@tk2msftngp13.phx.gbl... >> There you go encouraging the use of cursor based progarmming. ;-) >> >> "Tibor Karaszi" <tibor_please.no.email_kara***@hotmail.nomail.com> wrote >> in message news:uu$Y1qujFHA.3316@TK2MSFTNGP14.phx.gbl... >>> Or use the code already written for us, which also doesn't defrag if the >>> index isn't fragmented in the first place. Code found in Books Online, >>> DBCC SHOWCONTIG. >>> >>> -- >>> Tibor Karaszi, SQL Server MVP >>> http://www.karaszi.com/sqlserver/default.asp >>> http://www.solidqualitylearning.com/ >>> Blog: http://solidqualitylearning.com/blogs/tibor/ >>> >>> >>> "Cowboy (Gregory A. Beamer) - MVP" <NoSpamMgbworld@comcast.netNoSpamM> >>> wrote in message >>> news:9B1D603C-F198-4977-A6DC-4203CC13AA5E@microsoft.com... >>>> Schedule through SQL Agent job(s). >>>> >>>> You can iterate through the indexes in the system catalog tables/views >>>> and >>>> loop through to fire off the defrag, if you do not want to set it up >>>> individuallly. >>>> >>>> -- >>>> Gregory A. Beamer >>>> MVP; MCP: +I, SE, SD, DBA >>>> >>>> *************************** >>>> Think Outside the Box! >>>> *************************** >>>> >>>> >>>> "Mike" wrote: >>>> >>>>> How can I set up SQL Server to defragment all indexes on a weekly >>>>> basis, e.g. >>>>> every saturday? >>> >> Yes, I have used that technique in version 4.2 (or was it 1.1?), before server side cursors was
introduced. I think the term cursors is misinterpreted/misused, hence the example you mention ("re-do these cursors to another procedural technique"). I'm glad you didn't have to do that conversion... Personally, I prefer a cursor to the looping of temp table approach, I find cursor code more readable. ... In cases like maint scripts, for instance. -- Show quoteTibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://www.solidqualitylearning.com/ Blog: http://solidqualitylearning.com/blogs/tibor/ "JT" <some***@microsoft.com> wrote in message news:uLNefYRkFHA.1204@TK2MSFTNGP12.phx.gbl... > Below is an example of how some people iterate through a temporary table. It doesn't run any > faster, but at least they have the satisfaction of knowing they didn't use a cursor. I was once on > a project where the lead developer actually wanted to re-write avoer 20 cursor based stored > procedures like so. Fortunately, management pulled the plug on the project, and I was able to move > on to more meaningful work. > > CREATE #temptable > ( > id int NOT NULL IDENTITY (1, 1), > . . . > ) > . . . > select @id = select min(id) from #temptable > select @MAXID = select max(id) from #temptable > WHILE @id <= @MAXID > BEGIN > . . . > select @id = @id + 1 > END > > > "Tibor Karaszi" <tibor_please.no.email_kara***@hotmail.nomail.com> wrote in message > news:%23ARjZ63jFHA.1504@TK2MSFTNGP10.phx.gbl... >> LOL. Do you have a set based approach? ;-) >> >> -- >> Tibor Karaszi, SQL Server MVP >> http://www.karaszi.com/sqlserver/default.asp >> http://www.solidqualitylearning.com/ >> Blog: http://solidqualitylearning.com/blogs/tibor/ >> >> >> "JT" <some***@microsoft.com> wrote in message news:ubRC11vjFHA.3336@tk2msftngp13.phx.gbl... >>> There you go encouraging the use of cursor based progarmming. ;-) >>> >>> "Tibor Karaszi" <tibor_please.no.email_kara***@hotmail.nomail.com> wrote in message >>> news:uu$Y1qujFHA.3316@TK2MSFTNGP14.phx.gbl... >>>> Or use the code already written for us, which also doesn't defrag if the index isn't fragmented >>>> in the first place. Code found in Books Online, DBCC SHOWCONTIG. >>>> >>>> -- >>>> Tibor Karaszi, SQL Server MVP >>>> http://www.karaszi.com/sqlserver/default.asp >>>> http://www.solidqualitylearning.com/ >>>> Blog: http://solidqualitylearning.com/blogs/tibor/ >>>> >>>> >>>> "Cowboy (Gregory A. Beamer) - MVP" <NoSpamMgbworld@comcast.netNoSpamM> wrote in message >>>> news:9B1D603C-F198-4977-A6DC-4203CC13AA5E@microsoft.com... >>>>> Schedule through SQL Agent job(s). >>>>> >>>>> You can iterate through the indexes in the system catalog tables/views and >>>>> loop through to fire off the defrag, if you do not want to set it up >>>>> individuallly. >>>>> >>>>> -- >>>>> Gregory A. Beamer >>>>> MVP; MCP: +I, SE, SD, DBA >>>>> >>>>> *************************** >>>>> Think Outside the Box! >>>>> *************************** >>>>> >>>>> >>>>> "Mike" wrote: >>>>> >>>>>> How can I set up SQL Server to defragment all indexes on a weekly basis, e.g. >>>>>> every saturday? >>>> >>> > Yes, I agree on all points. The cursor is just SQL Server's standardized
implementation of looping through a temporary resultset. The only time I use cursors is for excuting a procdure against a small number of rows, and the cursor at least makes things more maintainable. By getting familiar with the various cursor options (like FAST_FORWORD argument) and using common sense, a developer can use cursors without it becomming a bottleneck. Show quote "Tibor Karaszi" <tibor_please.no.email_kara***@hotmail.nomail.com> wrote in message news:uv2k3lRkFHA.3148@TK2MSFTNGP09.phx.gbl... > Yes, I have used that technique in version 4.2 (or was it 1.1?), before > server side cursors was introduced. I think the term cursors is > misinterpreted/misused, hence the example you mention ("re-do these > cursors to another procedural technique"). I'm glad you didn't have to do > that conversion... Personally, I prefer a cursor to the looping of temp > table approach, I find cursor code more readable. ... In cases like maint > scripts, for instance. > > -- > Tibor Karaszi, SQL Server MVP > http://www.karaszi.com/sqlserver/default.asp > http://www.solidqualitylearning.com/ > Blog: http://solidqualitylearning.com/blogs/tibor/ > > > "JT" <some***@microsoft.com> wrote in message > news:uLNefYRkFHA.1204@TK2MSFTNGP12.phx.gbl... >> Below is an example of how some people iterate through a temporary table. >> It doesn't run any faster, but at least they have the satisfaction of >> knowing they didn't use a cursor. I was once on a project where the lead >> developer actually wanted to re-write avoer 20 cursor based stored >> procedures like so. Fortunately, management pulled the plug on the >> project, and I was able to move on to more meaningful work. >> >> CREATE #temptable >> ( >> id int NOT NULL IDENTITY (1, 1), >> . . . >> ) >> . . . >> select @id = select min(id) from #temptable >> select @MAXID = select max(id) from #temptable >> WHILE @id <= @MAXID >> BEGIN >> . . . >> select @id = @id + 1 >> END >> >> >> "Tibor Karaszi" <tibor_please.no.email_kara***@hotmail.nomail.com> wrote >> in message news:%23ARjZ63jFHA.1504@TK2MSFTNGP10.phx.gbl... >>> LOL. Do you have a set based approach? ;-) >>> >>> -- >>> Tibor Karaszi, SQL Server MVP >>> http://www.karaszi.com/sqlserver/default.asp >>> http://www.solidqualitylearning.com/ >>> Blog: http://solidqualitylearning.com/blogs/tibor/ >>> >>> >>> "JT" <some***@microsoft.com> wrote in message >>> news:ubRC11vjFHA.3336@tk2msftngp13.phx.gbl... >>>> There you go encouraging the use of cursor based progarmming. ;-) >>>> >>>> "Tibor Karaszi" <tibor_please.no.email_kara***@hotmail.nomail.com> >>>> wrote in message news:uu$Y1qujFHA.3316@TK2MSFTNGP14.phx.gbl... >>>>> Or use the code already written for us, which also doesn't defrag if >>>>> the index isn't fragmented in the first place. Code found in Books >>>>> Online, DBCC SHOWCONTIG. >>>>> >>>>> -- >>>>> Tibor Karaszi, SQL Server MVP >>>>> http://www.karaszi.com/sqlserver/default.asp >>>>> http://www.solidqualitylearning.com/ >>>>> Blog: http://solidqualitylearning.com/blogs/tibor/ >>>>> >>>>> >>>>> "Cowboy (Gregory A. Beamer) - MVP" <NoSpamMgbworld@comcast.netNoSpamM> >>>>> wrote in message >>>>> news:9B1D603C-F198-4977-A6DC-4203CC13AA5E@microsoft.com... >>>>>> Schedule through SQL Agent job(s). >>>>>> >>>>>> You can iterate through the indexes in the system catalog >>>>>> tables/views and >>>>>> loop through to fire off the defrag, if you do not want to set it up >>>>>> individuallly. >>>>>> >>>>>> -- >>>>>> Gregory A. Beamer >>>>>> MVP; MCP: +I, SE, SD, DBA >>>>>> >>>>>> *************************** >>>>>> Think Outside the Box! >>>>>> *************************** >>>>>> >>>>>> >>>>>> "Mike" wrote: >>>>>> >>>>>>> How can I set up SQL Server to defragment all indexes on a weekly >>>>>>> basis, e.g. >>>>>>> every saturday? >>>>> >>>> >> >  |
|||||||||||||||||||||||
Other interesting topics