The whole point of locking is to optimize for better concurrency. Imagine what life in the database world would be like if there were no locking. Chances are once you had more than one user utilizing the database, you would run into many problems like dirty reads and phantom values.
SQL Server 2008 includes shared, update, exclusive, intent, schema, and bulk update locks. The lock manager within the SQL Server database engine is responsible for managing all these types of locks in all the different transactions. Since holding a lock is not the best thing to do from a performance standpoint, you always want to lock at the most granular level, like at a row level. You can imagine the amount of memory and CPU resources that are required by the lock manager when it’s trying to lock at the lowest level, given potentially thousands of transactions a second. To help alleviate this strain on resources, the lock manager will escalate a lock (for example, from row to table level) given certain circumstances. In general, if a single T-SQL statement acquires at least 5,000 locks on a single nonpartitioned table (or index), or if the lock manager is running short on memory, the lock manager will issue an escalation.
In SQL Server 2008, the ALTER TABLE statement has a SET option for LOCK_ ESCALATION with three
If you use partitions and currently have locking issues, you should definitely consider changing the lock escalation to AUTO as follows:ALTER TABLE [MyTable] SET (LOCK_ESCALATION = AUTO)
You may be pleasantly surprised at the reduction in locking issues after changing to AUTO lock escalation.
SQL Server 2008 Related Interview Questions
|SQL Server 2000 Interview Questions||MSBI Interview Questions|
|SQL Server 2008 Interview Questions||SQL Server 2005 Interview Questions|
|SSIS(SQL Server Integration Services) Interview Questions||SSRS(SQL Server Reporting Services) Interview Questions|
|Microsoft Entity Framework Interview Questions||LINQ Interview Questions|
|SQL Server Analysis Services (SSAS) Interview Questions||Sql Server Dba Interview Questions|
SQL Server 2008 Related Practice Tests
|SQL Server 2000 Practice Tests||MSBI Practice Tests|
|SQL Server 2008 Practice Tests||SQL Server 2005 Practice Tests|
|SSIS(SQL Server Integration Services) Practice Tests||SSRS(SQL Server Reporting Services) Practice Tests|
|Microsoft Entity Framework Practice Tests||LINQ Practice Tests|
Sql Server 2008 Tutorial
Sql Server 2008 Overview
Sql Server Installation And Configuration
Sql Server Encryption
Automation And Monitoring
Integrated Full-text Search
New Datatypes In Sql Server 2008
T-sql Enhancements For Developers
T-sql Enhancements For Dbas
Sql Server And Xml
Sql Server Xml And Xquery Support
Linq To Sql
All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd
Wisdomjobs.com is one of the best job search sites in India.