SSMs can provide the ability to view the plans that are being executed. Live query plan can view the execution of a query, from one query plan action to another query plan operation. Live query plan provides overall query run progress and operational level execution statistics, such as the number of rows generated, the time of execution, the progress of the operation, and so on. Because the data is real-time and does not need to wait for the query to complete, these statistics are useful for debugging query performance issues. This feature starts with SQL Server 2016, but can also be run on 2014. Remark to SQL Server 2016, there are 2 ways to open statistics infrastructure to view live query statistics in other answers:
- On the target session, execute SET STATISTICS XML on; or SET STATISTICS profiles on;
- Starts the Query_poist_execution_showplan extension event. This is the server-level setting to start live query Statistics. can view
Starting with SQL Server SP1, SQL Server contains a lightweight statistics infrastructure. Here are 2 ways to start a lightweight Statistics infrastructure that can be used to view live query Statistics in other sessions:
- Using the global trace flag 7412
- Or start Query_poist_execution_showplan Extended events
SQL Server 2016 new features: Live Query Statistics
