Sql-server – Monitoring Query which executes thousand of run/minute and is generally fast

query-performancesql serversql-server-2017

I am looking for some advise here on one of our SQL database server with below behavior-

Queries running for this databases are generally considered good with avg run time of 20 ms.

Suddenly on some weird days it will go from 20 ms to 80 ms and its very hard for our monitoring process to capture which time exactly it shifted its run time and why-

Our current monitoring method include below 2:-

DMV's from cached TOP SQL queries which does not help much as metrics are cumulative so its hard to find the point in time where issue happened and plan or something changed.
Extended events for RPC COMPLETED, SP statement completed, SQL batch completed and SQL statement completed, but only for queries running over 5 secs.

We have not looked into QUER STORE yet due to some issues i am reading online because this DB is SQL2017 with AG setup in 2 DC configuration. Though there are trace flags for some but our Eng team is hesitant still to use QS on this server which is high OLTP with batch req/sec on avg 30-40K/ secs/

Please suggest or advise if i am missing anything except third party monitoring tool?

Best Answer

For the cumulative DMVs, if you're capturing them routinely, then you just need to compare the differential changes between the timeframe of when your query that you're tracing slows down. Then you should be able to pinpoint abnormalities.

You can also look into the sp_Blitz procedures, specifically sp_BlitzFirst during the moment of contention.

Related Solutions

Sql-server – Lots of short term blockings on SQL Server 2008-R2 database

This:

wait_type  wait_time_ms_per_sec
PAGELATCH_EX    733024.4
PAGELATCH_SH    328846.4
CPU_USED    7131.4
WRITELOG    6484.4

Shows that you have a latch contention problem, and only a latch contention problem.

Since

@ below was run 15 mins after we did node failover

The CPU_USED row should be accurate. And so your workload is only managing to use 7sec of CPU time per second. This is like only running on 7 cores. In the mean time you have 1000sec of page latch time, meaning that you have, on average 1000 sessions waiting to latch a page, and only 7 performing useful work.

You are also running very old software on a very large server. The throughput on this server is pretty high, suggesting that the operation it supports is important. Also consider your username. So you should consider, strongly, engaging Microsoft Support or a partner with expertise in this sort of thing.

That said, the next step is to identify the source of your latch waits. Generally these are either on a hot page of a table in your database, or a system page in TempDb. The sys.dm_exec_requests.wait_resource should tell you which.

If tempdb, then Recommendations to reduce allocation contention in SQL Server tempdb.

If a table in your database, then post the DDL (including indexes) for the table and a the details of the queries that are blocking.

There is a whitepaper on diagnosing and resolving latch contention on SQL 2008 here: Diagnosing and Resolving Latch Contention on SQL Server

SQL Server Performance Testing – Measuring Capacity and Scaling Needs

As others have said, there is no magic formula that will tell you how your database server will respond to a sudden "increase the volume by double the current one" (volume of what exactly, by the way).

The two things that can help you to get a general idea (neither of which you have, apparently):

Historical (over many months) data that show you the correlation between your server load, the volume of data, and the application workload (concurrent connections, user sessions, registered users, daily orders, or whatever makes sense in you case). "[P]erformance data for last 15 days" is completely useless.
Performance testing environment that's configured meaningfully similar to your production. In this day and age spinning up a virtualized test environment on one of the cloud providers and running it for a week costs peanuts, compared to the price you will pay for a production outage caused by unexpected performance problems. Performance burn-down tests should be a part of your release process.

If you start preparing both of these tomorrow, in half a year you will be able to answer your own question with some degree of confidence.

Related Question