SQL Server / JDBC 连接问题

发布于 2024-08-06 04:01:43 字数 2511 浏览 4 评论 0原文

我在 Java 服务器应用程序中遇到一些奇怪的行为，通常需要几毫秒的数据库操作偶尔需要更长的时间（30 秒 - 170 秒）才能完成。这与特定查询无关，因为我已经看到 SQL 更新和选择语句都发生延迟。另外，我的所有 select 语句都使用 NOLOCK 选项，因此我排除了可能的锁争用。

上次我看到延迟时，我设法从 JConsole 捕获以下堆栈跟踪；有问题的更新通常需要 5 毫秒才能完成，但此堆栈跟踪至少可以访问 10 - 20 秒。跟踪表明该语句已被执行，但检索结果存在一些延迟，尽管我可能是错的？显然，由于这是一个更新语句，我期望的唯一结果是行数（即不是一个大的数据结果集）。

在延迟期间，我在 SQL Server Management Studio 中看到了“传输级别错误”。

我的一个建议是，这些问题是由于 SQL Server 资源耗尽造成的。有人见过类似的东西吗？谁能阐明这个问题？

提前致谢。

堆栈跟踪：

Name: MessageRouterImplThread-2
State: RUNNABLE
Total blocked: 0  Total waited: 224

Stack trace: 
java.net.SocketInputStream.socketRead0(Native Method)
java.net.SocketInputStream.read(SocketInputStream.java:129)
com.microsoft.util.UtilSocketDataProvider.getArrayOfBytes(Unknown Source)
com.microsoft.util.UtilBufferedDataProvider.cacheNextBlock(Unknown Source)
com.microsoft.util.UtilBufferedDataProvider.getArrayOfBytes(Unknown Source)
com.microsoft.jdbc.sqlserver.SQLServerDepacketizingDataProvider.signalStartOfPacket(Unknown Source)
com.microsoft.util.UtilDepacketizingDataProvider.getByte(Unknown Source)
com.microsoft.util.UtilByteOrderedDataReader.readInt8(Unknown Source)
com.microsoft.jdbc.sqlserver.tds.TDSRequest.getTokenType(Unknown Source)
com.microsoft.jdbc.sqlserver.tds.TDSRequest.processReply(Unknown Source)
com.microsoft.jdbc.sqlserver.SQLServerImplStatement.getNextResultType(Unknown Source)
com.microsoft.jdbc.base.BaseStatement.commonTransitionToState(Unknown Source)
com.microsoft.jdbc.base.BaseStatement.postImplExecute(Unknown Source)
com.microsoft.jdbc.base.BasePreparedStatement.postImplExecute(Unknown Source)
com.microsoft.jdbc.base.BaseStatement.commonExecute(Unknown Source)
com.microsoft.jdbc.base.BaseStatement.executeUpdateInternal(Unknown Source)
com.microsoft.jdbc.base.BasePreparedStatement.executeUpdate(Unknown Source)
   - locked com.microsoft.jdbc.sqlserver.SQLServerConnection@c4b83f
org.apache.commons.dbcp.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:101)
org.springframework.jdbc.core.JdbcTemplate$2.doInPreparedStatement(JdbcTemplate.java:798)
org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:591)
org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:792)
org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:850)
org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:858)
org.springframework.jdbc.core.simple.SimpleJdbcTemplate.update(SimpleJdbcTemplate.java:237)

原文

I am experiencing some strange behaviour in my Java server application whereby database operations that usually take a few milliseconds are sporadically taking much longer (30s - 170s) to complete. This isn't isolated to a specific query as I've seen the delays occurring for both SQL update and select statements. Also, all of my select statements use the NOLOCK option so I've ruled out possible lock contention.

The last time I saw a delay I managed to capture the following stack trace from JConsole; the update in question typically takes 5ms to complete but this stack trace was accessible for at least 10 - 20 seconds. The trace suggests to me that the statement has been executed but there is some delay in retrieving the result although I could be wrong? Obviously as this was an update statement the only result I'd expect would be the row count (i.e. not a large result set of data).

I saw a "transport level error" in SQL Server Management Studio at around the time of the delay.

One suggestion I've had is that these problems are due to SQL Server resources being exhausted. Has anyone seen anything similar? Can anyone shed any light on this problem?

Thanks in advance.

Stack Trace:

Name: MessageRouterImplThread-2
State: RUNNABLE
Total blocked: 0  Total waited: 224

Stack trace: 
java.net.SocketInputStream.socketRead0(Native Method)
java.net.SocketInputStream.read(SocketInputStream.java:129)
com.microsoft.util.UtilSocketDataProvider.getArrayOfBytes(Unknown Source)
com.microsoft.util.UtilBufferedDataProvider.cacheNextBlock(Unknown Source)
com.microsoft.util.UtilBufferedDataProvider.getArrayOfBytes(Unknown Source)
com.microsoft.jdbc.sqlserver.SQLServerDepacketizingDataProvider.signalStartOfPacket(Unknown Source)
com.microsoft.util.UtilDepacketizingDataProvider.getByte(Unknown Source)
com.microsoft.util.UtilByteOrderedDataReader.readInt8(Unknown Source)
com.microsoft.jdbc.sqlserver.tds.TDSRequest.getTokenType(Unknown Source)
com.microsoft.jdbc.sqlserver.tds.TDSRequest.processReply(Unknown Source)
com.microsoft.jdbc.sqlserver.SQLServerImplStatement.getNextResultType(Unknown Source)
com.microsoft.jdbc.base.BaseStatement.commonTransitionToState(Unknown Source)
com.microsoft.jdbc.base.BaseStatement.postImplExecute(Unknown Source)
com.microsoft.jdbc.base.BasePreparedStatement.postImplExecute(Unknown Source)
com.microsoft.jdbc.base.BaseStatement.commonExecute(Unknown Source)
com.microsoft.jdbc.base.BaseStatement.executeUpdateInternal(Unknown Source)
com.microsoft.jdbc.base.BasePreparedStatement.executeUpdate(Unknown Source)
   - locked com.microsoft.jdbc.sqlserver.SQLServerConnection@c4b83f
org.apache.commons.dbcp.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:101)
org.springframework.jdbc.core.JdbcTemplate$2.doInPreparedStatement(JdbcTemplate.java:798)
org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:591)
org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:792)
org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:850)
org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:858)
org.springframework.jdbc.core.simple.SimpleJdbcTemplate.update(SimpleJdbcTemplate.java:237)

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

墨小墨 2024-08-13 04:01:43

“...数据库操作
通常需要几毫秒
偶尔需要更长的时间（30秒 -
170s）完成。”

您所描述的内容听起来像是由于过时的统计信息（和/或需要重建的索引）或不正确的参数嗅探而导致错误缓存的查询计划。超时可能会发生，因为服务器花费了更长的时间（

我会与您的 DBA 联系并首先更新统计信息，如果这不起作用，请重建查询中涉及的表的索引

通常需要注意不要运行）。在生产中，而不与您的管理员/DBA 交谈，并自行承担风险等）：

EXEC sp_updatestats

EXEC sp_refreshview 

EXEC sp_msForEachTable 'EXEC sp_recompile ''?'''

或者，您提到一天中的时间是否是一个因素

更新？ /strong>：您可以启动探查器跟踪：MS SQL Server 2008 - 如何记录并查找最昂贵的查询？，但不限于您的数据库。这样的跟踪，只要从 SSMS 启动即可根据该帖子，影响相对较低（3-5% 左右）。

"...whereby database operations that
usually take a few milliseconds are
sporadically taking much longer (30s -
170s) to complete."

What you are describing sounds like an incorrectly cached query plan, due to out of date statistics (and/or indexes that need rebuilding), or incorrect parameter sniffing. The timeout could be occuring because the server is taking longer than the default connection timeout.

I would talk to your DBA and first get statistics updated, and if that doesn't work get the indexes of the tables involved in the query rebuilt.

Run this on your Database (with the usual caveat about not runninhg in Production without talking to your admin/DBA, and run at own risk etc.):

EXEC sp_updatestats

EXEC sp_refreshview 

EXEC sp_msForEachTable 'EXEC sp_recompile ''?'''

Alternatively, you mention time of day being a factor. Could it be that a backup or scheduled job is occuring at that time?

Update: You could kick off a profiler trace: MS SQL Server 2008 - How Can I Log and Find the Most Expensive Queries? but don't restrict to your DB. Such a trace, as long as it is started from SSMS as per that post, is relatively low impact (3-5% ish).

回复收藏 0 原文