Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Update query is taking longer time Expand / Collapse
Author
Message
Posted Thursday, March 21, 2013 8:50 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, August 12, 2014 10:36 PM
Points: 27, Visits: 287
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
Posted Thursday, March 21, 2013 12:51 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 7:19 PM
Points: 7,127, Visits: 12,655
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
Post #1433979
Posted Friday, March 22, 2013 8:41 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, August 12, 2014 10:36 PM
Points: 27, Visits: 287
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
Posted Friday, March 22, 2013 9:25 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 7:19 PM
Points: 7,127, Visits: 12,655
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
Post #1434332
Posted Monday, March 25, 2013 11:55 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, August 12, 2014 10:36 PM
Points: 27, Visits: 287
Hi ,

it was 90.
Post #1435270
Posted Tuesday, March 26, 2013 5:12 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 3:40 AM
Points: 184, Visits: 347
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
Posted Tuesday, March 26, 2013 7:56 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 7:19 PM
Points: 7,127, Visits: 12,655
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
Post #1435492
Posted Tuesday, March 26, 2013 7:58 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 7:19 PM
Points: 7,127, Visits: 12,655
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
Post #1435495
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse