SqlCommand calling a SQL Server stored procedure times out

Question

I've got a conversion utility that basically copies values from one table to another. It's worked great for a while, but I've run into a strange issue with one customer. They got through 1.5 million records with the utility but now it is completely halted.

When calling a stored procedure from VB.Net, it just hangs until the SqlCommand times out. Calling the same sproc from Management Studio executes instantly. My VB.Net code for the SqlCommand is below (insertConn is defined and opened earlier, dr is a SqlDataReader that has been populated in a previous step from completely different SqlConnection and SqlCommand instances):

Dim conn As New SqlConnection("connection string here")
Dim insertConn As New SqlConnection("connection string here")
Dim dr As SqlDataReader = Nothing
Dim readCommand As New SqlCommand("my query here", conn)
conn.Open()
insertConn.Open()
...
dr = readCommand.ExecuteReader()
...
While dr.Read()
    Using insertCommand = New SqlCommand("dmDocumentFieldInsert", insertConn)
        insertCommand.CommandType = CommandType.StoredProcedure

        insertCommand.Parameters.AddWithValue("@DocumentKey", dr("DocumentKey"))
        insertCommand.Parameters.AddWithValue("@FieldId", "TITLE")
        insertCommand.Parameters.AddWithValue("@FieldValue", dr("DocumentTitle"))
        insertCommand.ExecuteNonQuery()
    End Using
End While

I've tried restarting SQL Server to clear any locks, recompiling the sproc, increasing the SqlCommand and SqlConnection timeouts all to no avail.

I checked the data that is getting added to the parameters and it's valid data...if I manually call the sproc with the same data it works fine.

I originally was not using the Using block but changed that to see if there was some resource issue that wasn't getting disposed/closed. Memory usage of the utility hovers at just around 5MB, so there don't appear to be any memory issues.

Does anyone have suggestions on what to try next for a solution?

EDIT Added loop and init code per comment requests

EDIT I updated statistics and rebuilt the table indexes, no change.

EDIT There are three indexes on the table that the data is getting copied to (dmDocumentField). If I disable all three indexes then the sproc executes perfectly, albeit much slower than when the index is present. If I enable any one of them, then the utility gets through a couple hundred records at most then dies with the same timeout on the sproc. Deleting and recreating the index has no effect. Table structure and indexes are as follows:

CREATE TABLE [dbo].[dmDocumentField](
[FieldKey] [bigint] IDENTITY(1,1) NOT NULL,
[DocumentKey] [char](36) NOT NULL,
[FieldId] [varchar](10) NOT NULL,
[FieldValue] [varchar](255) NOT NULL,
CONSTRAINT [PK_dmDocumentField] PRIMARY KEY NONCLUSTERED 
(
[FieldKey] ASC,
[DocumentKey] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

Indexes (besides the PK):

CREATE NONCLUSTERED INDEX [dmDocumentField_DocumentKey] ON [dbo].[dmDocumentField]
(
[DocumentKey] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

.

CREATE NONCLUSTERED INDEX [dmDocumentField_DocumentKey_IFieldId_IFieldValue] ON [dbo].[dmDocumentField]
(
[DocumentKey] ASC
)
INCLUDE (   [FieldId],
[FieldValue]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

I should also mention, the utility is in VB.Net 4 and the SQL Server in question is SQL Server 2008 R2 Standard. — Kettch19
You could try using the data access methods in Enterprise Library. — Darth Continent
Is this code called inside a loop? If yes, could you show also the loop code? — Steve
If you use .AddWithValue then SQL Server generates a different execution plan for every distinct size of the parameter it is presented with (from my own testing). If you set the .Size when you create the SQL parameter then you get just one execution plan. — Andrew Morton

Steve Steve · Accepted Answer · 2012-06-14T19:43:36

I will try this:

... 
dr = readCommand.ExecuteReader() 
... 
insertCommand = New SqlCommand("dmDocumentFieldInsert", insertConn) 
insertCommand.CommandType = CommandType.StoredProcedure 
insertCommand.Parameters.AddWithValue("@DocumentKey", string.Empty)) 
insertCommand.Parameters.AddWithValue("@FieldId", "TITLE") 
insertCommand.Parameters.AddWithValue("@FieldValue", string.Empty)) 

Dim tr As SqlTransaction
tr = insertConn.BeginTransaction
insertCommand.Transaction = tr
While dr.Read() 
        insertCommand.Parameters("@DocumentKey").Value = dr("DocumentKey") 
        insertCommand.Parameters("@FieldValue").Value = dr("DocumentTitle")
        insertCommand.ExecuteNonQuery() 
End While 

tr.Commit()

This probably will be less memory intensive (Create Command, Create Parameters repetead inside the loop) and with 1.5 million of record could be the difference.
NOTE I don't know the real datatype of @DocumentKey and @FieldValue, assumed to be strings, but if it is not the case then change the initial settings with an appropriate dummy value.

SqlCommand calling a SQL Server stored procedure times out

3 Answers