1

I would like to analyse performance of one of the big tables in the database. There are only two types of operations on it. Inserts and reads. Once data is entered it is never updated.

I need to find when the table is updated least frequently (for example Saturday between 3pm-6pm). I need to also find when the table is read least frequently.

Structure looks like this:

dbo.Table1
    [Column1] [INT] IDENTITY(1,1) NOT NULL,
    [Column2] [VARCHAR](50) NULL,
    [Column3] [DATETIME] NULL CONSTRAINT 
    [Column4] [INT] NULL,
    [column5] [VARCHAR](255) NULL,
    [column6] [INT] NULL,
    [column6] [VARCHAR](MAX) NULL,
    [column7] [INT] NULL,

There is clustered index1 on column1 and non clustered index2 on column2 and 3 including column1

We use: SQL Server 2012

1

Judging by your question it isn't particularly important that you correlate a particular record to the time it was read/written from/to the table, only aggregated information for the whole table. As Aaron said, the easiest and least intrusive method would be to constantly capture the usage_stats DMV, but this will not be accurate as the view is reset under numerous scenarios.

If you want an accurate view of read/write activity, my suggestion would be to use a stored procedure alongside each insert/select. In either case you maintain a separate table which splits each day into certain chunks, and you update it with total or individual reads and writes, depending on your preference.

CREATE TABLE dbo.Table1Usage (
    timeSlotStart DATETIME UNIQUE NOT NULL,
    inserts BIGINT NOT NULL DEFAULT 0,
    reads BIGINT NOT NULL DEFAULT 0
)
GO

CREATE PROCEDURE dbo.Table1Activity
    @reads INT = 0,
    @inserts INT = 0
AS
    DECLARE @currentTimeSlot DATETIME,
            @timeSlotLengthInMinutes INT

    SET @timeSlotLengthInMinutes = 60
    SET @currentTimeSlot = DATEADD(MINUTE, DATEDIFF(MINUTE, 0, GETDATE()) / @timeSlotLengthInMinutes * @timeSlotLengthInMinutes, 0)

    IF EXISTS (SELECT TOP (1) 1 FROM dbo.Table1Usage WHERE timeSlotStart = @currentTimeSlot)
    BEGIN
        UPDATE  dbo.Table1Usage
        SET     reads = reads + @reads, inserts = inserts + @inserts
        WHERE   timeSlotStart = @currentTimeSlot
    END
    ELSE
    BEGIN
        INSERT  dbo.Table1Usage (timeSlotStart, reads, inserts)
        VALUES  (@currentTimeSlot, @reads, @inserts)
    END
GO

If you want to record individual read/write requests, then something like this that runs after each insert/select statement:

INSERT  dbo.Table1
    SELECT  [...]
    FROM    [...]

EXEC dbo.Table1Activity @inserts = 1

SELECT  [...]
FROM    dbo.Table1

EXEC dbo.Table1Activity @reads = 1

And if you want to record total rows read/written, then something like this:

INSERT  dbo.Table1
    SELECT  [...]
    FROM    [...]

EXEC dbo.Table1Activity @inserts = @@ROWCOUNT

SELECT  [...]
FROM    dbo.Table1

EXEC dbo.Table1Activity @reads = @@ROWCOUNT

You can obviously collect the sum of all the above and use a single statement:

EXEC dbo.Table1Activity @reads = @totalReads, @inserts = @totalInserts
| improve this answer | |
1

SQL Server doesn't keep track of this information. You'd have to take snapshots from sys.dm_db_index_usage_stats frequently enough that you can graph activity, and you also need to account for service restarts, index rebuilds, etc. which can remove rows from the DMV and set the activity back to zero (this may look on your chart like no activity but it really means it has been reset).

This is not a straightforward task. I think you should add more information to your question explaining why you need this information, because maybe there are other ways to accomplish your real goal (knowing when a table is read less frequently is not a goal, it's the path you've chosen to your goal).

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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