I need to Rebuild Indexes when Reboot, modify structure, or Rebuild (for real)

Question

I need to Rebuild Indexes when Reboot, modify structure, or Rebuild (for real)

mark-1076786

Grasshopper

Points: 17
More actions
June 3, 2009 at 12:30 pm

#210234

Action: Add field to this table OR Rebuild all indexes on this table, or reboot SQL Server.
Consequence: All SQL Server processes are slow until I REBUILD INDEX for the last few indexes created on this table. When I rebuild, the fragmentation is only a fraction of a percent.
Note: If I add a field at night and then rebuild the last few indexes, the problem will arise the next morning as soon as users start working. That is, I need to rebuild a few indexes AFTER records are created.
This only happens to one table in the database.
DBCC on table does not return errors.
I'm not sure where to even start looking into this problem. Has anyone experienced anything similar?
Thanks,
Mark

Viewing 5 posts - 1 through 4 (of 4 total)

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Try updating statistics on that table when the problem happens

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

SQLIntel Grasshopper Points: 24 More actions · Answer 2

Thanks Gail. STATS_DATE shows that the statistics for all indexes were last updated a couple of days ago. They were updated because when I rebuilt indexes I did not SET STATISTICS_NORECOMPUTE = ON. So by default, the stats were updated.

And after this rebuild, I also had the problem. that is, I rebuilt all indexes on Sunday and Monday morning (the system was crawling), I had to rebuild a few recently created indexes.

Any other ideas?

Paul Randal One Orange Chip Points: 29544 More actions · Answer 3

Is this a newly deployed/changed database, or is this a database that hasn't change and is suddenly seeing problems? Has the workload suddenly increased?

Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005

SQLIntel Grasshopper Points: 24 More actions · Answer 4

Database has been live for 2 years now. Increase in workload is consistent. The problem started months ago, but only recently can I pinpoint what causes the problem and what solves it.