export service excessive sql traffic

Document ID : KB000076474
Last Modified Date : 04/04/2018
Show Technical Document Details
Issue:
We are required to turn on SQL Server auditing for all sql server transactions.  A SQL profiler trace shows this statement executing every minute:
exec sp_executesql N'SELECT EXPORT_ID, PROCESSOR_ID, SEARCH_ID, STATUS_ID, ROLE_ID, CONTACT_UUID, TENANT, NAME, DESCRIPTION, SCHEDULE_ID, CONTACTS_SCOPE, FOLDER_NAME, VIEW_NAME, RETENTION_DAYS FROM dbo.AL_EXPORT_DEF ...  
What APM process is executing this SQL and can we configure APP to do this less often? 
Environment:
CA Asset Portfolio Management 14.x and above
Resolution:
The export service is executing the SQL.
The export service setting for the 'On Demand Request Period' is the time, in MILLISECONDS, that the Export Service waits between export processing cycles. The default is  5000 (5 seconds). Each sql statement running every minute is due to the number of on demand threads + the thread for the export scheduled job.

To reduce the sql traffic against the database, change the export service setting for 'On Demand Max Threads' to 1 and set the 'On Demand Request Period' to 10 minutes or longer. After making changes, then stop the export service, perform an IISRESET, restart the export service.  
Note that exports may take a bit longer to execute, but this will reduce the amount of SQL traffic in your system.