SQL Server查询运行从ADO.NET比慢SSMS [英] SQL Server query runs slower from ADO.NET than in SSMS

问题描述

我有一个网站，需要15-30秒，而相同的查询从SQL Server Management Studio中0.5秒运行的查询。我看不出使用SQL事件探查任何锁定的问题，也可以从我SSMS手动复制的延迟。一个星期前，我脱离及复位这似乎奇迹般地解决问题的数据库。今天，当这个问题再次饲养它丑恶的头，我只是试图重建索引。这也解决了这一问题。不过，我不认为这是必然的指标问题，因为索引将不会自动重建一个简单的分离/附加，我的知识。

I have a query from a web site that takes 15-30 seconds while the same query runs in .5 seconds from SQL Server Management studio. I cannot see any locking issues using SQL Profiler, nor can I reproduce the delay manually from SSMS. A week ago, I detached and reattached the database which seemed to miraculously fix the problem. Today when the problem reared its ugly head again, I tried merely rebuilding the indexes. This also fixed the problem. However, I don't think it's necessarily an index problem since the indexes wouldn't be automatically rebuilt on a simple detach/attach, to my knowledge.

任何想法，这可能是造成延误？我的第一个念头是，也许有些参数嗅探存储过程调用（据说存储过程运行的CTE，如果该事项）是导致糟糕的执行计划，这可以解释这个问题的间歇性的。由于两个分离/重新连接和一个索引重建理论上应该无效缓存的查询计划，这是有道理的，但我不能确定如何验证这一点。此外，为什么不同样的查询（使用完全相同的参数直接从SQL事件探查器复制）时，表现出通过SSMS手动运行相同的延迟？

Any idea what could be causing the delay? My first thought was that perhaps some parameter sniffing on the stored procedure being called (said stored proc runs a CTE, if that matters) was causing a bad query plan, which would explain the intermittent nature of the problem. Since both detaching / reattaching and an index rebuild should theoretically invalidate the cached query plan, this makes sense, but I'm unsure how to verify this. Additionally, why wouldn't the same query (copied directly from SQL Profiler with the exact same parameters) exhibit the same delay when run manually through SSMS?

有什么想法？

推荐答案

如果一个糟糕的计划缓存那么同样糟糕的计划应该从SSMS中使用过，如果您运行具有相同参数的非常相同的查询。

If a bad plan is cached then the same bad plan should be used from SSMS too, if you run the very same query with identical arguments.

有不能更好的解决方案，发现的根源。试图窥视它解决了这个问题永远不会给你它实际上是固定的信心，希望拨开各种设置。此外，在下一次系统可能有不同的问题，你会相信这个同样的问题再次浮出水面，并应用一个坏的解决方案。

There cannot be better solution that finding the root cause. Trying to peek and poke various settings in the hope it fixes the problem will never give you the confidence it is actually fixed. Besides, next time the system may have a different problem and you'll believe this same problem re-surfaced and apply a bad solution.

要尝试的最好的事情是捕获错误的执行计划。 显示计划XML事件类探查事件是你的朋友，你可以得到的ADO.Net计划呼叫。这是一个非常沉重的事件，所以你应该重视探查并捕捉它，只有当问题表现，在很短的会话。

The best thing to try is to capture the bad execution plan. Showplan XML Event Class Profiler event is your friend, you can get the plan of the ADO.Net call. This is a very heavy event, so you should attach profiler and capture it only when the problem manifests itself, in a short session.

查询IO统计数据也能有所帮助。 RPC：完成和的 SQL：批量完成事件既包括读取和写入的，所以你可以比较ADO.Net调用主场迎战SSMS之一执行逻辑IO量。 （完全相同的查询，而params）大的差异表示不同的计划。 sys.dm_exec_query_stats动态管理是另一调查的途径。你可以在那里找到你的查询计划（S），并检查执行统计。

Query IO statistics can also be of help. RPC:Completed and SQL: Batch Completed events both include Reads and Writes so you can compare the amount of logical IO performed by ADO.Net invocation vs. SSMS one. Large difference (for exactly the same query and params) indicate different plans.sys.dm_exec_query_stats is another avenue of investigation. You can find your query plan(s) in there and inspect the execution stats.

所有这些都应该帮助建立确定性，如果问题是个不错的计划或别的东西，入手。

All these should help establish with certitude if the problem is a bad plan or something else, to start with.

这篇关于SQL Server查询运行从ADO.NET比慢SSMS的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！