Update query is taking longer time

Question

Update query is taking longer time

Alone

Right there with Babe

Points: 783
More actions
March 21, 2013 at 8:50 am

#394443

Hi,
We had to delete huge data from one table and after that we rebuild indexes associated with this table then update the statistics for this table.
But now for any update operation is taking longer time.
What should i check now?

Viewing 8 posts - 1 through 7 (of 7 total)

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

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 1

It could be page splits. What is the fill factor on the index you rebuilt and are you updating a variable length column (e.g. a VARCHAR or NVARCHAR) that either a) was NULL before the update or b) had a shorter length before the update?

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

Alone Right there with Babe Points: 783 More actions · Answer 2

Hi,

I have verified fill factor which didn't get changed after rebuilding indexes..

and normal update query which used to take eralier 0 sec but now it is taking 6-7 sec

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 3

Alone (3/22/2013)
Hi,
I have verified fill factor which didn't get changed after rebuilding indexes..

And what was it?

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

Alone Right there with Babe Points: 783 More actions · Answer 4

Alone

Right there with Babe

Points: 783

March 25, 2013 at 11:55 pm

#1600613

Hi ,

it was 90.

Seraj Alam-256815 SSC Eights! Points: 804 More actions · Answer 5

If the disk is performing normally and the query is similar that were used earlier for benchmarking, I do not see any solid reason. However, can you check if you missed to rebuild / create an index on column (say x) and the update query says where x=....

check the execution plan to confirm it is not scanning the table, an if it is probably you have missed one index.

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 6

Alone (3/25/2013)
Hi ,
it was 90.

I had a couple other questions too. What was the data type of the column you were updating and what was the nature of the change?

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 7

If you post the Actual execution plan for one of your long running updqtes that would help too.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato