数据库优化
数据库优化
1. 查看活动监视器。
2. 查看慢Sql.
3. 查看Resources Waits里面等等类型Lock的多不多。
4. 逻辑读太多，cpu就会高。
select * from syserrorlog where friendlymsg like '%列“%”不属于表 Table%'
select errorpage a,createdate aa,friendlymsg c1,* from syserrorlog where friendlymsg like '%was deadlocked on lock | communication buffer resources with another process and has been chosen as the deadlock victim. Rerun the transaction%'
order by createdate desc
获取慢sql语句
A.获取有关按平均 CPU 时间排在最前面的五百个查询的信息
SELECT TOP 500 DB_NAME([st].dbid) AS DB_NAME ,OBJECT_NAME([st].objectid) as OBJECTNAME,
SUBSTRING(st.text, (qs.statement_start_offset/2)+1,
((CASE qs.statement_end_offset
WHEN -1 THEN DATALENGTH(st.text)
ELSE qs.statement_end_offset
END - qs.statement_start_offset)/2) + 1) AS statement_text,total_worker_time/execution_count AS [Avg CPU Time],execution_count,
plan_generation_num,
last_execution_time,
total_worker_time,
last_worker_time,
min_worker_time,
max_worker_time,
max_logical_writes,
total_logical_reads,
min_logical_reads,
max_logical_reads,
total_physical_reads,
last_physical_reads,
min_physical_reads,
max_physical_reads,
total_logical_writes,
last_logical_writes,
min_logical_writes
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
where st.dbid = DB_ID('fx361_new')
ORDER BY total_worker_time/execution_count DESC;
B. 查看系统等待的sql：
SELECT ss.host_name, req.session_id ,req.status ,req.blocking_session_id
,req.wait_type ,req.wait_time ,req.wait_resource
,req.transaction_id ,st.text
FROM sys.dm_exec_requests req
CROSS APPLY sys.dm_exec_sql_text(req.sql_handle) as ST
cross apply sys.dm_exec_sessions ss
where req.status = N'suspended' and ss.session_id=req.session_id
C.返回按批执行的 SQL 查询的文本，并提供有关它们的统计信息
SELECT DB_NAME([s2].dbid) as DBNAME,s2.dbid, OBJECT_NAME([s2].objectid) as OBJECTNAME,
s1.sql_handle,
(SELECT TOP 1 SUBSTRING(s2.text,statement_start_offset / 2+1 ,
( (CASE WHEN statement_end_offset = -1
THEN (LEN(CONVERT(nvarchar(max),s2.text)) * 2)
ELSE statement_end_offset END) - statement_start_offset) / 2+1)) AS sql_statement,
plan_generation_num,
last_execution_time,
total_worker_time,
last_worker_time,
min_worker_time,
max_worker_time,
max_logical_writes,
total_logical_reads,
min_logical_reads,
max_logical_reads,
total_physical_reads,
last_physical_reads,
min_physical_reads,
max_physical_reads,
total_logical_writes,
last_logical_writes,
min_logical_writes
FROM sys.dm_exec_query_stats AS s1
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS s2
WHERE s2.objectid is null
ORDER BY s1.sql_handle, s1.statement_start_offset, s1.statement_end_offset;
