海阔天高

暂无签名

  • 博客访问: 999543
  • 博文数量: 280
  • 用 户 组: 普通用户
  • 注册时间: 2009-04-22 00:56
个人简介

暂无介绍

ITPUB论坛APP

ITPUB论坛APP



APP发帖 享双倍积分

文章分类

全部博文(280)

文章存档

2016年(1)

2013年(11)

2012年(37)

2011年(25)

2010年(44)

2009年(31)

2008年(15)

2007年(17)

2006年(47)

2005年(49)

2004年(2)

2000年(1)

我的朋友
微信关注

IT168企业级官微



微信号:IT168qiye



系统架构师大会



微信号:SACC2013

订阅
热词专题

分类: 数据库开发技术

某存储过程调用频繁,绝大部分调用在1秒内结束。在每30分钟内,有30到40次调用超过10秒。从业务上我们知道这个过程调用很频繁,但到是什么程度,我们还不清楚。另外,从执行计划上来看,已经最优,没有优化SQL的空间了。

下面是在这种情况下的调优过程。
[@more@]

1. 系统检测

1)取系统等待并且阻塞的进程

SET NOCOUNT ON

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

GO

CREATE TABLE #block(

[current_time] [datetime] NOT NULL,

[resource_type] [nvarchar](60) NOT NULL,

[requested_object_name] [nvarchar](128) NULL,

[request_mode] [nvarchar](60) NOT NULL,

[request_status] [nvarchar](60) NOT NULL,

[resource_description] [nvarchar](2048) NULL,

[wait_duration_ms] [bigint] NULL,

[wait_type] [nvarchar](60) NULL,

[blocked_session_id] [smallint] NULL,

[blocked_start] [datetime] NOT NULL,

[blocked_command] [nvarchar](max) NULL,

[blocked_currentstat] [nvarchar](max) NULL,

[blocking_session_id] [smallint] NULL,

[blocking_start] [datetime] NULL,

[blocking_command] [nvarchar](max) NULL,

[blocking_currentstat] [nvarchar](max) NULL,

[blocking_wait_type] [nvarchar](60) NULL,

[blocking_wait_resource] [nvarchar](256) NULL,

[blocking_blocking] [smallint] NULL,

[blocking_wait_time] [int] NULL,

[open_transaction_count] [int] NULL,

[open_resultset_count] [int] NULL

)

GO

INSERT INTO #block

SELECT

GETDATE() AS [current_time]

,dtl.resource_type

,CASE

WHEN dtl.resource_type IN ('DATABASE', 'FILE', 'METADATA') THEN dtl.resource_type

WHEN dtl.resource_type = 'OBJECT' THEN OBJECT_NAME(dtl.resource_associated_entity_id)

WHEN dtl.resource_type IN ('KEY', 'PAGE', 'RID') THEN (

SELECT OBJECT_NAME([object_id])

FROM sys.partitions

WHERE sys.partitions.hobt_id = dtl.resource_associated_entity_id

)

ELSE 'Unidentified'

END AS requested_object_name

,dtl.request_mode

,dtl.request_status

,dowt.resource_description

,dowt.wait_duration_ms

,dowt.wait_type

,dowt.session_id AS [blocked_session_id]

,sp_blocked.start_time AS [blocked_start]

,desc_blocked.[text] AS [blocked_command]

,SUBSTRING(desc_blocked.[text], sp_blocked.statement_start_offset / 2,

( CASE WHEN sp_blocked.statement_end_offset = -1

THEN LEN(CONVERT(NVARCHAR(MAX), desc_blocked.[text])) * 2

ELSE sp_blocked.statement_end_offset

END - sp_blocked.statement_start_offset ) / 2) AS [blocked_currentstat]

,dowt.blocking_session_id

,sp_blocking.start_time AS [blocking_start]

,desc_blocking.[text] AS [blocking_command]

,SUBSTRING(desc_blocking.[text], sp_blocking.statement_start_offset / 2,

( CASE WHEN sp_blocking.statement_end_offset = -1

THEN LEN(CONVERT(NVARCHAR(MAX), desc_blocking.[text])) * 2

ELSE sp_blocking.statement_end_offset

END - sp_blocking.statement_start_offset ) / 2) AS [blocking_currentstat]

,sp_blocking.wait_type [blocking_wait_type]

,sp_blocking.wait_resource AS [blocking_wait_resource]

,sp_blocking.blocking_session_id AS [blocking_blocking]

,sp_blocking.wait_time AS [blocking_wait_time]

,sp_blocking.open_transaction_count

,sp_blocking.open_resultset_count

FROM sys.dm_tran_locks dtl

INNER JOIN sys.dm_os_waiting_tasks dowt

ON dtl.lock_owner_address = dowt.resource_address

INNER JOIN sys.dm_exec_requests sp_blocked

ON dowt.[session_id] = sp_blocked.session_id

LEFT JOIN sys.dm_exec_requests sp_blocking

ON dowt.[blocking_session_id] = sp_blocking.session_id

CROSS APPLY sys.[dm_exec_sql_text](sp_blocked.[sql_handle]) AS desc_blocked

OUTER APPLY sys.[dm_exec_sql_text](sp_blocking.[sql_handle]) AS desc_blocking

--WHERE dtl.[resource_database_id] = DB_ID()

IF @@ROWCOUNT > 0

SELECT * FROM #block

WAITFOR DELAY '00:00:02'

GO 1000

2)监控性能计数器,查看是否有过长的队列或日志更新

Disk

Displays

Avg. Disk Queue Length

Avg. Disk sec/Transfer

SQLServer: Database object counter

Displays

Log Bytes Per Flush

Number of bytes in the log buffer when the buffer is flushed.

Log Flushes/sec

Number of log flushes per second.

Log Flush Wait Time

Total wait time (milliseconds) to flush the log.

Log Flush Waits/sec

Number of commits per second that are waiting on log flush.

3)查是否有挂起的IO请求

select

db_name(i.database_id) as db,

m.name,

m.physical_name,

i.io_stall as [user_waittime_for_io(ms)],

p.io_type,

p.io_pending_ms_ticks as [waittime_in_pending_queue]

from sys.dm_io_virtual_file_stats(NULL, NULL) as i

inner join sys.dm_io_pending_io_requests as p

on i.file_handle = p.io_handle

inner join sys.master_files as m

on m.database_id = i.database_id

and m.file_id = i.file_id

4)查看前15条调用最频繁的存储过程,或读写最多的存储过程

SELECT TOP 15 a.object_id, a.database_id, OBJECT_NAME(object_id, database_id) 'proc name',

a.cached_time, a.last_execution_time, a.total_elapsed_time,

a.total_elapsed_time/a.execution_count AS [avg_elapsed_time],

a.execution_count,

a.execution_count/(datediff(ss, sqlserver_start_time, last_execution_time) ) as execution_count_per_second,

a.total_physical_reads/a.execution_count avg_physical_reads,

a.total_logical_writes,

a.total_logical_writes/ a.execution_count avg_logical_reads,

a.last_elapsed_time,

a.total_elapsed_time / a.execution_count avg_elapsed_time,

b.text,c.query_plan

FROM sys.dm_exec_procedure_stats AS a

CROSS APPLY sys.dm_exec_sql_text(a.sql_handle) as b

CROSS APPLY sys.dm_exec_query_plan(a.plan_handle) as c

cross join sys.dm_os_sys_info as d

ORDER BY [total_elapsed_time] DESC;

GO

2. 检测结果

1)可以看到阻塞的源头是WRITELOG

2)但windows的性能计数器磁盘队列小于1,日志更新的计数器也无特别异常;

3)系统没有挂起的IO请求;

4)改存储过程调用频繁,每秒12次调用;用profiler可看到重复调用现象;

3. 调优方案

1)报告应用端有重复调用现象;应用端修改重复调用;

2)相关存储过程可以使用nolock提示减低隔离级别;

3)至此,性能应该有很大提高。但调用逻辑还可以进一步优化。如果性能再次成为问题的话,优化调用逻辑;

阅读(26759) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~
评论热议
请登录后评论。

登录 注册