Database Defragmenting and Reindexing

Usage and Config Platform
1

(imported topic written by SystemAdmin)

In 6.0 and 7.0, a default maintenance plan is created that will re-index the QUESTIONRESULTS, FIXLETRESULTS and ACTIONRESULTS tables every 6 hours. These tables each have a primary key constraint and a clustered index on them.

As an optimization, its possible to perform an index defragment operation instead of reindexing. There are some subtle differences between these operations:

  1. Defragmenting will not lock the database when it runs. BES Consoles, FillDB and any other database operations that are running will continue to run normally even while database defragmenting is occuring. Reindex a database table lcoks all other processes from using that table.

  2. Defragmenting will usually take longer to run than a reindex (estamate 1-2x longer). Defragmentation run time is proportional to the amount of fragmentation and the size of the table while reindex is proportional to the size of the table.

  3. Defragmenting does not update statistical counters in the database, reindexing will.

  4. Defragmenting isn’t able to defragment the index structure itself (only leaf nodes are defragmented) while reindex will defragment all structures on the table. Reindexing actually drops the clustered index and rebuilds it as well as defragment the leaf nodes.

  5. Defragmenting will skip pages if they are in use and not defragment them.

  6. Defragmenting may fail occasionally if the database is under heavy load or blocked at the time. Once a week failures are ok but if you see failures regularly there is likely a problem with the job.

Given the trade-offs between these two operations, you can optimize the database performance by performing the defragment operation during operational hours of BES (since it doesn’t lock the database) and run re-indexing operations in the off-hours to ensure the tables eventually get completely defragmented and statistics are updated.

I’ll attach a sql query which will create the following schedule. You should disable the default ‘BFEnterprise Results Table Reindexing Job’ prior to creating the new schedule as the old maintenance task is not needed and will collide with the new plan. You should also disable/delete the staggered windows described in this post (http://forum.bigfix.com/viewtopic.php?id=1153) if you made those modifications (the non-results table weekly re-index job can stay though).

Schedule created by ‘BFEnterprise daily reindex and defragging.sql’:

Midnight - Reindex QUESTIONRESULTS

2am - Reindex FIXLETRESULTS

4am - Reindex ACTIONRESULTS

6am, Noon, 6pm - Defragment QUESTIONRESULTS

8am, 2pm, 8pm - Defragment FIXLETRESULTS

10am, 4pm, 10pm - Defragment ACTIONRESULTS

A total of 6 maintenance jobs are created to implement the above schedule.

Finally, an important note for implementing this policy in 6.0. It’s possible for the defragment query to cause deadlocks to occur unless you update two stored procedures. I’ll attach the sql queries to update these stored procedures in the next post. This change made is included in BES 7.0 and you should not update these stored procedures if you are already using BES 7.0.

2

(imported comment written by SystemAdmin)

Here are the sql queries to update the stored procedures for 6.0 to prevent deadlocks.

The following stored procedures are affected:

  1. fetch_questionresultsAdmin

  2. fetch_fixletresultsAdmin

As a result of this change you may see the following behavior changes:

  1. Prevents deadlocks appearing in the BES Console (even without the defragment update).

  2. non-master BES Console Operators with large numbers of managed computers (over 5,000) may see faster BES Console load times.

3

(imported comment written by SystemAdmin)

second file.

4

(imported comment written by SystemAdmin)

Here’s an updated version for 7.0.7 and 7.0.9. It will try to delete the jobs prior to creating them so if you already created the previous versions you can just run the attached script to update them for 7.0.7.

http://support.bigfix.com/download/bes/misc/BFEnterpriseDailyReindexDefraggingg_7_0_7.sql

5

(imported comment written by SystemAdmin)

Created a new script for BES 7.1. The 7.0 defrag step would fail on the QUESTIONRESULTS table. This script removes previous versions of the defrag queries so you can just run it to replace older versions (use the SA account when running the script).

http://software.bigfix.com/download/bes/misc/BFEnterpriseDailyReindexDefragging_7_1.sql

6

(imported comment written by Lion014291)

Is there a script that works with 7.2.4.60. I attempted to run the 7.1 script and recieved the following errors

Msg 14262, Level 16, State 1, Procedure sp_verify_job_identifiers, Line 67

The specified @job_name (‘BFEnterprise QuestionResults Defragment Job’) does not exist.

Msg 14262, Level 16, State 1, Procedure sp_verify_job_identifiers, Line 67

The specified @job_name (‘BFEnterprise FixletResults Defragment Job’) does not exist.

Msg 14262, Level 16, State 1, Procedure sp_verify_job_identifiers, Line 67

The specified @job_name (‘BFEnterprise ActionResults Defragment Job’) does not exist.

Msg 14262, Level 16, State 1, Procedure sp_verify_job_identifiers, Line 67

The specified @job_name (‘BFEnterprise QuestionResults Reindex Job’) does not exist.

Msg 14262, Level 16, State 1, Procedure sp_verify_job_identifiers, Line 67

The specified @job_name (‘BFEnterprise FixletResults Reindex Job’) does not exist.

Msg 14262, Level 16, State 1, Procedure sp_verify_job_identifiers, Line 67

The specified @job_name (‘BFEnterprise ActionResults Reindex Job’) does not exist.

I hope I am doing this correctly or not I only ran the 7.1 script. Shuld I have run the previos version or just the 7.1 version

7

(imported comment written by SystemAdmin)

Ah, yes, the 7.1 script works for 7.2 too. Those error messages are expected the first time you run the script. It is trying to delete any existing jobs with the same name and since they don’t exist it gives an error message about it. If you look at your Jobs list you’ll see that the job actually got created despite the error messages.

So, you can safely ignore these messages, maybe there is a better way to do this to avoid the messages though…

8

(imported comment written by ktwatts91)

Will there be any updates to these scripts for 7.2.5.22 and beyond? Or are they tuned well enough now that they stand alone?

9

(imported comment written by SystemAdmin)

The scripts are good for 7.2.5.22. Beyond 7.2, you won’t need to use them though. We’re updating the default maintenance jobs that are created by our installers to be better.

10

(imported comment written by srsbesuser91)

Hey Tyler - I have upgraded my test environment to 8.0.584. I see in your latest post that these additional maintenance jobs are no longer needed “beyond 7.2”. Can you please confirm this.

Also, can you document a listing of the maintenance jobs (by name) that are expected to be installed with the 8.0 installer and the expected run frequency of each.

Thanks!

11

(imported comment written by SystemAdmin)

Confirmed, the expected maintenance job for 8.0 is named: “BFEnterprise Full Database Reindex”. It does a Reindex of all tables once a day at midnight.

The 8.0 upgrade will remove the 7.2 maintenance job and create the new one. If you’ve created any custom maintenance plans, those need to be removed manually after the upgrade.

We did a case study of database fragmentation over time and determined that once a day was the most optimal schedule. We also learned that some tables which we weren’t reindexing before (like COMPUTERS) would affect performance so they needed to be included, so we’re now reindexing all tables.

12

(imported comment written by hmkjr91)

Hi Tyler,

Given the statement in your last post –

“We also learned that some tables which we weren’t reindexing before (like COMPUTERS) would affect performance so they needed to be included, so we’re now reindexing all tables.”

Would this be something that would be advisable for those of us still running 7.2.5.22? If so, is that something you are willing to add to your script?

Thanks much!

13

(imported comment written by SystemAdmin)

I’m glad I found this post. I have just started looking into why some of our defragment jobs have been failing, and it looks like maybe they should not even be there anymore.

What I am seeing is this. We have 4 BFEnterpsie jobs on our SQL server. We have upgraded to version 8 by the way. The jobs I see are:

BFEnterprise ActionResults Defragment Job

BFEnterprise FixletResults Defragment Job

BFEnterprise Full Database Reindex

BFEnterprise QuestionResults Defragment Job

The first 2 are failing consistently. The other 2 appear to be running properly.

If I am reading this thread correctly, I should only have the “BFEnterprise Full Database Reindex”, and I can remove the 3 defragment jobs?

Is that correct? Or do I still have a problem that needs to be fixed?

Thanks!

14

(imported comment written by SystemAdmin)

Would this be something that would be advisable for those of us still running 7.2.5.22? If so, is that something you are willing to add to your script?

Yes, reindexing the entire database should be a good idea in 7.2 too. Here’s an export of the 8.0 job that you could replace the 7.2 job will. You should disable the 7.2 job and remember that when you update to 8.0, you’ll need to go back and disable this job otherwise you’ll have 2 full database reindex jobs and they’ll likely collide with each other.

http://software.bigfix.com/download/bes/misc/BFEnterprise_FullReindexPlan.sql

If I am reading this thread correctly, I should only have the “BFEnterprise Full Database Reindex”, and I can remove the 3 defragment jobs?

That’s correct, you should only have the Full Database Reindex job and you should now remove the other 3.

15

(imported comment written by gswaters91)

I know it has been a few months since the last post here but I tried running the 8.0 job export in my 7.2 environment and I received the following error:

Msg 8145, Level 16, State 1, Procedure sp_add_jobschedule, Line 0

@schedule_uid is not a parameter for procedure sp_add_jobschedule.

Does this job require a certain version of SQL Server?

16

(imported comment written by gswaters91)

Answered my own question … with a little more digging.

The posted example requires SQL 2008. My organization is still running SQL Server 2005.

The parameters for the stored procedure, sp_add_jobschedule, have changed from SQL2005 to SQL2008, so you need to change the script to match.

  • SQL 2005 uses @schedule_id (an integer) and SQL 2008 uses @schedule_uid (a uniqueidentifier)

I changed the following line:

@schedule_uid=N’325eb1de-a17a-45c8-98f9-1b10fe3fa3fe’

to this:

@schedule_id=3

Why did I pick 3? I did the following query:

select * from sysjobschedules

(on the MSDB database)

… and saw that 3 was available.

I used the following blog posting as my reference: