This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(41.6, 15%, 14%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3ETB%3C/text%3E%3C/svg%3E)
Hi,
We have some suspicion of performance degradation on our Azure SQL Database.
I would like to benchmark the data I/O throughput on one database without and conflictual activity on our server.
We have observed an increasing occurrence of timeouts in our queries despite our workload did not change in this timeline.
In most cases, the Data I/O graph in the different metrics view on the portal did not show a spike or a saturation of our Data I/O DTU, neither the different dmv on our database.
And the timed out queries only seems to do some table scan which relies on Data I/O performances (often data are in cache after that and only logical reads are observed)
Has anyone tried to measure such performances on Azure SQL database?
Regards,
An Azure relational database service.
You can use HammerDB for that purpose. See this step-by-step article on how to do it.
My suggestion, in addition to benchmark the IO of your database, make sure you create a maintenance plan for your indexes and statistics using Azure Automation. Create also the missing indexes and the situation will disappear.
Thanks!
We already have a strict maintenance plan, with daily reorganize / rebuild / update stat based on fragmentation, fill factor and update frequencies.
We create missing indexes with precaution, because of the write/update latency it adds on each operation, and we accept that in some cases a scan is preferred instead of a new index only useful in some edge case.
What I am seeing is that previous queries which worked well for a long time start to fail studently. There queries are not related, only by the fact that they indue some (expected) scan.
I will read about HammerDB, meanwhile I am trying to understand if I can track performance indicators using sys.dm_user_db_resource_governance or other DMV. If you have any idea or experience feel free to share. :) Thanks again for you answer.
IWhat is the service tier of the database Thomas?
We have multiple pool with different service tiers (standard and premium pools).
But we have 2 standard pools (140 database per pool) and 100 eDTU where we have detected a performance degradation since last month in our region (France Central).
Because I didn't track performance metrics before that, it's difficult to point what changed in the environment. But now that I collect a lot of metrics (wait types and so on), we are able to detect what seems to be poor performance in terms of physical IO reads.
Now I am trying to track data IO congestion via dm_io_virtual_file_stats, aggregated between all databases in the pool, to check the raw amount of data read from disk, with a granularity of 60 secs.
Perhaps unrelated, but I have also detected a significant amount of PREEMPTIVE_HTTP lock wait type on some queries. Sometimes half of the allotted time (with a client timeout of 30 seconds) is spent in this wait (the other was spent on BUFFER_IO as expected from a table scan).
Whatever, without any data to compare before we observed the degradation, it's complex to diagnose exactly what is causing this,
Please note that Standard tiers use remote storage, and even during maintenance tasks you can perceive degradation of the database. Premium tiers use local attached SSD storage. Scans that cannot find the data pages on cache will go to retrieve data from that remote storage.
About the PREEMPTIVE_HTTP make sure they are not related to API calls doing preemptive HTTP authentication. Preemptive waits can really degrade the performance of the database and be behind timouts appearing.
Check the preemptive_switches_count on the dm_os_schedulers DMV, this gives you an idea of the many times preemptive waits happen
About the PREEMPTIVE_HTTP make sure they are not related to API calls doing preemptive HTTP authentication. Preemptive waits can really degrade the performance of the database and be behind timouts appearing.
I am not sure sure how I should detect API calls doing preemptive HTTP. Our SQL code does not relate to any API calls.
Check the preemptive_switches_count on the dm_os_schedulers DMV, this gives you an idea of the many times preemptive waits happen
I need to track the evolution of this value. But just comparing the raw value with values of our premium pool, the values are greater in the standard pool of a magnitude of x10.
I need to track evolution of these values over time.
On SQL Server extended stored procedures and RPC calls are usually behind these preemptive calls. Any elastic queries used?
No, nothing fancy there: only standard sql queries. Even our sql database user are contained. We have some basic diagnostic settings enabled at pool level on the portal, to send some metrics to log analytics, but nothing else.