SQL 性能优化

-----------------------查看被锁表：------------------------------------
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