November 6, 2014 at 6:01 pm
We have an app that I think is abusing Query Notifications. And by abuse I mean trying to register them on very insert/update heavy tables that are updated every 2 minutes. It's so bad that the app actually fails to register additional notifications because it hits the internal table limit.
This app is running on a single instance of SQL Server 2008 R2 Standard on a virtual machine with 24 vCPU and 72 GB RAM.
CPU is getting slammed on this box, and as I monitor for rogue queries I seem to have a gap between what I think the workload should be based on user queries, and what the perfmon counters show as actually being used by sqlserver.exe. This leads me to believe it's not user queries but something internal to the data engine. When I found out how Query Notifications are being used by the app, leads me to suspect that as a culprit.
So I'm looking for specific perfmon counters or other metrics that would help me empirically show that the excess CPU consumption is due to query notifications. Maybe there is something I can use to at least show service broker traffic?
Do I need to break out the SQLDiag / trace / SQL Nexus hammer? Or is there something lighter weight? I have the Confio Ignite tool running against this instance, but the queries it collects don't seem to explain all the CPU used.
November 7, 2014 at 3:57 am
This was removed by the editor as SPAM
November 7, 2014 at 7:39 am
Stewart "Arturius" Campbell (11/7/2014)
Try using the DMV'se.g.select * from sys.dm_exec_requests
Yes, but that is instantaneous, I want to aggregate the impact over time. And yes I could insert dm_exec_reguests into a table and aggregate wait_time or cpu_time as I see fit, but I was hoping to not have to roll my own solution, but maybe that's what my best option.
November 7, 2014 at 8:02 am
This was removed by the editor as SPAM
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply