[MS SQL] - 系统表常用操作

1. 查看语句执行时间 

SELECT top 100 EQS.total_worker_time,EQS.total_worker_time AS TotalWorkerTime
           ,EQS.total_logical_reads + EQS.total_logical_writes AS TotalLogicalIO
           ,EQS.execution_count As ExeCnt
           ,EQS.last_execution_time AS LastUsage
           ,EQS.total_worker_time / EQS.execution_count/1000/1000 as AvgCPUTimeMiS
           ,(EQS.total_logical_reads + EQS.total_logical_writes) / EQS.execution_count   AS AvgLogicalIO
           ,DB.name AS DatabaseName
           ,SUBSTRING(EST.text,1 + EQS.statement_start_offset / 2,(CASE WHEN EQS.statement_end_offset = -1 THEN LEN(convert(nvarchar(max), EST.text)) * 2 ELSE EQS.statement_end_offset END - EQS.statement_start_offset) / 2) AS SqlStatement
FROM sys.dm_exec_query_stats AS EQS
CROSS APPLY sys.dm_exec_sql_text(EQS.sql_handle) AS EST
CROSS APPLY sys.dm_exec_query_plan(EQS.plan_handle) AS EQP
LEFT JOIN sys.databases AS DB ON EST.dbid = DB.database_id
WHERE EQS.last_execution_time > '2019-01-15 08:00:00'
and EQS.total_worker_time / EQS.execution_count/1000/1000 > 60

2.表占物理空间

exec sp_spaceused 'b_item'

3.查所有过程

SELECT * FROM sysobjects o WHERE EXISTS(SELECT 1 FROM sysComments WHERE id= o.id and text LIKE '%物料不在可用季度中%')

4.查字段在哪个表

select table_name from INFORMATION_SCHEMA.columns where column_name='banid'

5.某个表占内存多少

select OBJECT_NAME(object_id) 表名,COUNT(*) 页数,COUNT(*)*8/1024.0 Mb

from sys.dm_os_buffer_descriptors a,sys.allocation_units b,sys.partitions c

where a.allocation_unit_id=b.allocation_unit_id

and b.container_id=c.hobt_id

and database_id=DB_ID()

group by OBJECT_NAME(object_id)

order by 2 desc