590
社区成员
发帖
与我相关
我的任务
分享SSRS为什么生成4万条数据的报表会Down掉？
普通的报表，非交叉报表。数据库查询返回所有数据10秒不到。
在SQL Server Business Intelligence Development Studio上测试，大概90秒左右。
部署到服务器后就怎样都不能成功生成报表了，查看内存涨到2GB，之后一直没有响应。最后超时，Down了。
环境是： Windows 2008 + SSRS 2008,32位的。SSRS2008的设置都是安装时的默认设置。
硬件是: Xeon 5530 * 2,8GB,单320GB Sata。
请问谁有做过SSRS方面的压力测试？
是否SSRS的配置有问题？需要修改什么选项？
...全文
请发表友善的回复…
发表回复
十林 2012-09-25
- 打赏
- 举报
楼主也许可以看看数据源的设置。 看看BIDS中的数据源设置是否和报表管理器上的是否一致。
两者用的数据驱动程序以及程序版本是否一致。
两者用的数据驱动程序以及程序版本是否一致。
lg314 2012-01-27
- 打赏
- 举报
可能是执行计划问题，在语句上加hint看看，
或者直接写一个存储过程
还有可能是ssrs的参数造成的执行计划问题，数据表上有没有用char varchar之类的非unicode字符串？
ssrs默认把text类型转换成nvarchar.
xx有索引，而且xx是 char varchar的话，这个有些情况下查询不会用到索引，因为??是nvarchar，执行计划会把表的xx列先隐式转换成nchar nvarchar，然后再进行匹配，所以可能会造成扫表。
解决方法是
select * from table
xx=convert(varchar(1024),??)
还有一些特殊情况，实在没办法就用存储过程，或者再做一个计算结果表
select * from table
where xx=??
option(hash join)或者直接写一个存储过程
还有可能是ssrs的参数造成的执行计划问题，数据表上有没有用char varchar之类的非unicode字符串？
ssrs默认把text类型转换成nvarchar.
select * from table
where xx=??xx有索引，而且xx是 char varchar的话，这个有些情况下查询不会用到索引，因为??是nvarchar，执行计划会把表的xx列先隐式转换成nchar nvarchar，然后再进行匹配，所以可能会造成扫表。
解决方法是
select * from table
xx=convert(varchar(1024),??)
还有一些特殊情况，实在没办法就用存储过程，或者再做一个计算结果表
代码蜗牛sky 2012-01-13
- 打赏
- 举报
没有人知道解决方案吗？
代码蜗牛sky 2012-01-12
- 打赏
- 举报
[Quote=引用 4 楼 stublue 的回复:]
可以在 SSRS 中设置查询超时时间。数据集的属性里。
设置为0，试试。
[/Quote]
如果是指数据库查询超时的话，我已经试过了。没有效果，而且数据库查询也就10秒左右。
可以在 SSRS 中设置查询超时时间。数据集的属性里。
设置为0，试试。
[/Quote]
如果是指数据库查询超时的话，我已经试过了。没有效果，而且数据库查询也就10秒左右。
代码蜗牛sky 2012-01-12
- 打赏
- 举报
数据量少的时候没有问题，测试1万条是可以的。而且我在开发环境，4万条是完全没有问题的。
叶子 2012-01-11
- 打赏
- 举报
把你的4万数据，换成1万数据试试，情况是否依旧。
Leon_He2014 2012-01-11
- 打赏
- 举报
可以在 SSRS 中设置查询超时时间。数据集的属性里。
设置为0，试试。
设置为0，试试。
代码蜗牛sky 2012-01-11
- 打赏
- 举报
ui!ReportManager_0-99!56f0!01/11/2012-13:23:21:: v VERBOSE: Read Permission: Update System Security Policies
extensionfactory!ReportServer_0-98!1dec!01/11/2012-13:23:21:: e ERROR: Exception caught instantiating TERADATA report server extension: System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> System.IO.FileNotFoundException: Could not load file or assembly 'Teradata.Client.Provider, Version=12.0.0.0, Culture=neutral, PublicKeyToken=76b417ee2e04956c' or one of its dependencies. 系统找不到指定的文件。
File name: 'Teradata.Client.Provider, Version=12.0.0.0, Culture=neutral, PublicKeyToken=76b417ee2e04956c'
at System.Reflection.Assembly._nLoad(AssemblyName fileName, String codeBase, Evidence assemblySecurity, Assembly locationHint, StackCrawlMark& stackMark, Boolean throwOnFileNotFound, Boolean forIntrospection)
at System.Reflection.Assembly.nLoad(AssemblyName fileName, String codeBase, Evidence assemblySecurity, Assembly locationHint, StackCrawlMark& stackMark, Boolean throwOnFileNotFound, Boolean forIntrospection)
at System.Reflection.Assembly.InternalLoad(AssemblyName assemblyRef, Evidence assemblySecurity, StackCrawlMark& stackMark, Boolean forIntrospection)
at System.Reflection.Assembly.InternalLoad(String assemblyString, Evidence assemblySecurity, StackCrawlMark& stackMark, Boolean forIntrospection)
at System.Reflection.Assembly.Load(String assemblyString)
at Microsoft.ReportingServices.DataExtensions.TeradataConnectionWrapper.LoadTdAssembly()
at Microsoft.ReportingServices.DataExtensions.TeradataConnectionWrapper.GetTdConnectionType()
at Microsoft.ReportingServices.DataExtensions.TeradataConnectionWrapper.CreateTdConnectionObject()
at Microsoft.ReportingServices.DataExtensions.TeradataConnectionWrapper..ctor()
警告: 程序集绑定日志记录被关闭。
要启用程序集绑定失败日志记录，请将注册表值 [HKLM\Software\Microsoft\Fusion!EnableLog] (DWORD)设置为 1。
注意: 会有一些与程序集绑定失败日志记录关联的性能损失。
要关闭此功能，请移除注册表值 [HKLM\Software\Microsoft\Fusion!EnableLog]。
--- End of inner exception stack trace ---
at System.RuntimeTypeHandle.CreateInstance(RuntimeType type, Boolean publicOnly, Boolean noCheck, Boolean& canBeCached, RuntimeMethodHandle& ctor, Boolean& bNeedSecurityCheck)
at System.RuntimeType.CreateInstanceSlow(Boolean publicOnly, Boolean fillCache)
at System.RuntimeType.CreateInstanceImpl(Boolean publicOnly, Boolean skipVisibilityChecks, Boolean fillCache)
at System.Activator.CreateInstance(Type type, Boolean nonPublic)
at System.RuntimeType.CreateInstanceImpl(BindingFlags bindingAttr, Binder binder, Object[] args, CultureInfo culture, Object[] activationAttributes)
at System.Activator.CreateInstance(Type type, BindingFlags bindingAttr, Binder binder, Object[] args, CultureInfo culture, Object[] activationAttributes)
at System.Reflection.Assembly.CreateInstance(String typeName, Boolean ignoreCase, BindingFlags bindingAttr, Binder binder, Object[] args, CultureInfo culture, Object[] activationAttributes)
at Microsoft.ReportingServices.Diagnostics.ExtensionClassFactory.<>c__DisplayClass2.<CreateExtensionObject>b__0()
at Microsoft.ReportingServices.Diagnostics.RevertImpersonationContext.<>c__DisplayClass1.<Run>b__0(Object state)
at System.Security.SecurityContext.runTryCode(Object userData)
at System.Runtime.CompilerServices.RuntimeHelpers.ExecuteCodeWithGuaranteedCleanup(TryCode code, CleanupCode backoutCode, Object userData)
at System.Security.SecurityContext.RunInternal(SecurityContext securityContext, ContextCallback callBack, Object state)
at System.Security.SecurityContext.Run(SecurityContext securityContext, ContextCallback callback, Object state)
at Microsoft.ReportingServices.Diagnostics.RevertImpersonationContext.Run(ContextBody callback)
at Microsoft.ReportingServices.Diagnostics.ExtensionClassFactory.CreateExtensionObject(Extension extConfig, Boolean typeOnly).
library!ReportServer_0-98!5988!01/11/2012-13:23:22:: Call to GetSystemPropertiesAction().
library!ReportServer_0-98!1dec!01/11/2012-13:23:22:: Call to GetDataSourceContentsAction(/数据源/RsCompositiveBusiness).
library!ReportServer_0-98!5988!01/11/2012-13:23:22:: Call to GetPropertiesAction(/数据源/RsCompositiveBusiness, PathBased).
library!ReportServer_0-98!1dec!01/11/2012-13:23:22:: Call to ListChildrenAction(/, False).
library!ReportServer_0-98!db20!01/11/2012-13:23:22:: Call to ListChildrenAction(/数据源, False).
library!ReportServer_0-98!1dec!01/11/2012-13:23:22:: Call to ListChildrenAction(/, False).
library!ReportServer_0-98!db20!01/11/2012-13:23:22:: Call to ListChildrenAction(/数据源, False).
library!ReportServer_0-98!1dec!01/11/2012-13:23:22:: Call to GetSystemPropertiesAction().
library!ReportServer_0-98!66d4!01/11/2012-13:26:23:: i INFO: RenderForNewSession('/RaisingReports/CR_ContractListNoPaying')
webserver!ReportServer_0-98!66d4!01/11/2012-13:26:24:: i INFO: Processed report. Report='/RaisingReports/CR_ContractListNoPaying', Stream=''
library!WindowsService_141!c974!01/11/2012-13:26:29:: i INFO: Call to CleanBatch()
extensionfactory!ReportServer_0-98!1dec!01/11/2012-13:23:21:: e ERROR: Exception caught instantiating TERADATA report server extension: System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> System.IO.FileNotFoundException: Could not load file or assembly 'Teradata.Client.Provider, Version=12.0.0.0, Culture=neutral, PublicKeyToken=76b417ee2e04956c' or one of its dependencies. 系统找不到指定的文件。
File name: 'Teradata.Client.Provider, Version=12.0.0.0, Culture=neutral, PublicKeyToken=76b417ee2e04956c'
at System.Reflection.Assembly._nLoad(AssemblyName fileName, String codeBase, Evidence assemblySecurity, Assembly locationHint, StackCrawlMark& stackMark, Boolean throwOnFileNotFound, Boolean forIntrospection)
at System.Reflection.Assembly.nLoad(AssemblyName fileName, String codeBase, Evidence assemblySecurity, Assembly locationHint, StackCrawlMark& stackMark, Boolean throwOnFileNotFound, Boolean forIntrospection)
at System.Reflection.Assembly.InternalLoad(AssemblyName assemblyRef, Evidence assemblySecurity, StackCrawlMark& stackMark, Boolean forIntrospection)
at System.Reflection.Assembly.InternalLoad(String assemblyString, Evidence assemblySecurity, StackCrawlMark& stackMark, Boolean forIntrospection)
at System.Reflection.Assembly.Load(String assemblyString)
at Microsoft.ReportingServices.DataExtensions.TeradataConnectionWrapper.LoadTdAssembly()
at Microsoft.ReportingServices.DataExtensions.TeradataConnectionWrapper.GetTdConnectionType()
at Microsoft.ReportingServices.DataExtensions.TeradataConnectionWrapper.CreateTdConnectionObject()
at Microsoft.ReportingServices.DataExtensions.TeradataConnectionWrapper..ctor()
警告: 程序集绑定日志记录被关闭。
要启用程序集绑定失败日志记录，请将注册表值 [HKLM\Software\Microsoft\Fusion!EnableLog] (DWORD)设置为 1。
注意: 会有一些与程序集绑定失败日志记录关联的性能损失。
要关闭此功能，请移除注册表值 [HKLM\Software\Microsoft\Fusion!EnableLog]。
--- End of inner exception stack trace ---
at System.RuntimeTypeHandle.CreateInstance(RuntimeType type, Boolean publicOnly, Boolean noCheck, Boolean& canBeCached, RuntimeMethodHandle& ctor, Boolean& bNeedSecurityCheck)
at System.RuntimeType.CreateInstanceSlow(Boolean publicOnly, Boolean fillCache)
at System.RuntimeType.CreateInstanceImpl(Boolean publicOnly, Boolean skipVisibilityChecks, Boolean fillCache)
at System.Activator.CreateInstance(Type type, Boolean nonPublic)
at System.RuntimeType.CreateInstanceImpl(BindingFlags bindingAttr, Binder binder, Object[] args, CultureInfo culture, Object[] activationAttributes)
at System.Activator.CreateInstance(Type type, BindingFlags bindingAttr, Binder binder, Object[] args, CultureInfo culture, Object[] activationAttributes)
at System.Reflection.Assembly.CreateInstance(String typeName, Boolean ignoreCase, BindingFlags bindingAttr, Binder binder, Object[] args, CultureInfo culture, Object[] activationAttributes)
at Microsoft.ReportingServices.Diagnostics.ExtensionClassFactory.<>c__DisplayClass2.<CreateExtensionObject>b__0()
at Microsoft.ReportingServices.Diagnostics.RevertImpersonationContext.<>c__DisplayClass1.<Run>b__0(Object state)
at System.Security.SecurityContext.runTryCode(Object userData)
at System.Runtime.CompilerServices.RuntimeHelpers.ExecuteCodeWithGuaranteedCleanup(TryCode code, CleanupCode backoutCode, Object userData)
at System.Security.SecurityContext.RunInternal(SecurityContext securityContext, ContextCallback callBack, Object state)
at System.Security.SecurityContext.Run(SecurityContext securityContext, ContextCallback callback, Object state)
at Microsoft.ReportingServices.Diagnostics.RevertImpersonationContext.Run(ContextBody callback)
at Microsoft.ReportingServices.Diagnostics.ExtensionClassFactory.CreateExtensionObject(Extension extConfig, Boolean typeOnly).
library!ReportServer_0-98!5988!01/11/2012-13:23:22:: Call to GetSystemPropertiesAction().
library!ReportServer_0-98!1dec!01/11/2012-13:23:22:: Call to GetDataSourceContentsAction(/数据源/RsCompositiveBusiness).
library!ReportServer_0-98!5988!01/11/2012-13:23:22:: Call to GetPropertiesAction(/数据源/RsCompositiveBusiness, PathBased).
library!ReportServer_0-98!1dec!01/11/2012-13:23:22:: Call to ListChildrenAction(/, False).
library!ReportServer_0-98!db20!01/11/2012-13:23:22:: Call to ListChildrenAction(/数据源, False).
library!ReportServer_0-98!1dec!01/11/2012-13:23:22:: Call to ListChildrenAction(/, False).
library!ReportServer_0-98!db20!01/11/2012-13:23:22:: Call to ListChildrenAction(/数据源, False).
library!ReportServer_0-98!1dec!01/11/2012-13:23:22:: Call to GetSystemPropertiesAction().
library!ReportServer_0-98!66d4!01/11/2012-13:26:23:: i INFO: RenderForNewSession('/RaisingReports/CR_ContractListNoPaying')
webserver!ReportServer_0-98!66d4!01/11/2012-13:26:24:: i INFO: Processed report. Report='/RaisingReports/CR_ContractListNoPaying', Stream=''
library!WindowsService_141!c974!01/11/2012-13:26:29:: i INFO: Call to CleanBatch()
代码蜗牛sky 2012-01-11
- 打赏
- 举报
rsreportserver.config的部分配置
<Service>
<IsSchedulingService>True</IsSchedulingService>
<IsNotificationService>True</IsNotificationService>
<IsEventService>True</IsEventService>
<PollingInterval>10</PollingInterval>
<WindowsServiceUseFileShareStorage>False</WindowsServiceUseFileShareStorage>
<MemoryLimit>60</MemoryLimit>
<RecycleTime>720</RecycleTime>
<MaximumMemoryLimit>80</MaximumMemoryLimit>
<MaxAppDomainUnloadTime>30</MaxAppDomainUnloadTime>
<MaxQueueThreads>0</MaxQueueThreads>
<UrlRoot>http://databaseserver/reportserver</UrlRoot>
<UnattendedExecutionAccount>
<UserName></UserName>
<Password></Password>
<Domain></Domain>
</UnattendedExecutionAccount>
<PolicyLevel>rssrvpolicy.config</PolicyLevel>
<WebServiceAccount>NT Authority\NetworkService</WebServiceAccount>
<IsWebServiceEnabled>True</IsWebServiceEnabled>
<FileShareStorageLocation>
<Path></Path>
</FileShareStorageLocation>
</Service>
<Service>
<IsSchedulingService>True</IsSchedulingService>
<IsNotificationService>True</IsNotificationService>
<IsEventService>True</IsEventService>
<PollingInterval>10</PollingInterval>
<WindowsServiceUseFileShareStorage>False</WindowsServiceUseFileShareStorage>
<MemoryLimit>60</MemoryLimit>
<RecycleTime>720</RecycleTime>
<MaximumMemoryLimit>80</MaximumMemoryLimit>
<MaxAppDomainUnloadTime>30</MaxAppDomainUnloadTime>
<MaxQueueThreads>0</MaxQueueThreads>
<UrlRoot>http://databaseserver/reportserver</UrlRoot>
<UnattendedExecutionAccount>
<UserName></UserName>
<Password></Password>
<Domain></Domain>
</UnattendedExecutionAccount>
<PolicyLevel>rssrvpolicy.config</PolicyLevel>
<WebServiceAccount>NT Authority\NetworkService</WebServiceAccount>
<IsWebServiceEnabled>True</IsWebServiceEnabled>
<FileShareStorageLocation>
<Path></Path>
</FileShareStorageLocation>
</Service>
勿勿 2012-01-11
- 打赏
- 举报
关注我也遇到这样的问题。有时候会超时！
