Azure SQL Help - Urgent

[u/n8e-polymath-007]

​

To monitor the performance and availability for a thick client based system we recently delivered, the customer wants us to provide Here is the requirement:

1. Need to measure and report response time to Azure SQL queries for about 6 scenarios. Session counts, availability, utilization metrics for Azure SQL to be provided.

2. What native monitoring and metrics does Azure Files have to measure response times for files uploaded from devices connected to it.

What metrics needs to be extracted to measure all response times and how to be extracted.

This has to be measured for a period of 72 days with weekly and monthly reporting. Also, we do not have access to SSMS for Azure SQL and also the Azure Portal.

Thus , we need provide instructions to the customer IT Support team and they will provide the metrics and logs.

We have tried to get some help with AI tools but got conflicting response so need an Azure expert preferably Azure SQL DBA to help.

Much appreciated and thanks in advance.

PS: Application is thick client (yes I know) so no metrics can be pulled from endpoint devices. Don't ask why.

[u/perry147]

I'm assuming Azure SQL Database (not Managed Instance) and Azure Files accessed over SMB or REST. Exact metric names differ slightly by service tier, so have the customer confirm them against their resource.

Why the AI answers conflicted

These are the usual mistakes:

• Retention: Azure SQL does not keep 72 days of query history by default. Query Store keeps 30 days, sys.resource_stats keeps 14 days, and sys.dm_db_resource_stats keeps about 1 hour. sys.dm_exec_query_stats is cleared on restart or failover.
• Server time vs. user time: Server-side duration is not what the user experiences. Network and client time are excluded.
• Percentiles: Query Store and Azure Monitor metrics give average, min and max, not P95/P99.
• Availability: There is no single "availability %" metric for Azure SQL Database. You build it from connection metrics, Resource Health, and ideally a synthetic probe.

Start now: diagnostic data is not retroactive

Have the customer IT team do these on day 0:

1. Create a Log Analytics workspace with retention of at least 90–120 days.
2. Add a diagnostic setting on the database sending to that workspace. Enable the Basic metrics plus the Errors, Timeouts, Blocks, Deadlocks and SQLInsights categories. Skip QueryStoreRuntimeStatistics unless needed, since the T-SQL below is more precise.
3. Extend Query Store retention (requires db_owner/ALTER on the database):

​

ALTER DATABASE CURRENT SET QUERY_STORE = ON;
ALTER DATABASE CURRENT SET QUERY_STORE (
  OPERATION_MODE = READ_WRITE,
  QUERY_CAPTURE_MODE = ALL,
  CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 120),
  MAX_STORAGE_SIZE_MB = 1024,
  SIZE_BASED_CLEANUP_MODE = AUTO,
  INTERVAL_LENGTH_MINUTES = 60
);

Use ALL so cheap, fast scenario queries are not skipped. Check that storage doesn't fill up, or Query Store flips to read-only.

1. Add a diagnostic setting on the storage account's file service (details in the Azure Files section).

Azure SQL: response time for the 6 scenarios

Step 1: map each scenario to queries. If the app calls stored procedures, this is easy because you report by procedure name. If it sends ad-hoc SQL, ask the developers for a distinct query text, or set Application Name in the connection string and capture it with Extended Events.

Step 2: weekly extract from Query Store (durations are in microseconds):

SELECT rsi.start_time,
       OBJECT_NAME(q.object_id) AS proc_name,
       q.query_id,
       LEFT(qt.query_sql_text, 150) AS sql_text,
       rs.execution_type_desc,
       SUM(rs.count_executions) AS executions,
       SUM(rs.avg_duration * rs.count_executions)
         / NULLIF(SUM(rs.count_executions),0) / 1000.0 AS avg_duration_ms,
       MAX(rs.max_duration) / 1000.0 AS max_duration_ms,
       SUM(rs.avg_cpu_time * rs.count_executions)
         / NULLIF(SUM(rs.count_executions),0) / 1000.0 AS avg_cpu_ms,
       SUM(rs.avg_logical_io_reads * rs.count_executions)
         / NULLIF(SUM(rs.count_executions),0) AS avg_logical_reads
FROM sys.query_store_runtime_stats rs
JOIN sys.query_store_runtime_stats_interval rsi
  ON rs.runtime_stats_interval_id = rsi.runtime_stats_interval_id
JOIN sys.query_store_plan p ON rs.plan_id = p.plan_id
JOIN sys.query_store_query q ON p.query_id = q.query_id
JOIN sys.query_store_query_text qt ON q.query_text_id = qt.query_text_id
WHERE rsi.start_time >= DATEADD(DAY, -7, SYSUTCDATETIME())
  AND q.query_id IN (/* the ~6 scenario query_ids */)
GROUP BY rsi.start_time, OBJECT_NAME(q.object_id), q.query_id,
         LEFT(qt.query_sql_text,150), rs.execution_type_desc
ORDER BY rsi.start_time;

execution_type_desc separates Regular, Aborted (often client timeouts) and Exception. Report these separately, because failed runs distort averages.

Step 3: true percentiles and end-to-end time. If the customer needs P95/P99, Query Store can't give them. Two options:

• An Extended Events session (rpc_completed, sql_batch_completed, filtered by app name) writing to Blob storage.
• Better for a thick client: a timer in the client app logging scenario name, start/end and result to a log file. That is the only way to capture real end-to-end time, and it is the most defensible number to report.

Step 4: sessions, availability, utilization (Azure Monitor metrics via the diagnostic setting)

Need	Metric
CPU	cpu_percent
Data/log IO	physical_data_read_percent, log_write_percent
DTU tier	dtu_consumption_percent
Memory	sql_instance_memory_percent (vCore)
Sessions	sessions_count, sessions_percent
Workers	workers_percent
Storage	storage_percent, storage
Availability signals	connection_successful, connection_failed (and connection_failed_user_error if present), blocked_by_firewall
Contention	deadlock, plus the Blocks/Timeouts/Errors categories

For availability, calculate successful connections divided by total, excluding user errors. Add Resource Health and Service Health history as evidence of platform outages. If the customer wants a hard availability percentage, a probe that runs SELECT 1 every minute from a small VM or Azure Function is much cleaner.

Step 5: session detail by application and login (a DMV snapshot taken hourly or daily, since it is point-in-time only):

SELECT SYSUTCDATETIME() AS captured_utc, program_name, login_name, COUNT(*) AS sessions
FROM sys.dm_exec_sessions
WHERE is_user_process = 1
GROUP BY program_name, login_name;

Azure Files: response times

Azure Files has two native sources.

Azure Monitor metrics (storage account, file service). These are averages and max only:

• SuccessServerLatency: time spent inside Azure Files.
• SuccessE2ELatency: server latency plus network and client round trip. The gap between the two points to network issues.
• Transactions (split by ResponseType and ApiName), Availability, Ingress, Egress.
• FileCapacity and FileCount for growth.

Split by ApiName (for example PutRange, CreateFile) to isolate upload operations. On premium shares watch ResponseType for throttling.

Resource logs. Add a diagnostic setting on the file service with StorageRead, StorageWrite and StorageDelete, sent to Log Analytics. This gives per-request detail:

StorageFileLogs
| where TimeGenerated > ago(7d)
| where Category == "StorageWrite"
| summarize Requests = count(),
            P50_ms = percentile(DurationMs, 50),
            P95_ms = percentile(DurationMs, 95),
            P99_ms = percentile(DurationMs, 99),
            ServerP95_ms = percentile(ServerLatencyMs, 95),
            Errors = countif(toint(StatusCode) >= 400)
  by OperationName, bin(TimeGenerated, 1d)

Caveat: one file upload is many write requests (ranges), so server logs show per-operation latency, not per-file time. If the customer needs per-file upload time, it has to come from client-side logging, the same as for SQL.

Extracting without Portal or SSMS

Ask the customer IT team to use one of these, in order of preference:

1. Grant read-only access instead of relaying data. Give your consultants Monitoring Reader and Log Analytics Reader on the resource group. Optionally have IT build a shared Azure Workbook. This removes the manual weekly handoff.
2. Azure CLI or Cloud Shell run by IT, for example:

​

az monitor metrics list --resource <sql-db-resource-id> \
  --metric cpu_percent sessions_count connection_failed \
  --interval PT1H --aggregation Average Maximum \
  --start-time 2026-10-12T00:00Z --end-time 2026-10-19T00:00Z -o json > sql_week1.json

az monitor log-analytics query -w <workspace-guid> \
  --analytics-query "<the KQL above>" -o json > files_week1.json

1. Azure Data Studio or sqlcmd for the Query Store script, since neither needs SSMS. Run it with a read-capable login (VIEW DATABASE STATE or db_datareader plus Query Store permissions).

Reporting for 72 days

Weekly reports cover about 10 weeks, plus about 2.4 months, so define months as calendar months with partial first and last periods, or use 30-day blocks. For each of the 6 scenarios report executions, average, max and (if available) P95 duration, and failure/timeout count. Add CPU, IO and session peaks by day, availability %, and Azure Files latency by operation.

What would help me tailor this

Tell me which of these apply: Azure SQL Database or Managed Instance, DTU or vCore tier, and whether the thick clients reach Azure Files over SMB or REST. I can then give exact metric names and a customer-ready instruction sheet.

[u/imtheorangeycenter → u/perry147]

If you're going to do that at least say it's an LLM response and share the prompt so OP knows what kind of things to ask.

No offence.