SQLSERVER数据库死锁与优化杂谈

</tr>
<tr>
<td>DATABASE_CHECKPOINT</td>
<td>Used to serialize checkpoints within a database.</td>

</tr>
<tr>
<td>CLR_PROCEDURE_HASHTABLE</td>
<td>Internal use only.</td>

</tr>
<tr>
<td>CLR_UDX_STORE</td>
<td>Internal use only.</td>

</tr>
<tr>
<td>CLR_DATAT_ACCESS</td>
<td>Internal use only.</td>

</tr>
<tr>
<td>CLR_XVAR_PROXY_LIST</td>
<td>Internal use only.</td>

</tr>
<tr>
<td>DBCC_CHECK_AGGREGATE</td>
<td>Internal use only.</td>

</tr>
<tr>
<td>DBCC_CHECK_RESULTSET</td>
<td>Internal use only.</td>

</tr>
<tr>
<td>DBCC_CHECK_TABLE</td>
<td>Internal use only.</td>

</tr>
<tr>
<td>DBCC_CHECK_TABLE_INIT</td>
<td>Internal use only.</td>

</tr>
<tr>
<td>DBCC_CHECK_TRACE_LIST</td>
<td>Internal use only.</td>

</tr>
<tr>
<td>DBCC_FILE_CHECK_OBJECT</td>
<td>Internal use only.</td>

</tr>
<tr>
<td>DBCC_PERF</td>
<td>Used to synchronize internal performance monitor counters.</td>

</tr>
<tr>
<td>DBCC_PFS_STATUS</td>
<td>Internal use only.</td>

</tr>
<tr>
<td>DBCC_OBJECT_METADATA</td>
<td>Internal use only.</td>

</tr>
<tr>
<td>DBCC_HASH_DLL</td>
<td>Internal use only.</td>

</tr>
<tr>
<td>EVENTING_CACHE</td>
<td>Internal use only.</td>

</tr>
<tr>
<td>FCB</td>
<td>Used to synchronize access to the file control block.</td>

</tr>
<tr>
<td>FCB_REPLICA</td>
<td>Internal use only.</td>

</tr>
<tr>
<td>FGCB_ALLOC</td>
<td>Use to synchronize access to round robin allocation information within a filegroup.</td>

</tr>
<tr>
<td>FGCB_ADD_REMOVE</td>
<td>Use to synchronize access to filegroups for add,drop,grow,and shrink file operations.</td>

</tr>
<tr>
<td>FILEGROUP_MANAGER</td>
<td>Internal use only.</td>

</tr>
<tr>
<td>FILE_MANAGER</td>
<td>Internal use only.</td>

</tr>
<tr>
<td>FILESTREAM_FCB</td>
<td>Internal use only.</td>

</tr>
<tr>
<td>FILESTREAM_FILE_MANAGER</td>
<td>Internal use only.</td>

</tr>
<tr>
<td>FILESTREAM_GHOST_FILES</td>
<td>Internal use only.</td>

</tr>
<tr>
<td>FILESTREAM_DFS_ROOT</td>
<td>Internal use only.</td>

</tr>
<tr>
<td>LOG_MANAGER</td>
<td>Internal use only.</td>

</tr>
<tr>
<td>FULLTEXT_DOCUMENT_ID</td>
<td>Internal use only.</td>

</tr>
<tr>
<td>FULLTEXT_DOCUMENT_ID_TRANSACTION</td>
<td>Internal use only.</td>

</tr>
<tr>
<td>FULLTEXT_DOCUMENT_ID_NOTIFY</td>
<td>Internal use only.</td>

</tr>
<tr>
<td>FULLTEXT_LOGS</td>
<td>Internal use only.</td>

</tr>
<tr>
<td>FULLTEXT_CRAWL_LOG</td>
<td>Internal use only.</td>

</tr>
<tr>
<td>FULLTEXT_ADMIN</td>
<td>Internal use only.</td>

</tr>
<tr>
<td>FULLTEXT_AMDIN_COMMAND_CACHE</td>
<td>Internal use only.</td>

</tr>
<tr>
<td>FULLTEXT_LANGUAGE_TABLE</td>
<td>Internal use only.</td>

</tr>
<tr>
<td>FULLTEXT_CRAWL_DM_LIST</td>
<td>Internal use only.</td>

</tr>
<tr>
<td>FULLTEXT_CRAWL_CATALOG</td>
<td>Internal use only.</td>

</tr>
<tr>
<td>FULLTEXT_FILE_MANAGER</td>
<td>Internal use only.</td>

</tr>
<tr>
<td>DATABASE_MIRRORING_REDO</td>
<td>Internal use only.</td>

</tr>
<tr>
<td>DATABASE_MIRRORING_SERVER</td>
<td>Internal use only.</td>

</tr>
<tr>
<td>DATABASE_MIRRORING_CONNECTION</td>
<td>Internal use only.</td>

</tr>
<tr>
<td>DATABASE_MIRRORING_STREAM</td>
<td>Internal use only.</td>

</tr>
<tr>
<td>QUERY_OPTIMIZER_VD_MANAGER</td>
<td>Internal use only.</td>

</tr>
<tr>
<td>QUERY_OPTIMIZER_ID_MANAGER</td>
<td>Internal use only.</td>

</tr>
<tr>
<td>QUERY_OPTIMIZER_VIEW_REP</td>
<td>Internal use only.</td>

</tr>
<tr>
<td>RECOVERY_BAD_PAGE_TABLE</td>
<td>Internal use only.</td>

</tr>
<tr>
<td>RECOVERY_MANAGER</td>
<td>Internal use only.</td>

</tr>
<tr>
<td>SECURITY_OPERATION_RULE_TABLE</td>
<td>Internal use only.</td>

</tr>
<tr>
<td>SECURITY_OBJPERM_CACHE</td>
<td>Internal use only.</td>

</tr>
<tr>
<td>SECURITY_CRYPTO</td>
<td>Internal use only.</td>

</tr>
<tr>
<td>SECURITY_KEY_RING</td>
<td>Internal use only.</td>

</tr>
<tr>
<td>SECURITY_KEY_LIST</td>
<td>Internal use only.</td>

</tr>
<tr>
<td>SERVICE_BROKER_CONNECTION_RECEIVE</td>
<td>Internal use only.</td>

</tr>
<tr>
<td>SERVICE_BROKER_TRANSMISSION</td>
<td>Internal use only.</td>

</tr>
<tr>
<td>SERVICE_BROKER_TRANSMISSION_UPDATE</td>
<td>Internal use only.</td>

</tr>
<tr>
<td>SERVICE_BROKER_TRANSMISSION_STATE</td>
<td>Internal use only.</td>

</tr>
<tr>
<td>SERVICE_BROKER_TRANSMISSION_ERRORS</td>
<td>Internal use only.</td>

</tr>
<tr>
<td>SSBXmitWork</td>
<td>Internal use only.</td>

</tr>
<tr>
<td>SERVICE_BROKER_MESSAGE_TRANSMISSION</td>
<td>Internal use only.</td>

</tr>
<tr>
<td>SERVICE_BROKER_MAP_MANAGER</td>
<td>Internal use only.</td>

</tr>
<tr>
<td>SERVICE_BROKER_HOST_NAME</td>
<td>Internal use only.</td>

</tr>
<tr>
<td>SERVICE_BROKER_READ_CACHE</td>
<td>Internal use only.</td>

</tr>
<tr data-find="_2">
<td>SERVICE_BROKER_WAITFOR_MANAGER</td>
<td data-find="_1">Used to synchronize an instance level map of waiter queues. One queue exists per database ID,Database Version,and Queue ID tuple. Contention on latches of this class can occur when many connections are: In a WAITFOR(RECEIVE) wait state; calling WAITFOR(RECEIVE); exceeding the WAITFOR timeout; receiving a message; committing or rolling back the transaction that contains the WAITFOR(RECEIVE); You can reduce the contention by reducing the number of threads in a WAITFOR(RECEIVE) wait state.</td>

</tr>
<tr>
<td>SERVICE_BROKER_WAITFOR_TRANSACTION_DATA</td>
<td>Internal use only.</td>

</tr>
<tr>
<td>SERVICE_BROKER_TRANSMISSION_TRANSACTION_DATA</td>
<td>Internal use only.</td>

</tr>
<tr>
<td>SERVICE_BROKER_TRANSPORT</td>
<td>Internal use only.</td>

</tr>
<tr>
<td>SERVICE_BROKER_MIRROR_ROUTE</td>
<td>Internal use only.</td>

</tr>
<tr>
<td>TRACE_ID</td>
<td>Internal use only.</td>

</tr>
<tr>
<td>TRACE_AUDIT_ID</td>
<td>Internal use only.</td>

</tr>
<tr>
<td>TRACE</td>
<td>Internal use only.</td>

</tr>
<tr>
<td>TRACE_CONTROLLER</td>
<td>Internal use only.</td>

</tr>
<tr>
<td>TRACE_EVENT_QUEUE</td>
<td>Internal use only.</td>

</tr>
<tr>
<td>TRANSACTION_DISTRIBUTED_MARK</td>
<td>Internal use only.</td>

</tr>
<tr>
<td>TRANSACTION_OUTCOME</td>
<td>Internal use only.</td>

</tr>
<tr>
<td>NESTING_TRANSACTION_READONLY</td>
<td>Internal use only.</td>

</tr>
<tr>
<td>NESTING_TRANSACTION_FULL</td>
<td>Internal use only.</td>

</tr>
<tr>
<td>MSQL_TRANSACTION_MANAGER</td>
<td>Internal use only.</td>

</tr>
<tr>
<td>DATABASE_AUTONAME_MANAGER</td>
<td>Internal use only.</td>

</tr>
<tr>
<td>UTILITY_DYNAMIC_VECTOR</td>
<td>Internal use only.</td>

</tr>
<tr>
<td>UTILITY_SPARSE_BITMAP</td>
<td>Internal use only.</td>

</tr>
<tr>
<td>UTILITY_DATABASE_DROP</td>
<td>Internal use only.</td>

</tr>
<tr>
<td>UTILITY_DYNAMIC_MANAGER_VIEW</td>
<td>Internal use only.</td>

</tr>
<tr>
<td>UTILITY_DEBUG_FILESTREAM</td>
<td>Internal use only.</td>

</tr>
<tr>
<td>UTILITY_LOCK_INFORMATION</td>
<td>Internal use only.</td>

</tr>
<tr>
<td>VERSIONING_TRANSACTION</td>
<td>Internal use only.</td>

</tr>
<tr>
<td>VERSIONING_TRANSACTION_LIST</td>
<td>Internal use only.</td>

</tr>
<tr>
<td>VERSIONING_TRANSACTION_CHAIN</td>
<td>Internal use only.</td>

</tr>
<tr>
<td>VERSIONING_STATE</td>
<td>Internal use only.</td>

</tr>
<tr>
<td>VERSIONING_STATE_CHANGE</td>
<td>Internal use only.</td>

</tr>
<tr>
<td>KTM_VIRTUAL_CLOCK</td>
<td>Internal use only.</td>

</tr>

维护：对数据库、索引或文件组进行维护的任务。

杂项：杂项任务，如启用跟踪标志或从内存中删除 DLL。

信息：收集并显示各种类型信息的任务。

验证：对数据库、表、索引、目录、文件组或数据库页的分配进行的验证操作。

DBCC shrinkdatabase

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

?执行结果如下：

?

各字段含义如下：

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

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

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

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

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

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

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

SQL如下：

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

DBCC参考网址：

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

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

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

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

查询结果如下：

Operation

而这个BitMap在数据库第7页：

DCM页 差异变更(Differential Changed Map，DCM)页面他跟踪一个文件中的哪一个区在最新一次完整数据库备份之后被修改过。SQLSERVER用在增量备份时只对已发生数据变更的分区进行增量备份即可

注：此文章为原创，欢迎转载，请在文章页面明显位置给出此文链接！若您觉得这篇文章还不错，请点击下右下角的推荐】，非常感谢！

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!