Microsoft KB, TechNet:
-- How to resolve blocking problems that are caused by lock escalation in SQL Server
http://support.microsoft.com/kb/323630/en-us
-- Lock Escalation (Database Engine)
http://msdn.microsoft.com/en-us/library/ms184286.aspx
-- Minimize Blocking in SQL Server
http://technet.microsoft.com/en-us/magazine/cc434694.aspx
Other sites:
-- Troubleshoot SQL Server Locking
http://www.nwsummit.com/tech/mssql.html
Показаны сообщения с ярлыком database optimization. Показать все сообщения
Показаны сообщения с ярлыком database optimization. Показать все сообщения
суббота, 13 сентября 2008 г.
Understanding and Resolving SQL Server 7.0 or 2000 Blocking Problems
// from http://support.microsoft.com/kb/224453/
SUMMARY
This article is an update for SQL Server 7.0 of the following article, which applies to SQL Server 6.x, in the Microsoft Knowledge Base:
162361 (http://support.microsoft.com/kb/162361/EN-US/) INF: Understanding and Resolving SQL Server Blocking Problems
Much of the information contained in the above article has already been updated and included in SQL Server 7.0 Books Online in the "Understanding and Avoiding Blocking" topic. Carefully review this information before proceeding with this article; it will not be repeated here. This article focuses on how to monitor SQL Server to capture pertinent system information and how to analyze that information to successfully resolve blocking issues.
This article uses the same standard terminology as defined in the above information. In this discussion, the term "connection" refers to a single logged-on session of the database. Each connection appears as a system process ID (SPID). Each of these SPIDs is often referred to as a process, although it is not a separate process context in the usual sense. Rather, each SPID consists of the server resources and data structures necessary to service the requests of a single connection from a given client. A single client application may have one or more connections. From the perspective of SQL Server, there is no difference between multiple connections from a single client application on a single client computer and multiple connections from multiple client applications or multiple client computers. One connection can block another connection, regardless of whether they emanate from the same application or separate applications on two different client computers.
SUMMARY
This article is an update for SQL Server 7.0 of the following article, which applies to SQL Server 6.x, in the Microsoft Knowledge Base:
162361 (http://support.microsoft.com/kb/162361/EN-US/) INF: Understanding and Resolving SQL Server Blocking Problems
Much of the information contained in the above article has already been updated and included in SQL Server 7.0 Books Online in the "Understanding and Avoiding Blocking" topic. Carefully review this information before proceeding with this article; it will not be repeated here. This article focuses on how to monitor SQL Server to capture pertinent system information and how to analyze that information to successfully resolve blocking issues.
This article uses the same standard terminology as defined in the above information. In this discussion, the term "connection" refers to a single logged-on session of the database. Each connection appears as a system process ID (SPID). Each of these SPIDs is often referred to as a process, although it is not a separate process context in the usual sense. Rather, each SPID consists of the server resources and data structures necessary to service the requests of a single connection from a given client. A single client application may have one or more connections. From the perspective of SQL Server, there is no difference between multiple connections from a single client application on a single client computer and multiple connections from multiple client applications or multiple client computers. One connection can block another connection, regardless of whether they emanate from the same application or separate applications on two different client computers.
How to resolve blocking problems that are caused by lock escalation in SQL Server
// from http://support.microsoft.com/kb/323630
SUMMARY
Lock escalation is the process of converting many fine-grained locks (such as row or page locks) into table locks. Microsoft SQL Server dynamically determines when to perform lock escalation. When making this decision, SQL Server takes into account the number of locks that are held on a particular scan, the number of locks that are held by the whole transaction, and the memory that is being used for locks in the system as a whole. Typically, SQL Server's default behavior results in lock escalation occurring only at those points where it would improve performance or when you must reduce excessive system lock memory to a more reasonable level. However, some application or query designs may trigger lock escalation at a time when it is not desirable, and the escalated table lock may block other users. This article discusses how to determine whether lock escalation is causing blocking and how to deal with undesirable lock escalation.
MORE INFORMATION
How to Determine Whether Lock Escalation Is Causing Blocking
Lock escalation does not cause most blocking problems. To determine whether lock escalation is occurring around the time when you experience blocking issues, start a SQL Profiler trace that includes the Lock:Escalation event. If you do not see any Lock:Escalation events, lock escalation is not occurring on your server and the information in this article does not apply to your situation.
If lock escalation is occurring, verify that the escalated table lock is blocking other users.
For more information about how to identify the head blocker and how to identify the lock resource held by the head blocker that is blocking other server process IDs (SPIDs), click the following article number to view the article in the Microsoft Knowledge Base:
224453 (http://support.microsoft.com/kb/224453/) Understanding and resolving SQL Server 7.0 or 2000 blocking problems
If the lock that is blocking other users is anything other than a TAB (table-level) lock with a lock mode of S (shared), or X (exclusive), lock escalation is not the issue. In particular, if the TAB lock is an intent lock (such as a lock mode of IS, IU, or IX), this is not the result of lock escalation. If your blocking problems are not being caused by lock escalation, see the article Q224453 for troubleshooting steps.
How to Prevent Lock Escalation
The simplest and safest way to prevent lock escalation is to keep transactions short and to reduce the lock footprint of expensive queries so that the lock escalation thresholds are not exceeded. There are several ways to obtain this goal, many of which are listed:
• Break up large batch operations into several smaller operations. For example, suppose you ran the following query to remove several hundred thousand old records from an audit table, and then you found that it caused a lock escalation that blocked other users:
DELETE FROM LogMessages WHERE LogDate < '2/1/2002'
By removing these records a few hundred at a time, you can dramatically reduce the number of locks that accumulate per transaction and prevent lock escalation. For example:
SET ROWCOUNT 500
delete_more:
DELETE FROM LogMessages WHERE LogDate < '2/1/2002'
IF @@ROWCOUNT > 0 GOTO delete_more
SET ROWCOUNT 0
• Reduce the query's lock footprint by making the query as efficient as possible. Large scans or large numbers of Bookmark Lookups may increase the chance of lock escalation; additionally, it increases the chance of deadlocks, and generally adversely affects concurrency and performance. After you find the query that causes lock escalation, look for opportunities to create new indexes or to add columns to an existing index to remove index or table scans and to maximize the efficiency of index seeks. Consider pasting the query into a Query Analyzer query window to perform an automatic index analysis on it. To do so, on the Query menu, click Index Tuning Wizard in SQL Server 2000, or click Perform Index Analysis in SQL Server 7.0.
One goal of this optimization is to make index seeks return as few rows as possible to minimize the cost of Bookmark Lookups (maximize the selectivity of the index for the particular query). If SQL Server estimates that a Bookmark Lookup logical operator may return many rows, it may use a PREFETCH to perform the bookmark lookup. If SQL Server does use PREFETCH for a bookmark lookup, it must increase the transaction isolation level of a portion of the query to repeatable read for a portion of the query. This means that what may look similar to a SELECT statement at a read-committed isolation level may acquire many thousands of key locks (on both the clustered index and one nonclustered index), which can cause such a query to exceed the lock escalation thresholds. This is especially important if you find that the escalated lock is a shared table lock, which, however, is not commonly seen at the default read-committed isolation level. If a Bookmark Lookup WITH PREFETCH clause is causing the escalation, consider adding additional columns to the nonclustered index that appears in the Index Seek or the Index Scan logical operator below the Bookmark Lookup logical operator in the query plan. It may be possible to create a covering index (an index that includes all columns in a table that were used in the query), or at least an index that covers the columns that were used for join criteria or in the WHERE clause if including everything in the select column list is impractical.
A Nested Loop join may also use PREFETCH, and this causes the same locking behavior.
For more information, click the following article number to view the article in the Microsoft Knowledge Base:
260652 (http://support.microsoft.com/kb/260652/) Nested loop join that uses a "BOOKMARK LOOKUP ...WITH PREFETCH" may hold locks longer
• Lock escalation cannot occur if a different SPID is currently holding an incompatible table lock. Lock escalation always escalates to a table lock, and never to page locks. Additionally, if a lock escalation attempt fails because another SPID holds an incompatible TAB lock, the query that attempted escalation does not block while waiting for a TAB lock. Instead, it continues to acquire locks at its original, more granular level (row, key, or page), periodically making additional escalation attempts. Therefore, one method to prevent lock escalation on a particular table is to acquire and to hold a lock on a different connection that is not compatible with the escalated lock type. An IX (intent exclusive) lock at the table level does not lock any rows or pages, but it is still not compatible with an escalated S (shared) or X (exclusive) TAB lock. For example, assume that you must run a batch job that modifies a large number of rows in the mytable table and that has caused blocking that occurs because of lock escalation. If this job always completes in less than an hour, you might create a Transact-SQL job that contains the following code, and schedule the new job to start several minutes before the batch job's start time:
BEGIN TRAN
SELECT * FROM mytable (UPDLOCK, HOLDLOCK) WHERE 1=0
WAITFOR DELAY '1:00:00'
COMMIT TRAN
This query acquires and holds an IX lock on mytable for one hour, which prevents lock escalation on the table during that time. This batch does not modify any data or block other queries (unless the other query forces a table lock with the TABLOCK hint or if an administrator has disabled page or row locks by using an sp_indexoption stored procedure).
Additionally, you can disable lock escalation by enabling trace flag 1211. However, this trace flag disables all lock escalation globally in the instance of SQL Server. Lock escalation serves a very useful purpose in SQL Server by maximizing the efficiency of queries that are otherwise slowed down by the overhead of acquiring and releasing several thousands of locks. Lock escalation also helps to minimize the required memory to keep track of locks. The memory that SQL Server can dynamically allocate for lock structures is finite, so if you disable lock escalation and the lock memory grows large enough, attempts to allocate additional locks for any query may fail and the following error occurs:
Error: 1204, Severity: 19, State: 1
The SQL Server cannot obtain a LOCK resource at this time. Rerun your statement when there are fewer active users or ask the system administrator to check the SQL Server lock and memory configuration.
Note When a "1204" error occurs, it stops the processing of the current statement and causes a rollback of the active transaction. The rollback itself may block users or lead to a long database recovery time if you restart the SQL Server service.
Using a lock hint such as ROWLOCK only alters the initial lock plan. Lock hints do not prevent lock escalation.
The other methods of preventing lock escalation that are discussed earlier in this article are better options than enabling the trace flag. Additionally, the other methods generally result in better performance for the query than disabling lock escalation for the whole instance. Microsoft recommends enabling this trace flag only to mitigate severe blocking that is caused by lock escalation while other options, such as those discussed earlier in this article, are being investigated. To enable a trace flag so that it is turned on whenever SQL Server is started, add it as a server startup parameter.
To add a server startup parameter, right-click the server in SQL Enterprise Manager, click Properties, and then on the General tab, click Startup Parameters, and then add the following parameter (exactly as shown):
-T1211
You must cycle the SQL Server service for a new startup parameter to take effect. If you run the following query in Query Analyzer the trace flag takes effect immediately:
DBCC TRACEON (1211, -1)
However, if you do not add the -T1211 startup parameter, the effect of a traceon command is lost when the SQL Server service is cycled. Turning on the trace flag prevents any future lock escalations, but it does not reverse any lock escalations that have already occurred in an active transaction.
SUMMARY
Lock escalation is the process of converting many fine-grained locks (such as row or page locks) into table locks. Microsoft SQL Server dynamically determines when to perform lock escalation. When making this decision, SQL Server takes into account the number of locks that are held on a particular scan, the number of locks that are held by the whole transaction, and the memory that is being used for locks in the system as a whole. Typically, SQL Server's default behavior results in lock escalation occurring only at those points where it would improve performance or when you must reduce excessive system lock memory to a more reasonable level. However, some application or query designs may trigger lock escalation at a time when it is not desirable, and the escalated table lock may block other users. This article discusses how to determine whether lock escalation is causing blocking and how to deal with undesirable lock escalation.
MORE INFORMATION
How to Determine Whether Lock Escalation Is Causing Blocking
Lock escalation does not cause most blocking problems. To determine whether lock escalation is occurring around the time when you experience blocking issues, start a SQL Profiler trace that includes the Lock:Escalation event. If you do not see any Lock:Escalation events, lock escalation is not occurring on your server and the information in this article does not apply to your situation.
If lock escalation is occurring, verify that the escalated table lock is blocking other users.
For more information about how to identify the head blocker and how to identify the lock resource held by the head blocker that is blocking other server process IDs (SPIDs), click the following article number to view the article in the Microsoft Knowledge Base:
224453 (http://support.microsoft.com/kb/224453/) Understanding and resolving SQL Server 7.0 or 2000 blocking problems
If the lock that is blocking other users is anything other than a TAB (table-level) lock with a lock mode of S (shared), or X (exclusive), lock escalation is not the issue. In particular, if the TAB lock is an intent lock (such as a lock mode of IS, IU, or IX), this is not the result of lock escalation. If your blocking problems are not being caused by lock escalation, see the article Q224453 for troubleshooting steps.
How to Prevent Lock Escalation
The simplest and safest way to prevent lock escalation is to keep transactions short and to reduce the lock footprint of expensive queries so that the lock escalation thresholds are not exceeded. There are several ways to obtain this goal, many of which are listed:
• Break up large batch operations into several smaller operations. For example, suppose you ran the following query to remove several hundred thousand old records from an audit table, and then you found that it caused a lock escalation that blocked other users:
DELETE FROM LogMessages WHERE LogDate < '2/1/2002'
By removing these records a few hundred at a time, you can dramatically reduce the number of locks that accumulate per transaction and prevent lock escalation. For example:
SET ROWCOUNT 500
delete_more:
DELETE FROM LogMessages WHERE LogDate < '2/1/2002'
IF @@ROWCOUNT > 0 GOTO delete_more
SET ROWCOUNT 0
• Reduce the query's lock footprint by making the query as efficient as possible. Large scans or large numbers of Bookmark Lookups may increase the chance of lock escalation; additionally, it increases the chance of deadlocks, and generally adversely affects concurrency and performance. After you find the query that causes lock escalation, look for opportunities to create new indexes or to add columns to an existing index to remove index or table scans and to maximize the efficiency of index seeks. Consider pasting the query into a Query Analyzer query window to perform an automatic index analysis on it. To do so, on the Query menu, click Index Tuning Wizard in SQL Server 2000, or click Perform Index Analysis in SQL Server 7.0.
One goal of this optimization is to make index seeks return as few rows as possible to minimize the cost of Bookmark Lookups (maximize the selectivity of the index for the particular query). If SQL Server estimates that a Bookmark Lookup logical operator may return many rows, it may use a PREFETCH to perform the bookmark lookup. If SQL Server does use PREFETCH for a bookmark lookup, it must increase the transaction isolation level of a portion of the query to repeatable read for a portion of the query. This means that what may look similar to a SELECT statement at a read-committed isolation level may acquire many thousands of key locks (on both the clustered index and one nonclustered index), which can cause such a query to exceed the lock escalation thresholds. This is especially important if you find that the escalated lock is a shared table lock, which, however, is not commonly seen at the default read-committed isolation level. If a Bookmark Lookup WITH PREFETCH clause is causing the escalation, consider adding additional columns to the nonclustered index that appears in the Index Seek or the Index Scan logical operator below the Bookmark Lookup logical operator in the query plan. It may be possible to create a covering index (an index that includes all columns in a table that were used in the query), or at least an index that covers the columns that were used for join criteria or in the WHERE clause if including everything in the select column list is impractical.
A Nested Loop join may also use PREFETCH, and this causes the same locking behavior.
For more information, click the following article number to view the article in the Microsoft Knowledge Base:
260652 (http://support.microsoft.com/kb/260652/) Nested loop join that uses a "BOOKMARK LOOKUP ...WITH PREFETCH" may hold locks longer
• Lock escalation cannot occur if a different SPID is currently holding an incompatible table lock. Lock escalation always escalates to a table lock, and never to page locks. Additionally, if a lock escalation attempt fails because another SPID holds an incompatible TAB lock, the query that attempted escalation does not block while waiting for a TAB lock. Instead, it continues to acquire locks at its original, more granular level (row, key, or page), periodically making additional escalation attempts. Therefore, one method to prevent lock escalation on a particular table is to acquire and to hold a lock on a different connection that is not compatible with the escalated lock type. An IX (intent exclusive) lock at the table level does not lock any rows or pages, but it is still not compatible with an escalated S (shared) or X (exclusive) TAB lock. For example, assume that you must run a batch job that modifies a large number of rows in the mytable table and that has caused blocking that occurs because of lock escalation. If this job always completes in less than an hour, you might create a Transact-SQL job that contains the following code, and schedule the new job to start several minutes before the batch job's start time:
BEGIN TRAN
SELECT * FROM mytable (UPDLOCK, HOLDLOCK) WHERE 1=0
WAITFOR DELAY '1:00:00'
COMMIT TRAN
This query acquires and holds an IX lock on mytable for one hour, which prevents lock escalation on the table during that time. This batch does not modify any data or block other queries (unless the other query forces a table lock with the TABLOCK hint or if an administrator has disabled page or row locks by using an sp_indexoption stored procedure).
Additionally, you can disable lock escalation by enabling trace flag 1211. However, this trace flag disables all lock escalation globally in the instance of SQL Server. Lock escalation serves a very useful purpose in SQL Server by maximizing the efficiency of queries that are otherwise slowed down by the overhead of acquiring and releasing several thousands of locks. Lock escalation also helps to minimize the required memory to keep track of locks. The memory that SQL Server can dynamically allocate for lock structures is finite, so if you disable lock escalation and the lock memory grows large enough, attempts to allocate additional locks for any query may fail and the following error occurs:
Error: 1204, Severity: 19, State: 1
The SQL Server cannot obtain a LOCK resource at this time. Rerun your statement when there are fewer active users or ask the system administrator to check the SQL Server lock and memory configuration.
Note When a "1204" error occurs, it stops the processing of the current statement and causes a rollback of the active transaction. The rollback itself may block users or lead to a long database recovery time if you restart the SQL Server service.
Using a lock hint such as ROWLOCK only alters the initial lock plan. Lock hints do not prevent lock escalation.
The other methods of preventing lock escalation that are discussed earlier in this article are better options than enabling the trace flag. Additionally, the other methods generally result in better performance for the query than disabling lock escalation for the whole instance. Microsoft recommends enabling this trace flag only to mitigate severe blocking that is caused by lock escalation while other options, such as those discussed earlier in this article, are being investigated. To enable a trace flag so that it is turned on whenever SQL Server is started, add it as a server startup parameter.
To add a server startup parameter, right-click the server in SQL Enterprise Manager, click Properties, and then on the General tab, click Startup Parameters, and then add the following parameter (exactly as shown):
-T1211
You must cycle the SQL Server service for a new startup parameter to take effect. If you run the following query in Query Analyzer the trace flag takes effect immediately:
DBCC TRACEON (1211, -1)
However, if you do not add the -T1211 startup parameter, the effect of a traceon command is lost when the SQL Server service is cycled. Turning on the trace flag prevents any future lock escalations, but it does not reverse any lock escalations that have already occurred in an active transaction.
четверг, 4 сентября 2008 г.
SQL Server Optimization - References
http://blogs.msdn.com/queryoptteam/
-- Improving Performance with SQL Server 2005 Indexed Views
Published: May 1, 2005
By Author: Eric Hanson and Contributors: Gail Erickson, Lubor Kollar, Jason Ward
-- Improving Performance with SQL Server 2005 Indexed Views
Published: May 1, 2005
By Author: Eric Hanson and Contributors: Gail Erickson, Lubor Kollar, Jason Ward
SQL Server 2000 Optimization Tips
Original:
January 20, 2001
SQL Server 2000 Optimization Tips
By Sergey Vartanyan
http://www.databasejournal.com/features/mssql/article.php/1442691
Introduction
There are not so many differences in the internal architecture between SQL Server 7.0 and SQL Server 2000, as between SQL Server 6.5 and SQL Server 7.0. SQL Server 2000 has the same features as SQL Server 7.0: autogrow features, new storage engine, complete row level locking and so on. So, you can use all optimization tips from my previous article for SQL Server 7.0 (see this article): Some tips about database optimization for SQL Server 7.0
But there are some new features in SQL Server 2000, which you can use to increase performance of your databases.
In this article, I want to tell you, how you can increase performance of your databases by setting some options and configuring some parameters.
Distributed partitioned views
Distributed partitioned views allows you to partition tables horizontally across multiple servers. So, you can scale out one database server to a group of database servers that cooperate to provide the same performance levels as a cluster of database servers.
Due to distributed partitioned views, SQL Server 2000 now on the first place in the tpc-c tests.
For more information about tpc-c tests, see: TPC-C tests
Indexed Views
The new SQL Server 2000 feature are indexed views. You can create index on the view, and the result set of the view will be stored in the database. So, you can significantly improve the performance of an application where queries frequently perform certain joins or aggregations. Wnen you will modify the base table, the modifications will be automatically reflected in the view.
You should specify the SCHEMABINDING option for a CREATE VIEW statement, if you want to have a indexed view.
For more information, see: SQL Views
Using Tempdb for CREATE INDEX statement
When you create an index, you can specify WITH SORT_IN_TEMPDB option of the CREATE INDEX statement, which directs the database engine to use tempdb as a work area for the sorts required to build an index. This option reduces the index creation time when tempdb is on a different set of disks than the user database, but increases the amount of disk space used to create an index.
For more information, see: tempdb and Index Creation
Setting index order with CREATE INDEX statement
You can specify ascending or descending order with CREATE INDEX statement. The default is ascending order.
For more information, see: Table Indexes
Creating index defined on computed columns
In SQL Server 2000, you can create indexes on computed columns. The computed column must be deterministic, precise, and must not contain text, ntext, or image columns. The UNIQUE or PRIMARY KEY can also contain a computed column.
For more information, see: CREATE INDEX
Text in Row Data
SQL Server 2000 has eight types of pages in the data files. There are:
* Data pages
* Index pages
* Text/Image pages
* Global Allocation Map, Secondary Global Allocation Map pages
* Pages Free Space
* Index Allocation Map pages
* Bulk Changed Map pages
* Differential Changed Map pages
So, text and image values are stored on the Text/Image pages separately from the other data, stored on the Data pages.
SQL Server 2000 now supports a text in row table option that specifies that small text, ntext, and image values be placed in the Data pages with other data values in the same data row instead of Text/Image pages. This increases the speed of read and write operations and reduces the amount of space used to store small text, ntext, and image data values.
You can set a text in row table option with sp_tableoption stored procedure.
January 20, 2001
SQL Server 2000 Optimization Tips
By Sergey Vartanyan
http://www.databasejournal.com/features/mssql/article.php/1442691
Introduction
There are not so many differences in the internal architecture between SQL Server 7.0 and SQL Server 2000, as between SQL Server 6.5 and SQL Server 7.0. SQL Server 2000 has the same features as SQL Server 7.0: autogrow features, new storage engine, complete row level locking and so on. So, you can use all optimization tips from my previous article for SQL Server 7.0 (see this article): Some tips about database optimization for SQL Server 7.0
But there are some new features in SQL Server 2000, which you can use to increase performance of your databases.
In this article, I want to tell you, how you can increase performance of your databases by setting some options and configuring some parameters.
Distributed partitioned views
Distributed partitioned views allows you to partition tables horizontally across multiple servers. So, you can scale out one database server to a group of database servers that cooperate to provide the same performance levels as a cluster of database servers.
Due to distributed partitioned views, SQL Server 2000 now on the first place in the tpc-c tests.
For more information about tpc-c tests, see: TPC-C tests
Indexed Views
The new SQL Server 2000 feature are indexed views. You can create index on the view, and the result set of the view will be stored in the database. So, you can significantly improve the performance of an application where queries frequently perform certain joins or aggregations. Wnen you will modify the base table, the modifications will be automatically reflected in the view.
You should specify the SCHEMABINDING option for a CREATE VIEW statement, if you want to have a indexed view.
For more information, see: SQL Views
Using Tempdb for CREATE INDEX statement
When you create an index, you can specify WITH SORT_IN_TEMPDB option of the CREATE INDEX statement, which directs the database engine to use tempdb as a work area for the sorts required to build an index. This option reduces the index creation time when tempdb is on a different set of disks than the user database, but increases the amount of disk space used to create an index.
For more information, see: tempdb and Index Creation
Setting index order with CREATE INDEX statement
You can specify ascending or descending order with CREATE INDEX statement. The default is ascending order.
For more information, see: Table Indexes
Creating index defined on computed columns
In SQL Server 2000, you can create indexes on computed columns. The computed column must be deterministic, precise, and must not contain text, ntext, or image columns. The UNIQUE or PRIMARY KEY can also contain a computed column.
For more information, see: CREATE INDEX
Text in Row Data
SQL Server 2000 has eight types of pages in the data files. There are:
* Data pages
* Index pages
* Text/Image pages
* Global Allocation Map, Secondary Global Allocation Map pages
* Pages Free Space
* Index Allocation Map pages
* Bulk Changed Map pages
* Differential Changed Map pages
So, text and image values are stored on the Text/Image pages separately from the other data, stored on the Data pages.
SQL Server 2000 now supports a text in row table option that specifies that small text, ntext, and image values be placed in the Data pages with other data values in the same data row instead of Text/Image pages. This increases the speed of read and write operations and reduces the amount of space used to store small text, ntext, and image data values.
You can set a text in row table option with sp_tableoption stored procedure.
воскресенье, 31 августа 2008 г.
How do indexes affect locking and blocking?
How do indexes affect locking and blocking?
// from Hands-On SQL Server – Locking and Blocking – Kalen Delaney
In general, having good indexes will help reduce blocking problems, because it will help reduce the overall time of query execution.
The faster a query can complete, and the transaction containing it is committed,
the faster any locks acquired can be released. Most serious blocking problems are due to locks being held
for a long time, so anything you can do to minimize the time that locks are held is a good thing.
A related aspect in which proper indexing can help avoid blocking problems can be observed in Hands-On
Exercise #6C. This example shows that without any index, a simple select can block on a row other than the
row it is trying to access. You might think that this wasn’t possible if SQL Server truly supports row
locking, but without an index, SQL Server has no way to know what data is in the locked row, and doesn’t
know whether the locked row is actually the one it needs to access. When we change the query to add a
nonclustered index to the table, the index gives SQL Server enough information to know that it can ignore
the locked row, and directly access the row that is indicated in the WHERE clause of the SELECT
statement.
Hands-On #6C: Use of Index Avoids Blocking
-- Use Query Analyzer for this Exercise,
-- Close all existing connections and start a new one
-- Step 1: Create and populate a small heap table
USE pubs
GO
DROP TABLE t1
GO
CREATE TABLE t1 (a int)
GO
INSERT INTO t1 VALUES (1)
INSERT INTO t1 VALUES (3)
INSERT INTO t1 VALUES (5)
GO
-- Step 2: Update a single row
BEGIN TRAN
UPDATE t1
SET a = 7 WHERE a = 1
GO
EXEC sp_lock
GO
-- The output here should show you one X lock, on a RID;
-- that is the row that has been updated.
-- Step 2: In another query window, run a batch that
-- tries to select a completely different row than
-- the one that is being updated
USE pubs
SELECT * FROM t1
WHERE a = 3
GO
-- The above SELECT should block.
-- Step 3: Return to the first query window.
-- Examine sp_lock again and note that the second process -- is waiting for a lock on the same RID that was
locked
-- in Step 2.
EXEC sp_lock
GO
ROLLBACK TRAN
GO
-- Step 4: Stay in the same query window, and change the
-- script slightly to create an index on column ‘a’
Hands-On SQL Server – Locking and Blocking – Kalen Delaney
Page 74
USE pubs
GO
DROP TABLE t1
GO
-- Create and populate the same small table
CREATE TABLE t1 ( a int)
GO
-- Build an index on the table
CREATE INDEX idx1 ON t1(a)
GO
-- Insert the same three rows into the table
INSERT INTO t1 VALUES (1)
INSERT INTO t1 VALUES (3)
INSERT INTO t1 VALUES (5)
GO
-- Step 5 : Update a single row and look at the locks
BEGIN TRAN
UPDATE t1
SET a = 7 WHERE a = 1
EXEC sp_lock
-- Now the output should show you three X locks
-- one again on a RID and two KEY locks.
-- When the key column a is updated, the leaf level of
-- the nonclustered index is adjusted.
-- Since the leaf level of the index keeps all the keys
-- in sorted order, the old key with the value 1 is moved
-- from the beginning of the leaf level to the end,
-- because now its value is 7. However, until the
-- transaction is over, a ghost entry is left in the
-- original position and the key lock is maintained.
-- So there are two key locks: one for the old value
-- and one for the new.
-- Step 6: In another query window, run a batch that
-- tries to select a completely different row than
-- the one that is being updated
USE pubs
SELECT * FROM t1
WHERE a = 3
GO
-- The above SELECT will NOT block.
-- Step 7: Return to the first query window and rollback
ROLLBACK TRAN
GO
Another benefit to having indexes on a table is that there is one more option for controlling the types of
locks SQL Server will acquire. The stored procedure sp_indexoption lets you manually control the unit of
locking by letting you disallow page locks or row locks within an index. Since these options are available
only for indexes, there is no way to control the locking within the data pages of a heap. But remember that
if a table has a clustered index, the data pages are part of the index and are affected by the sp_indexoption
setting for the clustered index. The index options are set for each table or index individually. Two options,
‘AllowRowLocks’ and ‘AllowPageLocks’, are both set to TRUE initially for every table and index. If both
of these options are set to FALSE for a table, only full table locks are allowed. Note that there is no option
to disallow table locks; that option must always be available.
To verify whether row or page locks have been disallowed, you can use the indexproperty function. We can
disable row locks by either setting the ‘AllowRowLocks’ option to false, or by setting the
‘DisAllowRowLocks’ option to true. There are two related options for page locks: ‘AllowPageLocks’ and
‘DisAllowPageLocks’. However, there is only one parameter we can use to check whether row locks are
allowed and one to check whether page locks are allowed.
The following command checks whether row locks have been disallowed on the authors table by supplying
the name of the clustered index on the authors table. The INDEXPROPERTY function can return 3 values:
1 means row locks are disallowed, 0 means they are allowed, and NULL means we typed something
incorrectly.
SELECT INDEXPROPERTY (object_id('authors'), 'UPKCL_auidind','IsRowLockDisallowed')
To change the value so that row locks will no longer be allowed on the authors table, we can run either of
the following two commands:
EXEC sp_indexoption 'authors.UPKCL_auidind', 'AllowRowLocks', false
EXEC sp_indexoption 'authors.UPKCL_auidind', 'DisAllowRowLocks', true
// from Hands-On SQL Server – Locking and Blocking – Kalen Delaney
In general, having good indexes will help reduce blocking problems, because it will help reduce the overall time of query execution.
The faster a query can complete, and the transaction containing it is committed,
the faster any locks acquired can be released. Most serious blocking problems are due to locks being held
for a long time, so anything you can do to minimize the time that locks are held is a good thing.
A related aspect in which proper indexing can help avoid blocking problems can be observed in Hands-On
Exercise #6C. This example shows that without any index, a simple select can block on a row other than the
row it is trying to access. You might think that this wasn’t possible if SQL Server truly supports row
locking, but without an index, SQL Server has no way to know what data is in the locked row, and doesn’t
know whether the locked row is actually the one it needs to access. When we change the query to add a
nonclustered index to the table, the index gives SQL Server enough information to know that it can ignore
the locked row, and directly access the row that is indicated in the WHERE clause of the SELECT
statement.
Hands-On #6C: Use of Index Avoids Blocking
-- Use Query Analyzer for this Exercise,
-- Close all existing connections and start a new one
-- Step 1: Create and populate a small heap table
USE pubs
GO
DROP TABLE t1
GO
CREATE TABLE t1 (a int)
GO
INSERT INTO t1 VALUES (1)
INSERT INTO t1 VALUES (3)
INSERT INTO t1 VALUES (5)
GO
-- Step 2: Update a single row
BEGIN TRAN
UPDATE t1
SET a = 7 WHERE a = 1
GO
EXEC sp_lock
GO
-- The output here should show you one X lock, on a RID;
-- that is the row that has been updated.
-- Step 2: In another query window, run a batch that
-- tries to select a completely different row than
-- the one that is being updated
USE pubs
SELECT * FROM t1
WHERE a = 3
GO
-- The above SELECT should block.
-- Step 3: Return to the first query window.
-- Examine sp_lock again and note that the second process -- is waiting for a lock on the same RID that was
locked
-- in Step 2.
EXEC sp_lock
GO
ROLLBACK TRAN
GO
-- Step 4: Stay in the same query window, and change the
-- script slightly to create an index on column ‘a’
Hands-On SQL Server – Locking and Blocking – Kalen Delaney
Page 74
USE pubs
GO
DROP TABLE t1
GO
-- Create and populate the same small table
CREATE TABLE t1 ( a int)
GO
-- Build an index on the table
CREATE INDEX idx1 ON t1(a)
GO
-- Insert the same three rows into the table
INSERT INTO t1 VALUES (1)
INSERT INTO t1 VALUES (3)
INSERT INTO t1 VALUES (5)
GO
-- Step 5 : Update a single row and look at the locks
BEGIN TRAN
UPDATE t1
SET a = 7 WHERE a = 1
EXEC sp_lock
-- Now the output should show you three X locks
-- one again on a RID and two KEY locks.
-- When the key column a is updated, the leaf level of
-- the nonclustered index is adjusted.
-- Since the leaf level of the index keeps all the keys
-- in sorted order, the old key with the value 1 is moved
-- from the beginning of the leaf level to the end,
-- because now its value is 7. However, until the
-- transaction is over, a ghost entry is left in the
-- original position and the key lock is maintained.
-- So there are two key locks: one for the old value
-- and one for the new.
-- Step 6: In another query window, run a batch that
-- tries to select a completely different row than
-- the one that is being updated
USE pubs
SELECT * FROM t1
WHERE a = 3
GO
-- The above SELECT will NOT block.
-- Step 7: Return to the first query window and rollback
ROLLBACK TRAN
GO
Another benefit to having indexes on a table is that there is one more option for controlling the types of
locks SQL Server will acquire. The stored procedure sp_indexoption lets you manually control the unit of
locking by letting you disallow page locks or row locks within an index. Since these options are available
only for indexes, there is no way to control the locking within the data pages of a heap. But remember that
if a table has a clustered index, the data pages are part of the index and are affected by the sp_indexoption
setting for the clustered index. The index options are set for each table or index individually. Two options,
‘AllowRowLocks’ and ‘AllowPageLocks’, are both set to TRUE initially for every table and index. If both
of these options are set to FALSE for a table, only full table locks are allowed. Note that there is no option
to disallow table locks; that option must always be available.
To verify whether row or page locks have been disallowed, you can use the indexproperty function. We can
disable row locks by either setting the ‘AllowRowLocks’ option to false, or by setting the
‘DisAllowRowLocks’ option to true. There are two related options for page locks: ‘AllowPageLocks’ and
‘DisAllowPageLocks’. However, there is only one parameter we can use to check whether row locks are
allowed and one to check whether page locks are allowed.
The following command checks whether row locks have been disallowed on the authors table by supplying
the name of the clustered index on the authors table. The INDEXPROPERTY function can return 3 values:
1 means row locks are disallowed, 0 means they are allowed, and NULL means we typed something
incorrectly.
SELECT INDEXPROPERTY (object_id('authors'), 'UPKCL_auidind','IsRowLockDisallowed')
To change the value so that row locks will no longer be allowed on the authors table, we can run either of
the following two commands:
EXEC sp_indexoption 'authors.UPKCL_auidind', 'AllowRowLocks', false
EXEC sp_indexoption 'authors.UPKCL_auidind', 'DisAllowRowLocks', true
Подписаться на:
Сообщения (Atom)
Сообщения
