CA IT Asset ManagerCA Software Asset Manager (CA SAM)ASSET PORTFOLIO MGMT- SERVERSUPPORT AUTOMATION- SERVERCA Service Desk Manager - Unified Self ServiceKNOWLEDGE TOOLSCA Service Management - Asset Portfolio ManagementCA Service Management - Service Desk Manager
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?
CA Asset Portfolio Management 14.x and above
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.