This topic has two parts:

  • Part I - goes over the fragmentation that occurs in a SQL database and how to create a maintenance plan to rebuild these fragmented indexes.
  • Part II - covers how to update query optimization statistics on a table or indexed view in the database. This can dramatically increase performance even when you have the Part I maintenance plan in place. This optimization is recommended if you encounter a significant pause when resetting an import or filing session.

Updates to this document and other 1099 Pro CS SQL Server Configuration Recommendations can always be found on our Wiki site: 1099 Pro CS SQL Server Configuration Recommendations.

Part I: Index fragmentation

We have identified several table indexes that need to be rebuilt due to extended use (without proper database maintenance) and resulting fragmentation. Moving forward, any maintenance plan should rebuild all indexes on a regular basis or rebuild the indexes as part of scheduled maintenance. If you experience other performance issues not addressed here, contact your DBA and account manager to help identify the issue.

The following is a sample maintenance plan on one of our servers. Make sure your SQL Server Agent service is enabled and running.

  1. Download the following file and execute it as a SA or SysAdmin account to create the necessary stored procedures:
    MaintenanceSolution.sql



  2. Create a SQL Server Agent Job to add the first step to run the DBCC Check.


    1. Example:
      EXECUTE dbo.DatabaseIntegrityCheck
      @DATABASES = '<database name here>',
      @CheckCommands = 'CHECKDB',
      @LogToTable = 'Y';

  3. Add the second step to rebuild/reorganize the database.

    1. Example:
      EXECUTE [dbo].[IndexOptimize]
      @Databases = '<database name here>',
      @LogToTable = 'Y'

  4. Choose your desired frequency. We have ours set to run weekly.



  5. Set notifications to your DBA or application admin/manager.


Sources referenced: 
Ola Hallengren
E-mail: ola@hallengren.com
https://ola.hallengren.com/https://ola.hallengren.com/downloads.html 


Part II: Query optimization statistics

At 1099 Pro, we strive to deliver the best possible performance for your Corporate Suite (CS) program. In addition to the recommendation in Part I, we’ve found more specific performance tuning for the CS SQL Database(s) that can be implemented in about 30 minutes. We strongly recommend implementing the following periodic scheduled process: Updating query optimization statistics on a tables or indexed views in database. For an introduction to this topic, read this Microsoft article: UPDATE STATISTICS.

Contact your 1099 Pro Account Manager if you're interested in scheduling an appointment to implement Query Optimization Statistics. A 1099 Pro DBA (Database Administrator) will guide you through the process at no charge.


Note: Neither of the above approaches are mandatory if you already have an effective maintenance plan. If you have questions about the existing maintenance plan on your SQL database, contact your DBA for more information. If your DBA has questions, they should contact 1099 Pro at (888) Pro-1099.




  • No labels