In every learning session of performance tuning we like to repeat one sentence “DON’T EXECUTE THIS IN PEAK HOURS” or you must wait for peak hours to execute a specific query. For example we should never execute REBUILD INDEX statement or FULL BACKUP DATABASE statement in peak hours of a production database.

But is there any way to find out these peak and off-peak hours for a production database. Performance counter SQL SERVER: SQL Statistics\Batch Request/Sec can be little helpful, but what if I want to create a graph report of work load for a specific production database.

Follow given steps to accomplish your goal.

1. Create a table to store work load data for next 24 hours or any other period of your choice

CREATE TABLE dbo.LoadCounter
    (
      cntr_time DATETIME,
      cntr_value BIGINT
    )

2. Create a job so LoadCounter table can be filled after every 10 minutes (or after interval of your own choice) and your are done.

Note:  Don’t forget to provide SERVER NAME, DATABASE NAME and LOGIN NAME for following statements at marked places

USE [msdb]
GO
--Add new job with name LoadCounter
DECLARE @jobId BINARY(16)
EXEC  msdb.dbo.sp_add_job @job_name=N'LoadCounter',
            @enabled=1,
            @notify_level_eventlog=0,
            @notify_level_email=2,
            @notify_level_netsend=2,
            @notify_level_page=2,
            @delete_level=0,
            @category_name=N'[Uncategorized (Local)]',
            @owner_login_name=N'YourLoginNameHere', --Provide your own login name here
            @job_id = @jobId OUTPUT
select @jobId
GO
EXEC msdb.dbo.sp_add_jobserver @job_name=N'LoadCounter', @server_name = N'YOURserverNAMEhere'--Provide your datbase server name here
GO
-- Create job setp to insert counter record from sys.dm_os_performance_counters
USE [msdb]
GO
EXEC msdb.dbo.sp_add_jobstep @job_name=N'LoadCounter', @step_name=N'LoadCounter',
            @step_id=1,
            @cmdexec_success_code=0,
            @on_success_action=1,
            @on_fail_action=2,
            @retry_attempts=0,
            @retry_interval=0,
            @os_run_priority=0, @subsystem=N'TSQL',
            @command=N'INSERT  INTO dbo.LoadCounter ( cntr_time, cntr_value )
        SELECT  GETDATE() AS cntr_time,
                cntr_value
        FROM    sys.dm_os_performance_counters
        WHERE   counter_name = ''Batch Requests/sec''',
            @database_name=N'YourDatabaseNameHere', --Provide Your Database Name here
            @flags=0
GO
-- Create Schedule
USE [msdb]
GO
DECLARE @schedule_id int
EXEC msdb.dbo.sp_add_jobschedule @job_name=N'LoadCounter', @name=N'LoadCounter',
            @enabled=1,
            @freq_type=4,
            @freq_interval=1,
            @freq_subday_type=4,
            @freq_subday_interval=10, -- exectue after every 10 Minutes
            @freq_relative_interval=0,
            @freq_recurrence_factor=1,
            @active_start_date=20110114,
            @active_end_date=99991231,
            @active_start_time=0,
            @active_end_time=235959, @schedule_id = @schedule_id OUTPUT
select @schedule_id
GO

3. Execute following query to see the results or you can create a report (or a graph) on this query. And easily find out that during which hours your database end users are working actively or just sleeping ;) .

SELECT  cntr_time,
        cntr_value - ( SELECT TOP ( 1 )
                                cntr_value
                       FROM     dbo.LoadCounter
                       WHERE    cntr_time < OuterTable.cntr_time
                       ORDER BY cntr_time DESC
                     ) AS BatchPerTenMin
FROM    dbo.LoadCounter OuterTable
ORDER BY cntr_time

Note: SQL SERVER Agent service must be running to execute your job after given intervals.