Show a log of query activity
CONTEXT: When investigating a performance issue, I want to know what has been running
PROBLEM: SQL Monitor has rich query information on the overview screens, the performance diagnostics panels, and the query alerts, and the query trace in alert detail reports, but it's difficult to tie that information together
- Implement a query log
- Show query information on the analysis graph
Kenneth Fossum commented
The information provided in the top 10 expensive queries, is lacking. In our environments, we have multiple apps (or 'program_names') executing the same queries. Not being able to see which program_name that is executing the query is very frustrating.
Then, you should add the possibility of pulling out (graphical) reports on the top 10 CPU consumers (program_name) over time. This is valuable info in environments where you are managing several different apps on the same database.
You should add more information to the deadlock alerts.
In environments where one app can execute multiple queries (often the same queries), from several hosts (often App servers), the info you provide now is not sufficient:
Block occurred at: 3:09 AM
Blocked process: App1
Blocking process: App2
Object blocked: Database (MyDB)
Where is the SPID? App1 can have several connections to the DB executing the same queries, so I have no way of telling which process got blocked. You should provide something like this:
The deadlock victim was spid <SPID> with application name 'App1' by user 'Username' on host 'AppServer'.
Last query executed <Queryinfo>
This info should not only be visible through the web interface, but also in the email alert.
On the Analysis page you should add a blocking module where one can go and troubleshoot - real time and historically. Being able to go back and pinpoint lead blockers and victims, with relevant query info would be great.
My background is DBA in environments with a lot of different clients doing a lot of weird things causing all sorts of issues. Having the necessary tool that quickly can provide the necessary info needed to "shut them up" would be great :). Obviously I have my scripts and queries that will pull out info I need, but a nicely drawn graph with some black on white facts will increase my chances of convincing them that I am right.
Thank you for your time.