Schema Lock

  • Hi,

    My application is getting slow because of Schema lock. I have attached a excel for reference.

    Any solution for this.??

    Regards,

    Saravanan T

    Regards,
    Saravanan

  • Schema stability locks are taken by every single query (select, insert, update, delete) to ensure that the structure of the table doesn't change while they are running. The only thing that gets blocked by a Sch-S lock is a schema modification (alter table, drop/alter index)

    The last wait type showed SOS Scheduler, not a lock wait. Are you sure there's blocking and are you absolutely sure that the Sch-S locks are the cause of the blocking? If they are, you need to investigate what schema modifications are being done.

    From what you posted, it looks like high CPU and high IOs from a query. I'd start by optimising that procedure.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Gail,

    Actually it is not blocking . Its taking too much of time. It is a simple select from table.

    Regards,
    Saravanan

  • The time it's taking has nothing to do with the schema lock. As I mentioned, all queries (select, insert, update, delete) take schema stability locks.

    What's the query? How big's the table? How many rows are being returned?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Saravanan T (4/23/2010)


    Its taking too much of time. It is a simple select from table.

    Are other queries performing as usual?

    If yes... issue is related to the particular query, in this case follow Gail's suggestion and trace/finetune offending query.

    If no... start looking at the complete system e.g. host and database.

    _____________________________________
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at Amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
  • Gail,

    Please refer my attachement. Im just executing this GRPmainsc_Sp_initSr_Act. Its getting too much of time . Im using a view in this SP .I have attached a view also. View is returning only 68 Rows .

    Regards,
    Saravanan

Viewing 6 posts - 1 through 5 (of 5 total)

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