Just because we are a year or two away from the initial release of Dynamics CRM v4 does not mean that we are past some of the long ago solved problems.
Needless to say here is a item that you might want to look into if you are in "Database Cleaning Mode" or if you are having performance issues with a Dynamics CRM v4 footprint.
1) Run cleanup scripts to address existing Database problems!
Warning (probably unnecessary but): Before running any SQL Cleanup scripts backup the database in case you run into problems.
Note: You might want to set the Max Degree Of Parallelism option greater than Zero (1 is just fine) if you are running a multi CPU SQL Server. This option is under the Advanced options of your SQL Server. This one is a bit debatable among the DBAs and CRM experts. On small queries you want to define resources so the system doesn't burn up resources figuring things out and on more complex queries the benefits exist to let the system figure it out.
Now the next question you are going to ask is WHERE ARE THESE Cleanup scripts. Good Question!
Right now most teams (particularly in the larger CRM footprints) have their own or perhaps it is better to consider the partners supporting your CRM footprint. They have their own utilities, tips and tricks for great maintenance as well.
So where do you start?
Start here: An easy way to identify if you are running into an old issue with the AsyncOperationBaseTable is by checking how many records are in this table. Run a simple Count(*) and checking how many records you have of OperationType = ‘10’ and Succeeded/Completed.
If you do have this issue you can go back and check out Ben's blog and/or do a bit of Binging.