Update query is taking longer time

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » Administering » Update query is taking longer time

Update query is taking longer time

Rate Topic

Display Mode

Topic Options

Author

Message

Alone

Posted Thursday, March 21, 2013 8:50 AM

Grasshopper

Group: General Forum Members
Last Login: 2 days ago @ 8:10 AM
Points: 21, Visits: 200

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?

Post #1433833

opc.three

Posted Thursday, March 21, 2013 12:51 PM

SSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 11:14 PM
Points: 6,706, Visits: 11,738

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

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy

Post #1433979

Alone

Posted Friday, March 22, 2013 8:41 AM

Grasshopper

Group: General Forum Members
Last Login: 2 days ago @ 8:10 AM
Points: 21, Visits: 200

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

Post #1434307

opc.three

Posted Friday, March 22, 2013 9:25 AM

SSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 11:14 PM
Points: 6,706, Visits: 11,738

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

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy

Post #1434332

Alone

Posted Monday, March 25, 2013 11:55 PM

Grasshopper

Group: General Forum Members
Last Login: 2 days ago @ 8:10 AM
Points: 21, Visits: 200

Hi ,

it was 90.

Post #1435270

Seraj Alam-256815

Posted Tuesday, March 26, 2013 5:12 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, May 17, 2013 4:14 AM
Points: 151, Visits: 295

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.

Post #1435391

opc.three

Posted Tuesday, March 26, 2013 7:56 AM

SSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 11:14 PM
Points: 6,706, Visits: 11,738

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

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy

Post #1435492

opc.three

Posted Tuesday, March 26, 2013 7:58 AM

SSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 11:14 PM
Points: 6,706, Visits: 11,738

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

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy

Post #1435495

« Prev Topic | Next Topic »

Permissions