The Real World: Rebuilding Index - 1 Instance, 106 Databases

Question

The Real World: Rebuilding Index - 1 Instance, 106 Databases

Viewing 4 posts - 31 through 33 (of 33 total)

You must be logged in to reply to this topic. Login to reply

Kenneth Igiri SSCarpal Tunnel Points: 4998 More actions · Answer 1

Oscardmg (2/19/2015)
Hello
I want to know if after you rebuild and reorganize the need to update the statistics with the command "EXEC sp_updatestats"
Thanks.

A reindex also updates stats with a good sample size..

Br. Kenneth Igiri
https://kennethigiri.com
All nations come to my light, all kings to the brightness of my rising

Kenneth Igiri SSCarpal Tunnel Points: 4998 More actions · Answer 2

Thanks a lot... 🙂

Br. Kenneth Igiri
https://kennethigiri.com
All nations come to my light, all kings to the brightness of my rising

SQLBoar SSC Veteran Points: 278 More actions · Answer 3

No you should never use exec sp_updatestats, as it does all of them at once. Never recomend to use that on productions systems. Not only because of the immediate impact, but as others have alluded, you can end up with somewhat poorer quality statistics anyway. Use "update statistics .." instead for just the columns or indexes you need to refresh, see SQL BOL.

This DBA says - "It depends".

noor.soft2011 Newbie Points: 5 More actions · Answer 4

Hi,

Thanks for such a nice Script. I am getting below error while running the job. Can you please help me out.

The Error message is

Executed as user: NT SERVICE\SQLAgent$SQL2K12. ALTER INDEX failed because the following SET options have incorrect settings: 'QUOTED_IDENTIFIER'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or filtered indexes and/or query notifications and/or XML data type methods and/or spatial index operations. [SQLSTATE 42000] (Error 1934) Total number of rebuild statements:1 [SQLSTATE 01000] (Error 0) ALTER INDEX [IDX_NoddyDate] ON [dbo].[NoddyTable] REBUILD WITH (ONLINE = OFF,FILLFACTOR=80,SORT_IN_TEMPDB=ON,PAD_INDEX = ON, STATISTICS_NORECOMPUTE = OFF); [SQLSTATE 01000] (Error 0). The step failed.