Changes to System Views in SQL 2014

There are two new system views and two system views that have new columns added in SQL 2014. Most notable is sys.databases since this is widely used.

•   sys.xml_indexes – Has 3 new columns: xml_index_type, xml_index_type_description, and path_id.

https://msdn.microsoft.com/en-us/library/ms176003.aspx

• sys.databases – Has 3 new columns: is_auto_create_stats_incremental_on, is_query_store_on, and resource_pool_id.

https://msdn.microsoft.com/en-us/library/ms178534.aspx

• sys.column_store_row_groups – New view.  Provides clustered ColumnStore Index information on a per-segment basis to help determine which row groups have a high percentage of deleted rows and should be rebuilt.

https://msdn.microsoft.com/en-us/library/dn223749.aspx

• sys.dm_exec_query_profiles – New view.  Monitors real time query progress while a query is in execution.

https://msdn.microsoft.com/en-us/library/dn223301.aspx

–Configure query for profiling with sys.dm_exec_query_profiles

SET STATISTICS PROFILE ON

GO

–Optionally return the final results of the query to SHOWPLAN XML

SET SHOWPLAN XML ON

GO

–Next, run a query in this session

SELECT *

FROM RunningRoutes rt

CROSS JOIN RoadRaces race

WHERE [State] = ‘TX’

 –Run this in a different session than the session in which your query is running.

–Serialize the requests and return the final results to SHOWPLAN XML

SELECT 

node_id,physical_operator_name, SUM(row_count) row_count, SUM(estimate_row_count) AS estimate_row_count,

CAST(SUM(row_count)*100 AS float)/SUM(estimate_row_count)

FROM sys.dm_exec_query_profiles

WHERE session_id=<<your session id>>

GROUP BY node_id,physical_operator_name

ORDER BY node_id