I am working on a Flex application that is connecting via Flash Remoting to ColdFusion 8 with a SQL Server 2005 database. Most of the time, everything runs fine. However, from time to time, it will take an exceptionally long time for SQL Server to return data from a stored procedure call to ColdFusion; returning data from CF to Flex is very fast. When this happens, I can run the exact same call from Management Studio on the SQL Server or a ColdFusion page on the CF server and get results immediately. The most recent occurrence of the issue took about 90 seconds to return data to CF. During the 90 second window, I was able to run the stored procedure in Management Studio several times.
我正在开发一个Flex应用程序,它通过Flash Remoting连接到ColdFusion 8和SQL Server 2005数据库。大多数时候,一切都运行良好。但是,SQL Server有时会花费特别长的时间将数据从存储过程调用返回到ColdFusion;将数据从CF返回到Flex非常快。发生这种情况时,我可以从SQL Server上的Management Studio或CF服务器上的ColdFusion页面运行完全相同的调用,并立即获得结果。最近发生的问题大约需要90秒才能将数据返回给CF.在90秒窗口期间,我能够在Management Studio中多次运行存储过程。
I have tried using different drivers and this doesn't seem to matter. I have also kept an eye on server performance and haven't noticed anything unusual while this is happening. Has anyone seen this behavior before? Any ideas as to what I should be looking for.
我尝试使用不同的驱动程序,这似乎并不重要。我也一直关注服务器性能,并且在发生这种情况时没有注意到任何异常。以前有人见过这种行为吗?关于我应该寻找什么的任何想法。
2 个解决方案
#1
While it's working slowly, can you run "sp_who2" against your SQL Server? If it's a blocking issue, you'll see rows that have a value in the "BlkBy" column, meaning that they'r waiting for another process to complete before they can continue.
虽然它运行缓慢,但您可以针对SQL Server运行“sp_who2”吗?如果它是阻塞问题,您将看到在“BlkBy”列中具有值的行,这意味着他们等待另一个进程完成才能继续。
If that's the case, then there's other troubleshooting to do so you can figure out what's causing the blocks. This article provides an overview of locking and troubleshooting blocks. If that's your issue, please update your question and add more details, and we can help you go from there!
如果是这种情况,那么还有其他故障排除方法,您可以找出导致阻塞的原因。本文概述了锁定和故障排除块。如果这是您的问题,请更新您的问题并添加更多详细信息,我们可以帮助您从那里开始!
#2
Are you absolutely sure that the query being run in the sp is the same every time? For example, is it possible that when it slows down, the query has a different sort order? Possibly 9 times out of 10, the query returns quickly, and that 10th time is slow b/c the data you're getting is being sorted by some column that isn't indexed?
您是否绝对确定每次在sp中运行的查询都是相同的?例如,当它减慢时,查询是否可能有不同的排序顺序?可能是10次中的9次,查询返回很快,而第10次是慢的b / c您获得的数据是按某些未编入索引的列进行排序的?
In these situations, I'd try to have a SQL Trace set up (using sql profiler) and let it run for a while. Once the situation happens, let it run through, and then analyze the trace. Confirm beyond doubt that the query being run is the same as other executions of the same sp
在这些情况下,我会尝试设置SQL Trace(使用sql profiler)并让它运行一段时间。一旦情况发生,让它运行,然后分析跟踪。毫无疑问地确认正在运行的查询与同一sp的其他执行相同
#1
While it's working slowly, can you run "sp_who2" against your SQL Server? If it's a blocking issue, you'll see rows that have a value in the "BlkBy" column, meaning that they'r waiting for another process to complete before they can continue.
虽然它运行缓慢,但您可以针对SQL Server运行“sp_who2”吗?如果它是阻塞问题,您将看到在“BlkBy”列中具有值的行,这意味着他们等待另一个进程完成才能继续。
If that's the case, then there's other troubleshooting to do so you can figure out what's causing the blocks. This article provides an overview of locking and troubleshooting blocks. If that's your issue, please update your question and add more details, and we can help you go from there!
如果是这种情况,那么还有其他故障排除方法,您可以找出导致阻塞的原因。本文概述了锁定和故障排除块。如果这是您的问题,请更新您的问题并添加更多详细信息,我们可以帮助您从那里开始!
#2
Are you absolutely sure that the query being run in the sp is the same every time? For example, is it possible that when it slows down, the query has a different sort order? Possibly 9 times out of 10, the query returns quickly, and that 10th time is slow b/c the data you're getting is being sorted by some column that isn't indexed?
您是否绝对确定每次在sp中运行的查询都是相同的?例如,当它减慢时,查询是否可能有不同的排序顺序?可能是10次中的9次,查询返回很快,而第10次是慢的b / c您获得的数据是按某些未编入索引的列进行排序的?
In these situations, I'd try to have a SQL Trace set up (using sql profiler) and let it run for a while. Once the situation happens, let it run through, and then analyze the trace. Confirm beyond doubt that the query being run is the same as other executions of the same sp
在这些情况下,我会尝试设置SQL Trace(使用sql profiler)并让它运行一段时间。一旦情况发生,让它运行,然后分析跟踪。毫无疑问地确认正在运行的查询与同一sp的其他执行相同