Automatic Maintenance Plan Optimization vs Logical Scan Frag.

Hi!

SQL Server 7.0, SP4

I would like to know if it's normal that, with a Maintenance plan Optimization done every week, I got a Logical Scan Fragmentation of 99.99% for a non-clustered index.

I though that the logical scan should be as lower as it can.

I though that the Optimization plan should drop and recreate all indexes in a database.

My table is heavy (7GB). DOes the clustered is recreated too in this maintenance plan? Is it the reason why my Logical Scan Frag. is so high?

PS: Yes, the database is included in the list of the Maintenance plan.

Thanks a lot!

DavidMake sure your non-clustered index is being created after your clustered index. Recreating a clustered index changes the order of the data, and causes all the non-clustered indexes to be rebuilt as well, and not necessarily efficiently.

99% sounds pretty weird though. This could be an erroneous value.

blindman|||I don't recreate any indexes manually or by any script other than the Optimization option in the Maintenance Plan. BOL doesn't tell me a lot of information on what is done by this option "Reorganize data and index pages" and in which order. Does this use un DBCC CHECKDB or DBREINDEX?

Someone know what is done and what should be the result (at least an idea) on the indexes?

A query doing a index scan of 6GB of data shouldn't tell to the Tuning Wizard that the creation of another index on the specified WHERE clause field should be created to get an Index seek?

All this make me lost... Some ideas?

thanks a lot

Originally posted by blindman
Make sure your non-clustered index is being created after your clustered index. Recreating a clustered index changes the order of the data, and causes all the non-clustered indexes to be rebuilt as well, and not necessarily efficiently.

99% sounds pretty weird though. This could be an erroneous value.

blindman

AUTO_UPDATE_STATISTICS

We have the "AUTO_UPDATE_STATISTICS" set to "On" in our
database. When you update the stats manually you can chose
either a full scan or a sample (%/rows). What amount does
the "auto_update_statistics" sample? Is it sample or full?
Can we modify what the "AUTO_UPDATE_STATISTICS" is
sampling?
I checked with BOL and it didn't indicate either way.
Any help would be greatly appreciated.
Thanks
SusanHi Susan
This should have the answer to your question:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql2k/htm
l/statquery.asp
Also, DBCC SHOW_STATISTICS should tell you whether the existing statistics
were generated using a fullscan or sampling.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Susan" <susanbauer@.yahoo.com> wrote in message
news:046101c35612$af627050$a301280a@.phx.gbl...
> We have the "AUTO_UPDATE_STATISTICS" set to "On" in our
> database. When you update the stats manually you can chose
> either a full scan or a sample (%/rows). What amount does
> the "auto_update_statistics" sample? Is it sample or full?
> Can we modify what the "AUTO_UPDATE_STATISTICS" is
> sampling?
> I checked with BOL and it didn't indicate either way.
> Any help would be greatly appreciated.
> Thanks
> Susan
>|||Kalen
This link does not seem to work anymore
http://msdn.microsoft.com/library/default.asp?
url=/library/en-us/dnsql2k/html/statquery.asp
Regards
John|||It worked for me once I unwrapped it.
--
Andrew J. Kelly
SQL Server MVP
"John Bandettini" <johnbandettini@.yahoo.co.uk> wrote in message
news:089901c35677$db2ca920$a501280a@.phx.gbl...
> Kalen
> This link does not seem to work anymore
> http://msdn.microsoft.com/library/default.asp?
> url=/library/en-us/dnsql2k/html/statquery.asp
> Regards
> John

Auto update statistics

We have 'auto update statistics' turned on for all our databases.
We also have a job that updates statistics with full scan every morning of
every day.
I am seeing some high cpu on one of our servers and in running Profiler I see
a number of "SELECT StatMan([SCO])..." statements.
From what I understand this could be due to procedures recompiling due to a
temp table, or auto update statistics.
If by the chance it is due to having 'auto update statistics' turned on, and
I am updating statistics daily, can I do away with one or the other? Is doing
both rather over kill?
--
Message posted via http://www.sqlmonster.comIt is 'probably' ok to turn off autoupdate in this case. One situation
where it isn't is tables that are heavily modified - you could have queries
developing bad query plans after sufficient modifications altered the value
distributions. If this is the case you could simply enable specific tables
using the sp_autostats sproc.
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message
news:798b0fc1296d4@.uwe...
> We have 'auto update statistics' turned on for all our databases.
> We also have a job that updates statistics with full scan every morning of
> every day.
> I am seeing some high cpu on one of our servers and in running Profiler I
> see
> a number of "SELECT StatMan([SCO])..." statements.
> From what I understand this could be due to procedures recompiling due to
> a
> temp table, or auto update statistics.
> If by the chance it is due to having 'auto update statistics' turned on,
> and
> I am updating statistics daily, can I do away with one or the other? Is
> doing
> both rather over kill?
> --
> Message posted via http://www.sqlmonster.com
>|||Hi there,
There's a new database SET option, AUTO_UPDATE_STATISTICS_ASYNC, that you
can enable to improve the predictability of query response times. When you
enable this option, out-of-date statistics are put on a queue for updating by
a background worker thread, and the query that initiated the statistics
update compiles immediately rather than waiting for the statistics to be
updated.
Thank you,
Saleem Hakani
HTTP://WWW.SQLCOMMUNITY.COM (World Wide Microsoft SQL Server Community)
SQLTips, SQL Forums, SQL Blogs, SQL RADIO, SQL Events, SQL Scripts, SQL
Articles, SQL Clinic and a lot of SQL fun.
Register (Free):
http://www.sqlcommunity.com/RegistrationLoginPage/tabid/68/Default.aspx?returnurl=%2fHome%2ftabid%2f36%2fDefault.aspx
"TheSQLGuru" wrote:
> It is 'probably' ok to turn off autoupdate in this case. One situation
> where it isn't is tables that are heavily modified - you could have queries
> developing bad query plans after sufficient modifications altered the value
> distributions. If this is the case you could simply enable specific tables
> using the sp_autostats sproc.
>
> --
> Kevin G. Boles
> TheSQLGuru
> Indicium Resources, Inc.
> "cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message
> news:798b0fc1296d4@.uwe...
> > We have 'auto update statistics' turned on for all our databases.
> >
> > We also have a job that updates statistics with full scan every morning of
> > every day.
> >
> > I am seeing some high cpu on one of our servers and in running Profiler I
> > see
> > a number of "SELECT StatMan([SCO])..." statements.
> >
> > From what I understand this could be due to procedures recompiling due to
> > a
> > temp table, or auto update statistics.
> >
> > If by the chance it is due to having 'auto update statistics' turned on,
> > and
> > I am updating statistics daily, can I do away with one or the other? Is
> > doing
> > both rather over kill?
> >
> > --
> > Message posted via http://www.sqlmonster.com
> >
>
>|||The auto update kicks in when a certain thresshold of changes has
occurred since the last time the statistics were determined. So if you
update statistics every morning, then the auto update statistics will
not activate unless it thinks it is necessary. This could be on heavily
used tables, or temp tables.
IOW, I would keep it turned on. It also prevents problems if you missed
a table in your daily job, or if (for some reason) the job hasn't run
(completely).
--
Gert-Jan
"cbrichards via SQLMonster.com" wrote:
> We have 'auto update statistics' turned on for all our databases.
> We also have a job that updates statistics with full scan every morning of
> every day.
> I am seeing some high cpu on one of our servers and in running Profiler I see
> a number of "SELECT StatMan([SCO])..." statements.
> From what I understand this could be due to procedures recompiling due to a
> temp table, or auto update statistics.
> If by the chance it is due to having 'auto update statistics' turned on, and
> I am updating statistics daily, can I do away with one or the other? Is doing
> both rather over kill?
> --
> Message posted via http://www.sqlmonster.com