Friday, January 14, 2011

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]
--Add new job with name LoadCounter
EXEC  msdb.dbo.sp_add_job @job_name=N'LoadCounter',
            @category_name=N'[Uncategorized (Local)]',
            @owner_login_name=N'YourLoginNameHere', --Provide your own login name here
            @job_id = @jobId OUTPUT
select @jobId
EXEC msdb.dbo.sp_add_jobserver @job_name=N'LoadCounter', @server_name = N'YOURserverNAMEhere'--Provide your datbase server name here
-- Create job setp to insert counter record from sys.dm_os_performance_counters
USE [msdb]
EXEC msdb.dbo.sp_add_jobstep @job_name=N'LoadCounter', @step_name=N'LoadCounter',
            @os_run_priority=0, @subsystem=N'TSQL',
            @command=N'INSERT  INTO dbo.LoadCounter ( cntr_time, cntr_value )
        SELECT  GETDATE() AS cntr_time,
        FROM    sys.dm_os_performance_counters
        WHERE   counter_name = ''Batch Requests/sec''',
            @database_name=N'YourDatabaseNameHere', --Provide Your Database Name here
-- Create Schedule
USE [msdb]
DECLARE @schedule_id int
EXEC msdb.dbo.sp_add_jobschedule @job_name=N'LoadCounter', @name=N'LoadCounter',
            @freq_subday_interval=10, -- exectue after every 10 Minutes
            @active_end_time=235959, @schedule_id = @schedule_id OUTPUT
select @schedule_id

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


  1. That is what i was looking for so loooooooooooong. Thanks for sharing

  2. Its really a nice script

  3. i have around 50 databases on my server. How do i calculate the peak times for all databases ?


All suggestions are welcome