SqlServer不常用语句合集

最新推荐文章于 2023-05-04 09:56:25 发布

CPU不够了

最新推荐文章于 2023-05-04 09:56:25 发布

阅读量265

点赞数

分类专栏： SQL 文章标签： sql 数据库大数据异常处理

本文链接：https://blog.csdn.net/qq_36535245/article/details/109129348

版权

SQL 专栏收录该内容

4 篇文章 0 订阅

订阅专栏

大数据量删除数据

--大数据量下直接删除数据会锁表，导致数据库死锁。设置数据库每页数量可以解决
SET ROWCOUNT 2000
delete_more:
     delete from 表
	where 条件
IF @@ROWCOUNT > 0 GOTO delete_more
SET ROWCOUNT 0

数据库锁表查询语句

SELECT t1.resource_type [资源锁定类型],
       DB_NAME(resource_database_id) AS 数据库名,
       t1.resource_associated_entity_id 锁定对象,
       t1.request_mode AS 等待者请求的锁定模式,
       t1.request_session_id 等待者SID,
       t2.wait_duration_ms 等待时间,
       (
           SELECT text
           FROM sys.dm_exec_requests r
               CROSS APPLY sys.dm_exec_sql_text(r.sql_handle)
           WHERE r.session_id = t1.request_session_id
       ) AS 等待者要执行的SQL,
       t2.blocking_session_id [锁定者SID],
       (
           SELECT text
           FROM sys.sysprocesses p
               CROSS APPLY sys.dm_exec_sql_text(p.sql_handle)
           WHERE p.spid = t2.blocking_session_id
       ) 锁定者执行语句
FROM sys.dm_tran_locks t1,
     sys.dm_os_waiting_tasks t2
WHERE t1.lock_owner_address = t2.resource_address;

数据库sql执行耗时语句

SELECT TOP 3000
       total_worker_time / 1000 AS [总消耗CPU 时间(ms)],
       execution_count [运行次数],
       qs.total_worker_time / qs.execution_count / 1000 AS [平均消耗CPU 时间(ms)],
       last_execution_time AS [最后一次执行时间],
       min_worker_time / 1000 AS [最小执行时间(ms)],
       max_worker_time / 1000 AS [最大执行时间(ms)],
       SUBSTRING(   qt.text,
                    qs.statement_start_offset / 2 + 1,
                    (CASE
                         WHEN qs.statement_end_offset = -1 THEN
                             DATALENGTH(qt.text)
                         ELSE
                             qs.statement_end_offset
                     END - qs.statement_start_offset
                    ) / 2 + 1
                ) AS [使用CPU的语法],
       qt.text [完整语法],
       qt.dbid,
       dbname = DB_NAME(qt.dbid),
       qt.objectid,
       OBJECT_NAME(qt.objectid, qt.dbid) ObjectName
FROM sys.dm_exec_query_stats qs WITH (NOLOCK)
    CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
WHERE execution_count > 1
ORDER BY (qs.total_worker_time / qs.execution_count / 1000) DESC;