Đang chuẩn bị liên kết để tải về tài liệu:
Microsoft SQL Server 2008 R2 Unleashed- P143

Microsoft SQL Server 2008 R2 Unleashed- P143:SQL Server 2005 provided a number of significant new features and enhancements over what was available in SQL Server 2000. This is not too surprising considering there was a five-year gap between these major releases.Microsoft SQL Server 2008 is not as much of a quantum leap forward from SQL Server 2005 | 1364 CHAPTER 37 Locking and Performance table in parallel while preventing other processes that are not bulk copying data from accessing the table. If there are any indexes on the table or any other processes already holding locks on the table a bulk update lock cannot be granted. SQL Server Lock Granularity Lock granularity is essentially the amount of data locked as part of a query or update to provide complete isolation and serialization for the transaction. The Lock Manager needs to balance the concurrent access to resources versus the overhead of maintaining a large number of lower-level locks. For example the smaller the lock size the greater the number of concurrent users who can access the same table at the same time but the greater the overhead in maintaining those locks. The greater the lock size the less overhead required to manage the locks but concurrency is also less. Figure 37.6 demonstrates the trade-offs between lock size and concurrency. Performance FIGURE 37.6 Trade-offs between performance and concurrency depending on lock granularity. Currently SQL Server balances performance and concurrency by locking at the row level or higher. Based on a number of factors such as key distribution number of rows row density search arguments SARGs and so on the Query Optimizer makes lock granularity decisions internally and the programmer does not have to worry about such issues. SQL Server provides a number of T-SQL extensions that give you better control over query behavior from a locking standpoint. These Query Optimizer overrides are discussed in the Table Hints for Locking section later in this chapter. SQL Server provides the following locking levels DATABASE Whenever a SQL Server process is using a database other than master the Lock Manager grants a database lock to the process. These are always shared locks and they are used to keep track of when a database is in use to prevent another process from dropping the database setting the database offline or