亚洲乱色熟女一区二区三区丝袜,天堂√中文最新版在线,亚洲精品乱码久久久久久蜜桃图片,香蕉久久久久久av成人,欧美丰满熟妇bbb久久久

1、查詢出最近所有耗時(shí)最大的SQL語(yǔ)句

SELECT s2.dbid,
       s1.sql_handle,
 (
 SELECT TOP 1
                  SUBSTRING(   s2.text,
                               statement_start_offset / 2 + 1,
 ((CASE
 WHEN statement_end_offset = -1 THEN
 (LEN(CONVERT(NVARCHAR(MAX), s2.text)) * 2)
 ELSE
                                         statement_end_offset
 END
 ) - statement_start_offset
 ) / 2 + 1
 )
 ) AS 執(zhí)行SQL,
       last_worker_time '最后執(zhí)行總耗時(shí)(毫秒)',
       last_execution_time '最后執(zhí)行時(shí)間',
       total_worker_time '所有執(zhí)行總耗時(shí)(毫秒)',
       min_worker_time '執(zhí)行最小耗時(shí)(毫秒)',
       max_worker_time '執(zhí)行最大耗時(shí)(毫秒)',
       execution_count,
       plan_generation_num,
       total_physical_reads,
       last_physical_reads,
       min_physical_reads,
       max_physical_reads,
       total_logical_writes,
       last_logical_writes,
       min_logical_writes,
       max_logical_writes
FROM sys.dm_exec_query_stats AS s1
 CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS s2
WHERE s2.objectid IS NULL
ORDER BY last_worker_time DESC,
         s1.sql_handle,
         s1.statement_start_offset,
         s1.statement_end_offset;

說明：

● sys.dm_exec_query_stats AS s1：SQL Server中的系統(tǒng)視圖，提供了關(guān)于查詢統(tǒng)計(jì)信息，例如CPU時(shí)間、讀取次數(shù)和寫入次數(shù)等。

2、查詢數(shù)據(jù)庫(kù)每個(gè)數(shù)據(jù)表存儲(chǔ)占用

查詢指定數(shù)據(jù)庫(kù)的記錄數(shù)、總工占用空間KB、總共占用空間MB、已使用空間KB、已使用空間MB、未使用空間KB、未使用空間MB，這對(duì)于分析磁盤占用非常有用。

SELECT
 t.NAME AS 表名,
 s.Name AS 架構(gòu),
 p.rows AS 記錄數(shù),
 SUM ( a.total_pages ) * 8 AS 總工占用空間KB,
 CAST ( ROUND( ( ( SUM ( a.total_pages ) * 8 ) / 1024.00 ), 2 ) AS NUMERIC ( 36, 2 ) ) AS 總共占用空間MB,
 SUM ( a.used_pages ) * 8 AS 已使用空間KB,
 CAST ( ROUND( ( ( SUM ( a.used_pages ) * 8 ) / 1024.00 ), 2 ) AS NUMERIC ( 36, 2 ) ) AS 已使用空間MB,
 ( SUM ( a.total_pages ) - SUM ( a.used_pages ) ) * 8 AS 未使用空間KB,
 CAST (
 ROUND( ( ( SUM ( a.total_pages ) - SUM ( a.used_pages ) ) * 8 ) / 1024.00, 2 ) AS NUMERIC ( 36, 2 ) 
 ) AS 未使用空間MB 
FROM sys.tables t
INNER JOIN sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN sys.allocation_units a ON p.partition_id = a.container_id
LEFT OUTER JOIN sys.schemas s ON t.schema_id = s.schema_id 
WHERE t.NAME NOT LIKE 'dt%' AND t.is_ms_shipped = 0 AND i.OBJECT_ID > 0 
GROUP BY t.Name,s.Name,p.Rows 
ORDER BY 總共占用空間MB DESC;

3、當(dāng)前正在執(zhí)行的最耗時(shí)的前10個(gè)SQL語(yǔ)句

該語(yǔ)句適合排查生產(chǎn)環(huán)境實(shí)時(shí)SQL語(yǔ)句慢的情況。

SELECT TOP 10 
    r.session_id,
    r.request_id,
    r.start_time AS '開始時(shí)間',
    r.status AS '狀態(tài)',
    r.command AS '命令',
    t.text AS 'sql語(yǔ)句',
    DB_NAME(r.database_id) AS '數(shù)據(jù)庫(kù)名',
    r.blocking_session_id AS '正在阻塞其他會(huì)話的會(huì)話ID',
    w.wait_type AS '等待資源類型',
    r.wait_time AS '等待時(shí)間',
    r.wait_resource AS '等待的資源',
    w.waiting_tasks_count AS '當(dāng)前正在進(jìn)行等待的任務(wù)數(shù)',
    r.reads AS '物理讀次數(shù)',
    r.writes AS '寫次數(shù)',
    r.logical_reads AS '邏輯讀次數(shù)',
    r.row_count AS '返回結(jié)果行數(shù)'
FROM sys.dm_exec_requests AS r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS t
LEFT JOIN sys.dm_os_wait_stats AS w ON r.wait_type = w.wait_type
WHERE r.session_id > 50 -- 排除系統(tǒng)進(jìn)程
ORDER BY r.cpu_time DESC;

4、SQLServer查看鎖表和解鎖

-- 第一步查詢被鎖表
select request_session_id   spid,OBJECT_NAME(resource_associated_entity_id) tableName   
from   sys.dm_tran_locks where resource_type='OBJECT';
--參數(shù)說明 spid   鎖表進(jìn)程 ；tableName   被鎖表名
-- 第二步解鎖語(yǔ)句 需要拿到spid然后殺掉縮表進(jìn)程
declare @spid  int 
Set @spid  = 57 --鎖表進(jìn)程
declare @sql varchar(1000)
set @sql='kill '+cast(@spid  as varchar)
exec(@sql)

5、快速清理數(shù)據(jù)庫(kù)日志文件

USE master
ALTER DATABASE DB SET RECOVERY SIMPLE WITH NO_WAIT
ALTER DATABASE DB SET RECOVERY SIMPLE --調(diào)整為簡(jiǎn)單模式
USE DB
DBCC SHRINKFILE (N'DB_log' , 2, TRUNCATEONLY) --設(shè)置壓縮后的日志大小為2M，可以自行指定
USE master
ALTER DATABASE DB SET RECOVERY FULL WITH NO_WAIT
ALTER DATABASE DB SET RECOVERY FULL --還原為完全模式