为什么 SSRS 所基于的存储过程在几秒内返回结果时会报告超时?
我有一个报告,它呈现从存储过程返回的数据。 使用探查器,我可以从报告服务捕获对存储过程的调用。
报告失败,说明报告超时,但我可以从 SSMS 执行存储过程,它会在五到六秒内返回数据。
请注意,在示例测试运行中,仅将两行返回到报表进行呈现,但在存储过程中,它可能已经处理了数千甚至数百万条记录,以便整理传回报告服务的结果。
我知道存储过程可以进一步优化,但我不明白为什么 SSRS 会超时,而从 SSMS 执行似乎只需要几秒钟。
另外一个问题也浮出水面。 如果我重新创建存储过程,报告将再次开始完美呈现。 这很好,只是在很短的时间之后,报告再次开始超时。
超时的返回似乎与添加到运行报告的主表中的新数据有关。 在我测试的示例中,仅插入一百条新记录就足以搞砸报告。
我更正确地认为,根本原因不是报告。 这是导致从 SSRS 执行时超时的存储过程。
一旦再次超时,我迄今为止最好的解决方法是重新创建存储过程。 这似乎不是一个理想的解决方案。
该问题似乎也只发生在我们的生产环境中。 我们的测试和开发平台似乎没有出现同样的问题。 尽管开发和测试没有与生产相同数量的记录。
I have a report that renders data returned from a stored procedure. Using profiler I can catch the call to the stored procedure from the reporting services.
The report fails stating the report timed out yet I can execute the stored procedure from SSMS and it returns the data back in five to six seconds.
Note, in the example test run only two rows are returned to the report for rendering though within the stored procedure it may have been working over thousands or even millions of records in order to collate the result passed back to reporting services.
I know the stored procedure could be optimised more but I do not understand why SSRS would be timing out when the execution only seems to take a few seconds to execute from SSMS.
Also another issue has surfaced. If I recreate the stored procedure, the report starts to render perfectly fine again. That is fine except after a short period of time, the report starts timing out again.
The return of the time out seems to be related to new data being added into the main table the report is running against. In the example I was testing, just one hundred new records being inserted was enough to screw up the report.
I imagine more correctly its not the report that is the root cause. It is the stored procedure that is causing the time out when executed from SSRS.
Once it is timeing out again, I best fix I have so far is to recreate the stored procedure. This doesn't seem to be an ideal solution.
The problem also only seems to be occuring on our production environment. Our test and development platforms do not seem to be exhibiting the same problem. Though dev and test do not have the same volume of records as production.
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(4)
正如您所描述的,问题似乎来自存储过程中某些部分的执行计划的变化。 查看所使用的表上保留了哪些统计信息以及添加新行如何影响它们。
The problem, as you described it, seems to come from variations on the execution plan of some parts in your stored procedure. Look at what statistics are kept on the tables used and how adding new rows affect them.
我也遇到过这样的问题:SPROC 需要几秒钟才能运行,而 SSRS 只是超时。
根据我自己的经验,我发现有几种不同的方法可以解决这个问题。
示例:
...这意味着当 SSRS 执行 SPORC 时,它只会查看查询中的前几行以查找传递的参数,而不是整个查询。 这大大减少了 SSRS 中的执行时间。
DECLARE @MyTable AS TABLE
),那么在生成报告时,这些临时表对服务器来说非常密集(就内存而言)。 通过使用哈希临时表(SELECT MyCol1, MyCol2 INTO #MyTable
),SQL Server 会将临时表存储在服务器上的 TempDB 中,而不是存储在系统内存中,从而降低报告生成的强度。I have also had this issue where the SPROC takes seconds to run yet SSRS simply times out.
I have found from my own experience that there are a couple of different methods to overcome this issue.
Example:
...this means that when your SPORC is executed by SSRS it is only looking at the first few rows in your query for the passed parameters rather than the whole of your query. Which cuts down execution time considerably in SSRS.
DECLARE @MyTable AS TABLE
), these are really intensive on the server (In terms of memory) when generating reports. By using hash temp tables (SELECT MyCol1, MyCol2 INTO #MyTable
) instead, SQL Server will store your temp tables in TempDB on the server rather than in system memeory, making the report generation less intensive.有时将WITH RECOMPILE选项添加到存储过程的CREATE语句会有所帮助。
当过程探索的记录数量发生变化而导致原始执行计划不是最佳的情况下,这非常有效。
sometime adding WITH RECOMPILE option to the CREATE statement of stored procedure helps.
This is effective in situations when the number of records explored by the procedure changes in the way that the original execution plan is not optimal.
基本上到目前为止我所做的只是进一步优化存储过程,它似乎至少暂时解决了问题。
我仍然想知道从 SSMS 和 SSRS 调用存储过程有什么区别。
Basically all I've done so far was to optimise the sproc a bit more and it seems to at least temporarily solve the problem.
I would still like to know what the difference is between calling the sproc from SSMS and SSRS.