How to measure the number of database writes on SQL Server 2008 R2

Question

How would I measure the number of database writes on SQL Server 2008 R2

For example, how many updated rows and how many new rows in a database over a period of time (1 day, 1 week or 1 month)?

Answer 1

I would look at using extended events. Truthfully I have not worekd with these and it's one of those things I have not sat down and tried to learn...yet. So I cannot give you an exact example of how you would impliment with your example.

The advantage to these over trace files is no performance hit. They were specifically designed with performance in mind. So letting it collect information over a long period should not be a problem. You can check out using it for monitoring system activity here. Jonathan Keyhayias did a good month-long series on using extended events. The first day gives a good overview of them here.

He also created a SSMS add-in that makes it a little easier to work with the Extended Events sessions.

Answer 2

An audit trail would meet your needs ... this article describes how to implement a simple one yourself.

http://www.simple-talk.com/sql/database-administration/pop-rivetts-sql-server-faq-no.5-pop-on-the-audit-trail/

The basic concept works well, but beware of performance implications.

Answer 3

Use the various counters provided through performance monitor. You can also access these programatically.

Answer 4

As you can see, there are many ways to skin a cat.

The DMV's can also be used to provide this information.

If it is for a specific table then you could simply take the figure at the start and end of the month and calculate the difference:

SELECT row_count FROM sys.dm_db_partition_stats 
WHERE object_id = OBJECT_ID('backupset');

As a side note: Time permitting, playing with a number of these methods will directly and indirectly increase your knowledge of SQL server internals.