0

I have several dbs in production and I'd like to have a high level overview of the activity.

Ideally I'd like to know:

  • number of users accessing the db per day
  • number of inserts per table per day
  • number of updates per table per day

I thought about a little script that would run a query to get the rowcount of each table, and then save it to another db. I could execute it at night.

That would only give the number of inserts, but at least would be a first step.

Is there some tool, logging device, system table, or something that would let me get this kind of info?

I have SQL Server from 2008 onward.

Improve this question
3
  • 1
    What edition of SQL Server? Also, are your users accessing the databases with a unique user, or through a shared application user?
    – HandyD
    Feb 20, 2019 at 0:27
  • 1
    Accessing is tough. SELECTs aren’t logged anywhere so you’d have to enable auditing or something to capture those. And if you have a query (or batch) that accesses 2 tables in db1, 3 tables in db2, and 2 tables in db3, how many “accesses” are those? Where? For updates/inserts you can like at sys.dm_db_index_usage/operational_stats - this doesn’t tell you how many rows but it does track insert/update/delete operations.
    – Aaron Bertrand
    Feb 20, 2019 at 0:38
  • It's a mix of several versions, most ancient one is sql server 2008
    – opensas
    Feb 20, 2019 at 1:35

1 Answer 1

Reset to default
2

To track the inserts and updates, you can use the below query which will collect the SUM of inserts, updates and deletes against the HEAP or CLUSTERED INDEX for each user table in the database.

Log this to a table and then query the deltas over time to see the inserts\updates\deletes per day. NOTE: This would have to be run against each database you wish to monitor as these DMVs are database-scoped.

USE [Database]
GO
SELECT 
    DB_NAME() AS [DATABASE NAME],
    SUM(A.LEAF_INSERT_COUNT) AS INSERTS, 
    SUM(A.LEAF_UPDATE_COUNT) AS UPDATES, 
    SUM(A.LEAF_DELETE_COUNT) AS DELETES
FROM
    SYS.DM_DB_INDEX_OPERATIONAL_STATS (db_id(),NULL,NULL,NULL ) A 
    INNER JOIN SYS.INDEXES AS I ON I.[OBJECT_ID] = A.[OBJECT_ID] AND I.INDEX_ID = A.INDEX_ID
WHERE  OBJECTPROPERTY(A.[OBJECT_ID],'IsUserTable') = 1
    AND I.INDEX_ID IN (0,1)

The user access is a little harder. If your users access the databases through an application, and that application uses a single login for SQL Server access, then you can't just track logins to determine user access. You would need to track this via your application.

If you're using individual logins, then you could use a login trigger to add a record to a tracking table to record the user, login time and initial database. See the documentation for more info. A good example can be found here. This is actually for logging sysadmin logins, but could easily be modified for your purpose.

Improve this answer
3
  • Great tip, there are different approaches to how the logins are treated, so I understand that it will be harder to get the user count in a general way, but getting how many times anybody logs to the db would be pretty useful
    – opensas
    Feb 20, 2019 at 1:30
  • I'm doing a few quick tests, I guess the stats counters reinit when the server restarts, right???
    – opensas
    Feb 20, 2019 at 3:00
  • 2
    @opensas that’s right, you’d have to capture things frequently because it doesn’t persist beyond restarts.
    – Aaron Bertrand
    Feb 20, 2019 at 3:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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