Index Defrag Job

×
This job will help streamline your database speed. To utilize this feature, you must have v9.6 or later for both the website and Job Master. You will need to create a new script database using SQL, but we've included the steps about how to do this below. While this job is running, some of your indexes will be taken off-line, which can have a detrimental effect on the system, so we strongly recommend running this on off hours.
  • On your SQL Server, open SQL Server Management Studio
    • Right Click on Databases
    • Select New Database
      • Database Name: DBA_Scripts
      • Owner: this depends on your setup; it might be sa or a different account. Regardless, the dbuser that Jobmaster is using needs to have ownership rights of the dba_scripts db that is created.
      • Click on Add
    • Now that the database is created, click on the name on the Object Explorer
    • Click New Query

Open your website

  • System Management | System | Queries
    • Click on Add New Query image button
      • Description: Index Defrag
      • Notes: Birddog Query - Do Not Delete or Alter
      • Query: EXECUTE DBA_Scripts.dbo.dba_indexDefrag_sp @executeSQL = 1 , @printCommands = 1 , @debugMode = 1 , @printFragmentation = 1 , @forceRescan = 1 , @maxDopRestriction = 1 , @minPageCount = 8 , @maxPageCount = NULL , @minFragmentation = 1 , @rebuildThreshold = 30 , @defragDelay = '00:00:05' , @defragOrderColumn = 'page_count' , @defragSortOrder = 'DESC' , @excludeMaxPartition = 1 , @timeLimit = 360
        • NOTE: the @timeLimit = 360 is the time limit for the script to run in minutes. So, in this example, we've set this particular statement to run for a maximum of 6 hours.
      • Click on Save image button
  • System Management | Automation | Jobs
    • Click on Add Job image button
      • Select Execute Query on the Job Type DDL
      • Click on Add image button
    • You will be taken to /Enterprise/ExecuteQuery.aspx
      • On the Query DDL, select that Index Defrag query you created earlier.
      • This will result in the Description field automatically populating to Index Defrag
      • If you have any Job Groups set up, select the appropriate Maintenance one
      • Click on Edit Job Schedule image button
      • Edit Schedule popup appears
        • Frequency DDL should be changed to Weekly (or Monthly, depending upon your desires)
        • Change Day of Week / Time to something that won't negatively impact your business. Sunday at 1:00 AM is our general default recommendation.
        • Click on Save Job image button

From here, you are done. The job will run at the specified time. If you review the logging after this job has run, you should note a few things:

  • The log entry will tell you when it completed in the Event Time. For example, you might see 3/13/2014 4:46:05 AM.
  • In the Event Message, you will see the Duration. In the beginning, it will run for the entire length of time you specified in the @timeLimit part of the SQL query above until it stops, but as your indexes become healthier, it will stop before it reaches this time limit. If you never see it end before your time limit expires, you need to run this job more frequently, for longer periods of time or both. To start, we recommend running the job for 6 hours a week starting at 2am Sunday. If that never completes, start adding daily jobs that run for an hour or two everyday at 2am.  In our example that ended at 4:46 in the morning, we might see something like this: Duration - 04:44:52.9004930:-1 records processed.

The index defrag stored procedure (dba_indexDefrag) stores its progress in a log file (dba_indexDefragLog). Below is a query that will return any problem logs which we recommend that you schedule it to email the dba a few hours after the index job should complete. If you are running index defrag jobs every day, just set it to run once a day. This query returns errors and indexes that took longer than 10 minutes to rebuild and should probably be reviewed to make sure that aren't growth tables that need to be purged with the purge table job. Here is the query:

 
select databaseName,objectName,  indexName,fragmentation,page_count,dateTimeStart,dateTimeEnd,durationSeconds,errorMessage
from dba_indexDefragLog with(nolock)
where
--Look one day back
dateTimeStart>=DATEADD(day, -1, GETDATE())
and
(
--There was an error, so durationSeconds is -1
durationSeconds=-1
or
--Something was output to errorMessage
ISNULL(errorMessage,'')<>''
or
--Took longer than 10 minutes...we should review
durationSeconds >= 600
)
order by dateTimeStart desc
 
 
 *Please notice that this script runs against the server not a specific database. It will defrag the worst index on the server, no matter what database that it is in. If you have multiple databases on your SQL server you do NOT need to (nor should you) schedule multiple versions of this script to run at the same time.