blocks|key|3007|text|在运行报告时，尝试使用SQL事件探查器捕获执行计划。如果您没有任何CONVER_IMPLICIT操作符，请查看一下，例如，表/索引扫描。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3008|http://msdn.microsoft.com/en-us/library/ms190233.aspx|offset|length|3009|转换会阻止索引的使用，如果您传递的参数的类型与您比较它们的列的类型不同，则可能会发生这种情况。|3010|您可能会尝试向报表的查询添加选项(重新编译)，您的报表可能是parameter+sniffing的牺牲品。|3011|检查您的查询是否使用标量用户定义函数。它们可能是性能的真正杀手。如果您拥有它们，则可以将它们转换为table+valued+functions。|3012|entityMap|0|LINK|mutability|MUTABLE|url|1|http://www.databasejournal.com/features/mssql/article.php/3841271/T-SQL-Best-Practices--Parameter-Sniffing.htm|2|http://blog.falafel.com/2008/04/07/ImprovePerformanceOfTSQLUserDefinedFunctionsByConvertingScalarOnesToTablevaluedOnes.aspx^0|0|0|1H|0|0|0|U|I|1|0|1D|M|2|0^^$0|@$1|2|3|4|5|6|7|W|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|X|8|@]|9|@$D|Y|E|Z|1|10]]|A|$]]|$1|F|3|G|5|6|7|11|8|@]|9|@]|A|$]]|$1|H|3|I|5|6|7|12|8|@]|9|@$D|13|E|14|1|15]]|A|$]]|$1|J|3|K|5|6|7|16|8|@]|9|@$D|17|E|18|1|19]]|A|$]]|$1|L|3|-4|5|6|7|1A|8|@]|9|@]|A|$]]]|M|$N|$5|O|P|Q|A|$R|C]]|S|$5|O|P|Q|A|$R|T]]|U|$5|O|P|Q|A|$R|V]]]]

While running the report, try co catch execution plan using the SQL Profiler. Have a look if you don't have any CONVER_IMPLICIT operators for example and table/index scans in general.

<a href="http://msdn.microsoft.com/en-us/library/ms190233.aspx" rel="nofollow noreferrer">http://msdn.microsoft.com/en-us/library/ms190233.aspx</a>

The conversions prevent indexes from being used and may happen if you pass parameters which have different type than the columns you compare them against.

You might try to add OPTION(RECOMPILE) to the query of the report, it is possible that your report is a victim of <a href="http://www.databasejournal.com/features/mssql/article.php/3841271/T-SQL-Best-Practices--Parameter-Sniffing.htm" rel="nofollow noreferrer">parameter sniffing</a>.

Check if your queries use scalar user defined functions. They may be real killers of performance. If you have them, it may be possible to convert them to <a href="http://blog.falafel.com/2008/04/07/ImprovePerformanceOfTSQLUserDefinedFunctionsByConvertingScalarOnesToTablevaluedOnes.aspx" rel="nofollow noreferrer">table valued functions</a>.

blocks|key|2378480|text|我最终剥离了查询，基本上一次只有一条语句，直到我找到罪魁祸首。查询中的一个联接使用"with+(nolock+index(x))“提示联接到一个不断增长的表(数百万行)中。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2378481|删除查询分析器中的索引提示后，得到的结果与Reporting+Services中的结果相同-这是一个very速度很慢的查询。这本身并不令人惊讶-但实际上，当通过RS运行查询时，似乎没有使用该提示。|offset|length|style|BOLD|2378482|然后，我使用SET+FORCEPLAN+ON语句再次尝试在RS中运行报告。还有..。它起作用了-执行时间现在是几秒钟，正如它应该的那样。据我所知，FORCEPLAN选项强制SQL+Server按指定的顺序处理联接，并考虑任何提示。|2378483|当查询分析器显然将其考虑在内时，有没有人知道为什么通过RS的查询会忽略这个提示？|2378484|entityMap^0|0|1E|4|0|0|0^^$0|@$1|2|3|4|5|6|7|N|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|O|8|@$D|P|E|Q|F|G]]|9|@]|A|$]]|$1|H|3|I|5|6|7|R|8|@]|9|@]|A|$]]|$1|J|3|K|5|6|7|S|8|@]|9|@]|A|$]]|$1|L|3|-4|5|6|7|T|8|@]|9|@]|A|$]]]|M|$]]

I ended up stripping the query, basically one statement at a time, until I found the culprit. One of the joins in the query joined in an ever growing table (millions of rows), using a "with (nolock index(x))" hint.

Removing the index hint in Query Analyzer got me the same result as in Reporting Services - a very slow query. This is not surprising in itself - but indeed it seemed as if the query when run through RS did not use the hint.

I then tried running the report in RS again, using the SET FORCEPLAN ON statement. And... it worked - execution time is now a few seconds, as it should be. As I understand it, the FORCEPLAN option forces the SQL Server to process the joins in the order indicated, AND to take any hints into consideration.

Does anybody have any insights as to why the query through RS would ignore the hint, when the Query Analyzer obviously takes it into account?

blocks|key|751211|text|快速更新:任何执行时间超过30秒的查询似乎都会自动导致Reporting+Services超时。虽然SET+FORCEPLAN+ON解决了某些报告的问题，但仍有一些问题报告会超时。尽管执行时间超过30秒，但这些查询在Query+Analyzer中仍然可以正常工作。在消除了所有其他可能存在的超时设置问题后，我发现仍然存在无法修改的超时。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|751212|我怀疑这与ADO.NET的默认超时时间有关，微软让我们没有办法修改这个值。|751213|最后，我对查询进行了更深入的研究，尽我所能对其进行了重新排列，并设法减少了几秒钟的执行时间。现在，报告终于可以正常运行了。|751214|然而，这种不可修改的超时让我担心-如果SQL服务器在执行查询时的负载比正常情况下更高，会发生什么？|751215|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|J|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|K|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|L|8|@]|9|@]|A|$]]|$1|F|3|G|5|6|7|M|8|@]|9|@]|A|$]]|$1|H|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|I|$]]

A quick update: It would seem that any query that takes more than 30 seconds to execute, will automatically cause a time-out in Reporting Services. While SET FORCEPLAN ON solved the issues for some of the reports, there were still problem reports that would time-out. These queries still worked fine within Query Analyzer, although the execution time was larger than 30 seconds. After eliminating all other possible problems with time-out settings - at the SQL server, in Reporting Services, rsserver.config, in the IIS - I found that there still was a time-out that I could not modify. 

My suspicion is that it has to do with the ADO.NET default time-out, and that Microsoft has left us with no way of modifying this value.

Finally, I ended up having a deeper look at the query, rearranging it to the best of my ability, and managed to strip some seconds from the execution time. Now, finally, the reports are running as they should.

This unmodifiable time-out worries me, however - what will happen if the SQL server has a higher load than normal when the query is executed?

blocks|key|2378575|text|我也遇到过同样的情况。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2378576|在Management+Studio中，结果在20秒后出现，但是当我在visual+studio中运行报告时，它锁定了系统。|2378577|在SQL+profiler中，我跟踪了查询，并意识到它将我的查询转换为：|2378578|++++exec+sp_executesql+N'
++++++++++++++++....................
++++++++++++++++....................'+
,+@dateparameter1+=+'2010-06-01+00:00:00'
,+@datepamareter2+=+'2010-06-02+00:00:00'
,+@stringparameter=null|code-block|syntax|javascript|2378579|我已经检查了执行计划，并意识到我是参数嗅探的受害者。|2378580|我已经按照here所说的那样重新组织了我的查询，现在它工作得很好。|offset|length|2378581|entityMap|0|LINK|mutability|MUTABLE|url|http://www.databasejournal.com/features/mssql/article.php/3841271/T-SQL-Best-Practices--Parameter-Sniffing.htm^0|0|0|0|0|0|5|4|0|0^^$0|@$1|2|3|4|5|6|7|Y|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|Z|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|10|8|@]|9|@]|A|$]]|$1|F|3|G|5|H|7|11|8|@]|9|@]|A|$I|J]]|$1|K|3|L|5|6|7|12|8|@]|9|@]|A|$]]|$1|M|3|N|5|6|7|13|8|@]|9|@$O|14|P|15|1|16]]|A|$]]|$1|Q|3|-4|5|6|7|17|8|@]|9|@]|A|$]]]|R|$S|$5|T|U|V|A|$W|X]]]]

I have faced the same situation.

In Management Studio the results came after twenty seconds but when I run the report in visual studio it locked the system.

In SQL profiler I've traced the query and realised that it transforms my query as :

<pre><code> exec sp_executesql N'
 ....................
 ....................' 
, @dateparameter1 = '2010-06-01 00:00:00'
, @datepamareter2 = '2010-06-02 00:00:00'
, @stringparameter=null
</code></pre>

I've examined the execution plan and realized that I was a victim of parameter sniffing.

I've reorganized my query as it was told <a href="http://www.databasejournal.com/features/mssql/article.php/3841271/T-SQL-Best-Practices--Parameter-Sniffing.htm" rel="nofollow noreferrer">here</a>, and it works fine now..

blocks|key|969535|text|这也发生在我身上，这是参数嗅探。我使用了与过滤器相同的报告视图，只是使用了我想要的字段。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|969536|我所做的就是为过滤器创建一个视图，这个问题就解决了。|969537|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|F|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|G|8|@]|9|@]|A|$]]|$1|D|3|-4|5|6|7|H|8|@]|9|@]|A|$]]]|E|$]]

It happened to me too, it was parameter sniffing. I was using the same view of the report to use as filter, just using the fields that I wanted.

What I did was to create a view for the filter and it was solved.

We're using SQL Server 2005 with Reporting Services.

We have a number of reports, each containing a relatively simple SQL query - by "relatively" I mean that we do have a few joins, but nothing worse than that. We do not call any stored procedures in our queries - this is not a case of parameter sniffing.

When executing one of these reports (let's call it report A) through Reporting Services, it takes an extremely long time to complete - on the order of tens of minutes or even hours. When executing the corresponding SQL query in Query Analyzer, it completes in a few seconds.

The number of rows returned from the database can be as few as 1 - yet, the report never completes.

The other reports are working fine.

Looking in the ExecutionLog table on the Reporting Services, I can see that most of the time is in TimeDataRetrieval (and we're talking millions of seconds here...) - those times the report actually completes. If the report is manually aborted, TimeDataRetrieveal is zero and TimeProcessing is absurdly high instead.

I've looked into the logs of Reporting Services, but everything looks normal. 

Now, before you start suggesting "lock" - well, our queries do have the nolock hint turned on.

As it stands, I've reached the limit of my imagination trying to find the error. Any thoughts, insights would be gladly appreciated.

/Christoffer

Slow performance of Reporting Services, very fast in QueryAnalyser

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

我们将SQL Server 2005与Reporting Services一起使用。我们有许多报告，每个报告都包含一个相对简单的SQL查询--“相对”我的意思是我们确实有一些连接，但没有比这更糟糕的了。我们在查询中不调用任何存储过程-这不是参数嗅探的情况。当通过Reporting Services执行其中一个报告(我们...

问报表服务的性能很慢，在QueryAnalyser中非常快
EN

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问报表服务的性能很慢，在QueryAnalyser中非常快EN