> One thing I forgot to mention, in the script we are re-adding our indices
> to
> the new table exactly as they were in the original table.
>
> "Larry Herbinaux" wrote:
>
>> We created a script to create new tables for all tables that have an
>> Identity
>> column. The new tables now have Identity (Not For Replication). We then
>> copied all the data to the new tables, dropped the old tables and renamed
>> the
>> new temporary tables back to the original table name.
>>
>> We then ran the following query to reindex the tables:
>>
>> EXEC sp_MSforeachtable @command1="DBCC DBREINDEX (''?'') WITH
>> NO_INFOMSGS"
>>
>> We also ran sp_recompile on all of the stored procedures
>>
>> After running our stored procedures a second time (e.g. after the
>> recompile
>> has occured) we are seeing about a 5x performance degredation.
>>
>> Is there something else we need to do to get back to our original
>> performances for these stored procedures?

"Hilary Cotter" wrote:

> You may have introduced some fragmentation. I would update statistics and
> then try a defrag.
>
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
>
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
>
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
>
>
> "Larry Herbinaux" <LarryHerbinaux@discussions.microsoft.com> wrote in
> message news:C98D8860-8C8B-4305-9B6F-3BA13091A738@microsoft.com...
> > One thing I forgot to mention, in the script we are re-adding our indices
> > to
> > the new table exactly as they were in the original table.
> >
> > "Larry Herbinaux" wrote:
> >
> >> We created a script to create new tables for all tables that have an
> >> Identity
> >> column. The new tables now have Identity (Not For Replication). We then
> >> copied all the data to the new tables, dropped the old tables and renamed
> >> the
> >> new temporary tables back to the original table name.
> >>
> >> We then ran the following query to reindex the tables:
> >>
> >> EXEC sp_MSforeachtable @command1="DBCC DBREINDEX (''?'') WITH
> >> NO_INFOMSGS"
> >>
> >> We also ran sp_recompile on all of the stored procedures
> >>
> >> After running our stored procedures a second time (e.g. after the
> >> recompile
> >> has occured) we are seeing about a 5x performance degredation.
> >>
> >> Is there something else we need to do to get back to our original
> >> performances for these stored procedures?
>
>