none
Clustered Index Update or Insert Expensive Query Plan Operator

    Question

  • Hi Everyone,

    I'm in the process of analyzing a sql 2008 database for performance reasons. I have discovered a significant number of DML query plans that seem to show a high operator cost for 'Clustered Index Update' as well for plans that have a 'Clustered Index Insert' operator. By high I mean the operator cost shows one for example of having an estimated cost of 0.030003 (912%). Is this something that I should be concerned about? I'm going to soon (hopefully this week) run 'SET STATISTICS PROFILE ON' to see if that helps explain what might be going on. This particular table's row count is approx 50K. The table has one clustered index on a uniqueidentifier (Random) and one unique non clustered index on a FK column which points to a table that has a row count of approx 260K. There is also a FK to another table with 24 million rows. Most other tables have a similar physical design (PK clustered on random uniqueidentifer surrogate column. FK to this other table with 24 million rows). The system is a OLTP system and is more read intensive than write intensive. About 5 users with write capability in the system at a time. About 10-15 users with read ability active at the same time. Some reports are written against this database.

    The update statement does not attempt to update the clustered key column. It's possible it could update the non clustered key column though that typically never changes.

    I could try to post the show query plan XML if needed if I can get it to fit into this thread.

    Thank You.

    Morgan

    Monday, November 19, 2012 7:58 PM

All replies

  • Post your DDL script along with the update query. Based on the scenario you have to decide which columns needs to be indexed and which shouldn't be. If your update query is using a column in where clause which rarely changes then it is worth to index that column.

    Please mark as helpful and propose as answer if you find this as correct!!! Thanks, Rakesh.

    Tuesday, November 20, 2012 7:35 AM
  • You see Clustered Index Update iterator because the clustered index includes at the leaf level every single column of the table, regardless of the physical implementation of their storage.  Can you show us an execution plan of the query? 

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Blog: Large scale of database and data cleansing
    MS SQL Consultants: Improves MS SQL Database Performance

    Tuesday, November 20, 2012 7:46 AM
    Answerer
  • I forgot to add that the table has a trigger for auditing as well. Running the statement by itself on a machine with no other users produced about the same 912% for the clustered index update operator and only took about a second to finish.

    The XML for the execution plan was past the 60000 character limit. Is there another way to post that? I posted an image snippet for now. Thanks.

    Execution_Plan_1


    • Edited by mlord1 Tuesday, November 20, 2012 10:10 PM
    Tuesday, November 20, 2012 6:20 PM

  • Hi mlord,

    Any progress?

    Thank you for your question. 
    I am trying to involve someone more familiar with this topic for a further look at this issue. Sometime delay might be expected from the job transferring. Your patience is greatly appreciated. 
    Thank you for your understanding and support.


    Maggie Luo
    TechNet Community Support

    Friday, December 07, 2012 5:41 AM
    Moderator
  • The table has one clustered index on a uniqueidentifier (Random) and one unique non clustered

    Hallo Morgan,

    with respect - the problem is a house made problem. A RANDOM uniqueidentier as clustered key is a catastroph. I would recommend to check the fragmentation of your index. I assume a level of >90% and a av. page usage of ~50%.

    The problem with random uniqueifiere is that the values are "random" and need a resort in the b-tree and leaf levels of the CI. Can you post the result of the following query for analysis:

    SELECT	index_id,
    	index_type_desc,
    	index_depth,
    	index_level,
    	avg_fragmentation_in_percent,
    	avg_fragment_size_in_pages,
    	page_count,
    	record_count,
    	avg_page_space_used_in_percent
    FROM	sys.dm_db_index_physical_stats(db_id(), OBJECT_ID('dbo.yourtable'), 1, NULL, 'DETAILED');

    Furthermore check the statistics whether they are up to date:

    DBCC SHOW_STATISTICS('dbo.yourtable, Name_of_PrimaryKey) WITH HISTOGRAM;
    can you post both results?

    Uwe Ricken

    MCITP Database Administrator 2005
    MCITP Database Administrator 2008
    MCITP Microsoft SQL Server 2008, Database Development

    db Berater GmbH
    http://www-db-berater.de
    SQL Server Blog (german only)

    Friday, December 07, 2012 8:15 AM
  • Hi Morgan,

    The update statement does not attempt to update the clustered key column. It's possible it could update the non-clustered key column though that typically never changes.

    Even though the update is not updating the clustered key column, the clustered index itself still needs to be updated because the leaf level page of clustered index is where the actual data resides.

    Just because the clustered Index Update or Insert operator is showing high cost doesn't necessarily mean that it is problem. That operator may just be where the majority of the work is needed. If the update/insert performance differs each time for different parameters and if the time is not acceptable then we need further investigation which may require more in-depth support. The following MSDN blog post that talks about insert/update performance may help answer some questions.

    http://blogs.msdn.com/b/bartd/archive/2006/07/27/680518.aspx

    If you cannot determine your answer here or on your own, consider opening a support case with us. Visit this link to see the various support options that are available to better meet your needs:  http://support.microsoft.com/default.aspx?id=fh;en-us;offerprophone.

    Thanks,

    Cathy Miller

    Friday, December 14, 2012 10:35 PM
    Moderator