August 30, 2009 at 5:48 am
Hey all,
I have a table with about 35 million records. Using a date and a unique identifier I have been experiencing great performance on selects.
I had to do an update of a large number of those records and sicne then my select performance has gone down dramatically.
Im thinking it has something to do with the indexes? While I did not change them is there some kind of refresh I need to do after UPDATEing a table this size?
Just wanted to mention I did have the need to add a few columns to this table. I didnt see the performance change until after I did the updates.
Thanks
August 30, 2009 at 5:51 am
If possible rebuild the indexes and then update statistics. Or just update statistics will help.
August 30, 2009 at 1:22 pm
Adding columns won't normally cause a performance change. Adding data to the new columns will. Chances are, you have page splits all over the place. I'd recommend doing a rebuild on the clustered index and then rebuilding the other indexes.
--Jeff Moden
Change is inevitable... Change for the better is not.
September 1, 2009 at 12:25 pm
was the updates done on columns that are index? you did not mention if you ran update stats after the update.
September 1, 2009 at 2:01 pm
cconnors (9/1/2009)
was the updates done on columns that are index? you did not mention if you ran update stats after the update.
That won't matter if it's the clustered index.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 13, 2010 at 3:03 am
I didn't get it where the columns were indexed. I got that the columns were a date and a unique identifier. But perhaps the indexs for table could should be re-built after that update.
I'm wondering if he wouldn't see improved performance if he did index one of these columns he's using for his SELECT.
I'm also wondering if a non-clustered index wouldn't provide him better perfomance for the size of a table he has.
😉
January 13, 2010 at 8:55 am
RSage (1/13/2010)
I didn't get it where the columns were indexed. I got that the columns were a date and a unique identifier. But perhaps the indexs for table could should be re-built after that update.I'm wondering if he wouldn't see improved performance if he did index one of these columns he's using for his SELECT.
I'm also wondering if a non-clustered index wouldn't provide him better perfomance for the size of a table he has.
😉
Either way, page splits can cause slowness when you do a SELECT on a table.
A index reorganize after an large update should help..not to mention updating the statistics too...
The_SQL_DBA
MCTS
"Quality is never an accident; it is always the result of high intention, sincere effort, intelligent direction and skillful execution; it represents the wise choice of many alternatives."
January 14, 2010 at 7:17 am
Key point that many miss:
If you do a REORG of index, you MUST update the statistics manually.
If you do a REBUILD of index, you must NOT update the statistics manually - you will get WORSE statistics if you do so and do not specify FULLSCAN. If you do specify FULLSCAN then you have simply wasted effort and time for no gain.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply