>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.