New SQL DMV in SQL server 2019
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 |
SELECT TOP (25) improvement = CAST((misq.avg_total_user_cost * misq.avg_user_impact * (misq.user_seeks + misq.user_scans)) AS DECIMAL(20, 2)), (SUBSTRING( sql_text.text, misq.last_statement_start_offset / 2 + 1, (CASE misq.last_statement_start_offset WHEN-1 THEN DATALENGTH(sql_text.text) ELSE misq.last_statement_end_offset END - misq.last_statement_start_offset ) / 2 + 1 ) ) AS sql_text, CAST(misq.avg_total_user_cost AS DECIMAL(20, 2)) AS avg_total_user_cost, CAST(misq.avg_user_impact AS DECIMAL(20, 2)) AS avg_user_impact, misq.user_scans, misq.user_seeks, misq.last_user_seek, misq.last_user_scan, DB_NAME(mid.database_id) AS database_name, mid.statement AS user_table, mid.equality_columns, mid.inequality_columns, mid.included_columns FROM sys.dm_db_missing_index_group_stats_query AS misq INNER JOIN sys.dm_db_missing_index_groups AS mig ON mig.index_group_handle = misq.group_handle INNER JOIN sys.dm_db_missing_index_details AS mid ON mig.index_handle = mid.index_handle CROSS APPLY sys.dm_exec_sql_text(misq.last_sql_handle) AS sql_text WHERE mid.database_id = DB_ID() ORDER BY improvement DESC; GO |
— Selectivity from mssqltips.com
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 |
SELECT 'MostUnique' AS [Column] ,COUNT(DISTINCT MostUnique) AS UniqueValues ,COUNT(*) AS TotalRows ,CAST(COUNT(DISTINCT MostUnique) AS FLOAT) / COUNT(*) AS Selectivity FROM dbo.ColumnOrder UNION ALL SELECT 'SecondMost' AS [Column] ,COUNT(DISTINCT SecondMost) AS UniqueValues ,COUNT(*) AS TotalRows ,CAST(COUNT(DISTINCT SecondMost) AS FLOAT) / COUNT(*) AS Selectivity FROM dbo.ColumnOrder UNION ALL SELECT 'ThirdMost' AS [Column] ,COUNT(DISTINCT ThirdMost) AS UniqueValues ,COUNT(*) AS TotalRows ,CAST(COUNT(DISTINCT ThirdMost) AS FLOAT) / COUNT(*) AS Selectivity FROM dbo.ColumnOrder UNION ALL SELECT 'FourthMost' AS [Column] ,COUNT(DISTINCT FourthMost) AS UniqueValues ,COUNT(*) AS TotalRows ,CAST(COUNT(DISTINCT FourthMost) AS FLOAT) / COUNT(*) AS Selectivity FROM dbo.ColumnOrder; |