locked
Possible to check how much time an update statement on a table would take ? RRS feed

  • Question

  • Hello @all

    I have a table with a fragmented Index, the table has an actula "user_updates" from 226'699.

    How can i find out how much time an update on this table takes ?

    i would like to check if there is any performance increase when i regroup the index (rebuild), cause the index has a fragementation of 85% now... (or should i delete the index completely) cause there are no user_seeks... only user_updates on the table... and i thought that indexes only makes sense if users are reading from the table... am i wrong ? (cause on every update from the table, the index (indexes) have to be updated too...

    Thanks and Regards

    Dominic

    Tuesday, December 17, 2013 10:12 AM

Answers

  • Hello @all

    I have a table with a fragmented Index, the table has an actula "user_updates" from 226'699.

    How can i find out how much time an update on this table takes ?

    i would like to check if there is any performance increase when i regroup the index (rebuild), cause the index has a fragementation of 85% now... (or should i delete the index completely) cause there are no user_seeks... only user_updates on the table... and i thought that indexes only makes sense if users are reading from the table... am i wrong ? (cause on every update from the table, the index (indexes) have to be updated too...

    Thanks and Regards

    Dominic

    Hello,

    If such the case I think Index is just causing overhead it is not being utilized so you can remove it.As a matter of fact unused indexes are sometimes major cause for slowness of DML operation as extra time I/O goes for each row which is being updated.

    Its really difficult to predict how much time Update command will take it depends on various parameters


    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers

    • Proposed as answer by Kalman Toth Tuesday, December 17, 2013 5:41 PM
    • Marked as answer by Hubi8302 Thursday, December 19, 2013 7:34 AM
    Tuesday, December 17, 2013 10:39 AM
  • Rebuilding the index will not likely result in a modification be quicker. At least not more than marginal. In fact, it might wven be slower, since if you currently have space on the index page for the new row and then rebuild so you *don't* have space then the index rebuild will make that update be slower (at least for the cases where you get page splits). 

    However if the index isn't used to find rows (any of the other three columns), then you have an index which only cost you and doesn't help you!


    Tibor Karaszi, SQL Server MVP | web | blog

    • Proposed as answer by Kalman Toth Tuesday, December 17, 2013 5:41 PM
    • Marked as answer by Hubi8302 Thursday, December 19, 2013 7:34 AM
    Tuesday, December 17, 2013 10:40 AM
  • To do an update the row to update has to be found - the index might help with this and it might save more than updating the index loses.

    Sometimes it is better to get the PKs (or something unique-ish and small) for the rows to update then use that for the update.

    If you rebuild the index you could end up with more page splits and slow down the updates.

    If the index isn't being used at all (including enforcing integrity constraints) then delete it.

    • Proposed as answer by Kalman Toth Tuesday, December 17, 2013 5:41 PM
    • Marked as answer by Hubi8302 Thursday, December 19, 2013 7:34 AM
    Tuesday, December 17, 2013 10:58 AM
  • user_seeks = 327 , user_scan = 0, user_lookups = 0

    is there a possibility in sql to stop the time how long an update statments goes ?

    Regards, Dominic

    Are you running a batch update query? Any chance you can provide your statement?

    About your question, there is NO way to stop the execution on time based.

    • Marked as answer by Hubi8302 Thursday, December 19, 2013 7:34 AM
    Tuesday, December 17, 2013 11:48 AM
    Answerer

All replies

  • Hello @all

    I have a table with a fragmented Index, the table has an actula "user_updates" from 226'699.

    How can i find out how much time an update on this table takes ?

    i would like to check if there is any performance increase when i regroup the index (rebuild), cause the index has a fragementation of 85% now... (or should i delete the index completely) cause there are no user_seeks... only user_updates on the table... and i thought that indexes only makes sense if users are reading from the table... am i wrong ? (cause on every update from the table, the index (indexes) have to be updated too...

    Thanks and Regards

    Dominic

    Hello,

    If such the case I think Index is just causing overhead it is not being utilized so you can remove it.As a matter of fact unused indexes are sometimes major cause for slowness of DML operation as extra time I/O goes for each row which is being updated.

    Its really difficult to predict how much time Update command will take it depends on various parameters


    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers

    • Proposed as answer by Kalman Toth Tuesday, December 17, 2013 5:41 PM
    • Marked as answer by Hubi8302 Thursday, December 19, 2013 7:34 AM
    Tuesday, December 17, 2013 10:39 AM
  • Rebuilding the index will not likely result in a modification be quicker. At least not more than marginal. In fact, it might wven be slower, since if you currently have space on the index page for the new row and then rebuild so you *don't* have space then the index rebuild will make that update be slower (at least for the cases where you get page splits). 

    However if the index isn't used to find rows (any of the other three columns), then you have an index which only cost you and doesn't help you!


    Tibor Karaszi, SQL Server MVP | web | blog

    • Proposed as answer by Kalman Toth Tuesday, December 17, 2013 5:41 PM
    • Marked as answer by Hubi8302 Thursday, December 19, 2013 7:34 AM
    Tuesday, December 17, 2013 10:40 AM
  • To do an update the row to update has to be found - the index might help with this and it might save more than updating the index loses.

    Sometimes it is better to get the PKs (or something unique-ish and small) for the rows to update then use that for the update.

    If you rebuild the index you could end up with more page splits and slow down the updates.

    If the index isn't being used at all (including enforcing integrity constraints) then delete it.

    • Proposed as answer by Kalman Toth Tuesday, December 17, 2013 5:41 PM
    • Marked as answer by Hubi8302 Thursday, December 19, 2013 7:34 AM
    Tuesday, December 17, 2013 10:58 AM
  • Hello @all

    I have a table with a fragmented Index, the table has an actula "user_updates" from 226'699.


    Hello Hubi8302,

    Could you please let us know actual user_seeks and scans for the index on the table?

    Also provide the number of pages for your index?

    Tuesday, December 17, 2013 11:19 AM
    Answerer
  • user_seeks = 327 , user_scan = 0, user_lookups = 0

    is there a possibility in sql to stop the time how long an update statments goes ?

    Regards, Dominic

    Tuesday, December 17, 2013 11:29 AM
  • user_seeks = 327 , user_scan = 0, user_lookups = 0

    is there a possibility in sql to stop the time how long an update statments goes ?

    Regards, Dominic

    Are you running a batch update query? Any chance you can provide your statement?

    About your question, there is NO way to stop the execution on time based.

    • Marked as answer by Hubi8302 Thursday, December 19, 2013 7:34 AM
    Tuesday, December 17, 2013 11:48 AM
    Answerer
  • Be very careful when removing indexes, they might not be read from but they could be there to enforce uniqueness.
    • Proposed as answer by Kalman Toth Tuesday, December 17, 2013 5:41 PM
    Tuesday, December 17, 2013 12:34 PM
  • Rebuilding the index will not likely result in a modification be quicker. At least not more than marginal. In fact, it might wven be slower, since if you currently have space on the index page for the new row and then rebuild so you *don't* have space then the index rebuild will make that update be slower (at least for the cases where you get page splits). 

    However if the index isn't used to find rows (any of the other three columns), then you have an index which only cost you and doesn't help you!


    Tibor Karaszi, SQL Server MVP | web | blog


    Tibor, check out this forum thread: http://social.technet.microsoft.com/Forums/sqlserver/en-US/64ad4f52-2fd8-4266-b4a4-5657c8870246/needed-more-answerers?forum=sqlgetstarted

    Ed Price, Power BI & SQL Server Customer Program Manager (Blog, Small Basic, Wiki Ninjas, Wiki)

    Answer an interesting question? Create a wiki article about it!

    Friday, December 20, 2013 12:33 AM