June 4, 2012 at 3:32 pm
I have two database that need all their indexes to match.
I noticed some have REBUILD WITH (STATISTICS_NORECOMPUTE = ON) in the Alter Index
I need a script to remove the "with WITH (STATISTICS_NORECOMPUTE = ON)" part.
The source database does not have it and that what I need to match.
June 4, 2012 at 4:12 pm
Drop and recreate indexes..Script them out and remove the options that you dont need. I dont think that altering a live index is possible unless you are changing the columns in the index. I could be very well wrong on this. So please research this a bit more before trying out anything.
June 4, 2012 at 4:39 pm
You do not need to drop and recreate the index. You can use ALTER INDEX to specify the new NORECOMPUTE value.
ALTER INDEX [index_name]
ON [schema].
SET ( STATISTICS_NORECOMPUTE = OFF );
As an aside, how big are the indexes? Has this been your database all along and you just set it to non-default at one point by accident? It's possible it was a conscious decision to disable stats recomputes as those can affect performance if they happen in the middle of the day. The lucky query that happens when SQL Server decides to update stats has to wait for the update to complete before it can proceed. With norecompute off you cannot control when SQL Server will decide to recompute stats and on large table with many DML changes recomputes can create noticeable delays for applications depending on when they happen.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 4, 2012 at 6:32 pm
opc.three (6/4/2012)
It's possible it was a conscious decision to disable stats recomputes as those can affect performance if they happen in the middle of the day. The lucky query that happens when SQL Server decides to update stats has to wait for the update to complete before it can proceed. With norecompute off you cannot control when SQL Server will decide to recompute stats and on large table with many DML changes recomputes can create noticeable delays for applications depending on when they happen.
If predictable query response time is more important than recompiling for optimality reasons, you can use ALTER DATABASE to SET AUTO_UPDATE_STATISTICS_ASYNC ON to avoid this scenario.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
June 4, 2012 at 8:45 pm
I am using a Red Gate tool Data Compare to move the data between two testing environments. Some of the indexes on one box were updated with REBUILD WITH (STATISTICS_NORECOMPUTE = ON) The source sql box does not have that on its alter index statements, so I just need to remove that portion of the Alter Index statement. So it would just be ALTER INDEX [IX_tblUsersName] ON [dbo].[tblUsersName] REBUILD
The data won't sync without the structures being identical.
June 4, 2012 at 8:59 pm
jflynn-735049 (6/4/2012)
So it would just be ALTER INDEX [IX_tblUsersName] ON [dbo].[tblUsersName] REBUILD
The point opc.three was making is that you don't need to rebuild the index just to change this setting. He even provided the syntax to use.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply