偶发的存储过程调用性能问题的一次调优过程

某存储过程调用频繁，绝大部分调用在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）至此，性能应该有很大提高。但调用逻辑还可以进一步优化。如果性能再次成为问题的话，优化调用逻辑；

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/66009/viewspace-1060731/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/66009/viewspace-1060731/