How can I set up SQL Server to defragment all indexes on a w
ly 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.
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 w
ly basis, e
.g.
> every saturday?|||Or use the code already written for us, which also doesn't defrag if the ind
ex 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:
>|||There you go encouraging the use of cursor based progarmming. ;-)
"Tibor Karaszi" <tibor_please.no.email_karaszi@.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...
>|||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" <someone@.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_karaszi@.hotmail.nomail.com> wrote i
n
> message news:uu$Y1qujFHA.3316@.TK2MSFTNGP14.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_karaszi@.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" <someone@.microsoft.com> wrote in message
> news:ubRC11vjFHA.3336@.tk2msftngp13.phx.gbl...|||Yes, I have used that technique in version 4.2 (or was it 1.1?), before serv
er side cursors was
introduced. I think the term cursors is misinterpreted/misused, hence the ex
ample 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 app
roach, 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" <someone@.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 cu
rsor 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_karaszi@.hotmail.nomail.com> wrote i
n message
> news:%23ARjZ63jFHA.1504@.TK2MSFTNGP10.phx.gbl...
>|||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.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.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" <someone@.microsoft.com> wrote in message
> news:uLNefYRkFHA.1204@.TK2MSFTNGP12.phx.gbl...
>
