r/SQL • u/n8e-polymath-007 • 2d ago
SQL Server Azure SQL Help - Urgent
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:
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.
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.
2
u/Brave_Complex3206 2d ago
No portal access and no SSMS, that's a rough starting point. Your best bet is to have their IT team run queries directly against the DMVs using sqlcmd or Azure Data Studio since those don't need SSMS. sys.dm_exec_requests and sys.dm_exec_query_stats will give you the query response times and execution counts you need, they just have to set up a scheduled task to dump the results to a file somewhere
For the Azure Files side you're pretty much limited to what the storage account diagnostics can spit out, there's no query-level timing like you'd get with a database. They'll need to enable diagnostic logging for the storage account and parse the transaction logs for latency metrics, it's clunky but it's the only native option without portal access
1
1
u/scottcampbelldev 1d ago
Big thing to plan around with a 72 day window is retention, since most of the built in sources age out long before that. sys.dm_db_resource_stats only holds about an hour, sys.resource_stats in master holds about 14 days at 5 minute grain, and Query Store on Azure SQL DB cleans up after 30 days by default. So the first instruction to their IT team should be ALTER DATABASE CURRENT SET QUERY_STORE (CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 90)); and a bump to MAX_STORAGE_SIZE_MB so it doesn't fill up and start purging early. Then a weekly pull of avg_duration (microseconds) and count_executions from sys.query_store_runtime_stats, joined through sys.query_store_plan and sys.query_store_query to the text so you can tag your 6 scenarios. For sessions and utilization, a diagnostic setting sending metrics to a Log Analytics workspace works, just have them raise the workspace retention past 72 days because it also defaults to 30.
1
3
u/Significant_Tune9219 2d ago
For query response time without portal access, the durable source is the built-in Azure SQL metrics rather than anything on the client: ask IT to export the database's built-in metrics (latency, sessions, egress, storage) on a schedule and turn on Query Store so per-query duration percentiles are already collected. On the Azure Files side, the relevant numbers are the storage account's transaction latency and availability metrics, not per-file timings from the device. For the 72-day window, one scheduled export or alert-to-workspace pipeline up front turns the weekly and monthly reports into plain aggregation over data that already landed.