3

I'm using Change Tracking in SQL2008 R2 and seeing a very odd behaviour when trying to determine rows affected in a batch, a stored proc takes ~30 seconds to run when using a parameter value, but when I put the literal values in the call to the CHANGETABLE function it returns in <1s.

A call to the following takes ~30s:

DECLARE  @sync_last_received_anchor BigInt;
DECLARE  @sync_new_received_anchor BigInt;

SET @sync_last_received_anchor = 272361;
SET @sync_new_received_anchor = 273361;

SELECT [Customer].[CustomerID]
FROM dbo.tblCustomer AS [Customer] WITH (NOLOCK) 
    INNER JOIN CHANGETABLE(CHANGES [REDACTED].[dbo].[tblCustomer], @sync_last_received_anchor) AS [theChangeTable] 
    ON [theChangeTable].[CustomerID] = [Customer].[CustomerID]
WHERE ([theChangeTable].[SYS_CHANGE_OPERATION] = 'U' 
    AND [theChangeTable].[SYS_CHANGE_VERSION] <= @sync_new_received_anchor
)

However changing the CHANGETABLE line, as below, reduces it to ~1s.

    INNER JOIN CHANGETABLE(CHANGES [REDACTED].[dbo].[tblCustomer], 272361) AS [theChangeTable]

As we're running SP1 I presume the patch released in SQL2008 CU4 for CHANGETABLE being slow has been fixed (http://support.microsoft.com/kb/2276330).

I'm at a loss though why changing a parameter to a literal value would make so much difference?

Craig Parsons
  • 137
  • 1
  • 1
  • 4
  • 1
    Can you show us the execution plans? – Igor Borisenko Jan 19 '12 at 13:35
  • @Igor Of course - not sure of best format to share them in, so have exported as XML and changed the Schema name to [REDACTED] as it identifies our client. Version with Literal (number) in downloadable from [link](http://dl.dropbox.com/u/9443257/WithLiteral.sqlplan) and with Variable (paramater) [link](http://dl.dropbox.com/u/9443257/WithVariable.sqlplan). Thanks. – Craig Parsons Jan 19 '12 at 14:40

1 Answers1

4

It is likely that the stored procedure is doing parameter sniffing - i.e. it thinks the values you supplied are a good match for a plan it has already cached and it it isn't a good match at all.

There are multiple articles on how to approach this issue, one you can test and try on a DEV environment would be this:

http://blogs.msdn.com/b/axperf/archive/2010/05/07/important-sql-server-change-parameter-sniffing-and-plan-caching.aspx

Jimbo
  • 2,529
  • 19
  • 22
  • Spot on, that exactly what it was. I figured this out yesterday afternoon, but because I've a low reputation I couldn't answer my own question! If I added an OPTION (RECOMPILE) to the statement with parameters then I got the same performance. Thanks guys! – Craig Parsons Jan 20 '12 at 08:30