Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
SQLIntel
Starting Member
4 Posts |
 Posted - 2009-06-04 : 10:47:44
|
| Action: I add a 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. Before 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, seemingly, 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 |
|
|
dinakar
Master Smack Fu Yak Hacker
2507 Posts |
Posted - 2009-06-04 : 10:52:48
|
| what do you mean by "REBUILD INDEX for the last few indexes created on this table"? Also when you say "I add a field" do you mean you are adding a column to the table? Are you doing this on a regular basis? How big is the table (rows)?Dinakar Nethi************************Life is short. Enjoy it.************************http://weblogs.sqlteam.com/dinakar/ |
 |
|
|
SQLIntel
Starting Member
4 Posts |
Posted - 2009-06-04 : 12:47:03
|
| Thanks for asking Dinakar.1) By "rebuild index" I either do the following: ALTER INDEX abc ON xxx.yyy REBUILD WITH (FILLFACTOR = 75, PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, SORT_IN_TEMPDB = OFF, ONLINE = OFF)or I right-click on index in SSMS and choose Rebuild.2) Yes, with "add a field" I meant a "column".3) We add a couple of fields a month to this table.4) There are 600,000 rows in table.Also, this table is a key table in our system. It is constantly being written to and read from. |
|
|
|
dinakar
Master Smack Fu Yak Hacker
2507 Posts |
Posted - 2009-06-04 : 14:32:41
|
quote:
Originally posted by SQLIntel
Thanks for asking Dinakar.1) By "rebuild index" I either do the following: ALTER INDEX abc ON xxx.yyy REBUILD WITH (FILLFACTOR = 75, PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, SORT_IN_TEMPDB = OFF, ONLINE = OFF)or I right-click on index in SSMS and choose Rebuild.2) Yes, with "add a field" I meant a "column".3) We add a couple of fields a month to this table.4) There are 600,000 rows in table.Also, this table is a key table in our system. It is constantly being written to and read from.
My question was about what you meant by "last few indexes"..So to rephrase your question, everytime you add a column, you rebuild the indexes and performance is slow the next morning when users start hitting the db? And then you rebuild the indexes again to fix this?Dinakar Nethi************************Life is short. Enjoy it.************************http://weblogs.sqlteam.com/dinakar/ |
|
|
|
SQLIntel
Starting Member
4 Posts |
Posted - 2009-06-08 : 21:58:34
|
| Yes, that's accurate. But I only need to build the last two indexes that were created. This has been going on since before these "last two" indexes existed. Thanks.Reminder: Don't believe everything you think |
|
|
|
dinakar
Master Smack Fu Yak Hacker
2507 Posts |
Posted - 2009-06-09 : 12:33:18
|
| you might want to collect traces to see if there are any recompilations going on? You can also see DMVs to see the most recompiled queries/procs and see if they are using the tables whose schema is changing.Dinakar Nethi************************Life is short. Enjoy it.************************http://weblogs.sqlteam.com/dinakar/ |
|
|
|
SQLIntel
Starting Member
4 Posts |
Posted - 2009-06-15 : 12:06:01
|
| Thanks. I'll do both of those suggestions.-------------------------Reminder: Don't believe everything you think------------------------- |
|
|
|
|
|
|
|
|
I need to Rebuild Indexes when reboot, modify stru