Sql Server: How to Figure out Peak/Off-Peak Hours of Production Databases

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.