1. 查询某存储过程的访问情况
SELECT TOP 1000 db_name(d.database_id) as DBName, s.name as 存储名字, s.type_desc as 存储类型,
d.cached_time as SP 添加到缓存的时间, d.last_execution_time AS 上次执行 SP 的时间,
d.last_elapsed_time as [上次执行 SP 所用的时间(微秒)], d.total_elapsed_time [完成此 SP 的执行所用的总时间(微秒)],
d.total_elapsed_time/d.execution_count AS [平均执行时间(微秒)], d.execution_count as 自上次编译以来所执行的次数
FROM sys.procedures s INNER JOIN sys.dm_exec_procedure_stats d
ON s.object_id = d.object_id where s.name='存储过程的名称' ---- 请在此处替换要统计的 SP
ORDER BY d.total_elapsed_time/d.execution_count DESC
2. 查询包含关键字 (字符串) 的存储过程 SP
- Select distinct o.* from sysobjects o, syscomments s
- where o.xtype='P' and s.id=o.id and text like '% 关键字 %' order by o.name
3. 查询包含关键字 (字符串) 的 Job
- Select * from msdb.dbo.sysjobs
- where job_id in(Select job_id from msdb.dbo.sysjobsteps where command like '%XXXXXX%' )
4. 查询哪些 SQL 语句占用 CPU 的频率高
- SELECT TOP 10 [cpu_time],
- [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])='数据库名字' ---- 请在此处输入要统计的数据库名字
ORDER BY [cpu_time] DESC
5. DB 的单用户模式更新成多用户模式
alter database 数据库名字 set multi_user;
6. 查询某表的列的详情
select * from syscolumns where id=object_id('要统计的表') --- 请替换
7. 查询当前 SQL Server 日志信息
Exec xp_readerrorlog 0
其实 xp_readerrorlog 一共有 7 个参数
(1) 存档编号;
(2) 日志类型(1 为 SQL Server 日志, 2 为 SQL Agent 日志);
(3) 查询包含的字符串;
(4) 查询包含的字符串;
(5) LogDate 开始时间;
(6) 结果排序, 按 LogDate 排序(可以为降序 "Desc" Or 升序 "Asc");
(7) 结果排序, 按 LogDate 排序(可以为降序 "Desc" Or 升序 "Asc") .
8. 查询 Job 明细
- SELECT b.[name] [JobName]
- ,b.enabled [Enabled]
- ,a.step_id [StepID]
- ,b.description [JobDescription]
- ,a.step_name [StepName]
- ,a.command [Script]
- FROM msdb.dbo.sysjobsteps a
- INNER JOIN msdb.dbo.sysjobs b
- ON a.job_id=b.job_id
来源: https://www.cnblogs.com/xuliuzai/p/9649662.html