-----------------------查看被锁表：------------------------------------ select request_session_id spid,OBJECT_NAME(resource_associated_entity_id) tableName from sys.dm_tran_locks where resource_type=‘OBJECT‘ --spid 锁表进程 --tableName 被锁表名 --解锁： declare @spid int Set @spid = 57 --锁表进程 declare @sql varchar(1000) set @sql=‘kill ‘+cast(@spid as varchar) exec(@sql)
-----------------------------------查询CPU占用高的语句----------------------------------------- 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 -----------------------------------查询CPU占用高的语句----------------------------------------- select c.last_execution_time,c.execution_count,c.total_logical_reads,c.total_logical_writes,c.total_elapsed_time,c.last_elapsed_time, q.[text],DB_NAME(q.dbid) from (select top 50 qs.* from sys.dm_exec_query_stats qs order by qs.total_worker_time desc) as c cross apply sys.dm_exec_sql_text(plan_handle) as q where DB_NAME(q.dbid)=‘KONZEN_MOTO‘ order by c.total_worker_time desc go -----------------------------------执行计划----------------------------------------- SELECT top 5 DB_NAME(dbid),* FROM sys.dm_exec_cached_plans CROSS APPLY sys.dm_exec_query_plan(plan_handle) WHERE objtype =‘Proc‘ and dbid=11 GO sp_helpindex ‘Shop_Inventory‘ CREATE INDEX index_Shop_Inventory_1 ON Shop_Inventory(depotid)
https://msdn.microsoft.com/zh-cn/LIBRARY/ms181929(v=sql.120).aspx
https://msdn.microsoft.com/zh-cn/library/cc280701(v=sql.120).aspx
原文：http://www.cnblogs.com/FH-cnblogs/p/5066840.html