High CPU & Memory usage after enabling Change Tracking.

Question

Hi,

There is a need to enable change tracking in one of the application databases. This database is having close to 3000 tables and change tracking is to be enabled for nearly 1000 tables. After enabling change tracking, we made a performance analysis and observed high CPU and memory usage.

Is this the behaviour of CT or any db settings to be enabled to get better performance?

Answer 1

Yes it is predictable , why do you enable  CT on 1000 tables?

---

Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

MS SQL optimization: MS SQL Development and Optimization
MS SQL Blog: Large scale of database and data cleansing
MS SQL Consultants: Improves MS SQL Database Performance

Answer 2

Sad...

My requirement is like that.

Alternatively we wrote triggers to track the DML changes and amazingly performance degradation was NOT this much.

Any clue to improve the performance of CT?

Answer 3

trigger kills the performance of the server 

---

Ramesh Babu Vavilla MCTS,MSBI

Answer 4

What are you trying to capture? Is that possible to use server side trace file?

---

Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

MS SQL optimization: MS SQL Development and Optimization
MS SQL Blog: Large scale of database and data cleansing
MS SQL Consultants: Improves MS SQL Database Performance

Answer 5

Actually, I am trying to identify the incremental change data set for the identified tables. My requirement is to move the incremental change data set into a file (like CSV).

Can you please explain, what is server side trace file? Is it advantageous over CT?

Answer 6

http://vyaskn.tripod.com/server_side_tracing_in_sql_server.htm

>>Actually, I am trying to identify the incremental change data set for the identified tables.

Can you show an example?

---

Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

MS SQL optimization: MS SQL Development and Optimization
MS SQL Blog: Large scale of database and data cleansing
MS SQL Consultants: Improves MS SQL Database Performance

Answer 7

Once CT is enabled for a table, row versioning is automatically done by sql server. By using this info, i can provide "From Version" and identify what is the DML operation performed on the table (Insert /Update/Delete) after the given version. I am moving this data set into a file.

Answer 8

You can find this info by using  Profiler or Extend Events.

---

Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

MS SQL optimization: MS SQL Development and Optimization
MS SQL Blog: Large scale of database and data cleansing
MS SQL Consultants: Improves MS SQL Database Performance

Answer 9

Profiler / Traces are good in debugging activities. Can they act as good tool in my requirement?

My requirement is a kind of ETL operation, where it primarily depends on new / modified data.

Answer 10

Change tracking adds overhead, but it is mostly disk IO.  It reads the log file and logs the changes, not the tables directly.  What do your performance counters show for disk IO?

From what you describe, if you can change the tables, I would recommend adding a "LastModifiedDate" field and use that for your incremental changes.

However, if you cannot and the performance of triggers and change tracking is unacceptable, I would recommend using Transactional replication to another server/instance, and installing triggers or change tracking on the replicated copy.  Then you are not affecting the source server.

Answer 11

There is a need to enable change tracking in one of the application databases. This database is having close to 3000 tables and change tracking is to be enabled for nearly 1000 tables. After enabling change tracking, we made a performance analysis and observed high CPU and memory usage.

Is this the behaviour of CT or any db settings to be enabled to get better performance?

Yes it is the behavior of CT when applied to thousands of tables, millions of rows, and gigabytes of data.  

How could it not be!

This is a plague in the database field these days, users get the bright idea that they want to see a complete history of the universe, and they send it down as a "requirement", and it only slowly dawns on the dba's that this is horribly expensive - or simply impossible.

So, just make sure you run all the numbers, how many rows and gigabytes you're going to produce every day, IF your server is strong enough to do it at all.  Which it might not be.

This is especially bad at ETL time, when most ETL processes already take the server to 100% of capacity.

Josh

ps - back in the day, if there was a valid business reason to track changes to data, and there frequently is, this was built in at the applications level from day one.  To see people trying to retrofit it like this at the systems level, is almost always, well, a shock to everyone involved, let's put it that way.

Answer 12

hmm...

smart answer to put blame game on the other side. Let me ask you one question. If CT is not capable enough to handle such volumnious tables, it should have been documented.  http://msdn.microsoft.com/en-us/library/cc280565(v=sql.105).aspx 

Low overhead to DML operations.

Synchronous change tracking will always have some overhead. However, using change tracking can help minimize the overhead. The overhead will frequently be less than that of using alternative solutions, especially solutions that require the use triggers.

Above is the advantage of using CT, as per Microsoft. It says, the performance overhead is better than using triggers. But in reality it is not so. On reading these things only, we have enabled CT.

I appreciate your effort in justifying things but sorry to say that you lack in providing solution.

Answer 13

I appreciate your effort in justifying things but sorry to say that you lack in providing solution.

Not only have I not provided a solution, but it may be there is no practical solution currently based on SQL Server, that's why you have to run the numbers before talking any further.

Actually, all you ever said is that you have high CPU and memory usage.  Well, so what?  If you're getting the data you want, that's not a problem.  However, I read that to say that it was slowing down the rest of your system unacceptably.  Is that the case?

There is a quick line I've read about CDC, whether it was in BOL or in secondary documentation, that suggested it may not work well in ETL environments.  It was a huge understatement!  However, again, it depends on data volumes.  ETL might be three lines of data to one table, and then CDC would have no problem, nor of course would the somewhat lighter CT (and of course CT is only lighter until you want to see WHAT has changed, if you go back and compare the data to generate the deltas, you're just doing your own CDC).

What I'm really interested in, is whether these kinds of GLOBAL CHANGE requirements can or should EVER be done at the systems level.  There is someone new on this support group every month, maybe every week, trying to do them.  Neither CT nor CDC is meant for such heavy use.  There have been third-party packages around for years for "audit" that automatically build triggers in all your tables and put the deltas wherever you want them (actually, have you looked at the audit features in SQL Server?  Might be what you want and lighter weight they automatically output to external files).  But they don't really scale, either.  It's not their fault, they do what they do.  It is the PROBLEM that is the problem!  Or in other words, the requirements are unrealistic.

Speaking of which I wonder what your next step would be.  Most people given this assignment are also asked to archive the change logs, which isn't too hard but takes up a LOT of space.  And THEN we get the REAL problem: once you've archived them all in a giant history database ("warehouse"), building terabytes of data per year, THEN they want to process all that data efficiently.  THAT turns out to be virtually impossible within SQL Server.  You need some massively scalable techology and/or "Big Data" or "NoSQL" kinds of approaches.

So, there is no magic technological solution for you right now, and the likely next steps - are even harder!

Good luck.

Josh