7

We are on SQL Azure v12 Standard/S2 Service Tier and are not satisfied with the performance so I am trying to measure where the bottleneck is. I realize that we could be hitting the limits of the S2 service tier. S2 has 50 DTU and 2,570 transactions per minute limitation.

Memory Pressure: I looked at the sys.dm_exec_query_memory_grants and there are no pending memory grants and the requested memory is being granted. Page Life expectancy is about 600.

DTU The average DTU doesn't seem to be more than 50% unitized. avg_cpu_percent and avg_data_io_percent are rarely over 50%. avg_memory_usage_percent stays at 99%. I got this information from the sys.dm_db_resource_stats

Transactions Per Sec How do I measure Transactions/Sec in SQL Azure? The DMV sys.dm_os_performance_counters for Transactions/Sec doesn't have any values for the User databases. it has values for system databases.

  • with v12 you can try Query Store. Is not going to give to TPM, but will give you execution times. – Remus Rusanu Jul 1 '15 at 21:25
2

You can use this script written by Dimitri Furman from Microsoft. It will measure transactions/sec per database. You need to run this against the database where you want to measure transactions/sec. I tested this against V12 version and it works.

https://blogs.msdn.microsoft.com/dfurman/2015/04/02/collecting-performance-counter-values-from-a-sql-azure-database/

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.