see sessions from sys.dm_exec_requests in top 10 queries
I never see commands like BACKUP DATABASE or ALTER INDEX in top 10 queries and got the message from support that the information is taken from sys.dmexecquerystats
As it is now it is hard to track down where heavy I/O comes from if it is for example index maintenance or backup.
I think RedGate should record all commands from sys.dmexec_requests and join in the execution plans where they exist and other stats if needed
-
Nikhil commented
We had a production issue that did not trigger an alert on SQLMonitor. This was due to an ALTER INDEX statement that went un-noticed. It would be good to see these in SQLMonitor so that we can mitigate serious Prod issues.
-
kerstin commented
No trace in SQL monitor what is causing this heavy IO at night. It turns out to be Backup, checkdb and index maintenance jobs. No traces in the top SQL
-
kerstin commented
We had a serious performance problem where we could not see in redgate why the CPU went up to 100%. The reason was queries that never finished, and were not in sys.dm_exec_query_stats and were not seen in sql monitor. At the same time there was full backups running and dbcc checkdb, not seen in sql monitor. I would expect that everything I see in sp_whoisactive should be recorded in SQL monitor and was quite disappointed when I could not solve this problem using SQL monitor.
-
Jogchum Rooda commented
I think this would also be great. When the requests are stored in the db we can specifically filter out statements you would like to analyse (for instance deletes). However i think the RedGateMonitor database will grow fast when storing all requests from the monitored server, so it will have to be an option when adding a server.