锁与索引

　　大部分的锁极有可能是由于索引引起的。对于锁的分类我就不说了，网上说得很多。而索引分为聚集索引和非聚集索引。而对索引的操作有填充因子，这个可能对页面引起拆分。一旦有这样的操作将引发锁的问题。但无论你怎么进行设置都将会产生锁，除非你把索引去掉。但是我们不可能去掉索引，因为从这一方面，对性能无疑是有害的。

现在说一下填充因子：创建索引时，可以指定一个填充因子，以便在索引的每个叶级页上留出额外的间隙和保留一定百分比的空间，供将来表的数据存储容量进行扩充和减少页拆分的可能性。填充因子的值是从 0 到 100 的百分比数值，指定在创建索引后对数据页的填充比例。值为 100 时表示页将填满，所留出的存储空间量最小。只有当不会对数据进行更改时（例如，在只读表中）才会使用此设置。值越小则数据页上的空闲空间越大，这样可以减少在索引增长过程中对数据页进行拆分的需要，但需要更多的存储空间。当表中数据会发生更改时，这种设置更为适当。
说明即使对于一个面向许多插入和更新操作的应用程序来说，数据库读取次数一般也超过数据库写入次数的 5 到 10 倍。因此，指定一个不同于默认设置的填充因子会降低数据库的读取性能，而降低量与填充因子设置值成反比。例如，当填充因子的值为 50% 时，数据库的读取性能会降低两倍。

　　从上面来看不难看出填充因子对性能有极大的影响。至于如何解决呢？？？一种办法就是不上锁（加nolock关键字）这样你就能提高服务器的性能。但从这一方面违背了数据的完整与稳定。如果你的应用程序不需要非常完整（特别是在门户网站下）加上nolock无疑是一个好的解决方案。之前我在北京面试一家公司，当时就说性能的优化，当时面试官说：查询不需要上锁，所以他认为这个对性能没有影响。而实际的经验告诉我，这个影响是非常显著的。曾经优化过一个高并发性的插入加上与不加影响性能百分之五十。

　　好了，现在我也不废话了。拿个例子出来测试吧。

以下是创建测试数据库以极表：

代码

IF DB_ID('DeadlockDemo') IS NOT NULL DROP DATABASE DeadlockDemo
GO
CREATE DATABASE DeadlockDemo
GO
USE DeadlockDemo
GO
SET NOCOUNT ON
GO
IF OBJECT_ID ('BookmarkLookupDeadlock') IS NOT NULL DROP TABLE BookmarkLookupDeadlock
IF OBJECT_ID ('BookmarkLookupSelect') IS NOT NULL DROP PROC BookmarkLookupSelect
IF OBJECT_ID ('BookmarkLookupUpdate') IS NOT NULL DROP PROC BookmarkLookupUpdate
GO
CREATE TABLE BookmarkLookupDeadlock (col1 int, col2 int, col3 int, col4 char(100))
GO
DECLARE @int int
SET @int = 1
WHILE (@int <= 1000) BEGIN
    INSERT INTO BookmarkLookupDeadlock VALUES (@int*2, @int*2, @int*2, @int*2)
    SET @int = @int + 1
END
GO
CREATE CLUSTERED INDEX cidx_BookmarkLookupDeadlock ON BookmarkLookupDeadlock (col1)
CREATE NONCLUSTERED INDEX idx_BookmarkLookupDeadlock_col2 ON BookmarkLookupDeadlock (col2)
GO
CREATE PROC BookmarkLookupSelect @col2 int AS
BEGIN
    SELECT col2, col3 FROM BookmarkLookupDeadlock WHERE col2 BETWEEN @col2 AND @col2+1
END
GO
CREATE PROC BookmarkLookupUpdate @col2 int
AS
BEGIN
    UPDATE BookmarkLookupDeadlock SET col2 = col2+1 WHERE col1 = @col2
    UPDATE BookmarkLookupDeadlock SET col2 = col2-1 WHERE col1 = @col2
END
GO

以下是从数据库读取的SQL代码：

代码

USE DeadlockDemo
GO
SET NOCOUNT ON
IF OBJECT_ID('tempdb..#t1') IS NOT NULL
BEGIN
    DROP TABLE #t1
END
CREATE TABLE #t1 (col2 int, col3 int)
GO
WHILE (1=1)
BEGIN
    INSERT INTO #t1 EXEC BookmarkLookupSelect 4
    TRUNCATE TABLE #t1
END
GO

以下是从数据库更新的代码：

USE DeadlockDemo
GO
SET NOCOUNT ON
WHILE (1=1)
BEGIN
EXEC BookmarkLookupUpdate 4
END
GO

然后你执行测试一下，发现查询的代码将被kill掉，如果你去掉了非聚集索引idx_BookmarkLookupDeadlock_col2，则可顺利的进行，这说明非聚集索引对数据库的性能有极大的影响。

如果你执行以下SQL语句，再执行以上代码再测试一下：

alter PROC BookmarkLookupSelect @col2 int AS
BEGIN
SELECT col2, col3 FROM BookmarkLookupDeadlock(nolock) WHERE col2 BETWEEN @col2 AND @col2+1
END

你会发现一个结果，照常执行，从以上的测试对性能的优化可以得出以下几点：

去掉没有必要的索引

如果在业务允许的范围内，你可以加上nolock来提高性能，至于其它锁控制的关键字，大家也可以测试一下

本文参考的例子来源于：http://www.sqlservercentral.com/articles/deadlock/65614/