Database Reindexing

Usage and Config Platform
1

(imported topic written by SystemAdmin)

I’ve been looking into database re-indexing lately and have come up with some optimizations we can make. I’ll attach to SQL scripts that will create new maintenance plans for the BFEnterprise database.

  1. Weekly Full Database Reindex 6_0.sql - Adds a weekly maintenance job that re-indexes all the database tables except the QUESTIONRESULTS, ACTIONRESULTS and FIXLETRESULTS tables. The job runs on Saturday at 11pm and shouldn’t take more than a minute or two to complete. Fragmentation in these tables shouldn’t cause performance problems but it couldn’t hurt to keep these tables optimized and reduce disk space caused by fragmentation, the TEXTFIELDS table in particular can be quite large and fragmentation could cause minor slowness. This maintenance task will only work correctly in BES 6.0.

  2. Daily Results Reindex.sql - Creates 3 maintenance jobs, each re-indexes a different RESULTS table. Each job runs four times a day but I’ve spread them apart so one runs every 2 hours. Normally, all three tables are re-indexed every 6 hours at the same time and this changes it so that a different table is re-indexed every 2 hours instead. You should disable the current maintenance job, ‘BFEnterprise Results Table Reindexing Job’, before adding these maintenance job. This job should work in all versions of BES up to 7.0.

The scheduling change made by #2 is designed to help in situations where reindexing all 3 tables at the same time takes over 5 minutes together and locking the database for this long is noticeable to users. By spreading out the jobs we minimize the amount of time the database is locked at any given time.

2

(imported comment written by SystemAdmin)

Script #2…

3

(imported comment written by PaulPhillabaum)

These seemed to have made a huge difference for console performance. Thanks!!

4

(imported comment written by jr6591)

Tyler,

Will these work on BES 7?

Can these be imported or do we have to create a job and just use the script?

Thx

5

(imported comment written by SystemAdmin)

The Daily Results Reindex script will work in 7.0 since the RESULTS tables haven’t changed but the Weekly Full Database Reindex will not since some of the tables have been removed and a few more added. I’ll post a 7.0 update soon…

You can just run the scripts attached here, they create the maintenance job.

6

(imported comment written by SystemAdmin)

To run the script, you can cut and paste it into SQL Query Analyzer or go to File->Open and select the file in query analyzer. If you are using sql 2005, you can do the same thing in the SQL Server Management Studio tool.

7

(imported comment written by jr6591)

I was Parse the query instead of executing it. How can i tell if it is scheduled as I don’t see it in my Jobs or Database Maintenance Plans in SQL Enterprise Mgr.

8

(imported comment written by SystemAdmin)

You might need to refresh the sql enterprise manager, sometimes it doesn’t show until you manually request to refresh the list.

Otherwise, did you get an error message when you ran the query or did it just say ‘Completed’?

9

(imported comment written by jr6591)

Thx. Yes, I was not refreshing the screen.

10

(imported comment written by SystemAdmin)

Here’s the updated ‘Weekly Full Table Reindex 7_0.sql’ document.

If you had this maintenance task set up in 6.0, you’ll need to delete the 6_0 version and replace it with this version for 7_0.

The ‘Daily Results Reindex’ job works in both 6.0 and 7.0 so you can leave that in place.

11

(imported comment written by StacyLee)

I’ve downloaded and the new scripts and disabled the old job. Here are my results based on a 12 GB Database.

New Jobs elapsed time:

BFEnterprise ActionResults Reindex Job - 00:02:24

BFEnterprise QuestionResults Reindex Job - 00:02:00

BFEnterprise FixletResults Reindex Job - 00:06:06

BFEnterprise Full Database Reindex Job 6_0 - 00:04:19

Console Load Time

  • 00:02:19 For Console Screen to Appear
  • 00:04:20 For Console to finish loading

(I didn’t time it before running the new jobs nor did i clear the cache)

It’s seems about the same for me for console load times. I’ll see if it has helped others.

Console Computer Specs:

-2 Dual Core 2.80 Ghz Xeon Processors

-4 GB RAM

-64 Bit Vista

-10K RPM Sata Drive, Defragged nightly

As a data point We’ve been running the Maintenance job which reindexes the 3 tables at one time. Here are the total times and times per step from the last 4 times it was run.

Previous Daily Job (runs every 6hrs)

Total time: 10:54 (8/10/2007 6pm)

Reindex Action Results - 00:02:27

Reindex Question Results - 00:02:14

Reindex Fixlet Results - 00:06:13

Total time: 10:15 (8/10/2007 12pm)

Reindex Action Results - 00:02:11

Reindex Question Results - 00:01:54

Reindex Fixlet Results - 00:06:08

Total time: 10:15 (8/10/2007 6am)

Reindex Action Results - 00:02:20

Reindex Question Results - 00:05:20

Reindex Fixlet Results - 00:06:11

Total time: 10:15 (8/10/2007 12am)

Reindex Action Results - 00:02:09

Reindex Question Results - 00:02:04

Reindex Fixlet Results - 00:06:15

12

(imported comment written by SystemAdmin)

I’ve run some tests recently that showed running this full database reindex job can really help BES Console load times and database insertions. I would highly recommend using this.

The 7_0 script will work for 7.0, 7.1 and 7.2.

13

(imported comment written by jr6591)

Where can I get the script from?

14

(imported comment written by SystemAdmin)

It is an attachment to post #10. You need to be logged in to see it.

15

(imported comment written by SystemAdmin)

We implemented this and so far it seems like we had a pretty nice performance increase on our console.

16

(imported comment written by SystemAdmin)

Hi Tyler - have these housekeeping tasks been included in the BigFix product/installation (we’ve got 7.2.4.60 at the moment) or is it still an extra task? I’m pretty unfamiliar with SQL db’s, do you have any, or know where I can get some, instructions for implementing your sql scripts?

Thanks,

Mark.

17

(imported comment written by taltrade91)

Hi, I am logged in, but don’t see the scripts. How can I download it ?

18

(imported comment written by Shembop91)

What would it take to have this written to take care of the pokey DSS Database as well?

19

(imported comment written by SystemAdmin)

Got anything for 8.0?

We are currently having issues with our BES server losing connection to SQL daily around midnight, which means it has to be scheduled. Only thing I found running at that time was the full datbase reindex job. I did a P2V on our BES server in order for me to troubleshoot on the virtual environment, and so far just disabling the job helps but has caused some side effects.

I’m no DB and our last DB left (position open!) so there’s not much I can do other then repair or reinstall our SQL server which is our same BES server.

So if you have any reindex scripts for 8.0, I’d love to try them in place of the current full database reindex we have.