i was using PreparedStatement to load some data out of sql database, which has been working fine for quite some time. Till recently, for one day’s data, the query is not able to finish (hang on reading the response from database)
"stackTrace": [
{
"methodName": "socketRead0",
"fileName": "SocketInputStream.java",
"lineNumber": -2,
"className": "java.net.SocketInputStream",
"nativeMethod": true
},
{
"methodName": "socketRead",
"fileName": "SocketInputStream.java",
"lineNumber": 116,
"className": "java.net.SocketInputStream",
"nativeMethod": false
},
{
"methodName": "read",
"fileName": "SocketInputStream.java",
"lineNumber": 171,
"className": "java.net.SocketInputStream",
"nativeMethod": false
},
{
"methodName": "read",
"fileName": "SocketInputStream.java",
"lineNumber": 141,
"className": "java.net.SocketInputStream",
"nativeMethod": false
},
{
"methodName": "read",
"fileName": "IOBuffer.java",
"lineNumber": 2058,
"className": "com.microsoft.sqlserver.jdbc.TDSChannel",
"nativeMethod": false
},
{
"methodName": "readPacket",
"fileName": "IOBuffer.java",
"lineNumber": 6617,
"className": "com.microsoft.sqlserver.jdbc.TDSReader",
"nativeMethod": false
},
{
"methodName": "nextPacket",
"fileName": "IOBuffer.java",
"lineNumber": 6567,
"className": "com.microsoft.sqlserver.jdbc.TDSReader",
"nativeMethod": false
},
{
"methodName": "ensurePayload",
"fileName": "IOBuffer.java",
"lineNumber": 6540,
"className": "com.microsoft.sqlserver.jdbc.TDSReader",
"nativeMethod": false
},
{
"methodName": "skip",
"fileName": "IOBuffer.java",
"lineNumber": 7200,
"className": "com.microsoft.sqlserver.jdbc.TDSReader",
"nativeMethod": false
},
{
"methodName": "skipValue",
"fileName": "dtv.java",
"lineNumber": 3362,
"className": "com.microsoft.sqlserver.jdbc.ServerDTVImpl",
"nativeMethod": false
},
{
"methodName": "skipValue",
"fileName": "dtv.java",
"lineNumber": 162,
"className": "com.microsoft.sqlserver.jdbc.DTV",
"nativeMethod": false
},
{
"methodName": "skipValue",
"fileName": "Column.java",
"lineNumber": 152,
"className": "com.microsoft.sqlserver.jdbc.Column",
"nativeMethod": false
},
{
"methodName": "skipColumns",
"fileName": "SQLServerResultSet.java",
"lineNumber": 216,
"className": "com.microsoft.sqlserver.jdbc.SQLServerResultSet",
"nativeMethod": false
},
{
"methodName": "loadColumn",
"fileName": "SQLServerResultSet.java",
"lineNumber": 770,
"className": "com.microsoft.sqlserver.jdbc.SQLServerResultSet",
"nativeMethod": false
},
{
"methodName": "getterGetColumn",
"fileName": "SQLServerResultSet.java",
"lineNumber": 2036,
"className": "com.microsoft.sqlserver.jdbc.SQLServerResultSet",
"nativeMethod": false
},
{
"methodName": "getValue",
"fileName": "SQLServerResultSet.java",
"lineNumber": 2054,
"className": "com.microsoft.sqlserver.jdbc.SQLServerResultSet",
"nativeMethod": false
},
{
"methodName": "getValue",
"fileName": "SQLServerResultSet.java",
"lineNumber": 2040,
"className": "com.microsoft.sqlserver.jdbc.SQLServerResultSet",
"nativeMethod": false
},
{
"methodName": "getString",
"fileName": "SQLServerResultSet.java",
"lineNumber": 2525,
"className": "com.microsoft.sqlserver.jdbc.SQLServerResultSet",
"nativeMethod": false
},
the same query however works well with plain run in the sql tool, or if run using plain java statement.
and until I have added a dummy where clause “… and 1=1“, then suddenly the preparedstatement is able to return the result timely again.
in the beginning, i thought it was the dummy clause which made a difference strangely. turns out, this was a problem with sql server parameter sniffing.
the dummy where clause worked only because sql server now see it as a different query, hence not using the previous cached execution plan.
this can be reproduced by adding `
option (recompile)
to the query. this will trigger sql server to drop the previous cached execution plan, as such, even the original query, without the dummy where clause is now back to performing again.
SELECT * FROM sys.database_scoped_configurations;
==========================
looking further into this, the execution was stuck at using previous executing plan.
seems like because the first plan, in blue, is not working out. finally it created a new plan in
green.
and the wait for the blue plan was for HTDELETE. this is a change made in SQL server since 2014.
SQL Server 2014 now uses one shared hash table instead of per-thread copy.
This provides the benefit of significantly lowering the amount of memory
required to persist the hash table but, as you can imagine, the multiple
threads depending on that single copy of the hash table must synchronize with
each other before, for example, deallocating the hash table. To do so, those
threads wait on the HTDELETE (Hash Table DELETE) wait type.<o:p></o:p>
likely this is a bug in SQL server, which was hit when I am running the program using parallelStream(), which steam() would work.*
*There are multiple process hitting the same query.
Provides the benefit of significantly lowering the amount of memory required to persist the hash table but, as you can imagine, the multiple threads depending on that single copy of the hash table must synchronize with each other before, for example, deallocating the hash table. To do so, those threads wait on the HTDELETE (Hash Table DELETE) wait type.
lwpro2.wordpress.com 