Investigate what loads the service and the database

Users report that Access, Excel, or Power BI is slow, or the database is busy at certain times. You want to know which requests cause it, when they arrive, and what helps.

The service keeps a load log in the log file next to the administration store. It groups requests into shapes: a shape is the request without its values. A hundred queries from one form that differ only in the customer number are one shape with a hundred requests. This shows that a few shapes usually carry most of the load, and the log holds none of the users' data.

The administration API describes the fields and values.

1. Look at the most expensive shapes. Read LoadShapes, sorted by total time in descending order:

   GET /admin/v1/odata/LoadShapes?$orderby=TotalMs desc&$top=20&$select=Id,Alias,Kind,Text,Requests,TotalMs,P95Ms,DataMs,AverageRows,Advice

   Text describes the shape, for example dbo.Reparaturliste read: filter=(Werk eq ?); orderby=Datum; top. If DataMs is close to TotalMs, the time is spent in the database; if DataMs is small, it's spent in the service or in the transfer (Bytes).

2. Read the advice. For a shape, LoadAdvice names what makes it expensive regardless of the data, and what usually helps:

   Code	Typical cause	What to do
   wholeSet	Access opens a whole linked table; Power Query loads a table without a filter	Use a form or query with a criterion; filter in Power Query before loading
   unindexedFilter	The filter uses a column without an index	Have the database administrator create an index, or filter on an indexed column
   deepPaging	A program pages far into a large set with $skip	Narrow the set, or read on through the next link
   countLarge	Every page requests the total count	Request $count only when needed
   aggregateWhole	Aggregates over a whole large table	Narrow the set, or have it precomputed as an indexed view
   expensiveStatement	An own statement takes long	Check it in Query Store; store it as a procedure or view
   refreshLoop	A form with a timer or a short refresh interval, a query with frequent automatic refresh	Lengthen or turn off the interval

3. Recognize the pattern over time. Group LoadHours by Weekday (1 = Monday) and HourOfDay to see when the service is busy, or by Hour to follow the trend over weeks and months. Hour and weekday follow the server's clock.
4. Trace a single slow request. SlowRequests gives the time, duration, and CorrelationId. With the correlation ID, you find the same request in the service log and in the audit.

5. Continue in SQL Server. Every statement the service generates carries the comment /* wkl:<Id> */ after its first keyword, where <Id> is the shape's identifier from LoadShapes. The database administrator uses it to find the plan and its cost, for example in the plan cache:

   SELECT TOP (20) s.execution_count, s.total_worker_time / 1000 AS cpu_ms,
          s.total_logical_reads, t.text
   FROM sys.dm_exec_query_stats AS s
   CROSS APPLY sys.dm_exec_sql_text(s.sql_handle) AS t
   WHERE t.text LIKE N'%/* wkl:%'
   ORDER BY s.total_worker_time DESC;

   If Query Store is turned on for the database, you find the same identifier there in sys.query_store_query_text. You recognize the service's sessions by program_name = Wkl.Data.Gateway in sys.dm_exec_sessions.

6. Give the load to the manufacturer. If the manufacturer is to investigate where users wait, write the load to a file—through the administration with POST /admin/v1/export-load, or on the server with `export-load`:

   Wkl.Data.Gateway export-load --to C:\Temp\load.json --days 30

   The file is readable JSON. Check it before you send it: it contains the names of aliases, tables, and columns, but no persons, addresses, or values.

• Visible after a minute. The service writes the collected figures once a minute.
• Own statements with the sql right appear with their text without literals and comments. The service doesn't change them and therefore doesn't add a /* wkl:… */ comment. In SQL Server, you find them by their text or by program_name.
• Own Application Name. If a data source's connection string sets an Application Name itself, the service keeps it; program_name then shows that name.
• Load per person. Anyone who reads LoadPersons sees who queried how much and when. Grant the load-persons right only where an evaluation per person is permitted and agreed on; by default, the daily figures are kept for seven days (Gateway:LoadLog:PersonDays). The other sets don't name any person.
• What isn't counted. Requests to /health. If more than 5,000 distinct shapes come together in one minute, the service doesn't count the rest and reports this in its log.
• Cost. The load log doesn't make a request measurably longer. If you don't need it, turn it off with Gateway:LoadLog:Enabled = false.

• Configuration: Gateway:LoadLog—all settings
• Administration API: load log—fields, values, and export-load
• Check sign-in patterns and origins—the counterpart for sign-ins
• Security model and trust boundaries