ITKeyword,专注技术干货聚合推荐

注册 | 登录

[转] sql server 性能优化精华(系统工具级别sql)

nan8468 分享于

2021腾讯云限时秒杀,爆款1核2G云服务器298元/3年!(领取2860元代金券),
地址https://cloud.tencent.com/act/cps/redirect?redirect=1062

2021阿里云最低价产品入口+领取代金券(老用户3折起),
入口地址https://www.aliyun.com/minisite/goods

推荐:SQL Server 性能优化工具使用方法

数据和工作负荷示例 使用下例说明 SQL Server 性能工具的使用。首先创建下表。 create table testtable (nkey1 int identity, col2 char(300) default 'abc', ck


--SQL Server常用的性能计数器
SQLServer:Plan Cache ----Cache Hit Ratio
SQLserver:Buffer Manager----Buffer Cache Hit Ratio
SQLServer:Locks---- Lock Waits/sec
SQLServer:Access Methods----Full Scans/sec
SQLServer:Access Methods----Lazy Writes/sec


--正在执行中的SQL语句
SELECT
es.session_id,
database_name = DB_NAME(database_id),
er.cpu_time,
er.reads,
er.writes,
er.logical_reads,
login_name,
er.status,
blocking_session_id,
wait_type,
individual_query = SUBSTRING (qt.text, er.statement_start_offset/2, (CASE WHEN er.statement_end_offset = -1 THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2 ELSE er.statement_end_offset END - er.statement_start_offset)/2),
parent_query = qt.text,
program_name,
host_name,
nt_domain,
start_time
FROM
sys.dm_exec_requests er
    INNER JOIN sys.dm_exec_sessions es ON er.session_id = es.session_id
    CROSS APPLY sys.dm_exec_sql_text(er.sql_handle)as qt
WHERE
es.session_id > 50          
    AND es.session_Id NOT IN (@@SPID)
ORDER BY
1, 2


dbcc inputbuffer(id)

--执行次数最多的语句
SELECT TOP 100
execution_count,
total_worker_time / 1000 AS total_worker_time, 
total_logical_reads,
total_logical_writes,
[text]
FROM
sys.dm_exec_query_stats qs
CROSS APPLY
sys.dm_exec_sql_text(qs.sql_handle) AS st
ORDER BY
execution_count DESC

--执行时间最长的语句
SELECT TOP 100
execution_count,
total_worker_time / 1000 AS total_worker_time, 
total_logical_reads,
total_logical_writes,
[text]
FROM
sys.dm_exec_query_stats qs
CROSS APPLY
sys.dm_exec_sql_text(qs.sql_handle) AS st
ORDER BY
max_elapsed_time DESC


--消耗CPU最多的语句
SELECT TOP 100
execution_count,
total_worker_time / 1000 AS total_worker_time, 
total_logical_reads,
total_logical_writes,
[text]
FROM
sys.dm_exec_query_stats qs
CROSS APPLY
sys.dm_exec_sql_text(qs.sql_handle) AS st
ORDER BY
total_worker_time DESC

--消耗IO读最多的语句
SELECT TOP 100
execution_count,
total_worker_time / 1000 AS total_worker_time, 
total_logical_reads,
total_logical_writes,
[text]
FROM
sys.dm_exec_query_stats qs
CROSS APPLY
sys.dm_exec_sql_text(qs.sql_handle) AS st
ORDER BY
total_logical_reads DESC

--消耗IO写最多的语句
SELECT TOP 100
execution_count,
total_worker_time / 1000 AS total_worker_time, 
total_logical_reads,
total_logical_writes,
[text]
FROM
sys.dm_exec_query_stats qs
CROSS APPLY
sys.dm_exec_sql_text(qs.sql_handle) AS st
ORDER BY
total_logical_writes DESC


--查看缺失索引
SELECT * FROM sys.dm_db_missing_index_details ORDER BY object_id
SELECT * FROM sys.dm_db_missing_index_details where object_id = OBJECT_ID('t_market_bankproduct')

--查询sql执行时间
SET STATISTICS IO Off
SET STATISTICS TIME Off

推荐:SQL性能优化工具TKPROF

 全名为Trace Kernel Profile,用来格式化跟踪文件,是一个命令行工具。 基本的格式如下: tkprof tracefile outputfile … tracefile:要分析的trace文件 out

--SQL Server常用的性能计数器 SQLServer:Plan Cache ----Cache Hit Ratio SQLserver:Buffer Manager----Buffer Cache Hit Ratio SQLServer:Locks---- Lock Waits/sec SQLServer:Access Methods

相关阅读排行


相关内容推荐

最新文章

×

×

请激活账号

为了能正常使用评论、编辑功能及以后陆续为用户提供的其他产品,请激活账号。

您的注册邮箱: 修改

重新发送激活邮件 进入我的邮箱

如果您没有收到激活邮件,请注意检查垃圾箱。