Sql调优2

锁和事务

锁解决的问题，发生在数据库并发时

• 脏读： read uncommitted，在事务还没有提交时候读取，事务可能回滚， 并发性最好
• 不可重复读：read commited ，默认级别，
• 幻读
• 可重复读： 同一个事务里面发生不一致
  Repeatable read， 共享锁持有时间长，阻塞相对多，
  Serizable
  以上都由悲观锁实现
  Snapshot：乐观锁

锁的大小

• 行锁，粒度最小的锁级别
• 页锁，
• 表锁

锁的模式

• 排他锁，X锁，在写操作时使用，更新或者插入，事务结束后才释放
• 共享锁，S锁，读操作
• 意向锁，I锁，
• 更新锁，U锁，

各种锁有兼容情况

阻塞
降低锁数量，减少锁的持有时间
建议

• 缩短事务运行时间（范围，持续时间）
• 更新修改操作应尽可能靠近事务的结尾
• 避免多次更新相同的数据，不要在同一个事务中，多次更新数据
• 监控锁升级
• 同一个事务中，不要混用DML和DDL
• 业务代码应该有错误捕捉和处理逻辑

事务和锁对性能没有太大提升，数据库系统并不是为了性能存在，主要是以数据一致性未前提

查看事务和阻塞

select 
    tl1.resource_type as [Resource Type] 
    ,db_name(tl1.resource_database_id) as [DB Name] 
    ,case tl1.resource_type 
when  'OBJECT' then object_name(tl1.resource_associated_entity_id
,tl1.resource_database_id) 
        when 'DATABASE' then 'DB' 
        else 
            case when tl1.resource_database_id = db_id() 
                then 
                     (  select object_name(object_id, tl1.resource_database_id) 
                        from sys.partitions 
                        where hobt_id = tl1.resource_associated_entity_id ) 
                else '(Run under DB context)' 
            end 
    end as [Object] 
    ,tl1.resource_description as [Resource] 
    ,tl1.request_session_id as [Session] 
    ,tl1.request_mode as [Mode] 
    ,tl1.request_status as [Status] 
    ,wt.wait_duration_ms as [Wait (ms)] 
	   ,qi.sql 
    ,qi.query_plan 
from 
    sys.dm_tran_locks tl1 with (nolock) join sys.dm_tran_locks tl2 with (nolock) on 
        tl1.resource_associated_entity_id = tl2.resource_associated_entity_id 
    left outer join sys.dm_os_waiting_tasks wt with (nolock) on 
        tl1.lock_owner_address = wt.resource_address and tl1.request_status = 'WAIT' 
    outer apply 
    ( 
        select 
            substring(s.text, (er.statement_start_offset / 2) + 1, 
                 ((  case er.statement_end_offset 
                            when -1 
                            then datalength(s.text) 
                            else er.statement_end_offset 
                      end - er.statement_start_offset) / 2) + 1) as sql 
            , qp.query_plan 
        from 
            sys.dm_exec_requests er with (nolock) 
                cross apply sys.dm_exec_sql_text(er.sql_handle) s 
                outer apply sys.dm_exec_query_plan(er.plan_handle) qp 
        where 
            tl1.request_session_id = er.session_id 
    ) qi 
where 
    tl1.request_status <> tl2.request_status and 
    ( 
        tl1.resource_description = tl2.resource_description or 
        ( tl1.resource_description is null and tl2.resource_description is null ) 
    ) 
option (recompile)

In memory 技术

• 磁盘表： 传统表
• 内存优化表：内存表，不与缓存在一起，散列索引，必须有一个索引，单表最多8个索引，包含主键索引，除主键外，没有唯一索引，创建表时候就需要定义所有索引，
• Interop：解析引用内存优化表的T-sql
• Natively Compliled ：纯In memory对象

-- 检查数据库是否可以包含内存表
EXEC sp_MSforeachdb 'USE ? IF EXISTS (SELECT 1 FROM sys.filegroups FG 
JOIN sys.database_files F
ON FG.data_space_id = F.data_space_id
WHERE FG.type = ''FX'' AND F.type = 2) 
PRINT ''?'' + '' can contain memory-optimized tables.'' ';
GO

-- 查看内存表
select name,durability_desc 
from sys.tables where is_memory_optimized =1

传统表迁移内存优化表条件

• 数据库支持in-memory
• 仅对核心表，热点表迁移

优先级：分区表-》读写分离-》分库

分表，物理分表，逻辑上仍然是一个表

水平分表， 大表由若干个小表组成
按照时间，比如按年分表
查询时最好加上分区字段，避免全表扫描

sqlserver自带分区向导

数据库的高可用
读写分离
只读库，读写库，

分库

纵向分割

Sqlserver读写分离
Sqlserver 主从同步，使用订阅发布完成同步