CODEWITHSUNDEEP
c#sql-serversqlcommandtable-valued-parameterssqlparameter
Conorou
Conorou

Reputation: 31

Execution timeout expiry when executing a stored procedure which uses a table valued parameter

I have a stored procedure which uses a table valued parameter (tvp). In my application, I use a datatable as a SqlParameter to match the tvp structure. The problem is that it sometimes takes 25 seconds just to insert the data (30k rows give or take) into the tvp from the application once I've already executed the stored procedure, meaning the code inside the stored procedure itself only has 5 seconds (with a command timeout of 30 seconds) to complete which doesn't always happen with large amounts of data.

I'm fully aware that I can just increase the command timeout, but I'd like to get to the bottom of why it takes 25 seconds to insert the data into the tvp and what can be done to speed this up.

Just to be clear, this isn't the code inside the stored procedure in SSMS which is taking the 25 seconds, it's the application itself inserting the rows into the tvp after I've executed the stored procedure from the application.

This offending statement is below (our tvp has roughly 20 columns):

declare @p3 dbo.table_valued_parameter insert into @p3 (col1, col2, col3) values (v1, v2, v3)

My question is, why does it take 25 seconds to insert the 30k rows into the tvp and what methods can I use to speed this up? Perhaps the issue is using a DataTable for the SqlParameter? I also would have thought CommandTimeout would only start counting once the stored procedure itself has begun executing in SSMS, rather than beginning the count while preparing the parameters.

C# code below as requested (The GetDataTable method creates a DataTable by adding columns to a new DataTable which matches the definition of the tvp, and then adds rows to the DataTable by iterating over a list used elsewhere in the code).

List<SqlParameter> parameters = new List<SqlParameter>()
{
    new SqlParameter("@textParam1", "Value1"), 
    new SqlParameter("@testParam2", "Value2"),
    new SqlParameter("@tvp", GetDataTable())
};

DataSet dataSet = new DataSet();

SqlCommand command = new SqlCommand(StoredProcName); 

command.CommandType = CommandType.StoredProcedure;
command.Parameters.AddRange(parameters.ToArray());

using (SqlConnection connection = new SqlConnection(ConnectionString))
{
    connection.Open();
    command.Connection = connection;

    using (SqlDataAdapter dataAdapter = new SqlDataAdapter(command))
    {
        dataAdapter.Fill(dataSet);
    }

    connection.Close();
}

Upvotes: 1

Views: 1507

Answers (1)

Conorou
Conorou

Reputation: 31

I managed to grab the RPC call made by the application from profiler and use the same SQL code (and more importantly, parameters) that the application was using and run it in SSMS. In SSMS, the proc ran in about 2 seconds, from the application it was taking 30 seconds.

These are the steps that fixed this for me.

  1. Reading this great article really helped clarify the issue I was having: https://www.sommarskog.se/query-plan-mysteries.html

  2. From the article, I discovered the execution plan of the stored proc when called by the application was actually different to the execution plan when called in SSMS. I verified this by clearing the cache (DBCC FREEPROCCACHE), running the proc with the same input parameters in the app and in SSMS, and then querying sys.dm_exec_cached_plan which showed me 2 different cached plans. To fix this, I turned Arithabort ON for all applications (to match SSMS) - https://blog.sqlauthority.com/2018/08/07/sql-server-setting-arithabort-on-for-all-connecting-net-applications/

  3. As the proc in question inserts data (or deletes and reinserts to get the latest if data was outdated), I used this as a chance to help improve the processes around the data loading. This included removing a duplicate non clustered index, turning the staging table we used into a heap (this previously had a clustered index), removing the use of the TVP in the body of the proc and replacing with a temp table (as this prevents queries which use the TVP from going parallel: https://www.brentozar.com/archive/2018/06/how-table-variables-mess-with-parallelism/), using local variables to prevent parameter sniffing (i.e. declaring a new variable in the body of the proc and setting this as the value of the input param). This really helped speed up the process however, I was still getting occasional timeouts...

  4. The table which is the target of the insert/deletes from this stored proc is a very large table (100 million + rows) and also highly transactional - we insert/delete data from this table on an almost hourly basis. I noticed that this was hitting the auto update stats threshold more than once during a single load process. I also managed to match the time of the the timeouts to the time that the stats were being auto updated (https://blog.sqlauthority.com/2020/06/01/sql-server-statistics-modification-counter-sys-dm_db_stats_properties/). I turned off auto statistics on this table and instead set up a nightly job to update the statistics manually. We have not seen any further timeouts since.

Upvotes: 2

Related Questions