In SQL Server, I would like to know what related statistical metrics such as Oracle's 'SQL Service Response Time' or 'Response Time Per Txn' can evaluate the overall database performance.
Please tell me the name of the statistical metrics and how to collect it using sql .
CodePudding user response：
SQL Server does not accumulate statistics about transactions, but stats of execution are available for free in all editions for queries, procedures, triggers and UDF in DMV like :
SELECT * FROM sys.dm_exec_query_stats;
SELECT * FROM sys.dm_exec_procedure_stats;
SELECT * FROM sys.dm_exec_trigger_stats;
SELECT * FROM sys.dm_exec_function_stats;
The metrics to consider are the followings :
- execution_count,
- total_worker_time
- total_elapsed_time
...
As an example, to have a mean exec time, you must divide the total time by the execution_count
CodePudding user response：
You're looking for Windows Performance counters, there are a range of them, see example: https://www.brentozar.com/archive/2006/12/dba-101-using-perfmon-for-sql-performance-tuning/
These can be read by code.
this is a big topic, but if this is what you need, please describe what problem you want to address as it dictates which part of windows is interesting to that end.
Generally i look for:
- batch requests per second
- lock wait time
- deadlocks
- cache hit ratio
- target/ actual memory relation
- available memory
- context switches per second
- CPU utilization
what we need to act on is the values changing away from normal picture.