April 22, 2009 at 5:37 pm
Hi,
I have a couple of indexed views which slow down occasional data loading into the tables. They used to drop the views and re-create them after the data loading was completed to work this around. I am wondering if switching this option to ON for all the indexes on those views and later switching it OFF together with rebuilding the indexes would produce the same result? In BOL it says:
"STATISTICS_NORECOMPUTE = { ON | OFF } Specifies whether distribution statistics are recomputed."
Which is not clear if the indexes (when ON) don't get updated at all or only in part.
Thanks.
April 22, 2009 at 8:31 pm
Roust_m (4/22/2009)
Hi,I have a couple of indexed views which slow down occasional data loading into the tables. They used to drop the views and re-create them after the data loading was completed to work this around. I am wondering if switching this option to ON for all the indexes on those views and later switching it OFF together with rebuilding the indexes would produce the same result? In BOL it says:
"STATISTICS_NORECOMPUTE = { ON | OFF } Specifies whether distribution statistics are recomputed."
Which is not clear if the indexes (when ON) don't get updated at all or only in part.
Thanks.
To be honest I never worried about using this option as it will not allow for the query optimiser to update statistics. Why are you thinking to use this option? Rebuilding indexes will automatically update the stats anyway
April 22, 2009 at 9:56 pm
Because I don't want to drop the views/indexes and re-create them. Just switching the option on, if it gives the same result, is much more convenient. And I don't care about query optimiser at the time of massive data load which happens during the night when no user activity is present. After the data load I will switch the option OFF and re-build the indexes.
April 23, 2009 at 1:46 am
Up! 🙂
April 23, 2009 at 2:03 am
Turning that off is not the same as dropping the indexes. All you gain by turning that option off is that SQL won't automatically update statistics if it thinks that they are stale, but it will only do that on a read anyway, so if you're not reading the indexed views as part of the load you gain no benefit.
The indexes on the views will still be updated as the base data changes and that's likely where most of the cost of those indexes, ito the bulk load, is.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply