SQL 2008 Change Data Capture Query Performance Issue (SP vs. straight query)

Question

Before I open a ticket with Microsoft Support, I thought I'd try the community!

I have an application in development for which we are using Change Data Capture in SQL 2008 R2 (developer edition, currently). For some particularly complex queries, we wanted to wrap the queries into stored procedures, exposing common parameters, to avoid complexity in the client (the usual argument)...

In any case, what we've identified is that the following statement, as a standalone query, will run in about 3-5 seconds, regardless of the boundary conditions, whereas the exact same statement, as a stored procedure, jumps to 1.5 minutes to produce the same results. In addition, the SP version while running seems to switch user identities several times over the course of execution... Also, during execution of the SP, the CPU usage spikes.

Any thoughts?

The Query:

DECLARE @fromlsn BINARY(10),
    @tolsn   BINARY(10),
    @NodeID  varchar(6)
SET     @NodeID = '123456',
        @fromlsn = 0x000017E6000001AC0041,
        @tolsn = sys.fn_cdc_get_max_lsn()

DECLARE @min_lsn_TransactionDate BINARY(10),
        @min_TransactionDate smalldatetime

SELECT  @min_TransactionDate = MIN(TransactionDate)
FROM    cdc.fn_cdc_get_net_changes_dbo_tblOrders(sys.fn_cdc_increment_lsn(@fromlsn),@tolsn,'all with merge') 
WHERE   _NODEID_=@NodeId and __$operation<>1

SELECT  @min_lsn_TransactionDate = MIN(__$start_lsn)
FROM    cdc.dbo_tblOrders_CT with (nolock)
WHERE   _NODEID_=@NodeId
    AND TransactionDate=@min_TransactionDate

SELECT   Table1.TransactionDate
    ,Table1.OrderNumber
    ,Table1.SequenceNum 
    ,Table1.ItemNumber
    ,Table1.Quantity
    ,Table1.Price
    ,Table1.ExtPrice
FROM          cdc.fn_cdc_get_net_changes_dbo_tblOrders(sys.fn_cdc_increment_lsn(@fromlsn),@tolsn,'all with mask') Table1
WHERE   Table1._NodeID_=@NodeId
    AND (   Table1.__$operation=2
         OR (   Table1.__$operation=4  
            AND  ( sys.fn_cdc_is_bit_set(9,Table1.__$update_mask)=1 
                   OR sys.fn_cdc_is_bit_set(10,Table1.__$update_mask)=1 
                 )
             )
        )

And the associated Stored Procedure:

CREATE PROCEDURE testtesttest 
        @fromlsn BINARY(10),
        @tolsn   BINARY(10),
        @NodeID  varchar(10)
as 
DECLARE @min_lsn_TransactionDate BINARY(10),
        @min_TransactionDate smalldatetime

SELECT  @min_TransactionDate = MIN(TransactionDate)
FROM    cdc.fn_cdc_get_net_changes_dbo_tblOrders(sys.fn_cdc_increment_lsn(@fromlsn),@tolsn,'all with merge') 
WHERE   _NODEID_=@NodeId and __$operation<>1

SELECT  @min_lsn_TransactionDate = MIN(__$start_lsn)
FROM    cdc.dbo_tblOrders_CT with (nolock)
WHERE   _NODEID_=@NodeId
    AND TransactionDate=@min_TransactionDate

SELECT   Table1.TransactionDate
    ,Table1.OrderNumber
    ,Table1.SequenceNum 
    ,Table1.ItemNumber
    ,Table1.Quantity
    ,Table1.Price
    ,Table1.ExtPrice
FROM          cdc.fn_cdc_get_net_changes_dbo_tblOrders(sys.fn_cdc_increment_lsn(@fromlsn),@tolsn,'all with mask') Table1
WHERE   Table1._NodeID_=@NodeId
    AND (   Table1.__$operation=2
         OR (   Table1.__$operation=4  
            AND  ( sys.fn_cdc_is_bit_set(9,Table1.__$update_mask)=1 
                   OR sys.fn_cdc_is_bit_set(10,Table1.__$update_mask)=1 
                 )
             )
        )

Script to execute the SP:

DECLARE @fromlsn BINARY(10),
    @tolsn   BINARY(10),
    @NodeID  varchar(6)
SET     @NodeID = '123456',
        @fromlsn = 0x000017E6000001AC0041,
        @tolsn = sys.fn_cdc_get_max_lsn()

exec testtesttest @fromlsn,@tolsn,@NodeID

As indicated in the text above, as a Query, it takes abuot 3-5 seconds (in Management Studio). As a Stored Proceure, 1.5 minutes. As a query via the .Net framework providor (System.Data.SqlClient), 1.5 minutes. As a query via the OleDb SQLNCLI10 provider, 3-5 seconds. As an SP via either Framework or OleDb, 1.5 minutes.

Any thoughts?

Answer 1

My money would be on a bad query plan in the cache. Try either flushing the procedure cache (not on a live system!) or using the OPTION (Recompile) in the SP to see if that helps