Changes to System Views in SQL 2014

— By Lori Brown  @SQLSupahStah

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

Both comments and trackbacks are currently closed.
%d bloggers like this: