November 13, 2015 at 2:08 pm
The below query takes 3-4 seconds on HADR servers in one of my clusters, on fairly high-performing hardware. It is among the most expensive procedures/queries on the entire box. sys.dm_exec_query_stats shows total_worker_time of 3.9M for each query, consistently (22-rows in resultset).
SELECT replica_server_name, node_name
FROM master.sys.dm_hadr_availability_replica_cluster_nodes
The query gets run pretty frequently. I think it is part of internal Microsoft AG status monitoring.
I only have one other (smaller) cluster with AGs with which I can compare, and the above query behaves quite a bit better there: it runs in 140msec and has total_worker_time of 135K (8 rows in resultset).
I'm hoping a few of you can run this on your SQL 2012 Server (with AGs configured) and confirm whether it's slow/expensive like this for anyone else. Or share any other knowledge of this situation?
Servers are running SQL 2012 Enterprise, sp2, cu5
November 13, 2015 at 11:18 pm
I don't have numbers to offer as a comparison.
Total_worker_time is the total CPU microseconds accumulated since the plan was last compiled. Because 3.9M == 3.9 seconds and its query takes 3-4 seconds, and because 135k == 135 milliseconds and its query takes 140 milliseconds, those similar numbers suggest you are looking at the metrics for merely an execution_count = 1, and suggests (based upon one execution) that the query is CPU-bound. And yet sys.dm_hadr_availability_replica_cluster_nodes either returns cached results from SQL Server's address space (I am imagining the caching of a prior callback from the cluster) or returns uncached results, by fetching from the cluster at run time. Neither of those possibilities should be consuming significant CPU time. Fetching from SQL Server's address space should take no more than a handful of microseconds, and SQL Server waiting for results to be returned by the cluster should also be consuming no more than a handful of microseconds (waits are cheap, in terms of CPU demands). These numbers don't look right :).
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply