sql server 笔记（收集）

sqlserver官网：

http://technet.microsoft.com/zh-cn/library/ms167593%28v=sql.105%29.aspx

sqlserver监控

内存

对于SQL Server占用内存资源的监控主要集中在页面吞吐能力、页面错误和可用内存上，对虚拟内存的监控，则重点在于分页文件的使用率上。

SQL Server 2008性能监控

SQL Server提供的sys.dm_os_performance_counters计数器视图，主要对缓冲区管理器和内存管理器的一些计数器进行监控，比如页面的生存周期、检查点、惰性写入器和缓冲命中率等指标。

SQL Server 2008性能监控

sys.dm_os_performance_counters视图

SELECT object_name,COUNT(DISTINCT counter_name)
FROM sys.dm_os_performance_counters
GROUP BY object_name
ORDER BY object_name;

CPU监控

查找用户会话和操作系统线程的对照关系

select STasks.session_id, SThreads.os_thread_id
from sys.dm_os_tasks AS STasks
INNER JOIN sys.dm_os_threads AS SThreads
ON STasks.worker_address = SThreads.worker_address
WHERE STasks.session_id IS NOT NULL
ORDER BY STasks.session_id;

找到最耗CPU的top50 SQL

SELECT
      total_cpu_time,
      total_execution_count,
      number_of_statements,
      s2.text
      --(SELECT SUBSTRING(s2.text, statement_start_offset / 2, ((CASE WHEN statement_end_offset = -1 THEN (LEN(CONVERT(NVARCHAR(MAX), s2.text)) * 2) ELSE statement_end_offset END) - statement_start_offset) / 2) ) AS query_text
FROM
      (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.sql_handle --,
            --MIN(statement_start_offset) AS statement_start_offset,
            --MAX(statement_end_offset) AS statement_end_offset
      FROM
            sys.dm_exec_query_stats AS qs
      GROUP BY qs.sql_handle
      ORDER BY SUM(qs.total_worker_time) DESC) AS stats
      CROSS APPLY sys.dm_exec_sql_text(stats.sql_handle) AS s2

比如有一条SQL执行效率非常差，大概30秒~80秒不等，而且占据了所有CPU消耗时间的20%，并且执行次数也很多

select f_getconcatname(id) from table where id>1 and id<100

内存监控

缓冲池中前十位消耗内存最大的内存组件

SELECT TOP (10) type, SUM(single_pages_kb) AS [SPA Mem, KB]
FROM sys.dm_os_memory_clerks
GROUP BY type
ORDER BY SUM(single_pages_kb) DESC

缓冲区外进行分配了内存的内部组件

SELECT TOP (10) type, SUM(multi_pages_kb) AS Memory_allocated_KB
FROM sys.dm_os_memory_clerks
WHERE multi_pages_kb!=0
GROUP BY type

缓冲最大的前10条SQL

SELECT objtype AS 'Cached Object Type',
COUNT(*) AS 'Number of Plans',
SUM(CAST(size_in_bytes AS BIGINT))/1024/1024 AS 'Plan Cache Size (MB)',
AVG(usecounts) AS 'Avg Use Count'
FROM sys.dm_exec_cached_plans
GROUP BY objtype;

I/O性能监控

各磁盘和数据库的吞吐量和等待时间

SELECT b.name,c.name,c.physical_name,
a.num_of_reads,a.num_of_writes,
a.num_of_bytes_read,a.num_of_bytes_written,
a.io_stall_read_ms,a.io_stall_write_ms,
a.io_stall
FROM sys.dm_io_virtual_file_stats(NULL, NULL) a,
sys.databases b,sys.master_files c
WHERE a.database_id=b.database_id
AND a.file_id=c.file_id
AND a.database_id=c.database_id

挂起的 I/O 请求和对应的数据文件

SELECT
database_id,file_id,
io_stall,io_pending_ms_ticks,
scheduler_address
FROM sys.dm_io_virtual_file_stats(NULL, NULL)t1,
sys.dm_io_pending_io_requests as t2
WHERE t1.file_handle = t2.io_handle

SELECT object_name,COUNT(DISTINCT counter_name)
FROM sys.dm_os_performance_counters
GROUP BY object_name
ORDER BY object_name

等待事件
--所有等待事件，共计484个

SELECT * FROM sys.dm_os_wait_stats

--消耗时间占据95%的等待事件

WITH Waits AS
(
SELECT wait_type,wait_time_ms/1000. AS wait_time_s,
100.*wait_time_ms/SUM(wait_time_ms) OVER() AS pct,
ROW_NUMBER() OVER(order by wait_time_ms DESC) as rn
FROM sys.dm_os_wait_stats
WHERE wait_type NOT IN ('SLEEP_TASK','BROKER_TASK_STOP',
'SQLTRACE_BUFFER_FLUSH','CLR_AUTO_EVENT',
'CLR_MANUAL_EVENT','LAZYWRITER_SLEEP')
)
SELECT W1.wait_type,
CAST(W1.wait_time_s AS DECIMAL(12,2)) AS wait_time_s,
CAST(W1.pct AS DECIMAL(12,2)) AS pct,
CAST(SUM(W2.pct) AS DECIMAL(12,2)) AS running_pct
FROM Waits AS W1
INNER JOIN Waits AS W2
ON W2.rn<=W1.rn
GROUP BY W1.rn,W1.wait_type,W1.wait_time_s,W1.pct
HAVING SUM(W2.pct)-W1.pct<95

Top N分析

SELECT TOP 10 query_stats.query_hash AS "Query Hash",
SUM(query_stats.total_worker_time) / SUM(query_stats.execution_count) AS "平均CPU时间",
SUM(query_stats.total_worker_time) AS "总CPU时间",
SUM(query_stats.execution_count) AS "执行次数",
SUM(query_stats.total_physical_reads) AS "物理读取总次数",
SUM(query_stats.total_logical_reads) AS "逻辑读取总次数",
SUM(query_stats.total_logical_writes) AS "逻辑写入总次数",
MIN(query_stats.statement_text) AS "SQL语句"
FROM
(SELECT QS.*,
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) as query_stats
GROUP BY query_stats.query_hash
ORDER BY 2 DESC;

阻塞

SELECT
blocked_query.session_id AS blocked_session_id,
blocking_query.session_id AS blocking_session_id,
blocking_sql_text.text AS blocking_sql_text,
blocked_sql_text.text AS blocked_sql_text,
waits.wait_type AS blocking_resource,
blocked_query.command AS blocked_command,
blocking_query.command AS blocking_command,
blocked_query.wait_type AS blocked_wait_type,
blocked_query.wait_time AS blocked_wait_time,
blocking_query.total_elapsed_time AS blocking_elapsed_time,
GETDATE() happen_time
FROM sys.dm_exec_requests blocked_query
JOIN sys.dm_exec_requests 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)
) blocking_sql_text
CROSS APPLY
(
SELECT *
FROM sys.dm_exec_sql_text(blocked_query.sql_handle)
) blocked_sql_text
JOIN sys.dm_os_waiting_tasks waits
ON waits.session_id = blocking_query.session_id

sp_who，sp_lock
(sysprocesses,syslockinfo)
Sysprocesses，dbcc inputbuffer
(spid,blocked)
dm_exec_requests
(Session_id,blocking_session_id,sql_handle)
dm_os_waiting_tasks
(Session_id,blocking_session_id, resource_address)
dm_tran_locks
(lock_owner_address)
dm_exec_sql_text

use Test
--创建分析死锁使用到的两个表DeadLockTest_1&DeadLockTest_2
go

Begin Tran
Update DeadLockTest_2
Set Name=N'test2'
Where ID>0

Waitfor Delay '00:00:05'

Update DeadLockTest_1
Set Name=N'test1'
Where ID>0


Commit Tran

go