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

Before opening a ticket with Microsoft support, I thought I would try the community!

I is an application for development, for which we are using data capture changes in SQL 2008 R2 (developer version, currently). For some special complex questions, we want to wrap the question in the stored procedures, to expose the common parameter, to avoid complexity in the client (general argument) ...

In any case, What we have identified is that the following statement, as a standalone query, will run in approximately 3-5 seconds regardless of the boundary conditions, while the same statement, as a stored procedure To produce Inam jumps to 1.5 minutes. In addition, the running SP version switches the user identity several times during execution ... Besides, during the execution of SP, CPU Usage Spike

Any thoughts?

Question:

  Binary @fromlsn declaration (10), binary (10), @NodeID varchar (6) set @NodeID = '123456' @tolsn, @ fromlsn = 0x000017E60000010001, @tolsn = sys.fn_cdc_get_max_lsn () Announcement @min_lsn_TransactionDate binary (10), @min_TransactionDate selection smalldatetime @min_TransactionDate = Min (TransactionDate) cdc.fn_cdc_get_net_changes_dbo_tbl From Orders (sys.fn_cdc_increment_lsn (@fromlsn), @ tolsn, 'All With merge ') WHERE _NODEID _ = @ NodeId and __ $ operation & lt; & Gt; 1 SELECT @min_lsn_TransactionDate = MIN (__ $ start_lsn) with cdc.dbo_tbl Ordrrs (nolock) WHERE _NODEID _ = @ NodeId and TransactionDate = @ min_TransactionDate SELECT Table1.TransactionDate, Table1 Kordr number, table 1. Succensoam, table 1 ItemNumber, Table 1. Quantity, Table 1. Price, Table 1 Extrapist for CDC Fan C D Si_get_net_chenj_ Dibi_tiblo Ordrrs (Saisfan Seedeedisi_inkrit_elsan (@ Fomelsan) @ Tulson, "all with mask ') Table 1 where Table 1. 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 associated stored procedure:

  binary (10 @fromlsn process testtesttest create), binary (10 @tolsn) @ Nodid varchar (10) DECLARE @min_lsn_TransactionDate BINARY (10), @MinMrrransenseDateSmallalttime SELECT @min_TransactionDate = cdc.fn_cdc_get_net_changes_dbo_tbl Orders (sys.fn_cdc_increment_lsn (@fromlsn), @ tolsn, 'with all merges') WHERE _NODEID_ = @ Noded and __ $ operation & lt; & Gt; 1 SELECT @min_lsn_TransactionDate = MIN (__ $ start_lsn) with cdc.dbo_tbl order (nolock) WHERE _NODEID _ = @ NodeId and transaction date = @ min_TransactionDate select table 1.TransactionDate, Table1.OrderNumber, Table 1. SequenceNum, Table1.ItemNumber, Table1.Quantity, Table1.Price, Table1.ExtPrice to cdc.fn_cdc_get_net_changes_dbo_tbl Orders (sys.fn_cdc_increment_lsn), @ tolsn, 'with all masks) Table 1 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)) )  

To execute a script SP:

  Binary @fromls n Announcement (10), binary (10) @tolsn, @NodeID varchar (6) set @NodeID = '123456', @fromlsn = 0x000017E6000001AC0041, @tolsn = sys.fn_cdc_get_max_lsn () testtesttest @ fromlsn, @ tolsn, @ NodeID Executive  

indicated in the text above, as a question in the form, it takes 3-5 seconds (in the management studio) As a stored procedure, 1.5 minutes as a query, 1.5 minutes through a .NET frame provider (System.Data. SQLLient). As a query through the OleDb SQLNCLI10 provider, 3-5 seconds through a framework or ODDB as an SP, 1.5 minutes.

Any thoughts?

My money will be in a cache on a bad query plan. Either try to flush process cache (not live on the live system!) Or use option (recompile) in SP to see if