Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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)?

share|improve this question

migrated from stackoverflow.com Sep 9 '11 at 14:51

This question came from our site for professional and enthusiast programmers.

    
Is this a custom fork of SQL Server or something? 2009 is not a version I am aware of. Also, when you say updated or new rows, compared to what? –  JNK Sep 8 '11 at 15:28
    
Doesnt a simple trace on SQL Profiler work? –  LocustHorde Sep 8 '11 at 15:30
    
@JNK Thanks - I have corrected the edition –  Robert Sep 8 '11 at 15:35
    
@LocustHorde - I would not want to run the profiler for a month to collect the data –  Robert Sep 8 '11 at 15:35
1  
@robert are you looking for all tables, or just specific ones? –  SqlACID Sep 9 '11 at 15:27

4 Answers 4

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.

share|improve this answer

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.

share|improve this answer

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

share|improve this answer
    
The 'Physical Disk' counter would not tell me how many updated rows and how many new rows. –  Robert Sep 9 '11 at 9:37

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.