Optimizing OpenX (formerly known as OpenX Source or Revive Adserver) for Microsoft SQL Server requires addressing the platform’s high-write volume, ad-delivery speed, and tracking logging. Because OpenX was originally engineered primarily with MySQL workflows in mind, running it over an abstract database wrapper layer (like ADOdb) to connect to MS SQL Server makes database-level optimizations crucial for preventing latency. 1. Optimize Memory and Cache Allocation
Ad serving depends heavily on reading banner configurations, targeting rules, and zone data rapidly.
Buffer Pool Optimization: Ensure your MS SQL Server has enough RAM allocated to store OpenX’s delivery configuration data directly in memory. If memory usage is high, the server clears the cache frequently, which triggers slow disk reads (PAGEIOLATCH_SH waits).
Enable Ad-Hoc Workload Optimization: Because OpenX often generates dynamic, parameterized tracking queries, enable the optimize for ad hoc workloads configuration. This prevents your plan cache from filling up with single-use execution plans.
EXEC sp_configure ‘show advanced options’, 1; RECONFIGURE; EXEC sp_configure ‘optimize for ad hoc workloads’, 1; RECONFIGURE; Use code with caution. 2. Configure Concurrency and Isolation Levels
High-traffic ad delivery causes constant read-write conflicts. If you use default isolation levels, your ad-tracking statistics (writes) will block your ad delivery (reads), causing severe bottlenecks.
Enable Read Committed Snapshot Isolation (RCSI): Change the database isolation level to allow non-blocking reads. This ensures that readers do not block writers and writers do not block readers.
ALTER DATABASE YourOpenXDatabaseName SET READ_COMMITTED_SNAPSHOT ON WITH ROLLBACK IMMEDIATE; ALTER DATABASE YourOpenXDatabaseName SET ALLOW_SNAPSHOT_ISOLATION ON; Use code with caution. 3. Streamline High-Volume Logging Tables
The statistics tables (ox_data_bkt_m, ox_data_bkt_c, ox_data_bkt_a etc., which track impressions, clicks, and conversions) handle millions of row inserts.
Best practices to optimize SQL Server performance in Azure Stack Hub