Strange behaviour of select query with Join on CHANGETABLE
Hi Guys, While working towards optimization of a particular procedure in our application, I located the query which was taking around 60% of the whole batch. Then I tried to run that particular query separately and observed some strange behaviour. The query is as below; **DECLARE @assgnmentId INT,@changeID INT** **SET @changeID = 16548600** **SET @assgnmentId = 125** **SELECT** **1** **FROM cad.Assignment asi WITH(NOLOCK)** **INNER JOIN** **CHANGETABLE(CHANGES cad.Assignment, @changeID) AS liAsi** **ON asi.AssignmentID = liAsi.AssignmentID** **WHERE asi.AssignmentID = @assgnmentId** The above query takes few minutes to complete while it fetches simply 1 record with 1 column having value 1. Note that the join being with changetable is on identity column. However, If I simply select "***" instead of "1", the query runs without any time (not even 1 second). I searched a lot against it and found some texts on parameter sniffing (that may be the case with original query in our procedure) and used "OPTION (OPTIMIZE FOR (@assgnmentId = NULL))" and now the query works well with both "*" and "1". i.e. DECLARE @assgnmentId INT,@changeID INT SET @changeID = 16548600 SET @assgnmentId = 125 SELECT **1 -- * also works** FROM cad.Assignment asi WITH(NOLOCK) INNER JOIN CHANGETABLE(CHANGES cad.Assignment, @changeID) AS liAsi ON asi.AssignmentID = liAsi.AssignmentID WHERE asi.AssignmentID = @assgnmentId **OPTION (OPTIMIZE FOR (@assgnmentId = NULL))** Note that this is not working with "OPTION (RECOMPILE)". The sys.syscommittab table appears to have more than 80000000 records while the change tracking table for our "cad.Assignment" table seems to have less than 2000 records. While I was inspecting the actual execution plan of our procedure (and even this long running query), it was seeking and index on "sys.syscomittab". In our database the retention time for change tracking is 2 days with auto cleanup on. I saw some texts that suggests in sql server 2008 there were some problems in clean up of syscomittab table, but we are using SQL server 2012, so I think it should be ok as I can't see anything relevant to 2012. Can you guys guide me on how to explore; what's causing this? I have a solution of using OPTION clause, but I just don't know how its working as it doesn't seems to be parameter sniffing issue (the problem was getting replicated with literal values on actual query). Note that this is not the case with other change tables. Thanks, Yudhbir
There are a number of issues here that could be causing poor performance. First up, you have a table valued function. Is it a multi-statement table valued function? If so, those are notorious bottlenecks for performance. Since they use table variables they have no statistics and therefore the optimizer makes poor choices in implementing them. It sounds like you have a lot of data movement. Are you keeping your statistics up to date manually or just relying on the auto-update process? If you're counting on the auto-update process your stats could be way out of line. Getting them updated, possibly with a full scan will be helpful. You do know that putting the nolock hint on tables can lead to bad data being returned. Not only can you get dirty reads, "dog" when it should be "cat", but because of page splits and rearrangements you can miss rows or duplicate rows of data in your result sets. Instead of using nolock, examine whether your system can support snapshot isolation levels. That will reduce your locking contention without the danger of bad data. You might consider using OPTIMIZE FOR UNKNOWN instead of setting it to a NULL value. You'll get the same averaged sampling of the statistics. I couldn't say more without seeing the execution plan.
Hi Grant, Thanks for you valued time. Meanwhile I am more close to original problem. I am aware of OPTIMIZE FOR UNKNOWN, but think that the query hints should be used when nothing is left. I am proposing below type of query which is working quite quickly. -- Check if any of the assignments have changed IF EXISTS ( SELECT * --Leave it as * and not change to 1 FROM CHANGETABLE(CHANGES cad.Assignment, @changeID) AS ct WHERE ct.AssignmentID = @assgnmentId ) BEGIN SET @assignmentsChanged = 1 END Unfortunately, the problem is very intermittent, and was getting reproduced only one of our test machine two days back. The function I am using is not my own function, but the one Microsoft has provided for change tracking functionality. I will definitely post the query plans once able to reproduce the same again. However, the query plans differs simply on an index scan in 'Sys.SysCommitTab' table which is an internal sql server table. This table holds an entry for each committed transaction which manipulates any change tracked table. Moreover another problem is that 'Sys.SysCommitTab' table is growing steadily (containing more than 1,80,00,000 records). We have set up a retention period for "2 Days", but this is not cleaning up this table. A Known issue from Microsoft (Refer to
http://support.microsoft.com/kb/2446860). We can't use this fix as it is intended for SQL server 2008 and we are using 2012, (but the database have been configured to run on 2008 configuration). I think you can help me by looking into change tracking functionality of SQL server. I ran DBCC CHECKDB command in my database and it pointed out that "sys.syscommittab" table is corrupt. At the moment I am running DBCC CHECKDB WITH data loss option to get this fixed. :(