更新:对不起!由于旧描述的影响,我可能误导了您。问题在迁移后并没有立即存在,而是在迁移后一周开始出现。
我们最近将数据库和报表服务器迁移到了一个新的数据库服务器和一个新的报表服务器。
之前的配置:
- 数据库服务器:2008 Enterprise,DB01 / NamedInstance
- 报表服务器:与数据库服务器相同,本地模式,数据库凭据为NT AUTHORITY \ NETWORK SERVICE
现在的配置:
- 数据库服务器:2012 Enterprise,DC01(默认实例,非命名实例)
- 报表服务:移动到RP01(本地模式),数据库 凭据为SQL Account(sa)
迁移遵循MSDN迁移说明,并最终成功(尽管我们必须手动删除多余的规模部署服务器(与旧服务器名称相同),以使其正常工作,我认为这是SSRS错误)。
迁移后1周,报告在新报表服务器上开始运行得非常缓慢。
因此,我进行了以下分析:
在旧报表服务器(报表的数据库连接指向新数据库服务器)和新报表服务器中执行报表,旧报表服务器像以前一样快速运行(1秒),但新报表服务器运行非常缓慢(31秒)。
直接执行报表调用的存储过程,与以前一样非常快(50毫秒)。
诊断[ReportServer $ Instance]。[dbo]。[ExecutionLog]数据库,TimeDataRetrival在旧服务器中为50毫秒,在新服务器中为30050毫秒。
运行SQL Server Profiler,在旧服务器中执行报表,一切似乎正常。在新服务器上执行报表后,某些事件引起了我的注意。在每个批次的最后一个事件之后,它会“挂起”(运行)很长时间,然后生成“Audit Logout”。下面的示例实际上运行了10秒,但所有语句实际上都运行了不到1秒。
我怀疑:a)。已更改某些配置,例如帐户访问,而没有得到我的确认。 b)。新的报表服务器正在尝试验证没有适当访问权限的用户,并在那里“挂起”几秒钟,然后提供替代方案。
开始分析器输出:
Audit Login-网络协议:TCP / IP 设置quoted_identifier on 设置arithabort off 设置numeric_roundabort off 设置ansi_warnings on 设置ansi_padding on 设置ansi_nulls on 设置concat_null_yields_null on 设置cursor_close_on_commit off 设置implicit_transactions off set language us_english set dateformat mdy set datefirst 7 设置事务隔离级别为读取提交
报告服务器 sa 1440 100 2013年4月16日 16:10:14.393 0X2000002838F4010000000000
SQL:BatchStarting
声明@BatchID唯一标识符
set @BatchID = NEWID()
UPDATE [Event] WITH (TABLOCKX)
SET [BatchID] = @BatchID,
[ProcessStart] = GETUTCDATE(),
[ProcessHeartbeat] = GETUTCDATE()
FROM (
SELECT TOP 8 [EventID] FROM [Event] WITH (TABLOCKX) WHERE [ProcessStart] is NULL ORDER BY [TimeEntered]
) AS t1
WHERE [Event].[EventID] = t1.[EventID]
select top 8
E.[EventID],
E.[EventType],
E.[EventData]
from
[Event] E WITH (TABLOCKX)
where
[BatchID] = @BatchID
ORDER BY [TimeEntered]
报告服务器 sa 1440 100 2013年4月16日 16:10:14.393
SQL:BatchCompleted
声明 @BatchID 唯一标识符
set @BatchID = NEWID()
UPDATE [Event] WITH (TABLOCKX)
SET [BatchID] = @BatchID,
[ProcessStart] = GETUTCDATE(),
[ProcessHeartbeat] = GETUTCDATE()
FROM (
SELECT TOP 8 [EventID] FROM [Event] WITH (TABLOCKX) WHERE [ProcessStart] is NULL ORDER BY [TimeEntered]
) AS t1
WHERE [Event].[EventID] = t1.[EventID]
select top 8
E.[EventID],
E.[EventType],
E.[EventData]
from
[Event] E WITH (TABLOCKX)
where
[BatchID] = @BatchID
ORDER BY [TimeEntered]
Report Server sa 0 7 0 0 1440 100 2013-04-16 16:10:14.393 2013-04-16 16:10:14.393
SQL: 批处理开始
声明 @BatchID 为唯一标识符
set @BatchID = newid()
UPDATE [Notifications] WITH (TABLOCKX)
SET [BatchID] = @BatchID,
[ProcessStart] = GETUTCDATE(),
[ProcessHeartbeat] = GETUTCDATE()
FROM (
SELECT TOP 8 [NotificationID] FROM [Notifications] WITH (TABLOCKX) WHERE ProcessStart is NULL and
(ProcessAfter is NULL or ProcessAfter < GETUTCDATE()) ORDER BY [NotificationEntered]
) AS t1
WHERE [Notifications].[NotificationID] = t1.[NotificationID]
select top 8
-- Notification data
N.[NotificationID],
N.[SubscriptionID],
N.[ActivationID],
N.[ReportID],
N.[SnapShotDate],
N.[DeliveryExtension],
N.[ExtensionSettings],
N.[Locale],
N.[Parameters],
N.[SubscriptionLastRunTime],
N.[ProcessStart],
N.[NotificationEntered],
N.[Attempt],
N.[IsDataDriven],
SUSER_SNAME(Owner.[Sid]),
Owner.[UserName],
-- Report Data
O.[Path],
N.[ReportZone],
O.[Type],
SD.NtSecDescPrimary,
N.[Version],
Owner.[AuthType]
from
[Notifications] N with (TABLOCKX) inner join [Catalog] O on O.[ItemID] = N.[ReportID]
inner join [Users] Owner on N.SubscriptionOwnerID = Owner.UserID
left outer join [SecData] SD on O.[PolicyID] = SD.[PolicyID] AND SD.AuthType = Owner.AuthType
where
N.[BatchID] = @BatchID
ORDER BY [NotificationEntered]
报告服务器 sa 1440 100 2013-04-16 16:10:14.393
SQL:批处理完成
声明@BatchID唯一标识符
set @BatchID = newid()
UPDATE [Notifications] WITH (TABLOCKX)
SET [BatchID] = @BatchID,
[ProcessStart] = GETUTCDATE(),
[ProcessHeartbeat] = GETUTCDATE()
FROM (
SELECT TOP 8 [NotificationID] FROM [Notifications] WITH (TABLOCKX) WHERE ProcessStart is NULL and
(ProcessAfter is NULL or ProcessAfter < GETUTCDATE()) ORDER BY [NotificationEntered]
) AS t1
WHERE [Notifications].[NotificationID] = t1.[NotificationID]
select top 8
-- Notification data
N.[NotificationID],
N.[SubscriptionID],
N.[ActivationID],
N.[ReportID],
N.[SnapShotDate],
N.[DeliveryExtension],
N.[ExtensionSettings],
N.[Locale],
N.[Parameters],
N.[SubscriptionLastRunTime],
N.[ProcessStart],
N.[NotificationEntered],
N.[Attempt],
N.[IsDataDriven],
SUSER_SNAME(Owner.[Sid]),
Owner.[UserName],
-- Report Data
O.[Path],
N.[ReportZone],
O.[Type],
SD.NtSecDescPrimary,
N.[Version],
Owner.[AuthType]
from
[Notifications] N with (TABLOCKX) inner join [Catalog] O on O.[ItemID] = N.[ReportID]
inner join [Users] Owner on N.SubscriptionOwnerID = Owner.UserID
left outer join [SecData] SD on O.[PolicyID] = SD.[PolicyID] AND SD.AuthType = Owner.AuthType
where
N.[BatchID] = @BatchID
ORDER BY [NotificationEntered]
报告服务器 sa 0 7 0 0 1440 100 2013-04-16 16:10:14.393 2013-04-16 16:10:14.393
审核注销
报告服务器 sa 0 3836 6 10140 1440 100 2013-04-16 16:10:14.393 2013-04-16 16:10:24.533