Alter index, Backup database not present in top 50 queries

Kerstin · September 24, 2018 12:56PM

Are commands like for example alter index and backup database filtered out from top 50 queries in the instance overview ?
I would love to be able to see how an alter index command affects the instance and I am not able to find the queries although I know it is running at that time.

Tianjiao_Li · October 1, 2018 9:18AM

@Kerstin

Thanks for your feedback!

Can you also kindly post it on our user voice forum?

Tianjiao_Li · September 25, 2018 2:07PM

Hi @Kerstin

It looks like SQL Monitor has filtered out them. I'll speak to the development team and update you later.

Kerstin · September 25, 2018 3:06PM

thank you, I think those commands should not be filtered out, otherwise it is hard to see for example what causes I/O at night

Tianjiao_Li · September 26, 2018 3:03PM

Hi @Kerstin

The development team has confirmed SQL Monitor uses sys.dm_exec_query_stats, which contains cached execution plans for queries. Backup database and Alter index don't have any query plans, so they don't appear there.

Kerstin · September 27, 2018 11:06AM

Thank you. I would think it is more correct to use sys.dm_exec_requests where you can see all commands? And also sys.dm_exec_query_stats "Returns aggregate performance statistics for cached query plans in SQL Server" that is not what I want to see, I want to see all commands that was run during a certain timeframe, and the corresponding execution plan if they have one.