About performance tuning

Question

my english is poor--显示芯片信息
exec xp_msver 'Platform'--可参照联机帮助，有几项选择
--处理器类型
exec xp_msver 'ProcessorType'------------启动信息----------
--查看所有数据库用户登录信息
sp_helplogins 
--查看数据库启动时间
select convert(varchar(30),login_time,120) 
from master..sysprocesses where spid=1 
--查看数据库启动的参数
exec master..xp_msver---------------进程的信息
--sysprocesses   主数据库              进程
select * from master..sysprocesses
--查看数据库里用户和进程的信息
sp_who --========
使用xp_cmdshell 调用cmd命令得到cpu等信息

Answer 1

Thanks for your reply.As you know, select * from master..sysprocesses
we can get total CPU usage and I/O usage since one user login, but it can't tell us which sp or sql batch consumes most resource at a certain time...

Answer 2

--总计耗费CPU时间最长的查询计划
SELECT TOP 5 total_worker_time,last_worker_time,max_worker_time,min_worker_time,
       SUBSTRING(st.text,(qs.statement_start_offset/2)+1,
                ((CASE statement_end_offset WHEN -1 THEN DATALENGTH(st.text)
                  ELSE qs.statement_end_offset END
                  -qs.statement_start_offset)/2)+1) AS statement_text
FROM sys.dm_exec_query_stats AS qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
ORDER BY total_worker_time DESC
--单次执行耗费CPU时间最长的查询计划
SELECT TOP 5 total_worker_time,last_worker_time,max_worker_time,min_worker_time,
       SUBSTRING(st.text,(qs.statement_start_offset/2)+1,
                ((CASE statement_end_offset WHEN -1 THEN DATALENGTH(st.text)
                  ELSE qs.statement_end_offset END
                  -qs.statement_start_offset)/2)+1) AS statement_text
FROM sys.dm_exec_query_stats AS qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
ORDER BY max_worker_time DESC
--执行次数最多的查询计划
SELECT TOP 5 creation_time,last_execution_time,execution_count,
       SUBSTRING(st.text,(qs.statement_start_offset/2)+1,
         ((CASE statement_end_offset WHEN -1 THEN DATALENGTH(st.text)
           ELSE qs.statement_end_offset END
           -qs.statement_start_offset)/2)+1) AS statement_text
FROM sys.dm_exec_query_stats AS qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
ORDER BY execution_count DESC

Answer 3

--I/O最频繁前5语句
SELECT TOP 5 (total_logical_reads/execution_count) AS avg_logical_reads,
             (total_logical_writes/execution_count) AS avg_logical_writes,
             (total_physical_reads/execution_count) AS avg_physical_reads,
             execution_count,statement_start_offset,p.query_plan,q.text
FROM sys.dm_exec_query_stats 
CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS p
CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS q
ORDER BY (total_logical_reads+total_logical_writes)/execution_count DESC

Answer 4

--1 当前缓存中最耗费CPU
select top 50 
sum(qs.total_worker_time) as total_cpu_time, 
sum(qs.execution_count) as total_execution_count,
count(*) as  number_of_statements, 
qs.plan_handle  
from sys.dm_exec_query_stats qs
group by qs.plan_handle
order by sum(qs.total_worker_time) desc
  --可以查看sql
select top 50 
sum(qs.total_worker_time) as total_cpu_time, 
sum(qs.execution_count) as total_execution_count,
count(*) as  number_of_statements, 
q.text 
from sys.dm_exec_query_stats qs 
CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS p
CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS q
group by  q.text 
order by sum(qs.total_worker_time) desc

Answer 5

--哪个查询累计使用CPU时间最多，也可以查询sys.dm_exec_cached_plans并通过使用过滤器查找可疑的类似于‘%Hash Match%’, ‘%Sort%’这样CPU敏感的各种操作
select 
highest_cpu_queries.plan_handle, 
highest_cpu_queries.total_worker_time,
q.dbid,
q.objectid,
q.number,
q.encrypted,
q.[text]
from 
(select top 50 
qs.plan_handle, 
qs.total_worker_time
from sys.dm_exec_query_stats qs
order by qs.total_worker_time desc) as highest_cpu_queries
cross apply sys.dm_exec_sql_text(plan_handle) as q
order by highest_cpu_queries.total_worker_time desc

Answer 6

哪一组TSQL或过程占用了最多的CPU时间:
SELECT TOP 50 sum(qs.total_worker_time)/1000 AS total_cpu_time,
              sum(qs.execution_count)/1000 AS total_execution_count,
              count(*) AS number_of_statements,
              qs.plan_handle
FROM sys.dm_exec_query_stats qs
GROUP BY qs.plan_handle
ORDER BY sum(qs.total_worker_time) DESC

Answer 7

--谁阻塞了我
SELECT blocked_query.session_id AS blocked_session_id,
       blocking_query.session_id AS blocking_session_id,
       sql_text.text AS blocking_text,
       waits.wait_type AS blocking_resource
FROM sys.dm_exec_requests AS blocked_query
INNER JOIN
     sys.dm_exec_requests AS blocking_query
ON blocked_query.blocking_session_id=blocking_query.session_id
CROSS APPLY
(SELECT * FROM sys.dm_exec_sql_text(blocking_query.sql_handle)) AS sql_text
INNER JOIN
sys.dm_os_waiting_tasks AS waits
ON waits.session_id=blocking_query.session_id

Answer 8

Thank you very much, it looks good, I will take time to look into these statements.Another question, are there same functions in sql2000?

Answer 9

Thank you zefuzhang2008, it's very close to my issue...