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

Posted on January 14, 2011, in Performance Tuning, Sql Server 2005, Sql Server 2008, Sql Server Management, TSQL Tips n Tricks. Bookmark the permalink. 2 Comments.

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

  2. >Its really a nice script

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: