Schema Lock

Question

Schema Lock

Saravanan T

SSCrazy

Points: 2506
More actions
April 23, 2010 at 4:20 am

#221760

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

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

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

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

Saravanan T SSCrazy Points: 2506 More actions · Answer 2

Gail,

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

Regards,
Saravanan

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

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

PaulB-TheOneAndOnly SSC Guru Points: 66772 More actions · Answer 4

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.

Saravanan T SSCrazy Points: 2506 More actions · Answer 5

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