Slow Query Monitoring

OptimiDoc reads and writes a database on every page load, every device interaction, and every report. When the database becomes a bottleneck, the symptoms are rarely a single broken query — they are gradual response-time creep that is invisible until it starts paging your administrators. This article explains the three layers of slow query monitoring available to OptimiDoc operators: the built-in application logger, the SQL Server Query Store, and Extended Events. Each layer has a different cost-to-value ratio. Use the smallest tool that gives you the answer you need.

Audience

This article targets database administrators (DBAs) and platform operators of large OptimiDoc deployments — typically more than a few hundred users, more than a hundred devices, or environments where the customer is on a full SQL Server (Express / Standard / Enterprise) rather than LocalDB.

If you are looking for general database troubleshooting (connection pools, disk space, LocalDB limits, index maintenance), start with Database and Performance Issues instead.

Layer 1 — Application-level slow query log (built-in)

OptimiDoc ships an EF6 command interceptor that times every database query and writes slow ones to a dedicated log file.

When to use this layer

• Day-to-day production monitoring on every instance — no DBA action required, ships with the application.

• First port of call when an end user reports "the activity log is slow".

• The log is plain text — grep / Notepad++ search work fine.

Tuning the thresholds

Default thresholds (warn 2 s, error 10 s) are deliberately conservative for the indexed schema. If your customer is migrating from an older OptimiDoc release that pre-dates the index improvements, you may temporarily lower the warn threshold (e.g. to 500 ms) to capture a baseline, then raise it once the indexes are in place to avoid log noise.

To override:

<appSettings>
  <add key="SlowQuery.WarnMs" value="500" />
  <add key="SlowQuery.ErrorMs" value="5000" />
</appSettings>

Restart the IIS application pool for the new values to take effect.

What it will not catch

• Direct ADO.NET calls outside Entity Framework (rare in OptimiDoc).

• Queries issued by SQL Server Agent jobs, replication, or other tooling.

• Resource pressure that is not query-specific (locking, tempdb spills) — those need SQL-side instrumentation.

Layer 2 — SQL Server Query Store (persistent, dashboard-friendly)

Available on SQL Server 2016 and newer. Query Store is a built-in feature that captures query plans, runtime statistics, and regressions over time. It has a graphical UI in SQL Server Management Studio (Database → Query Store node).

Enable it (one-time, per database)

USE master;
ALTER DATABASE [OptimiDoc] SET QUERY_STORE = ON
(
    OPERATION_MODE        = READ_WRITE,
    INTERVAL_LENGTH_MINUTES = 60,
    MAX_STORAGE_SIZE_MB   = 1000,
    QUERY_CAPTURE_MODE    = AUTO,
    SIZE_BASED_CLEANUP_MODE = AUTO
);

What you get

When to use this layer

• Dedicated SQL Server (not LocalDB) on SQL 2016+.

• You want week-over-week trend data, not just "is this slow right now".

• You suspect a particular plan regression after a patch or schema change.

• The customer's DBA is comfortable in SSMS.

Cost

Storage overhead is small (1 GB cap above is generous; most installs settle around 100–300 MB). CPU overhead is sub-1 %.

Cleanup / disable

ALTER DATABASE [OptimiDoc] SET QUERY_STORE = OFF;
ALTER DATABASE [OptimiDoc] SET QUERY_STORE CLEAR;  -- optional

Layer 3 — Extended Events (ad-hoc, surgical)

Use when Query Store is not available (older SQL Server) or when you need an exact wire-level capture of every slow query during a specific incident window.

Quick session: capture every query > 2 s for 1 hour

USE master;

CREATE EVENT SESSION [OptimiDoc_SlowQueries] ON SERVER
ADD EVENT sqlserver.sql_statement_completed
(
    ACTION (sqlserver.client_app_name, sqlserver.database_name, sqlserver.session_id, sqlserver.sql_text)
    WHERE sqlserver.database_name = N'OptimiDoc'
      AND duration > 2000000  -- microseconds, so 2 s
)
ADD TARGET package0.event_file
(
    SET filename = N'C:\Logs\OptimiDoc_SlowQueries.xel',
        max_file_size = 100,    -- MB
        max_rollover_files = 4
);

ALTER EVENT SESSION [OptimiDoc_SlowQueries] ON SERVER STATE = START;

After the incident window, stop and inspect:

ALTER EVENT SESSION [OptimiDoc_SlowQueries] ON SERVER STATE = STOP;

-- Read the captured events
SELECT
    event_data.value('(/event/@timestamp)[1]',                'datetime2')   AS event_time,
    event_data.value('(/event/data[@name="duration"]/value)[1]',  'bigint') / 1000 AS duration_ms,
    event_data.value('(/event/action[@name="sql_text"]/value)[1]', 'nvarchar(max)') AS sql_text,
    event_data.value('(/event/action[@name="client_app_name"]/value)[1]','nvarchar(255)') AS app
FROM (
    SELECT CAST(event_data AS XML) AS event_data
    FROM sys.fn_xe_file_target_read_file('C:\Logs\OptimiDoc_SlowQueries*.xel', NULL, NULL, NULL)
) e
ORDER BY duration_ms DESC;

-- Drop when done
DROP EVENT SESSION [OptimiDoc_SlowQueries] ON SERVER;

When to use this layer

• Incident in progress — you want every slow query for the next hour.

• SQL Server pre-2016 without Query Store.

• Diving deeper than the application log can show (e.g. queries from SQL Agent jobs, replication, other applications hitting the same database).

What to watch for

Choosing the right layer

Decision shortcut: always start with Layer 1 (application log, free, already on). Add Layer 2 (Query Store) on any deployment with a real SQL Server and >100 users. Add Layer 3 (Extended Events) only during an active incident or for older SQL Server installations.

Common patterns in OptimiDoc slow query logs

When you start looking, expect to see a few recurring patterns. Most are harmless; recognise them so you can filter signal from noise.

Related articles

• Database and Performance Issues

• LocalDB to SQL Server

• Troubleshooting Guide

• Advanced Configuration