Post reply

Index info of Lock table

  • September 30, 2005 at 8:48 am

    #102833

    Confused with lock info (Index column) from EM- CurrentActivity- Locks/Objects.

    From BOL we know that Index column presents “The index associated with the resource. If the index is clustered, you see the table name instead.” What table name?

    In my server lock list there are, let say, 3 tables: tblA, tblB and tblC. When I check details (click) for tblB and tblC in Index column I see tblA name (some times for different SPID). Why is that as tblA has nothing to do with both tblB and tblC?  What does it means? Can it be the reason for performance issue?

    Thanks

  • September 30, 2005 at 1:54 pm

    #594405

    Hello,

    I have encountered problems in the past when a materialized view included the tables I expected and a few other tables that were not part of the insert, for example.  The materialized view was indexed, and caused several additional tables to be locked when the insert took place.  This can cause performance problems.

    Look through the objects on your system and see what accesses all of the tables that are causing an issue.  One approach is to right click on each table in EM | servername | databases | DBName | Tables | tablename | right click | select "Display Dependencies".

    If you do this for all three tables, you may see one or more views that are common for all of the tables.  If you see the view then you know where to start fixing things.

    There are probably other possible reasons, but this is one I encounter frequently.  This performance problem is especially common if the DB is used for both data collection and for reporting.  The reporting people like to have a ton of materialized views and indexes to improve query performance.  However, the data collection (inserts and updates) suffers from the number of indexes and views that need to be updated.

    Let us know if this helps.  Others probably have ides as well.

    Wayne

  • September 30, 2005 at 2:53 pm

    #594413

    Wayne, thanks a lot for your answer. I hope I've got your idea but just to clarify: if in Index column for locked tblB I see tblA that means that tblA locked either? Actually, I saw tblA in locked list also but what confused me most that SPID for these 2 tables were different. Probably, that means that there is "direct" lock and "indirect" one.

    Thanks

     

Viewing 3 posts - 1 through 2 (of 2 total)

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