数据库设计提高性能的几个方法.doc

数据库设计提高性能的几个方法1.使用ROWLOCK减少锁。2.使用KEEPFIXED PLAN提高性能（对于固定的SQL查询计划）声明set nocount onset transaction isolation level read committedSELECTSELECT TOP 10 cnt = COUNT(*) FROM dbo.LessThanOrEqualsPredicates WITH(ROWLOCK READPAST)WHERE tnPass = 1 AND uidPropID = uidPredicateID AND vtValue = vtPredicateValue OPTION(KEEPFIXED PLAN)SELECT uidProcID = uidProcessID, uidActID = uidActivationID FROM dbo.Instances WITH(ROWLOCK REPEATABLEREAD INDEX(CIX_Instances) WHERE (uidInstanceID = uidInstanceID)OPTION (KEEPFIXED PLAN)SELECT COALESCE(MAX(CAST(empid AS BIGINT), 0) + 1 FROM is_emppersonal WITH (XLOCK, PAGLOCK) OPTION (FORCE ORDER)INSERTINSERT INTO dbo.GreaterThanPredicates WITH (ROWLOCK)(uidPropID, vtValue, uidPredicateGroupID, tnPass)VALUES (uidPredicateID, vtPredicateValue, uidANDGroupID, tnPass)UPDATEUPDATE dbo.PredicateGroup WITH (ROWLOCK)SET nNumFirstPassPredicates = nNumFirstPassPredicates + 1 FROM dbo.PredicateGroup WITH (ROWLOCK INDEX (CIX_PredicateGroup) WHERE uidPredicateANDGroupID = uidANDGroupID OPTION(KEEPFIXED PLAN)DELETEDELETE FROM dbo.Parts WITH (ROWLOCK)FROM (SELECT TOP 100 uidPartID FROM dbo.PartZeroSum WITH (ROWLOCK) ORDER BY nID) as pszJOIN dbo.Parts p WITH (ROWLOCK) ON psz.uidPartID = p.uidPartIDOPTION (KEEPFIXED PLAN, FORCE ORDER)事务BEGIN TRANSACTIONCOMMIT TRANSACTION注释ROWLOCK READPAST指定数据库引擎不读取由其他事务锁定的行和页。数据库引擎跳过这些行或页，而不是在释放锁之前阻塞当前事务。仅可在运行于READ COMMITTED 或REPEATABLE READ 隔离级别的事务中指定READPAST。KEEPFIXEDPLAN强制查询优化器不因统计信息的更改而重新编译查询。FORCE ORDER指定在查询优化过程中保持由查询语法指示的联接顺序UPDLOCK 指定采用更新锁并保持到事务完成。REPEATABLEREAD指定事务在REPEATABLE READ 隔离级别运行时，使用相同的锁定语义执行一次扫描。INDEX ( index_val ,. n ) 指定查询优化器处理语句时要使用的索引的名称或ID。只能为每个表指定一个索引提示。( SELECT * FROM is_emppersonal WITH (INDEX(PK_is_emppersonal) )锁的粒度: 锁的类型:(1) 共享锁:共享锁用于所有的只读数据操作.(2) 修改锁:修改锁在修改操作的初始化阶段用来锁定可能要被修改的资源，这样可以避免使用共享锁造成的死锁现象(3) 独占锁:独占锁是为修改数据而保留的。它所锁定的资源，其他事务不能读取也不能修改。独占锁不能和其他锁兼容。(4) 架构锁结构锁分为结构修改锁（Sch-M）和结构稳定锁（Sch-S）。执行表定义语言操作时，SQL Server采用Sch-M锁，编译查询时，SQL Server采用Sch-S锁。(5) 意向锁意向锁说明SQL Server有在资源的低层获得共享锁或独占锁的意向。(6) 批量修改锁批量复制数据时使用批量修改锁SQL Server锁类型 (1) HOLDLOCK: 在该表上保持共享锁，直到整个事务结束，而不是在语句执行完立即释放所添加的锁。 (2)NOLOCK：不添加共享锁和排它锁，当这个选项生效后，可能读到未提交读的数据或“脏数据”，这个选项仅仅应用于SELECT语句。 (3) PAGLOCK：指定添加页锁（否则通常可能添加表锁）。 (4) READCOMMITTED用与运行在提交读隔离级别的事务相同的锁语义执行扫描。默认情况下，SQL Server 2000 在此隔离级别上操作。(5) READPAST: 跳过已经加锁的数据行，这个选项将使事务读取数据时跳过那些已经被其他事务锁定的数据行，而不是阻塞直到其他事务释放锁，READPAST仅仅应用于READ COMMITTED隔离性级别下事务操作中的SELECT语句操作。 (6) READUNCOMMITTED：等同于NOLOCK。 (7) REPEATABLEREAD：设置事务为可重复读隔离性级别。 (8) ROWLOCK：使用行级锁，而不使用粒度更粗的页级锁和表级锁。 (9) SERIALIZABLE：用与运行在可串行读隔离级别的事务相同的锁语义执行扫描。等同于 HOLDLOCK。 (10) TABLOCK：指定使用表级锁，而不是使用行级或页面级的锁，SQL Server在该语句执行完后释放这个锁，而如果同时指定了HOLDLOCK，该锁一直保持到这个事务结束。 (11) TABLOCKX：指定在表上使用排它锁，这个锁可以阻止其他事务读或更新这个表的数据，直到这个语句或整个事务结束。 (12) UPDLOCK ：指定在读表中数据时设置更新 锁（update lock）而不是设置共享锁，该锁一直保持到这个语句或整个事务结束，使用UPDLOCK的作用是允许用户先读取数据（而且不阻塞其他用户读数据），并且保证在后来再更新数据时，这一段时间内这些数据没有被其他用户修改。SQL Server提供几个DMV,查看lockssys.dm_exec_requestssys.dm_tran_lockssys.dm_os_waiting_taskssys.dm_tran_database_transactions(1)select * from sys.dm_tran_locks where resource_typeDATABASE -and resource_database_id=DB_ID()(2)SELECT session_id, blocking_session_id,*FROM sys.dm_exec_requestsWHERE blocking_session_id 0(3)SELECTrequest_session_id as Spid,Coalesce( + . + + isnull(. + ,), + . + ,) AS Object,l.resource_type as Type,request_mode as Mode,request_status as StatusFROM sys.dm_tran_locks lLEFT JOIN sys.partitions pON l.resource_associated_entity_id = p.hobt_idLEFT JOIN sys.indexes iON p.object_id = i.object_idAND p.index_id = i.index_idLEFT JOIN sys.objects oON p.object_id = o.object_idLEFT JOIN sys.schemas sON o.schema_id = s.schema_idLEFT JOIN sys.objects o2ON l.resource_associated_entity_id = o2.object_idLEFT JOIN sys.schemas s2ON o2.schema_id = s2.schema_idLEFT JOIN sys.databases dbON