0

Question

Why is a Truncate-Table statement causing poor Insert/Select performance on subsequent calls, and is there any harm based on my use-case (described below) in leaving it as a heap?

Background

In a QA process, data is being dropped into some work tables for Union operations against subsets of the table, and then truncated before the next workload. This was devised because the individual queries join a series of large tables with non-relative filters (two different databases, two different datasets, being unioned in the QA database).

Realistically, there's rarely >50k rows at a time, but could theoretically be up to 1M rows. Originally, there was a Temp Table being used, with a Drop-Create at the top of the loop per-item of work. When the work table was instantiated, the Drop-Create was replaced with a Truncate operation. The indexes were left the same, but the performance was terrible.

From there, the original indexes (that matched the use case) were dropped and replaced with a single PK on the Identity column and no other indexes. While this improved performance (from hours to minutes), this was a far cry from the seconds it accomplished in TempDB.

As a crap-shoot, I decided to drop the last of the indexes on the table, and leave it a heap, and I'm back to the old performance of when it was in TempDB. As for why it isn't just kept in TempDB, that's because TempDB is abused as-is in the environment, and would intermittently suffer major performance hits dependent on what else was occurring in the environment. To stabilize the process, we chose to migrate all operations within the executing database instead.

Code Examples

create table qa.load_table
    (
    is_qa bit not null,
    data_value sql_variant not null,
    store_id int not null,
    row_id int not null identity(-2147483648, 1) primary key clustered
    );

create index
    [ix_filtered_is_qa]
on qa.load_table
    (data_value, store_id)
where
    is_qa = 1;

create index
    [ix_filtered_is_not_qa]
on qa.load_table
    (data_value, store_id)
where
    is_qa = 0;

Note

While I'm not showing the Insert code, know that the Select of the data returns sub-second in a vacuum using the same Inline Table-Valued Functions that is executed in a Loop within the procedure. To prove this, I'm pasting the Time and IO statistics from an execution of the Select statements.

(7646 rows affected)
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'qa_contact'. Scan count 4, logical reads 88, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 78 ms,  elapsed time = 77 ms.

(7646 rows affected)
Table 'suppress'. Scan count 7646, logical reads 22938, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'contact'. Scan count 1, logical reads 517, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'type'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'filter_criteria'. Scan count 1, logical reads 6, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'purpose'. Scan count 0, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'header'. Scan count 0, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'download'. Scan count 1, logical reads 117, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 110 ms,  elapsed time = 117 ms.

Edit

Table storing keys to be ranked in the work table discussed above:

if not exists(select * from sys.objects where [object_id] = OBJECT_ID(N'qa.detail_keys'))
    create table qa.detail_keys
        (
        is_include tinyint not null, -- 0 is exclude, 1 is include, 2 is budget exclude, 3 is distance & exclude, 4 is distance & include
        session_begin datetime not null constraint [FK_test_session_detail_keys] foreign key references qa.test_session(session_begin) on delete cascade,
        detail_id int not null,
        segment_id int not null,
        store_id int not null,
        group_id int null,
        row_id int not null identity(-2147483648, 1),
        varKey sql_variant not null,
        constraint [CX_session_list_detail_keys] unique clustered(session_begin, is_include, detail_id, row_id),
        constraint [PK_session_detail_keys] primary key nonclustered(session_begin, row_id)
        )
    on
        ps_qa_session(session_begin) -- daily partitions; TTL: 7 days
    with
        (DATA_COMPRESSION = PAGE);

Function definition as used in the Insert statement:

create function qa.fn_session_include_keys
    (
    @session_begin datetime,
    @detail_id int,
    @include_distance bit
    )
returns table
return
    (
    with cte
    as  (
        select
            varKey
        from
            qa.detail_keys iK
        where
            iK.session_begin = @session_begin and
            (iK.is_include = 1 or iK.is_include = 4 and @include_distance = 1) and
            iK.detail_id = @detail_id
            except
        select
            varKey
        from
            qa.detail_keys xK
        where
            xK.session_begin = @session_begin and 
            (xK.is_include = 0 or xK.is_include = 3 and @include_distance = 1) and
            xK.detail_id = @detail_id
        ),
        cteIncludeKey
    as  (
        select
            varKey,
            [min_row_id] = min(lDK.row_id)
        from
            qa.detail_keys lDK
        where
            lDK.session_begin = @session_begin and
            (lDK.is_include = 1 or lDK.is_include = 4 and @include_distance = 1) and
            lDK.detail_id = @detail_id and
            lDK.varKey in (select * from cte)
        group by
            varKey
        )
    select
        lDK.*
    from
        qa.detail_keys lDK
        inner join cteIncludeKey iK on
            lDK.varKey = iK.varKey and
            lDK.row_id = min_row_id
    where
        session_begin = @session_begin and
        (is_include = 1 or is_include = 4 and @include_distance = 1) and
        detail_id = @detail_id
    );

Insert statement using above function:

/* QA Records */
insert into qa.load_table
    (
    is_qa,
    varKey,
    store_id
    )
select 
    convert(bit, 1) as is_qa,
    varKey,
    case @is_grouped when 1 then store_id else -1 end as store_id
from
    qa.fn_session_include_keys(@session_begin, @list_detail_wk, 0)
order by
    varKey,
    store_id;

Paste the Plan link

  • 1
    I would try putting the index back, and updating the statistics after the truncation – James Jenkins Oct 7 at 17:58
  • I'd also drop the indexes after the truncate and re-create them after the row load if you have the potential for a large insert happening. – LowlyDBA Oct 7 at 18:05
  • And don't expect to see the same performance in general between a tempdb table and a normal one (esp if you're in Full Recovery Mode). – LowlyDBA Oct 7 at 18:08
  • Previously I have received suggestions to the contrary, that you shouldn't be calling Update Statistics in a loop on a table, or realistically in a procedure at all (aside from a maintenance procedure). I believe this came after I tried using Update Statistics in a prototype, and found little benefit (execution plans frequently estimated only 1 row, when I knew there to be 10k-50k). – Solonotix Oct 7 at 18:25
  • Can you also include the slow queries and their plans (brentozar.com/pastetheplan) – LowlyDBA Oct 7 at 19:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.