September 23, 2008 at 7:40 am
Hi there, does any one know if there is there a DMV which tracks the counts of DML (insert, update, delete) statements per table? SQL 2005.
If not this any good ideas on how to measure this?
Thanks in advance.
September 23, 2008 at 8:51 am
I don't think there's one to do this per table.
You could use SQLTrace to do this. What's the intention? See a level of activity per table?
September 23, 2008 at 9:03 am
Hello, we are planning to use transactional replication for some high throughput databases. I wanted to gather info on the DML of various tables to help us decide whether or not they should be included in the replication.
September 23, 2008 at 9:25 am
There's no DMV that tracks that. You can run profiler or you can put a trigger on to the tables to count the changes made.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 23, 2008 at 9:41 am
Ironically enough though you will be able to see most of that information once you have those tables as replicated articles. 😛
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
September 23, 2008 at 9:48 am
Couldn't you do something with sys.dm_db_index_usage_stats? It does not separate updates/deleted/inserts but has a user_updates column and a system_updates column that I assume would be the number of times the index has changed. You may not be able to, but it might be worth investigating. Although if Steve and Gail didn't mention it, it probably is not the correct information.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
September 23, 2008 at 9:53 am
You can, however if a column isn't indexed a change to it won't reflect in that DMV. I don't know if, for the purposes of that DMV, all the columns in the table are considered part of the cluster or not.
I also don't know if an insert of 1000 rows would count as 1 change or 1000. I suspect 1.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 23, 2008 at 10:00 am
I was wondering the same about the clustered index or the heap (some people still don't always have a clustered index). That's why I mentioned that it might be worth investigating. It would certainly have less impact than running a trace or triggers.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
September 23, 2008 at 10:24 am
Jack Corbett (9/23/2008)
I was wondering the same about the clustered index or the heap (some people still don't always have a clustered index). That's why I mentioned that it might be worth investigating. It would certainly have less impact than running a trace or triggers.
Indeed, and it should give a fair indication or relative activity, even if it's not completely accurate.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 23, 2008 at 10:24 am
Jack and Gail Thankyou for your replies. sys.dm_db_index_usage_stats is a very good starting point for this as all of the transactions are very small and all table are indexed. Appreciate your help 🙂
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply