SQL Server Administration: 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
SETSTATISTICSPROFILEON
GO
--Optionally return the final results of the query to SHOWPLAN XML
SET SHOWPLAN XMLON
GO
--Next, run a query in this session
SELECT*
FROM RunningRoutes rt
CROSSJOIN 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 ASfloat)/SUM(estimate_row_count)
FROMsys.dm_exec_query_profiles
WHERE session_id=<<your session id>>
GROUPBY node_id,physical_operator_name
ORDERBY node_id
SQLRx has worked hard to minimize the impact of monitoring SQL Servers to the point that we are able to monitor high transaction systems with little impact (1% load) on the target system. Contact us today to help you monitor your SQL servers!
|
