SQLSERVER数据库死锁与优化
死锁杂谈
当数据库死锁时，SqlServer的会释放一个优先级较低的锁，让另一个事务运行;所以，即时去捕捉数据库死锁，是挺不容易的。
如果，数据库死锁比较长时间，那么死锁是可以被捕捉的。
可以用SqlServer的活动监视器来查看，哪些进程锁了数据库。
首先打开SqlServer的活动监视器，然后可以看到，界面里有进程，查看资源，数据文件I / O，最近消耗大量资源的查询四项。
四项显示内容如下：
进程：在进程里可以看到哪些进程被阻塞，查看属性【阻塞者】可以看到，【阻塞者】的会话ID。
等待资源：等待资源里有一些锁，可以看看那些锁累计等待时间较多。
数据文件I / O：数据文件I / O记录一些数据库的MDF，LDF的读写速度。
最近消耗大量资源的查询：记录一些消耗资源较大的SQL查询。
查询进程里被死锁的会话ID，然后执行下面的SQL，进行解锁。
声明@spid int设置@spid =518-锁表进程会话ID
声明@sql varchar （1000 ）
设置 @sql = 'kill' + cast （ @spid as varchar ）
exec （ @sql ）
也可以用下面的SQL语句查询死锁进程，这样查询死锁进程，定位比较快。
从 sys中选择request_session_id spid ，OBJECT_NAME （ resource_associated_entity_id ） tableName
。dm_tran_locks其中resource_type = 'OBJECT'
优化杂谈
最近消耗大量资源的查询也可以用SQL查询。
下面SQL是查询最耗时的前10条SQL语句。
SELECT TOP 10 total_worker_time /1000 AS [ 自编译以来执行所用的CPU时间总量（ ms -毫秒）] ，
total_elapsed_time / 1000为 [完成执行此计划所用的总时间] ，
total_elapsed_time / execution_count / 1000as[平均完成执行此计划所用时间] ，
execution_count 为 [上次编译以来所执行的次数] ，
creation_time 为 [编译计划的时间] ，
deqs 。total_worker_time / deqs 。execution_count /1000 AS [ 平均使用CPU时间（ ms ）] ，
last_execution_time AS [上次开始执行计划的时间] ，
total_physical_reads [ total_logical_reads ]执行的物理读取总次数，
total_logical_reads / execution_count [平均逻辑读取]次数] ，
min_worker_time / 1000 AS [ 单次执行期间所用的最小CPU时间（ ms ）] ，
max_worker_time / 1000 AS [单次执行期间所用的最大CPU 时间（毫秒）] ，
SUBSTRING （ DEST 。文本， deqs 。 statement_start_offset /2+1 ，
（ CASE WHEN deqs 。 statement_end_offset =- 1 THEN DATALENGTH （ DEST 。文本） ELSE deqs 。 statement_end_offset END - deqs 。 statement_start_offset ） /2+ 1 ） AS [执行SQL ] ，
dest 。文本作为 [完整SQL ] ，
DB_NAME （ DEST 。 DBID ） 作为 [数据库名称] ，
OBJECT_NAME （ DEST 。 OBJECTID ， DEST 。 DBID ） 作为 [对象名称]
， deqs 。plan_handle [查询所属的已编译计划]
来自sys 。dm_exec_query_stats deqs WITH （NOLOCK ）
CROSS APPLY系统。dm_exec_sql_text （ deqs 。 sql_handle的） AS DEST -平均使用CPU时间降序
ORDER BY（ deqs 。 total_worker_time / deqs 。 execution_count /1000 ） DESC
在SqlServer的活动监视器里，查看资源等待。
通常可以看到等待类别是锁存器的排在最上面，如下图：
闩锁【闩锁】虽然是一种轻量级的锁，但等待的锁越多，肯定越影响数据库性能。
执行下面的SQL，查看下哪些锁存比较耗资源。
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
闩锁类
描述
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语句如下：
DBCC shrinkdatabase（ N '库名'， 1 ）
执行结果如下：
各字段含义如下：
DBID：数据库引擎试图收缩的文件的数据库标识号。
FILEID：数据库引擎尝试收缩的文件的文件标识号。
CurrentSize：文件当前占用的8 KB页数。
MinimumSize：文件最低可以占用的8 KB页数。这与文件的最小大小或最初创建时的大小相对应。
UsedPages：文件当前使用的8 KB页数。
EstimatedPages：数据库引擎估计文件能够收缩到的8 KB页数。
如果收缩不成功，可以查看下数据库是否有可以收缩的空间。
SQL如下：
SELECT名称，大小/ 128.0-CAST （FILEPROPERTY （名称， 'SpaceUsed' ） AS int ）/ 128.0 AS AvailableSpaceInMB
来自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如下：
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
格式化堆里的数据页
页:
[1]