Rebuild all indexes in SQL 2008...

Friends, we use a command like:
EXECUTE master.dbo.xp_sqlmaint '-D THEDB -WriteHistory -RebldIdx 100'
or the command-lie equivalent to rebuild all indexes, and recommend this
technique to our clients. Apparently, .xp_sqlmaint and sqlmaint.exe will be
gone for SQL 2008.
For SQL 2008, what technique do you prefer/recommend for rebuilding all
indexes in all tables?
We add new tables to our product regularly as we add features, so we'd
prefer a solution that does not required specifying commands on a per-table
basis. Thanks in advance for your words!
Very sincerely,
James Hunter Ross
Have a look at the help in BOL under sys.dm_db_index_physical_stats. There
is an example (all the way near the bottom) on how to reindex each index
based on the fragmentation level. If you really wish to rebuild them all
(which I don't recommend) you can cut out the checks for the frag levels.
Oh and welcome back. Haven't seen you here for a while.
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"James Hunter Ross" <james.ross@.oneilsoft.com> wrote in message
news:uJVUiUjTIHA.5360@.TK2MSFTNGP03.phx.gbl...
> Friends, we use a command like:
> EXECUTE master.dbo.xp_sqlmaint '-D THEDB -WriteHistory -RebldIdx 100'
> or the command-lie equivalent to rebuild all indexes, and recommend this
> technique to our clients. Apparently, .xp_sqlmaint and sqlmaint.exe will
> be gone for SQL 2008.
> For SQL 2008, what technique do you prefer/recommend for rebuilding all
> indexes in all tables?
> We add new tables to our product regularly as we add features, so we'd
> prefer a solution that does not required specifying commands on a
> per-table basis. Thanks in advance for your words!
> Very sincerely,
> James Hunter Ross
>
|||Thank you Andrew, that's what I need!
Yeah, I've been hiding away with SQL Server Compact Edition 3.1/3.5 and lots
of Windows Mobile barcode scanning devices for the last year or more. The
CE newsgroups are not nearly as active! Our server-side stuff is being
redied for SQL Server 2008 so I've come up for air recently.
James
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OqwxMCkTIHA.4280@.TK2MSFTNGP06.phx.gbl...
> Have a look at the help in BOL under sys.dm_db_index_physical_stats. There
> is an example (all the way near the bottom) on how to reindex each index
> based on the fragmentation level. If you really wish to rebuild them all
> (which I don't recommend) you can cut out the checks for the frag levels.
> Oh and welcome back. Haven't seen you here for a while.
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "James Hunter Ross" <james.ross@.oneilsoft.com> wrote in message
> news:uJVUiUjTIHA.5360@.TK2MSFTNGP03.phx.gbl...
>
|||Enjoy the sunshine while you can!! :-)
You may wish to limit the items you rebuild to those > 1000 pages (a number
I think Microsoft recommends IIRC). I would definitely not bother with
items < 100 pages.
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net
"James Hunter Ross" <james.ross@.oneilsoft.com> wrote in message
news:uAvvw$kTIHA.4104@.TK2MSFTNGP05.phx.gbl...
> Thank you Andrew, that's what I need!
> Yeah, I've been hiding away with SQL Server Compact Edition 3.1/3.5 and
> lots of Windows Mobile barcode scanning devices for the last year or more.
> The CE newsgroups are not nearly as active! Our server-side stuff is
> being redied for SQL Server 2008 so I've come up for air recently.
> James
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:OqwxMCkTIHA.4280@.TK2MSFTNGP06.phx.gbl...
>
|||Good point Kevin, thanks. (I sure enjoyed just letting SQLMaint.exe figure
everything out, even it it did adopt a brutish approach.)
James
"TheSQLGuru" <kgboles@.earthlink.net> wrote in message
news:13nqoerop5shq62@.corp.supernews.com...
> Enjoy the sunshine while you can!! :-)
> You may wish to limit the items you rebuild to those > 1000 pages (a
> number I think Microsoft recommends IIRC). I would definitely not bother
> with items < 100 pages.
> --
> Kevin G. Boles
> Indicium Resources, Inc.
> SQL Server MVP
> kgboles a earthlink dt net
>
> "James Hunter Ross" <james.ross@.oneilsoft.com> wrote in message
> news:uAvvw$kTIHA.4104@.TK2MSFTNGP05.phx.gbl...
>