SQLSERVER数据库死锁与优化

成长路上除了

死锁杂谈

当数据库死锁时，SqlServer的会释放一个优先级较低的锁，让另一个事务运行;所以，即时去捕捉数据库死锁，是挺不容易的。

如果，数据库死锁比较长时间，那么死锁是可以被捕捉的。

可以用SqlServer的活动监视器来查看，哪些进程锁了数据库。

首先打开SqlServer的活动监视器，然后可以看到，界面里有进程，查看资源，数据文件I / O，最近消耗大量资源的查询四项。

四项显示内容如下：

进程：在进程里可以看到哪些进程被阻塞，查看属性【阻塞者】可以看到，【阻塞者】的会话ID。

等待资源：等待资源里有一些锁，可以看看那些锁累计等待时间较多。

数据文件I / O：数据文件I / O记录一些数据库的MDF，LDF的读写速度。

最近消耗大量资源的查询：记录一些消耗资源较大的SQL查询。

查询进程里被死锁的会话ID，然后执行下面的SQL，进行解锁。

1. 声明@spid int设置@spid =  518  -锁表进程会话ID
   
2. 声明@sql varchar （1000 ）
   
3. 设置 @sql = 'kill' + cast （ @spid as varchar ）
   
4. exec （ @sql ）

复制代码

也可以用下面的SQL语句查询死锁进程，这样查询死锁进程，定位比较快。

1. 从   sys中选择request_session_id spid ，  OBJECT_NAME （ resource_associated_entity_id ） tableName   
   
2. 。dm_tran_locks其中resource_type = 'OBJECT'

复制代码

优化杂谈
最近消耗大量资源的查询也可以用SQL查询。
下面SQL是查询最耗时的前10条SQL语句。

1. SELECT TOP 10 total_worker_time /  1000 AS [ 自编译以来执行所用的CPU时间总量（ ms -毫秒）] ，
   
2. total_elapsed_time / 1000  为 [完成执行此计划所用的总时间] ，
   
3. total_elapsed_time / execution_count / 1000  as  [平均完成执行此计划所用时间] ，
   
4. execution_count   为 [上次编译以来所执行的次数] ，     
   
5. creation_time 为 [编译计划的时间] ，
   
6. deqs 。total_worker_time / deqs 。execution_count /  1000 AS [ 平均使用CPU时间（ ms ）] ，
   
7. last_execution_time AS [上次开始执行计划的时间] ，
   
8. total_physical_reads [ total_logical_reads ]执行的物理读取总次数，
   
9. total_logical_reads / execution_count [平均逻辑读取]次数] ，
   
10. min_worker_time / 1000 AS [ 单次执行期间所用的最小CPU时间（ ms ）] ，
    
11. max_worker_time / 1000 AS [单次执行期间所用的最大CPU 时间（毫秒）] ，
    
12. SUBSTRING （ DEST 。文本， deqs 。 statement_start_offset /  2  +  1 ，         
    
13. （ CASE WHEN deqs 。 statement_end_offset =  - 1 THEN DATALENGTH （ DEST 。文本） ELSE deqs 。 statement_end_offset END - deqs 。 statement_start_offset ） /  2  + 1 ） AS [执行SQL ] ，  
    
14. dest 。文本作为 [完整SQL ] ，
    
15. DB_NAME （ DEST 。 DBID ） 作为 [数据库名称] ，
    
16. OBJECT_NAME （ DEST 。 OBJECTID ， DEST 。 DBID ） 作为 [对象名称]
    
17. ， deqs 。plan_handle [查询所属的已编译计划]
    
18. 来自sys 。dm_exec_query_stats deqs WITH （NOLOCK ）
    
19. CROSS APPLY系统。dm_exec_sql_text （ deqs 。 sql_handle的） AS DEST -平均使用CPU时间降序
    
20. ORDER BY  （ deqs 。 total_worker_time / deqs 。 execution_count /  1000 ） DESC

复制代码

在SqlServer的活动监视器里，查看资源等待。

通常可以看到等待类别是锁存器的排在最上面，如下图：

闩锁【闩锁】虽然是一种轻量级的锁，但等待的锁越多，肯定越影响数据库性能。
执行下面的SQL，查看下哪些锁存比较耗资源。

1. SELECT * FROM sys 。dm_os_latch_stats

复制代码

查询结果如下图所示：


从结果中可以看到各种锁类型的请求的次数，等待时间，最大等待时间（毫秒）。
但这些锁类型都是英文简写，需要使用下面表格查询它们的真实意义。
通过对比表格，我们发现了最消耗资源的ACCESS_METHODS_DATASET_PARENT锁的意义是并发操作时资源访问的锁。那么想降低并发操作，就可以减少ACCESS_METHODS_DATASET_PARENT锁的资源消耗了。
Latch参考网址：https：//docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-os-latch-stats-transact-sql?view = sql- 服务器-2017


[td]

闩锁类	描述
ALLOC_CREATE_RINGBUF	由SQL Server在内部使用，以初始化创建分配环缓冲区的同步。
ALLOC_CREATE_FREESPACE_CACHE	用于初始化堆的内部自由空间缓存的同步。
ALLOC_CACHE_MANAGER	用于同步内部一致性测试。
ALLOC_FREESPACE_CACHE	用于将对页面缓存的访问与堆和二进制大对象（BLOB）的可用空间同步。当多个连接尝试同时将行插入堆或BLOB时，可能会发生此类锁存器上的争用。您可以通过对对象进行分区来减少此争用。每个分区都有自己的锁存器。分区将在多个锁存器上分配插入。
ALLOC_EXTENT_CACHE	用于同步对包含未分配页面的扩展区缓存的访问。当多个连接尝试同时在同一分配单元中分配数据页时，可能会发生此类锁存器上的争用。可以通过划分该分配单元所属的对象来减少该争用。
ACCESS_METHODS_DATASET_PARENT	用于在并行操作期间同步子数据集对父数据集的访问。
ACCESS_METHODS_HOBT_FACTORY	用于同步对内部哈希表的访问。
ACCESS_METHODS_HOBT	用于同步对HoBt的内存中表示的访问。
ACCESS_METHODS_HOBT_COUNT	用于同步对HoBt页面和行计数器的访问。
ACCESS_METHODS_HOBT_VIRTUAL_ROOT	用于同步对内部B树的根页抽象的访问。
ACCESS_METHODS_CACHE_ONLY_HOBT_ALLOC	用于同步工作表访问。
ACCESS_METHODS_BULK_ALLOC	用于同步批量分配器中的访问。
ACCESS_METHODS_SCAN_RANGE_GENERATOR	用于在并行扫描期间同步对范围生成器的访问。
ACCESS_METHODS_KEY_RANGE_GENERATOR	用于在关键范围并行扫描期间同步对预读操作的访问。
APPEND_ONLY_STORAGE_INSERT_POINT	用于在快速仅附加存储单元中同步插入。
APPEND_ONLY_STORAGE_FIRST_ALLOC	用于同步仅附加存储单元的第一个分配。
APPEND_ONLY_STORAGE_UNIT_MANAGER	用于快速仅附加存储单元管理器内的内部数据结构访问同步。
APPEND_ONLY_STORAGE_MANAGER	用于在快速仅附加存储单元管理器中同步收缩操作。
BACKUP_RESULT_SET	用于同步并行备份结果集。
BACKUP_TAPE_POOL	用于同步备份磁带池。
BACKUP_LOG_REDO	用于同步备份日志重做操作。
BACKUP_INSTANCE_ID	用于同步备份性能监视器计数器的实例ID的生成。
BACKUP_MANAGER	用于同步内部备份管理器。
BACKUP_MANAGER_DIFFERENTIAL	用于将差异备份操作与DBCC同步。
BACKUP_OPERATION	用于备份操作中的内部数据结构同步，例如数据库，日志或文件备份。
BACKUP_FILE_HANDLE	用于在还原操作期间同步文件打开操作。
缓冲	用于同步对数据库页面的短期访问。在读取或修改任何数据库页面之前，需要缓冲区锁存器。缓冲区锁存器争用可能表示存在多个问题，包括热页和慢速I / O.此锁存器类涵盖了页锁存器的所有可能用途。sys.dm_os_wait_stats区分了由I / O操作和页面上的读写操作引起的页面锁存等待。
BUFFER_POOL_GROW	用于缓冲池增长操作期间的内部缓冲区管理器同步。
DATABASE_CHECKPOINT	用于序列化数据库中的检查点。
CLR_PROCEDURE_HASHTABLE	限内部使用。
CLR_UDX_STORE	限内部使用。
CLR_DATAT_ACCESS	限内部使用。
CLR_XVAR_PROXY_LIST	限内部使用。
DBCC_CHECK_AGGREGATE	限内部使用。
DBCC_CHECK_RESULTSET	限内部使用。
DBCC_CHECK_TABLE	限内部使用。
DBCC_CHECK_TABLE_INIT	限内部使用。
DBCC_CHECK_TRACE_LIST	限内部使用。
DBCC_FILE_CHECK_OBJECT	限内部使用。
DBCC_PERF	用于同步内部性能监视器计数器。
DBCC_PFS_STATUS	限内部使用。
DBCC_OBJECT_METADATA	限内部使用。
DBCC_HASH_DLL	限内部使用。
EVENTING_CACHE	限内部使用。
FCB	用于同步对文件控制块的访问。
FCB_REPLICA	限内部使用。
FGCB_ALLOC	用于同步对文件组中的循环分配信息的访问。
FGCB_ADD_REMOVE	用于同步对文件组的访问，以便添加，删除，增大和收缩文件操作。
FILEGROUP_MANAGER	限内部使用。
文件管理器	限内部使用。
FILESTREAM_FCB	限内部使用。
FILESTREAM_FILE_MANAGER	限内部使用。
FILESTREAM_GHOST_FILES	限内部使用。
FILESTREAM_DFS_ROOT	限内部使用。
LOG_MANAGER	限内部使用。
FULLTEXT_DOCUMENT_ID	限内部使用。
FULLTEXT_DOCUMENT_ID_TRANSACTION	限内部使用。
FULLTEXT_DOCUMENT_ID_NOTIFY	限内部使用。
FULLTEXT_LOGS	限内部使用。
FULLTEXT_CRAWL_LOG	限内部使用。
FULLTEXT_ADMIN	限内部使用。
FULLTEXT_AMDIN_COMMAND_CACHE	限内部使用。
FULLTEXT_LANGUAGE_TABLE	限内部使用。
FULLTEXT_CRAWL_DM_LIST	限内部使用。
FULLTEXT_CRAWL_CATALOG	限内部使用。
FULLTEXT_FILE_MANAGER	限内部使用。
DATABASE_MIRRORING_REDO	限内部使用。
DATABASE_MIRRORING_SERVER	限内部使用。
DATABASE_MIRRORING_CONNECTION	限内部使用。
DATABASE_MIRRORING_STREAM	限内部使用。
QUERY_OPTIMIZER_VD_MANAGER	限内部使用。
QUERY_OPTIMIZER_ID_MANAGER	限内部使用。
QUERY_OPTIMIZER_VIEW_REP	限内部使用。
RECOVERY_BAD_PAGE_TABLE	限内部使用。
恢复管理器	限内部使用。
SECURITY_OPERATION_RULE_TABLE	限内部使用。
SECURITY_OBJPERM_CACHE	限内部使用。
SECURITY_CRYPTO	限内部使用。
SECURITY_KEY_RING	限内部使用。
SECURITY_KEY_LIST	限内部使用。
SERVICE_BROKER_CONNECTION_RECEIVE	限内部使用。
SERVICE_BROKER_TRANSMISSION	限内部使用。
SERVICE_BROKER_TRANSMISSION_UPDATE	限内部使用。
SERVICE_BROKER_TRANSMISSION_STATE	限内部使用。
SERVICE_BROKER_TRANSMISSION_ERRORS	限内部使用。
SSBXmitWork	限内部使用。
SERVICE_BROKER_MESSAGE_TRANSMISSION	限内部使用。
SERVICE_BROKER_MAP_MANAGER	限内部使用。
SERVICE_BROKER_HOST_NAME	限内部使用。
SERVICE_BROKER_READ_CACHE	限内部使用。
SERVICE_BROKER_WAITFOR_MANAGER	用于同步服务器队列的实例级别映射。每个数据库ID，数据库版本和队列ID元组存在一个队列。当许多连接是：在WAITFOR（RECEIVE）等待状态时，可能会发生此类锁存器上的争用; 叫WAITFOR（RECEIVE）; 超过WAITFOR超时; 收到消息; 提交或回滚包含WAITFOR（RECEIVE）的交易; 您可以通过减少WAITFOR（RECEIVE）等待状态中的线程数来减少争用。
SERVICE_BROKER_WAITFOR_TRANSACTION_DATA	限内部使用。
SERVICE_BROKER_TRANSMISSION_TRANSACTION_DATA	限内部使用。
SERVICE_BROKER_TRANSPORT	限内部使用。
SERVICE_BROKER_MIRROR_ROUTE	限内部使用。
trace_id的	限内部使用。
TRACE_AUDIT_ID	限内部使用。
跟踪	限内部使用。
TRACE_CONTROLLER	限内部使用。
TRACE_EVENT_QUEUE	限内部使用。
TRANSACTION_DISTRIBUTED_MARK	限内部使用。
TRANSACTION_OUTCOME	限内部使用。
NESTING_TRANSACTION_READONLY	限内部使用。
NESTING_TRANSACTION_FULL	限内部使用。
MSQL_TRANSACTION_MANAGER	限内部使用。
DATABASE_AUTONAME_MANAGER	限内部使用。
UTILITY_DYNAMIC_VECTOR	限内部使用。
UTILITY_SPARSE_BITMAP	限内部使用。
UTILITY_DATABASE_DROP	限内部使用。
UTILITY_DYNAMIC_MANAGER_VIEW	限内部使用。
UTILITY_DEBUG_FILESTREAM	限内部使用。
UTILITY_LOCK_INFORMATION	限内部使用。
VERSIONING_TRANSACTION	限内部使用。
VERSIONING_TRANSACTION_LIST	限内部使用。
VERSIONING_TRANSACTION_CHAIN	限内部使用。
VERSIONING_STATE	限内部使用。
VERSIONING_STATE_CHANGE	限内部使用。
KTM_VIRTUAL_CLOCK	限内部使用。

DBCC杂谈
DBCC语句是SQL Server  的数据库控制台命令，共有以下四种类型。

维护：对数据库，索引或文件组进行维护的任务。
杂项：杂项任务，如启用跟踪标志或从内存中删除DLL。
信息：收集并显示各种类型信息的任务。
验证：对数据库，表，索引，目录，文件组或数据库页的分配进行的验证操作。

DBCC shrinkdatabase
DBCC shrinkdatabase用于收缩数据库，SQL语句如下：

1. DBCC shrinkdatabase  （ N '库名'  ， 1 ）

复制代码

执行结果如下：



各字段含义如下：

DBID：数据库引擎试图收缩的文件的数据库标识号。

FILEID：数据库引擎尝试收缩的文件的文件标识号。

CurrentSize：文件当前占用的8 KB页数。

MinimumSize：文件最低可以占用的8 KB页数。这与文件的最小大小或最初创建时的大小相对应。

UsedPages：文件当前使用的8 KB页数。

EstimatedPages：数据库引擎估计文件能够收缩到的8 KB页数。

如果收缩不成功，可以查看下数据库是否有可以收缩的空间。

SQL如下：

1. SELECT名称，大小/ 128.0  -  CAST （FILEPROPERTY （名称， 'SpaceUsed' ） AS int ）/ 128.0 AS AvailableSpaceInMB
   
2. 来自sys 。database_files ;

复制代码

如果有空间还收缩不成功，则可能是别原因。

DBCC参考网址：https：//docs.microsoft.com/zh-cn/sql/t-sql/database-console-commands/dbcc-shrinkdatabase-transact-sql?view = sql-server-2017

数据库日志杂谈

SqlServer的数据库日志对执行的SQL语句进行了加密，所以，在日志里，我们看不到真正执行的SQL语句。

如果想查看SQL语句，需要借助一些工具，如ApexSQLLog。

不过，虽然看不到SQL语句，也可以通过日志看出一些数据库问题，比如，可以查看数据库执行了多少次插入，更新等操作。

查看数据库日志的SQL如下：

1. SELECT * FROM [ sys ] 。[ fn_dblog ] （ NULL ， NULL ）

复制代码

查询结果如下：

查询结果各字段含义如下：

手术

上下文

解释

LOP_SET_BITS

LCX_DIFF_MAP

设置位图，资料：差异（Differential）备份：只备份上次完整备份后，做修改的部分。备份单位是区（Extent）。意味着某个区内即使只有一页做了变动，则在差异备份里会被体现。差异备份依靠一个BitMap的进行维护，一个位对应一个区，自上次完整备份后，被修改的区会被置为1，而BitMap的中被置为1对应的区会被差异备份所备份。而到下一次完整备份后，BitMap中所有的Bit都会被重置为0而这个BitMap在数据库第7页：DCM页差异变更（差异变换图，DCM）页面他跟踪一个文件中的哪一个区在最新一次完整数据库备份之后被修改过.SQLSERVER用在增量备份时只对已发生数据变更的分区进行增量备份即可

LOP_BEGIN_XACT

事务开始

LOP_MODIFY_ROW

LCX_HEAP

修改堆表中的某一行记录

LOP_PREP_XACT

准备启动数据库

LOP_COMMIT_XACT

提交事务

LOP_MODIFY_ROW

LCX_BOOT_PAGE

修改数据库启动页

LOP_MODIFY_HEADER

LCX_PFS

修改PFS页的页头部信息

LOP_INSERT_ROWS

LCX_CLUSTERED

插入数据到聚集索引的索引页

LOP_INSERT_ROWS

LCX_INDEX_LEAF

插入数据到索引的叶子节点即数据页

LOP_FORMAT_PAGE

LCX_CLUSTERED

重新组织聚集索引

LOP_DELETE_SPLIT

LCX_CLUSTERED

删除聚集索引表的一行记录引起页拆分

LOP_MODIFY_HEADER

LCX_HEAP

修改堆表的某页的页头信息

LOP_BEGIN_CKPT

LCX_NULL

检查点开始

LOP_END_CKPT

LCX_NULL

检查点结束

LOP_SET_FREE_SPACE

LCX_PFS

修改PFS页设置那个数据页是空闲的

LOP_ROOT_CHANGE

LCX_CLUSTERED

聚集索引的根节点改变

LOP_INSERT_ROWS

LCX_HEAP

插入数据到堆表

LOP_FORMAT_PAGE

LCX_HEAP

格式化堆里的数据页

LOP_LOCK_XACT

在事务里获取锁

LOP_FORMAT_PAGE

LCX_HEAP

格式化堆里的数据页