213 lines
14 KiB
Transact-SQL
213 lines
14 KiB
Transact-SQL
SELECT TOP 10
|
||
[session_id],
|
||
[request_id],
|
||
[start_time] AS '开始时间',
|
||
[status] AS '状态',
|
||
[command] AS '命令',
|
||
dest.[text] AS 'sql语句',
|
||
DB_NAME([database_id]) AS '数据库名',
|
||
[blocking_session_id] AS '正在阻塞其他会话的会话ID',
|
||
[wait_type] AS '等待资源类型',
|
||
[wait_time] AS '等待时间',
|
||
[wait_resource] AS '等待的资源',
|
||
[reads] AS '物理读次数',
|
||
[writes] AS '写次数',
|
||
[logical_reads] AS '逻辑读次数',
|
||
[row_count] AS '返回结果行数'
|
||
FROM sys.[dm_exec_requests] AS der
|
||
CROSS APPLY
|
||
sys.[dm_exec_sql_text](der.[sql_handle]) AS dest
|
||
WHERE [session_id]>50 AND DB_NAME(der.[database_id])='dwms4'
|
||
ORDER BY [cpu_time] DESC;
|
||
|
||
|
||
--在SSMS里选择以文本格式显示结果
|
||
SELECT TOP 10
|
||
dest.[text] AS 'sql语句'
|
||
FROM sys.[dm_exec_requests] AS der
|
||
CROSS APPLY
|
||
sys.[dm_exec_sql_text](der.[sql_handle]) AS dest
|
||
WHERE [session_id]>50
|
||
ORDER BY [cpu_time] DESC;
|
||
|
||
SELECT TOP 10
|
||
[session_id],
|
||
[request_id],
|
||
[start_time] AS '开始时间',
|
||
[status] AS '状态',
|
||
[command] AS '命令',
|
||
dest.[text] AS 'sql语句',
|
||
DB_NAME([database_id]) AS '数据库名',
|
||
[blocking_session_id] AS '正在阻塞其他会话的会话ID',
|
||
der.[wait_type] AS '等待资源类型',
|
||
[wait_time] AS '等待时间',
|
||
[wait_resource] AS '等待的资源',
|
||
[dows].[waiting_tasks_count] AS '当前正在进行等待的任务数',
|
||
[reads] AS '物理读次数',
|
||
[writes] AS '写次数',
|
||
[logical_reads] AS '逻辑读次数',
|
||
[row_count] AS '返回结果行数'
|
||
FROM sys.[dm_exec_requests] AS der
|
||
INNER JOIN [sys].[dm_os_wait_stats] AS dows
|
||
ON der.[wait_type]=[dows].[wait_type]
|
||
CROSS APPLY
|
||
sys.[dm_exec_sql_text](der.[sql_handle]) AS dest
|
||
WHERE [session_id]>50
|
||
ORDER BY [cpu_time] DESC;
|
||
|
||
SELECT TOP 10
|
||
total_worker_time/execution_count AS avg_cpu_cost, plan_handle,
|
||
execution_count,
|
||
(SELECT SUBSTRING(text, statement_start_offset/2 + 1,
|
||
(CASE WHEN statement_end_offset = -1
|
||
THEN LEN(CONVERT(nvarchar(max), text)) * 2
|
||
ELSE statement_end_offset
|
||
END - statement_start_offset)/2)
|
||
FROM sys.dm_exec_sql_text(sql_handle)) AS query_text
|
||
FROM sys.dm_exec_query_stats
|
||
ORDER BY [avg_cpu_cost] DESC
|
||
|
||
;SELECT
|
||
DatabaseName = DB_NAME(database_id)
|
||
,[Number Indexes Missing] = count(*)
|
||
FROM sys.dm_db_missing_index_details
|
||
GROUP BY DB_NAME(database_id)
|
||
ORDER BY 2 DESC;
|
||
SELECT TOP 10
|
||
[Total Cost] = ROUND(avg_total_user_cost * avg_user_impact * (user_seeks + user_scans),0)
|
||
, avg_user_impact
|
||
, TableName = statement
|
||
, [EqualityUsage] = equality_columns
|
||
, [InequalityUsage] = inequality_columns
|
||
, [Include Cloumns] = included_columns
|
||
FROM sys.dm_db_missing_index_groups g
|
||
INNER JOIN sys.dm_db_missing_index_group_stats s
|
||
ON s.group_handle = g.index_group_handle
|
||
INNER JOIN sys.dm_db_missing_index_details d
|
||
ON d.index_handle = g.index_handle
|
||
ORDER BY [Total Cost] DESC;
|
||
|
||
|
||
USE master
|
||
GO
|
||
--如果要指定数据库就把注释去掉
|
||
SELECT * FROM sys.[sysprocesses] WHERE [spid]>50 --AND DB_NAME([dbid])='gposdb'
|
||
SELECT COUNT(*) FROM [sys].[dm_exec_sessions] WHERE [session_id]>50;
|
||
;
|
||
|
||
|
||
SELECT TOP 10
|
||
[session_id],
|
||
[request_id],
|
||
[start_time] AS '开始时间',
|
||
[status] AS '状态',
|
||
[command] AS '命令',
|
||
dest.[text] AS 'sql语句',
|
||
DB_NAME([database_id]) AS '数据库名',
|
||
[blocking_session_id] AS '正在阻塞其他会话的会话ID',
|
||
[wait_type] AS '等待资源类型',
|
||
[wait_time] AS '等待时间',
|
||
[wait_resource] AS '等待的资源',
|
||
[reads] AS '物理读次数',
|
||
[writes] AS '写次数',
|
||
[logical_reads] AS '逻辑读次数',
|
||
[row_count] AS '返回结果行数'
|
||
FROM sys.[dm_exec_requests] AS der
|
||
CROSS APPLY
|
||
sys.[dm_exec_sql_text](der.[sql_handle]) AS dest
|
||
WHERE [session_id]>50 AND DB_NAME(der.[database_id])='dwms4'
|
||
ORDER BY [cpu_time] DESC
|
||
|
||
;
|
||
|
||
|
||
(@LOCATIONID nvarchar(9),@GOODTYPE int,@VOLTYPE int,@cnt int)
|
||
SELECT TOP (@cnt) LOC.* FROM t_wmsLocation LOC WHERE LOC.STATE = 0 AND goodsType =@GOODTYPE AND LOC.VOLTYPE = @VOLTYPE
|
||
AND NOT EXISTS (SELECT 1 FROM T_WMSSTOCK WHERE LOCATIONID = LOC.LOCATIONID) AND LOCATIONID < @LOCATIONID ORDER BY LOC.layer,LOC.locationId DESC;
|
||
SELECT TOP (@cnt) LOC.* FROM t_wmsLocation LOC WHERE LOC.STATE = 0 AND goodsType =@GOODTYPE AND LOC.VOLTYPE = @VOLTYPE
|
||
AND NOT EXISTS (SELECT 1 FROM T_WMSSTOCK WHERE LOCATIONID = LOC.LOCATIONID) AND LOCATIONID > @LOCATIONID ORDER BY LOC.layer,LOC.locationId ;
|
||
|
||
|
||
--查看CPU数和user scheduler数目
|
||
SELECT cpu_count,scheduler_count FROM sys.dm_os_sys_info
|
||
--查看最大工作线程数
|
||
SELECT max_workers_count FROM sys.dm_os_sys_info；
|
||
|
||
|
||
SELECT
|
||
scheduler_address,
|
||
scheduler_id,
|
||
cpu_id,
|
||
status,
|
||
current_tasks_count,
|
||
current_workers_count,active_workers_count
|
||
FROM sys.dm_os_schedulers;
|
||
|
||
|
||
SELECT TOP 10
|
||
total_worker_time/execution_count AS avg_cpu_cost, plan_handle,
|
||
execution_count,
|
||
(SELECT SUBSTRING(text, statement_start_offset/2 + 1,
|
||
(CASE WHEN statement_end_offset = -1
|
||
THEN LEN(CONVERT(nvarchar(max), text)) * 2
|
||
ELSE statement_end_offset
|
||
END - statement_start_offset)/2)
|
||
FROM sys.dm_exec_sql_text(sql_handle)) AS query_text
|
||
FROM sys.dm_exec_query_stats
|
||
ORDER BY [avg_cpu_cost] DESC;
|
||
|
||
|
||
SELECT
|
||
DatabaseName = DB_NAME(database_id)
|
||
,[Number Indexes Missing] = count(*)
|
||
FROM sys.dm_db_missing_index_details
|
||
GROUP BY DB_NAME(database_id)
|
||
ORDER BY 2 DESC;
|
||
|
||
|
||
SELECT TOP 10
|
||
[Total Cost] = ROUND(avg_total_user_cost * avg_user_impact * (user_seeks + user_scans),0)
|
||
, avg_user_impact
|
||
, TableName = statement
|
||
, [EqualityUsage] = equality_columns
|
||
, [InequalityUsage] = inequality_columns
|
||
, [Include Cloumns] = included_columns
|
||
FROM sys.dm_db_missing_index_groups g
|
||
INNER JOIN sys.dm_db_missing_index_group_stats s
|
||
ON s.group_handle = g.index_group_handle
|
||
INNER JOIN sys.dm_db_missing_index_details d
|
||
ON d.index_handle = g.index_handle
|
||
ORDER BY [Total Cost] DESC;
|
||
|
||
/*
|
||
avg_total_user_cost:减少的用户查询的平均成本
|
||
avg_user_impact:户查询可能获得的平均百分比收益
|
||
user_scans:扫描次数
|
||
user_seeks:查找次数。
|
||
equality_columns: 哪个字段缺失了索引(where 后面的筛选字段)
|
||
included_columns:查询的涵盖列（简单来讲就是 select 后面的字段）。
|
||
*/
|
||
|
||
|
||
SELECT avg_user_impact,
|
||
avg_total_user_cost,
|
||
avg_total_user_cost * avg_user_impact * ( user_scans + user_seeks ) AS PossibleImprovement ,
|
||
last_user_seek ,
|
||
last_user_scan ,
|
||
[statement] AS [Object] ,
|
||
'CREATE INDEX [IDX_' + CONVERT(VARCHAR(32), GS.group_handle) + '_'
|
||
+ CONVERT(VARCHAR(32), D.index_handle) + '_'
|
||
+ REPLACE(REPLACE(REPLACE([STATEMENT], ']', ''), '[', ''), '.', '')
|
||
+ ']' + ' ON ' + [statement] + ' (' + ISNULL(equality_columns, '')
|
||
+ CASE WHEN equality_columns IS NOT NULL
|
||
AND inequality_columns IS NOT NULL THEN ','
|
||
ELSE ''
|
||
END + ISNULL(inequality_columns, '') + ')' + ISNULL(' INCLUDE ('
|
||
+ included_columns
|
||
+ ')', '') AS Create_Index_Syntax
|
||
FROM sys.dm_db_missing_index_groups AS G
|
||
INNER JOIN sys.dm_db_missing_index_group_stats AS GS ON G.index_group_handle = GS.group_handle
|
||
INNER JOIN sys.dm_db_missing_index_details AS D ON G.index_handle = D.index_handle
|
||
ORDER BY PossibleImprovement DESC ;
|
||
|
||
|