Reorganize data and index pages

I have a couple of questions here so I will break them out.
1. I have used the Database Maintenance plan to schedule a Reorganization of
the data and index pages that runs every weekend. I lef the free space per
page percentage to 10%.
-A: Do I need to do anything special to make sure the statistics are
updated such as sp_updatestats? Can I trust that by doing that alone my
indexes are in the best shape and that my data is defragmented the best way?
Isn't this like a DBCC REINDEX or maybe a complete rebuild of the indexes?
-B: It seems that from what I've read that the 10% varies on what your
database is used for. Is that correct?
2. If I need to defrag databases that aren't on that plan, I'll normally
just do the following when I see fragmentation above 20 percent:
USE Database
DBCC SHOWCONTIG WITH TABLERESULTS, ALL_INDEXES
USE Database
DBCC INDEXDEFRAG (Database, table, index)
GO
3. I notice that the sysindexes table has an index called tsysindexes that
has logical scan fragmentation at 99, but it's small at 119 pages. Is that a
concern? I don't ever manually defrag the system tables as I believe I'm not
supposed to, but I'm not sure if the weekend job gets it.
Thanks
JasonHi Jason, If you are rebuing indexes using DBCC DBREINDEX then you don't have
to worry about updating the statistics as it's done right after indexes are
rebuilt. however you may have to update the statistics if you are using DBCC
INDEXDEFRAG.
You can find some good tips and scripts related to this topic @.
<WWW.SQLCOMMUNITY.COM>
<http://www.sqlcommunity.com/Default.aspx?grm2id=14&tabid=56>
<http://www.sqlcommunity.com/SQLTips/tabid/77/grm2id/28/Default.aspx>
<http://www.sqlcommunity.com/Default.aspx?grm2id=12&tabid=56>
<http://www.sqlcommunity.com/Default.aspx?grm2id=7&tabid=56>
<http://www.sqlcommunity.com/Default.aspx?grm2id=9&tabid=56>
Hope this helps.
--
Thank you,
<WWW.SQLCOMMUNITY.COM> (World Wide Community for SQL Server Professionals)
SQLTips, SQL Automation Scripts, SQL Articles, SQL Blogs, SQL Events, SQL
Forums, etc
"jason7655" wrote:
> I have a couple of questions here so I will break them out.
> 1. I have used the Database Maintenance plan to schedule a Reorganization of
> the data and index pages that runs every weekend. I lef the free space per
> page percentage to 10%.
> -A: Do I need to do anything special to make sure the statistics are
> updated such as sp_updatestats? Can I trust that by doing that alone my
> indexes are in the best shape and that my data is defragmented the best way?
> Isn't this like a DBCC REINDEX or maybe a complete rebuild of the indexes?
> -B: It seems that from what I've read that the 10% varies on what your
> database is used for. Is that correct?
> 2. If I need to defrag databases that aren't on that plan, I'll normally
> just do the following when I see fragmentation above 20 percent:
> USE Database
> DBCC SHOWCONTIG WITH TABLERESULTS, ALL_INDEXES
> USE Database
> DBCC INDEXDEFRAG (Database, table, index)
> GO
> 3. I notice that the sysindexes table has an index called tsysindexes that
> has logical scan fragmentation at 99, but it's small at 119 pages. Is that a
> concern? I don't ever manually defrag the system tables as I believe I'm not
> supposed to, but I'm not sure if the weekend job gets it.
> Thanks
> Jason|||Thanks.
I probably should have mentioned that we are on SQL Server 2000.
"SQLGurus" wrote:
> Hi Jason, If you are rebuing indexes using DBCC DBREINDEX then you don't have
> to worry about updating the statistics as it's done right after indexes are
> rebuilt. however you may have to update the statistics if you are using DBCC
> INDEXDEFRAG.
> You can find some good tips and scripts related to this topic @.
> <WWW.SQLCOMMUNITY.COM>
> <http://www.sqlcommunity.com/Default.aspx?grm2id=14&tabid=56>
> <http://www.sqlcommunity.com/SQLTips/tabid/77/grm2id/28/Default.aspx>
> <http://www.sqlcommunity.com/Default.aspx?grm2id=12&tabid=56>
> <http://www.sqlcommunity.com/Default.aspx?grm2id=7&tabid=56>
> <http://www.sqlcommunity.com/Default.aspx?grm2id=9&tabid=56>
> Hope this helps.
> --
> Thank you,
> <WWW.SQLCOMMUNITY.COM> (World Wide Community for SQL Server Professionals)
> SQLTips, SQL Automation Scripts, SQL Articles, SQL Blogs, SQL Events, SQL
> Forums, etc
>
> "jason7655" wrote:
> > I have a couple of questions here so I will break them out.
> >
> > 1. I have used the Database Maintenance plan to schedule a Reorganization of
> > the data and index pages that runs every weekend. I lef the free space per
> > page percentage to 10%.
> > -A: Do I need to do anything special to make sure the statistics are
> > updated such as sp_updatestats? Can I trust that by doing that alone my
> > indexes are in the best shape and that my data is defragmented the best way?
> > Isn't this like a DBCC REINDEX or maybe a complete rebuild of the indexes?
> > -B: It seems that from what I've read that the 10% varies on what your
> > database is used for. Is that correct?
> >
> > 2. If I need to defrag databases that aren't on that plan, I'll normally
> > just do the following when I see fragmentation above 20 percent:
> > USE Database
> > DBCC SHOWCONTIG WITH TABLERESULTS, ALL_INDEXES
> >
> > USE Database
> > DBCC INDEXDEFRAG (Database, table, index)
> > GO
> >
> > 3. I notice that the sysindexes table has an index called tsysindexes that
> > has logical scan fragmentation at 99, but it's small at 119 pages. Is that a
> > concern? I don't ever manually defrag the system tables as I believe I'm not
> > supposed to, but I'm not sure if the weekend job gets it.
> >
> > Thanks
> > Jason|||The commands Kevin gave were for SQL2000.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"jason7655" <jason7655@.discussions.microsoft.com> wrote in message
news:FE53F490-A4CF-4345-B974-4D9B411F9943@.microsoft.com...
> Thanks.
> I probably should have mentioned that we are on SQL Server 2000.
> "SQLGurus" wrote:
>> Hi Jason, If you are rebuing indexes using DBCC DBREINDEX then you don't
>> have
>> to worry about updating the statistics as it's done right after indexes
>> are
>> rebuilt. however you may have to update the statistics if you are using
>> DBCC
>> INDEXDEFRAG.
>> You can find some good tips and scripts related to this topic @.
>> <WWW.SQLCOMMUNITY.COM>
>> <http://www.sqlcommunity.com/Default.aspx?grm2id=14&tabid=56>
>> <http://www.sqlcommunity.com/SQLTips/tabid/77/grm2id/28/Default.aspx>
>> <http://www.sqlcommunity.com/Default.aspx?grm2id=12&tabid=56>
>> <http://www.sqlcommunity.com/Default.aspx?grm2id=7&tabid=56>
>> <http://www.sqlcommunity.com/Default.aspx?grm2id=9&tabid=56>
>> Hope this helps.
>> --
>> Thank you,
>> <WWW.SQLCOMMUNITY.COM> (World Wide Community for SQL Server
>> Professionals)
>> SQLTips, SQL Automation Scripts, SQL Articles, SQL Blogs, SQL Events, SQL
>> Forums, etc
>>
>> "jason7655" wrote:
>> > I have a couple of questions here so I will break them out.
>> >
>> > 1. I have used the Database Maintenance plan to schedule a
>> > Reorganization of
>> > the data and index pages that runs every weekend. I lef the free space
>> > per
>> > page percentage to 10%.
>> > -A: Do I need to do anything special to make sure the statistics are
>> > updated such as sp_updatestats? Can I trust that by doing that alone my
>> > indexes are in the best shape and that my data is defragmented the best
>> > way?
>> > Isn't this like a DBCC REINDEX or maybe a complete rebuild of the
>> > indexes?
>> > -B: It seems that from what I've read that the 10% varies on what your
>> > database is used for. Is that correct?
>> >
>> > 2. If I need to defrag databases that aren't on that plan, I'll
>> > normally
>> > just do the following when I see fragmentation above 20 percent:
>> > USE Database
>> > DBCC SHOWCONTIG WITH TABLERESULTS, ALL_INDEXES
>> >
>> > USE Database
>> > DBCC INDEXDEFRAG (Database, table, index)
>> > GO
>> >
>> > 3. I notice that the sysindexes table has an index called tsysindexes
>> > that
>> > has logical scan fragmentation at 99, but it's small at 119 pages. Is
>> > that a
>> > concern? I don't ever manually defrag the system tables as I believe
>> > I'm not
>> > supposed to, but I'm not sure if the weekend job gets it.
>> >
>> > Thanks
>> > Jason|||The links were specific to 2005, and that's what I was referring to.
Although one of them mentions a script for 2000.
Wasn't taking anything away from it...just mentioning that I left off an
important piece of information b/c 2005 goes a long way in helping one
monitor that sort of thing.
"Andrew J. Kelly" wrote:
> The commands Kevin gave were for SQL2000.
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "jason7655" <jason7655@.discussions.microsoft.com> wrote in message
> news:FE53F490-A4CF-4345-B974-4D9B411F9943@.microsoft.com...
> > Thanks.
> >
> > I probably should have mentioned that we are on SQL Server 2000.
> >
> > "SQLGurus" wrote:
> >
> >> Hi Jason, If you are rebuing indexes using DBCC DBREINDEX then you don't
> >> have
> >> to worry about updating the statistics as it's done right after indexes
> >> are
> >> rebuilt. however you may have to update the statistics if you are using
> >> DBCC
> >> INDEXDEFRAG.
> >>
> >> You can find some good tips and scripts related to this topic @.
> >> <WWW.SQLCOMMUNITY.COM>
> >>
> >> <http://www.sqlcommunity.com/Default.aspx?grm2id=14&tabid=56>
> >> <http://www.sqlcommunity.com/SQLTips/tabid/77/grm2id/28/Default.aspx>
> >> <http://www.sqlcommunity.com/Default.aspx?grm2id=12&tabid=56>
> >> <http://www.sqlcommunity.com/Default.aspx?grm2id=7&tabid=56>
> >> <http://www.sqlcommunity.com/Default.aspx?grm2id=9&tabid=56>
> >>
> >> Hope this helps.
> >> --
> >> Thank you,
> >> <WWW.SQLCOMMUNITY.COM> (World Wide Community for SQL Server
> >> Professionals)
> >> SQLTips, SQL Automation Scripts, SQL Articles, SQL Blogs, SQL Events, SQL
> >> Forums, etc
> >>
> >>
> >> "jason7655" wrote:
> >>
> >> > I have a couple of questions here so I will break them out.
> >> >
> >> > 1. I have used the Database Maintenance plan to schedule a
> >> > Reorganization of
> >> > the data and index pages that runs every weekend. I lef the free space
> >> > per
> >> > page percentage to 10%.
> >> > -A: Do I need to do anything special to make sure the statistics are
> >> > updated such as sp_updatestats? Can I trust that by doing that alone my
> >> > indexes are in the best shape and that my data is defragmented the best
> >> > way?
> >> > Isn't this like a DBCC REINDEX or maybe a complete rebuild of the
> >> > indexes?
> >> > -B: It seems that from what I've read that the 10% varies on what your
> >> > database is used for. Is that correct?
> >> >
> >> > 2. If I need to defrag databases that aren't on that plan, I'll
> >> > normally
> >> > just do the following when I see fragmentation above 20 percent:
> >> > USE Database
> >> > DBCC SHOWCONTIG WITH TABLERESULTS, ALL_INDEXES
> >> >
> >> > USE Database
> >> > DBCC INDEXDEFRAG (Database, table, index)
> >> > GO
> >> >
> >> > 3. I notice that the sysindexes table has an index called tsysindexes
> >> > that
> >> > has logical scan fragmentation at 99, but it's small at 119 pages. Is
> >> > that a
> >> > concern? I don't ever manually defrag the system tables as I believe
> >> > I'm not
> >> > supposed to, but I'm not sure if the weekend job gets it.
> >> >
> >> > Thanks
> >> > Jason
>|||Sorry I didn't click on the links.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"jason7655" <jason7655@.discussions.microsoft.com> wrote in message
news:B87939FC-6CAB-48DC-8ECB-129B76AB5486@.microsoft.com...
> The links were specific to 2005, and that's what I was referring to.
> Although one of them mentions a script for 2000.
> Wasn't taking anything away from it...just mentioning that I left off an
> important piece of information b/c 2005 goes a long way in helping one
> monitor that sort of thing.
> "Andrew J. Kelly" wrote:
>> The commands Kevin gave were for SQL2000.
>> --
>> Andrew J. Kelly SQL MVP
>> Solid Quality Mentors
>>
>> "jason7655" <jason7655@.discussions.microsoft.com> wrote in message
>> news:FE53F490-A4CF-4345-B974-4D9B411F9943@.microsoft.com...
>> > Thanks.
>> >
>> > I probably should have mentioned that we are on SQL Server 2000.
>> >
>> > "SQLGurus" wrote:
>> >
>> >> Hi Jason, If you are rebuing indexes using DBCC DBREINDEX then you
>> >> don't
>> >> have
>> >> to worry about updating the statistics as it's done right after
>> >> indexes
>> >> are
>> >> rebuilt. however you may have to update the statistics if you are
>> >> using
>> >> DBCC
>> >> INDEXDEFRAG.
>> >>
>> >> You can find some good tips and scripts related to this topic @.
>> >> <WWW.SQLCOMMUNITY.COM>
>> >>
>> >> <http://www.sqlcommunity.com/Default.aspx?grm2id=14&tabid=56>
>> >> <http://www.sqlcommunity.com/SQLTips/tabid/77/grm2id/28/Default.aspx>
>> >> <http://www.sqlcommunity.com/Default.aspx?grm2id=12&tabid=56>
>> >> <http://www.sqlcommunity.com/Default.aspx?grm2id=7&tabid=56>
>> >> <http://www.sqlcommunity.com/Default.aspx?grm2id=9&tabid=56>
>> >>
>> >> Hope this helps.
>> >> --
>> >> Thank you,
>> >> <WWW.SQLCOMMUNITY.COM> (World Wide Community for SQL Server
>> >> Professionals)
>> >> SQLTips, SQL Automation Scripts, SQL Articles, SQL Blogs, SQL Events,
>> >> SQL
>> >> Forums, etc
>> >>
>> >>
>> >> "jason7655" wrote:
>> >>
>> >> > I have a couple of questions here so I will break them out.
>> >> >
>> >> > 1. I have used the Database Maintenance plan to schedule a
>> >> > Reorganization of
>> >> > the data and index pages that runs every weekend. I lef the free
>> >> > space
>> >> > per
>> >> > page percentage to 10%.
>> >> > -A: Do I need to do anything special to make sure the statistics
>> >> > are
>> >> > updated such as sp_updatestats? Can I trust that by doing that alone
>> >> > my
>> >> > indexes are in the best shape and that my data is defragmented the
>> >> > best
>> >> > way?
>> >> > Isn't this like a DBCC REINDEX or maybe a complete rebuild of the
>> >> > indexes?
>> >> > -B: It seems that from what I've read that the 10% varies on what
>> >> > your
>> >> > database is used for. Is that correct?
>> >> >
>> >> > 2. If I need to defrag databases that aren't on that plan, I'll
>> >> > normally
>> >> > just do the following when I see fragmentation above 20 percent:
>> >> > USE Database
>> >> > DBCC SHOWCONTIG WITH TABLERESULTS, ALL_INDEXES
>> >> >
>> >> > USE Database
>> >> > DBCC INDEXDEFRAG (Database, table, index)
>> >> > GO
>> >> >
>> >> > 3. I notice that the sysindexes table has an index called
>> >> > tsysindexes
>> >> > that
>> >> > has logical scan fragmentation at 99, but it's small at 119 pages.
>> >> > Is
>> >> > that a
>> >> > concern? I don't ever manually defrag the system tables as I believe
>> >> > I'm not
>> >> > supposed to, but I'm not sure if the weekend job gets it.
>> >> >
>> >> > Thanks
>> >> > Jason
>>