asked on

how to find out any execution plan with index hints from whoisactive query log

hi,

right now we are using whoisactive query log to find out missing index from query plan column of :

  EXEC dbo.sp_WhoIsActive  

@find_block_leaders = 1,
  
    @show_system_spids = 1,  

     @show_own_spid = 1, 

     @get_plans =1 ,  

     @get_additional_info =1,

     @get_transaction_info = 1 
,
                                @return_schema = 1,
                                @schema = @schema OUTPUT;
        SET @schema = REPLACE(@schema, '<table_name>', @destination_table);

      print @schema;
        EXEC ( @schema );
    END;

Open in new window

but if there are too much log and I have to click each query plan item and see any index hints!
Any way to select from the result table by above on ANY query plan has index hints?

ste5an

My two favorite optimization tools:

- The query store.
- The TOP IO query report.

And for more automated query tuning: Ola Hallengren's Index Optimize script.
Also Brent Ozar's scripts are a very useful resource for this task.

marrowyung

ASKER

And for more automated query tuning: Ola Hallengren's Index Optimize script.

yeah I knew this but do not have a chance to learn that part yet, usually I just use the ola backup and restore, this is it.

Any video/URL to show me how to use that to find missing index and unused index so that I can add and drop index faster ?

Also Brent Ozar's scripts are a very useful resource for this task.

sorry ,what is that mean ? you mean Ola Hallengren script comes from the idea of Brent Ozar ?

- The query store.

- The TOP IO query report.

query store is the same thing, I have check one by one!

what is the top IO requery report from ? SQL server performance dashboard ?

so we can't do it by using whoisactive ? the log I post above ?