{"id":969,"date":"2017-03-04T03:04:01","date_gmt":"2017-03-03T21:34:01","guid":{"rendered":"http:\/\/pramodsingla.com\/?p=969"},"modified":"2017-03-04T03:04:01","modified_gmt":"2017-03-03T21:34:01","slug":"mssql-server-find-missing-indexes","status":"publish","type":"post","link":"https:\/\/pramodsingla.com\/?p=969","title":{"rendered":"MSSQL Server: Find Missing Indexes"},"content":{"rendered":"<p><strong>Problem Statement: <\/strong>Script to find the missing indexes using 3 different ways.<br \/>\n<strong>Solution: 1) Find missing indexes\u00a0using\u00a0DMVs<\/strong><\/p>\n<blockquote>\n<pre><span style=\"color:green;\"><i>--Find\u00a0top\u00a0ten\u00a0missing\u00a0indexes\u00a0using\u00a0following\u00a0DMV<\/i><\/span>\u00a0\n<span style=\"color:blue;\">SELECT<\/span>\u00a0<span style=\"color:blue;\">TOP<\/span>\u00a0<span style=\"color:black;\">10<\/span>\u00a0<span style=\"color:maroon;\">(<\/span>\u00a0<span style=\"color:maroon;\">user_seeks<\/span>\u00a0<span style=\"color:maroon;\">)<\/span>\u00a0<span style=\"color:silver;\">*<\/span>\u00a0<span style=\"color:maroon;\">avg_total_user_cost<\/span>\u00a0<span style=\"color:silver;\">*<\/span>\u00a0<span style=\"color:maroon;\">(<\/span>\u00a0<span style=\"color:maroon;\">avg_user_impact<\/span>\u00a0<span style=\"color:silver;\">*<\/span>\u00a0<span style=\"color:black;\">0.01<\/span>\u00a0<span style=\"color:maroon;\">)<\/span>\u00a0\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<span style=\"color:blue;\">AS<\/span>\u00a0\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<span style=\"color:maroon;\">[index_advantage]<\/span><span style=\"color:silver;\">,<\/span><span style=\"color:green;\"><i>--%advantage\u00a0in\u00a0the\u00a0query<\/i><\/span>\u00a0\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<span style=\"color:maroon;\">migs<\/span><span style=\"color:silver;\">.<\/span><span style=\"color:maroon;\">last_user_seek<\/span><span style=\"color:silver;\">,<\/span>\u00a0\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<span style=\"color:green;\"><i>--Date\u00a0and\u00a0time\u00a0of\u00a0last\u00a0seek\u00a0caused\u00a0by\u00a0user\u00a0queries\u00a0that\u00a0the\u00a0recommended\u00a0index\u00a0in\u00a0the\u00a0group\u00a0could\u00a0have\u00a0been\u00a0used\u00a0for.<\/i><\/span>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<span style=\"color:maroon;\">mid<\/span><span style=\"color:silver;\">.<\/span><span style=\"color:maroon;\">[statement]<\/span>\u00a0\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<span style=\"color:blue;\">AS<\/span>\u00a0<span style=\"color:maroon;\">[Database.Schema.Table]<\/span><span style=\"color:silver;\">,<\/span>\u00a0\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<span style=\"color:red;\">'Create\u00a0index\u00a0idx_'<\/span>\u00a0\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<span style=\"color:silver;\">+<\/span>\u00a0<span style=\"color:fuchsia;\"><i>Parsename<\/i><\/span><span style=\"color:maroon;\">(<\/span>\u00a0<span style=\"color:maroon;\">mid<\/span><span style=\"color:silver;\">.<\/span><span style=\"color:maroon;\">[statement]<\/span><span style=\"color:silver;\">,<\/span>\u00a0<span style=\"color:black;\">1<\/span><span style=\"color:maroon;\">)<\/span>\u00a0<span style=\"color:silver;\">+<\/span>\u00a0<span style=\"color:red;\">'_'<\/span>\u00a0\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<span style=\"color:silver;\">+<\/span>\u00a0<span style=\"color:fuchsia;\"><i>Cast<\/i><\/span><span style=\"color:maroon;\">(<\/span><span style=\"color:#ff0080;\"><b>row_number<\/b><\/span><span style=\"color:maroon;\">(<\/span><span style=\"color:maroon;\">)<\/span>\u00a0<span style=\"color:blue;\">OVER<\/span><span style=\"color:maroon;\">(<\/span><span style=\"color:blue;\">ORDER<\/span>\u00a0<span style=\"color:blue;\">BY<\/span>\u00a0<span style=\"color:maroon;\">(<\/span><span style=\"color:maroon;\">user_seeks<\/span><span style=\"color:maroon;\">)<\/span>\u00a0<span style=\"color:silver;\">*<\/span>\u00a0\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<span style=\"color:maroon;\">avg_total_user_cost<\/span>\u00a0<span style=\"color:silver;\">*<\/span>\u00a0<span style=\"color:maroon;\">(<\/span>\u00a0\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<span style=\"color:maroon;\">avg_user_impact<\/span>\u00a0<span style=\"color:silver;\">*<\/span>\u00a0<span style=\"color:black;\">0.01<\/span>\u00a0<span style=\"color:maroon;\">)<\/span>\u00a0<span style=\"color:blue;\">DESC<\/span><span style=\"color:maroon;\">)<\/span>\u00a0<span style=\"color:blue;\">AS<\/span>\u00a0<span style=\"color:black;\"><i>VARCHAR<\/i><\/span><span style=\"color:maroon;\">(<\/span><span style=\"color:black;\">150<\/span><span style=\"color:maroon;\">)<\/span><span style=\"color:maroon;\">)<\/span>\u00a0\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<span style=\"color:silver;\">+<\/span>\u00a0<span style=\"color:red;\">'\u00a0on\u00a0'<\/span>\u00a0<span style=\"color:silver;\">+<\/span>\u00a0<span style=\"color:fuchsia;\"><i>Parsename<\/i><\/span><span style=\"color:maroon;\">(<\/span>\u00a0<span style=\"color:maroon;\">mid<\/span><span style=\"color:silver;\">.<\/span><span style=\"color:maroon;\">[statement]<\/span><span style=\"color:silver;\">,<\/span>\u00a0<span style=\"color:black;\">1<\/span><span style=\"color:maroon;\">)<\/span>\u00a0<span style=\"color:silver;\">+<\/span>\u00a0<span style=\"color:red;\">'('<\/span>\u00a0<span style=\"color:silver;\">++<\/span>\u00a0\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<span style=\"color:maroon;\">equality_columns<\/span>\u00a0<span style=\"color:silver;\">+<\/span>\u00a0<span style=\"color:red;\">')'<\/span>\u00a0<span style=\"color:silver;\">+<\/span>\u00a0<span style=\"color:blue;\">CASE<\/span>\u00a0<span style=\"color:blue;\">WHEN<\/span>\u00a0<span style=\"color:maroon;\">mid<\/span><span style=\"color:silver;\">.<\/span><span style=\"color:maroon;\">included_columns<\/span>\u00a0<span style=\"color:blue;\">IS<\/span>\u00a0<span style=\"color:blue;\">NOT<\/span>\u00a0\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<span style=\"color:blue;\">NULL<\/span>\u00a0<span style=\"color:blue;\">THEN<\/span>\u00a0\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<span style=\"color:red;\">'\u00a0include\u00a0('<\/span><span style=\"color:silver;\">+<\/span>\u00a0<span style=\"color:maroon;\">mid<\/span><span style=\"color:silver;\">.<\/span><span style=\"color:maroon;\">included_columns<\/span><span style=\"color:silver;\">+<\/span><span style=\"color:red;\">')'<\/span>\u00a0<span style=\"color:blue;\">ELSE<\/span>\u00a0<span style=\"color:red;\">''<\/span>\u00a0<span style=\"color:blue;\">END<\/span>\u00a0\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<span style=\"color:maroon;\">create_index<\/span><span style=\"color:silver;\">,<\/span>\u00a0\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<span style=\"color:red;\">'drop\u00a0index\u00a0idx_'<\/span>\u00a0\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<span style=\"color:silver;\">+<\/span>\u00a0<span style=\"color:fuchsia;\"><i>Parsename<\/i><\/span><span style=\"color:maroon;\">(<\/span>\u00a0<span style=\"color:maroon;\">mid<\/span><span style=\"color:silver;\">.<\/span><span style=\"color:maroon;\">[statement]<\/span><span style=\"color:silver;\">,<\/span>\u00a0<span style=\"color:black;\">1<\/span><span style=\"color:maroon;\">)<\/span>\u00a0<span style=\"color:silver;\">+<\/span>\u00a0<span style=\"color:red;\">'_'<\/span>\u00a0\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<span style=\"color:silver;\">+<\/span>\u00a0<span style=\"color:fuchsia;\"><i>Cast<\/i><\/span><span style=\"color:maroon;\">(<\/span><span style=\"color:#ff0080;\"><b>row_number<\/b><\/span><span style=\"color:maroon;\">(<\/span><span style=\"color:maroon;\">)<\/span>\u00a0<span style=\"color:blue;\">OVER<\/span><span style=\"color:maroon;\">(<\/span><span style=\"color:blue;\">ORDER<\/span>\u00a0<span style=\"color:blue;\">BY<\/span>\u00a0<span style=\"color:maroon;\">(<\/span><span style=\"color:maroon;\">user_seeks<\/span><span style=\"color:maroon;\">)<\/span>\u00a0<span style=\"color:silver;\">*<\/span>\u00a0\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<span style=\"color:maroon;\">avg_total_user_cost<\/span>\u00a0<span style=\"color:silver;\">*<\/span>\u00a0<span style=\"color:maroon;\">(<\/span>\u00a0\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<span style=\"color:maroon;\">avg_user_impact<\/span>\u00a0<span style=\"color:silver;\">*<\/span>\u00a0<span style=\"color:black;\">0.01<\/span>\u00a0<span style=\"color:maroon;\">)<\/span>\u00a0<span style=\"color:blue;\">DESC<\/span><span style=\"color:maroon;\">)<\/span>\u00a0<span style=\"color:blue;\">AS<\/span>\u00a0<span style=\"color:black;\"><i>VARCHAR<\/i><\/span><span style=\"color:maroon;\">(<\/span><span style=\"color:black;\">150<\/span><span style=\"color:maroon;\">)<\/span><span style=\"color:maroon;\">)<\/span>\u00a0\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<span style=\"color:silver;\">+<\/span>\u00a0<span style=\"color:red;\">'\u00a0on\u00a0'<\/span>\u00a0<span style=\"color:silver;\">+<\/span>\u00a0<span style=\"color:fuchsia;\"><i>Parsename<\/i><\/span><span style=\"color:maroon;\">(<\/span>\u00a0<span style=\"color:maroon;\">mid<\/span><span style=\"color:silver;\">.<\/span><span style=\"color:maroon;\">[statement]<\/span><span style=\"color:silver;\">,<\/span>\u00a0<span style=\"color:black;\">1<\/span><span style=\"color:maroon;\">)<\/span>\u00a0\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<span style=\"color:maroon;\">drop_index<\/span><span style=\"color:silver;\">,<\/span>\u00a0\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<span style=\"color:maroon;\">mid<\/span><span style=\"color:silver;\">.<\/span><span style=\"color:maroon;\">equality_columns<\/span><span style=\"color:silver;\">,<\/span>\u00a0\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<span style=\"color:maroon;\">mid<\/span><span style=\"color:silver;\">.<\/span><span style=\"color:maroon;\">inequality_columns<\/span><span style=\"color:silver;\">,<\/span>\u00a0\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<span style=\"color:maroon;\">mid<\/span><span style=\"color:silver;\">.<\/span><span style=\"color:maroon;\">included_columns<\/span><span style=\"color:silver;\">,<\/span>\u00a0\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<span style=\"color:maroon;\">migs<\/span><span style=\"color:silver;\">.<\/span><span style=\"color:maroon;\">unique_compiles<\/span><span style=\"color:silver;\">,<\/span>\u00a0\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<span style=\"color:maroon;\">migs<\/span><span style=\"color:silver;\">.<\/span><span style=\"color:maroon;\">user_seeks<\/span><span style=\"color:silver;\">,<\/span>\u00a0\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<span style=\"color:maroon;\">migs<\/span><span style=\"color:silver;\">.<\/span><span style=\"color:maroon;\">avg_total_user_cost<\/span><span style=\"color:silver;\">,<\/span>\u00a0\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<span style=\"color:green;\"><i>--\u00a0\u00a0Average\u00a0cost\u00a0of\u00a0the\u00a0user\u00a0queries\u00a0that\u00a0could\u00a0be\u00a0reduced\u00a0by\u00a0the\u00a0index\u00a0in\u00a0the\u00a0group.<\/i><\/span>\u00a0\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<span style=\"color:maroon;\">migs<\/span><span style=\"color:silver;\">.<\/span><span style=\"color:maroon;\">avg_user_impact<\/span>\u00a0\n<span style=\"color:green;\"><i>--Average\u00a0percentage\u00a0benefit\u00a0that\u00a0user\u00a0queries\u00a0could\u00a0experience,<\/i><\/span>\u00a0\n<span style=\"color:blue;\">FROM<\/span>\u00a0\u00a0\u00a0<span style=\"color:maroon;\">sys<\/span><span style=\"color:silver;\">.<\/span><span style=\"color:maroon;\">dm_db_missing_index_group_stats<\/span>\u00a0<span style=\"color:blue;\">AS<\/span>\u00a0<span style=\"color:maroon;\">migs<\/span>\u00a0<span style=\"color:blue;\">WITH<\/span>\u00a0<span style=\"color:maroon;\">(<\/span><span style=\"color:maroon;\">NOLOCK<\/span><span style=\"color:maroon;\">)<\/span>\u00a0\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<span style=\"color:blue;\">INNER<\/span>\u00a0<span style=\"color:blue;\">JOIN<\/span>\u00a0<span style=\"color:maroon;\">sys<\/span><span style=\"color:silver;\">.<\/span><span style=\"color:maroon;\">dm_db_missing_index_groups<\/span>\u00a0<span style=\"color:blue;\">AS<\/span>\u00a0<span style=\"color:maroon;\">mig<\/span>\u00a0<span style=\"color:blue;\">WITH<\/span>\u00a0<span style=\"color:maroon;\">(<\/span><span style=\"color:maroon;\">NOLOCK<\/span><span style=\"color:maroon;\">)<\/span>\u00a0\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<span style=\"color:blue;\">ON<\/span>\u00a0<span style=\"color:maroon;\">migs<\/span><span style=\"color:silver;\">.<\/span><span style=\"color:maroon;\">group_handle<\/span>\u00a0<span style=\"color:silver;\">=<\/span>\u00a0<span style=\"color:maroon;\">mig<\/span><span style=\"color:silver;\">.<\/span><span style=\"color:maroon;\">index_group_handle<\/span>\u00a0\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<span style=\"color:blue;\">INNER<\/span>\u00a0<span style=\"color:blue;\">JOIN<\/span>\u00a0<span style=\"color:maroon;\">sys<\/span><span style=\"color:silver;\">.<\/span><span style=\"color:maroon;\">dm_db_missing_index_details<\/span>\u00a0<span style=\"color:blue;\">AS<\/span>\u00a0<span style=\"color:maroon;\">mid<\/span>\u00a0<span style=\"color:blue;\">WITH<\/span>\u00a0<span style=\"color:maroon;\">(<\/span><span style=\"color:maroon;\">NOLOCK<\/span><span style=\"color:maroon;\">)<\/span>\u00a0\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<span style=\"color:blue;\">ON<\/span>\u00a0<span style=\"color:maroon;\">mig<\/span><span style=\"color:silver;\">.<\/span><span style=\"color:maroon;\">index_handle<\/span>\u00a0<span style=\"color:silver;\">=<\/span>\u00a0<span style=\"color:maroon;\">mid<\/span><span style=\"color:silver;\">.<\/span><span style=\"color:maroon;\">index_handle<\/span>\u00a0\n<span style=\"color:blue;\">WHERE<\/span>\u00a0\u00a0<span style=\"color:maroon;\">mid<\/span><span style=\"color:silver;\">.<\/span><span style=\"color:maroon;\">database_id<\/span>\u00a0<span style=\"color:silver;\">=<\/span>\u00a0<span style=\"color:fuchsia;\"><i>Db_id<\/i><\/span><span style=\"color:maroon;\">(<\/span><span style=\"color:maroon;\">)<\/span>\u00a0<span style=\"color:green;\"><i>--\u00a0Remove\u00a0this\u00a0to\u00a0see\u00a0for\u00a0entire\u00a0instance<\/i><\/span>\u00a0\n<span style=\"color:blue;\">ORDER<\/span>\u00a0\u00a0<span style=\"color:blue;\">BY<\/span>\u00a0<span style=\"color:maroon;\">index_advantage<\/span>\u00a0<span style=\"color:blue;\">DESC<\/span>\u00a0\n<span style=\"color:blue;\">OPTION<\/span>\u00a0<span style=\"color:maroon;\">(<\/span><span style=\"color:maroon;\">RECOMPILE<\/span><span style=\"color:maroon;\">)<\/span><span style=\"color:silver;\">;<\/span><\/pre>\n<\/blockquote>\n<p><strong>2) Find missing indexes on FK<\/strong><\/p>\n<blockquote><p><strong><span style=\"color:blue;\">SELECT<\/span>\u00a0<span style=\"color:maroon;\">fk<\/span><span style=\"color:silver;\">.<\/span><span style=\"color:blue;\">NAME<\/span>\u00a0\u00a0\u00a0\u00a0<span style=\"color:blue;\">AS<\/span>\u00a0<span style=\"color:maroon;\">CONSTRAINT_NAME<\/span><span style=\"color:silver;\">,<\/span><\/strong><br \/>\n<strong> <span style=\"color:maroon;\">s<\/span><span style=\"color:silver;\">.<\/span><span style=\"color:blue;\">NAME<\/span>\u00a0\u00a0\u00a0\u00a0\u00a0<span style=\"color:blue;\">AS<\/span>\u00a0<span style=\"color:maroon;\">SCHEMA_NAME<\/span><span style=\"color:silver;\">,<\/span><\/strong><br \/>\n<strong> <span style=\"color:maroon;\">o<\/span><span style=\"color:silver;\">.<\/span><span style=\"color:blue;\">NAME<\/span>\u00a0\u00a0\u00a0\u00a0\u00a0<span style=\"color:blue;\">AS<\/span>\u00a0<span style=\"color:maroon;\">TABLE_NAME<\/span><span style=\"color:silver;\">,<\/span><\/strong><br \/>\n<strong> <span style=\"color:maroon;\">fkc_c<\/span><span style=\"color:silver;\">.<\/span><span style=\"color:blue;\">NAME<\/span>\u00a0<span style=\"color:blue;\">AS<\/span>\u00a0<span style=\"color:maroon;\">CONSTRAINT_COLUMN_NAME<\/span><\/strong><br \/>\n<strong> <span style=\"color:blue;\">FROM<\/span>\u00a0\u00a0\u00a0<span style=\"color:maroon;\">sys<\/span><span style=\"color:silver;\">.<\/span><span style=\"color:maroon;\">foreign_keys<\/span>\u00a0<span style=\"color:blue;\">AS<\/span>\u00a0<span style=\"color:maroon;\">fk<\/span><\/strong><br \/>\n<strong> <span style=\"color:blue;\">JOIN<\/span>\u00a0<span style=\"color:maroon;\">sys<\/span><span style=\"color:silver;\">.<\/span><span style=\"color:maroon;\">foreign_key_columns<\/span>\u00a0<span style=\"color:blue;\">AS<\/span>\u00a0<span style=\"color:maroon;\">fkc<\/span><\/strong><br \/>\n<strong> <span style=\"color:blue;\">ON<\/span>\u00a0<span style=\"color:maroon;\">fk<\/span><span style=\"color:silver;\">.<\/span><span style=\"color:maroon;\">object_id<\/span>\u00a0<span style=\"color:silver;\">=<\/span>\u00a0<span style=\"color:maroon;\">fkc<\/span><span style=\"color:silver;\">.<\/span><span style=\"color:maroon;\">constraint_object_id<\/span><\/strong><br \/>\n<strong> <span style=\"color:blue;\">JOIN<\/span>\u00a0<span style=\"color:maroon;\">sys<\/span><span style=\"color:silver;\">.<\/span><span style=\"color:maroon;\">columns<\/span>\u00a0<span style=\"color:blue;\">AS<\/span>\u00a0<span style=\"color:maroon;\">fkc_c<\/span><\/strong><br \/>\n<strong> <span style=\"color:blue;\">ON<\/span>\u00a0<span style=\"color:maroon;\">fkc<\/span><span style=\"color:silver;\">.<\/span><span style=\"color:maroon;\">parent_object_id<\/span>\u00a0<span style=\"color:silver;\">=<\/span>\u00a0<span style=\"color:maroon;\">fkc_c<\/span><span style=\"color:silver;\">.<\/span><span style=\"color:maroon;\">object_id<\/span><\/strong><br \/>\n<strong> <span style=\"color:blue;\">AND<\/span>\u00a0<span style=\"color:maroon;\">fkc<\/span><span style=\"color:silver;\">.<\/span><span style=\"color:maroon;\">parent_column_id<\/span>\u00a0<span style=\"color:silver;\">=<\/span>\u00a0<span style=\"color:maroon;\">fkc_c<\/span><span style=\"color:silver;\">.<\/span><span style=\"color:maroon;\">column_id<\/span><\/strong><br \/>\n<strong> <span style=\"color:blue;\">LEFT<\/span>\u00a0<span style=\"color:blue;\">JOIN<\/span>\u00a0<span style=\"color:maroon;\">sys<\/span><span style=\"color:silver;\">.<\/span><span style=\"color:maroon;\">index_columns<\/span>\u00a0<span style=\"color:maroon;\">ic<\/span><\/strong><br \/>\n<strong> <span style=\"color:blue;\">JOIN<\/span>\u00a0<span style=\"color:maroon;\">sys<\/span><span style=\"color:silver;\">.<\/span><span style=\"color:maroon;\">columns<\/span>\u00a0<span style=\"color:blue;\">AS<\/span>\u00a0<span style=\"color:maroon;\">c<\/span><\/strong><br \/>\n<strong> <span style=\"color:blue;\">ON<\/span>\u00a0<span style=\"color:maroon;\">ic<\/span><span style=\"color:silver;\">.<\/span><span style=\"color:maroon;\">object_id<\/span>\u00a0<span style=\"color:silver;\">=<\/span>\u00a0<span style=\"color:maroon;\">c<\/span><span style=\"color:silver;\">.<\/span><span style=\"color:maroon;\">object_id<\/span><\/strong><br \/>\n<strong> <span style=\"color:blue;\">AND<\/span>\u00a0<span style=\"color:maroon;\">ic<\/span><span style=\"color:silver;\">.<\/span><span style=\"color:maroon;\">column_id<\/span>\u00a0<span style=\"color:silver;\">=<\/span>\u00a0<span style=\"color:maroon;\">c<\/span><span style=\"color:silver;\">.<\/span><span style=\"color:maroon;\">column_id<\/span><\/strong><br \/>\n<strong> <span style=\"color:blue;\">ON<\/span>\u00a0<span style=\"color:maroon;\">fkc<\/span><span style=\"color:silver;\">.<\/span><span style=\"color:maroon;\">parent_object_id<\/span>\u00a0<span style=\"color:silver;\">=<\/span>\u00a0<span style=\"color:maroon;\">ic<\/span><span style=\"color:silver;\">.<\/span><span style=\"color:maroon;\">object_id<\/span><\/strong><br \/>\n<strong> <span style=\"color:blue;\">AND<\/span>\u00a0<span style=\"color:maroon;\">fkc<\/span><span style=\"color:silver;\">.<\/span><span style=\"color:maroon;\">parent_column_id<\/span>\u00a0<span style=\"color:silver;\">=<\/span>\u00a0<span style=\"color:maroon;\">ic<\/span><span style=\"color:silver;\">.<\/span><span style=\"color:maroon;\">column_id<\/span><\/strong><br \/>\n<strong> <span style=\"color:blue;\">JOIN<\/span>\u00a0<span style=\"color:maroon;\">sys<\/span><span style=\"color:silver;\">.<\/span><span style=\"color:maroon;\">objects<\/span>\u00a0<span style=\"color:blue;\">AS<\/span>\u00a0<span style=\"color:maroon;\">o<\/span><\/strong><br \/>\n<strong> <span style=\"color:blue;\">ON<\/span>\u00a0<span style=\"color:maroon;\">o<\/span><span style=\"color:silver;\">.<\/span><span style=\"color:maroon;\">object_id<\/span>\u00a0<span style=\"color:silver;\">=<\/span>\u00a0<span style=\"color:maroon;\">fk<\/span><span style=\"color:silver;\">.<\/span><span style=\"color:maroon;\">parent_object_id<\/span><\/strong><br \/>\n<strong> <span style=\"color:blue;\">JOIN<\/span>\u00a0<span style=\"color:maroon;\">sys<\/span><span style=\"color:silver;\">.<\/span><span style=\"color:maroon;\">schemas<\/span>\u00a0<span style=\"color:blue;\">AS<\/span>\u00a0<span style=\"color:maroon;\">s<\/span><\/strong><br \/>\n<strong> <span style=\"color:blue;\">ON<\/span>\u00a0<span style=\"color:maroon;\">o<\/span><span style=\"color:silver;\">.<\/span><span style=\"color:maroon;\">schema_id<\/span>\u00a0<span style=\"color:silver;\">=<\/span>\u00a0<span style=\"color:maroon;\">s<\/span><span style=\"color:silver;\">.<\/span><span style=\"color:maroon;\">schema_id<\/span><\/strong><br \/>\n<strong> <span style=\"color:blue;\">WHERE<\/span>\u00a0\u00a0<span style=\"color:maroon;\">c<\/span><span style=\"color:silver;\">.<\/span><span style=\"color:blue;\">NAME<\/span>\u00a0<span style=\"color:blue;\">IS<\/span>\u00a0<span style=\"color:blue;\">NULL<\/span><\/strong><\/p><\/blockquote>\n<p><strong>3) Find missing indexes\u00a0using stored query plans.<\/strong><\/p>\n<blockquote>\n<pre>\n<span style=\"color:blue;\">SELECT<\/span>\u00a0<span style=\"color:maroon;\">MissingIndexNode<\/span><span style=\"color:silver;\">.<\/span><span style=\"color:#ff0080;\"><b>value<\/b><\/span><span style=\"color:maroon;\">(<\/span><span style=\"color:red;\">'(MissingIndexGroup\/@Impact)[1]'<\/span><span style=\"color:silver;\">,<\/span>\u00a0<span style=\"color:red;\">'float'<\/span><span style=\"color:maroon;\">)<\/span>\u00a0\u00a0\u00a0\u00a0\u00a0<span style=\"color:blue;\">AS<\/span>\u00a0\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<span style=\"color:maroon;\">impact<\/span><span style=\"color:silver;\">,<\/span>\u00a0\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<span style=\"color:fuchsia;\"><i>Object_name<\/i><\/span><span style=\"color:maroon;\">(<\/span><span style=\"color:maroon;\">sub<\/span><span style=\"color:silver;\">.<\/span><span style=\"color:maroon;\">objectid<\/span><span style=\"color:silver;\">,<\/span>\u00a0<span style=\"color:maroon;\">sub<\/span><span style=\"color:silver;\">.<\/span><span style=\"color:maroon;\">dbid<\/span><span style=\"color:maroon;\">)<\/span>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<span style=\"color:blue;\">AS<\/span>\u00a0\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<span style=\"color:maroon;\">calling_object_name<\/span><span style=\"color:silver;\">,<\/span>\u00a0\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<span style=\"color:maroon;\">MissingIndexNode<\/span><span style=\"color:silver;\">.<\/span><span style=\"color:#ff0080;\"><b>value<\/b><\/span>\u00a0<span style=\"color:maroon;\">(<\/span><span style=\"color:red;\">'(MissingIndexGroup\/MissingIndex\/@Database)[1]'<\/span><span style=\"color:silver;\">,<\/span>\u00a0\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<span style=\"color:red;\">'VARCHAR(128)'<\/span><span style=\"color:maroon;\">)<\/span>\u00a0\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<span style=\"color:silver;\">+<\/span>\u00a0<span style=\"color:red;\">'.'<\/span>\u00a0\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<span style=\"color:silver;\">+<\/span>\u00a0<span style=\"color:maroon;\">MissingIndexNode<\/span><span style=\"color:silver;\">.<\/span><span style=\"color:#ff0080;\"><b>value<\/b><\/span>\u00a0<span style=\"color:maroon;\">(<\/span><span style=\"color:red;\">'(MissingIndexGroup\/MissingIndex\/@Schema)[1]'<\/span><span style=\"color:silver;\">,<\/span>\u00a0\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<span style=\"color:red;\">'VARCHAR(128)'<\/span><span style=\"color:maroon;\">)<\/span>\u00a0\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<span style=\"color:silver;\">+<\/span>\u00a0<span style=\"color:red;\">'.'<\/span>\u00a0\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<span style=\"color:silver;\">+<\/span>\u00a0<span style=\"color:maroon;\">MissingIndexNode<\/span><span style=\"color:silver;\">.<\/span><span style=\"color:#ff0080;\"><b>value<\/b><\/span>\u00a0<span style=\"color:maroon;\">(<\/span><span style=\"color:red;\">'(MissingIndexGroup\/MissingIndex\/@Table)[1]'<\/span><span style=\"color:silver;\">,<\/span>\u00a0\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<span style=\"color:red;\">'VARCHAR(128)'<\/span><span style=\"color:maroon;\">)<\/span>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<span style=\"color:blue;\">AS<\/span>\u00a0\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<span style=\"color:maroon;\">table_name<\/span><span style=\"color:silver;\">,<\/span>\u00a0\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<span style=\"color:fuchsia;\"><i>Stuff<\/i><\/span><span style=\"color:maroon;\">(<\/span><span style=\"color:maroon;\">(<\/span><span style=\"color:blue;\">SELECT<\/span>\u00a0<span style=\"color:red;\">','<\/span>\u00a0<span style=\"color:silver;\">+<\/span>\u00a0<span style=\"color:maroon;\">c<\/span><span style=\"color:silver;\">.<\/span><span style=\"color:#ff0080;\"><b>value<\/b><\/span><span style=\"color:maroon;\">(<\/span><span style=\"color:red;\">'(@Name)[1]'<\/span><span style=\"color:silver;\">,<\/span>\u00a0<span style=\"color:red;\">'VARCHAR(128)'<\/span><span style=\"color:maroon;\">)<\/span>\u00a0\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<span style=\"color:blue;\">FROM<\/span>\u00a0\u00a0\u00a0<span style=\"color:maroon;\">MissingIndexNode<\/span><span style=\"color:silver;\">.<\/span><span style=\"color:#ff0080;\"><b>nodes<\/b><\/span>\u00a0<span style=\"color:maroon;\">(<\/span>\u00a0\n<span style=\"color:red;\">'MissingIndexGroup\/MissingIndex\/\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0ColumnGroup[@Usage=''EQUALITY'']\/Column'<\/span>\n<span style=\"color:maroon;\">)<\/span>\u00a0<span style=\"color:blue;\">AS<\/span>\u00a0<span style=\"color:maroon;\">t<\/span>\u00a0<span style=\"color:maroon;\">(<\/span>\u00a0<span style=\"color:maroon;\">c<\/span>\u00a0<span style=\"color:maroon;\">)<\/span>\u00a0\n\u00a0<span style=\"color:blue;\">FOR<\/span>\u00a0<span style=\"color:maroon;\">XML<\/span>\u00a0<span style=\"color:maroon;\">PATH<\/span><span style=\"color:maroon;\">(<\/span><span style=\"color:red;\">''<\/span><span style=\"color:maroon;\">)<\/span><span style=\"color:maroon;\">)<\/span><span style=\"color:silver;\">,<\/span>\u00a0<span style=\"color:black;\">1<\/span><span style=\"color:silver;\">,<\/span>\u00a0<span style=\"color:black;\">1<\/span><span style=\"color:silver;\">,<\/span>\u00a0<span style=\"color:red;\">''<\/span><span style=\"color:maroon;\">)<\/span>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<span style=\"color:blue;\">AS<\/span>\u00a0\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<span style=\"color:maroon;\">equality_columns<\/span><span style=\"color:silver;\">,<\/span>\u00a0\n<span style=\"color:fuchsia;\"><i>Stuff<\/i><\/span><span style=\"color:maroon;\">(<\/span><span style=\"color:maroon;\">(<\/span><span style=\"color:blue;\">SELECT<\/span>\u00a0<span style=\"color:red;\">','<\/span>\u00a0<span style=\"color:silver;\">+<\/span>\u00a0<span style=\"color:maroon;\">c<\/span><span style=\"color:silver;\">.<\/span><span style=\"color:#ff0080;\"><b>value<\/b><\/span><span style=\"color:maroon;\">(<\/span><span style=\"color:red;\">'(@Name)[1]'<\/span><span style=\"color:silver;\">,<\/span>\u00a0<span style=\"color:red;\">'VARCHAR(128)'<\/span><span style=\"color:maroon;\">)<\/span>\u00a0\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<span style=\"color:blue;\">FROM<\/span>\u00a0\u00a0\u00a0<span style=\"color:maroon;\">MissingIndexNode<\/span><span style=\"color:silver;\">.<\/span><span style=\"color:#ff0080;\"><b>nodes<\/b><\/span>\u00a0<span style=\"color:maroon;\">(<\/span>\u00a0\n<span style=\"color:red;\">'MissingIndexGroup\/MissingIndex\/\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0ColumnGroup[@Usage=''INEQUALITY'']\/Column'<\/span>\n<span style=\"color:maroon;\">)<\/span>\u00a0<span style=\"color:blue;\">AS<\/span>\u00a0<span style=\"color:maroon;\">t<\/span>\u00a0<span style=\"color:maroon;\">(<\/span>\u00a0<span style=\"color:maroon;\">c<\/span>\u00a0<span style=\"color:maroon;\">)<\/span>\u00a0\n\u00a0<span style=\"color:blue;\">FOR<\/span>\u00a0<span style=\"color:maroon;\">XML<\/span>\u00a0<span style=\"color:maroon;\">PATH<\/span><span style=\"color:maroon;\">(<\/span><span style=\"color:red;\">''<\/span><span style=\"color:maroon;\">)<\/span><span style=\"color:maroon;\">)<\/span><span style=\"color:silver;\">,<\/span>\u00a0<span style=\"color:black;\">1<\/span><span style=\"color:silver;\">,<\/span>\u00a0<span style=\"color:black;\">1<\/span><span style=\"color:silver;\">,<\/span>\u00a0<span style=\"color:red;\">''<\/span><span style=\"color:maroon;\">)<\/span>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<span style=\"color:blue;\">AS<\/span>\u00a0\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<span style=\"color:maroon;\">inequality_columns<\/span><span style=\"color:silver;\">,<\/span>\u00a0\n<span style=\"color:fuchsia;\"><i>Stuff<\/i><\/span><span style=\"color:maroon;\">(<\/span><span style=\"color:maroon;\">(<\/span><span style=\"color:blue;\">SELECT<\/span>\u00a0<span style=\"color:red;\">','<\/span>\u00a0<span style=\"color:silver;\">+<\/span>\u00a0<span style=\"color:maroon;\">c<\/span><span style=\"color:silver;\">.<\/span><span style=\"color:#ff0080;\"><b>value<\/b><\/span><span style=\"color:maroon;\">(<\/span><span style=\"color:red;\">'(@Name)[1]'<\/span><span style=\"color:silver;\">,<\/span>\u00a0<span style=\"color:red;\">'VARCHAR(128)'<\/span><span style=\"color:maroon;\">)<\/span>\u00a0\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<span style=\"color:blue;\">FROM<\/span>\u00a0\u00a0\u00a0<span style=\"color:maroon;\">MissingIndexNode<\/span><span style=\"color:silver;\">.<\/span><span style=\"color:#ff0080;\"><b>nodes<\/b><\/span>\u00a0<span style=\"color:maroon;\">(<\/span>\u00a0\n<span style=\"color:red;\">'MissingIndexGroup\/MissingIndex\/\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0ColumnGroup[@Usage=''INCLUDE'']\/Column'<\/span>\n<span style=\"color:maroon;\">)<\/span>\u00a0<span style=\"color:blue;\">AS<\/span>\u00a0<span style=\"color:maroon;\">t<\/span>\u00a0<span style=\"color:maroon;\">(<\/span>\u00a0<span style=\"color:maroon;\">c<\/span>\u00a0<span style=\"color:maroon;\">)<\/span>\u00a0\n\u00a0<span style=\"color:blue;\">FOR<\/span>\u00a0<span style=\"color:maroon;\">XML<\/span>\u00a0<span style=\"color:maroon;\">PATH<\/span><span style=\"color:maroon;\">(<\/span><span style=\"color:red;\">''<\/span><span style=\"color:maroon;\">)<\/span><span style=\"color:maroon;\">)<\/span><span style=\"color:silver;\">,<\/span>\u00a0<span style=\"color:black;\">1<\/span><span style=\"color:silver;\">,<\/span>\u00a0<span style=\"color:black;\">1<\/span><span style=\"color:silver;\">,<\/span>\u00a0<span style=\"color:red;\">''<\/span><span style=\"color:maroon;\">)<\/span>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<span style=\"color:blue;\">AS<\/span>\u00a0\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<span style=\"color:maroon;\">include_columns<\/span><span style=\"color:silver;\">,<\/span>\u00a0\n<span style=\"color:maroon;\">sub<\/span><span style=\"color:silver;\">.<\/span><span style=\"color:maroon;\">usecounts<\/span>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<span style=\"color:blue;\">AS<\/span>\u00a0\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<span style=\"color:maroon;\">qp_usecounts<\/span><span style=\"color:silver;\">,<\/span>\u00a0\n<span style=\"color:maroon;\">sub<\/span><span style=\"color:silver;\">.<\/span><span style=\"color:maroon;\">refcounts<\/span>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<span style=\"color:blue;\">AS<\/span>\u00a0\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<span style=\"color:maroon;\">qp_refcounts<\/span><span style=\"color:silver;\">,<\/span>\u00a0\n<span style=\"color:maroon;\">qs<\/span><span style=\"color:silver;\">.<\/span><span style=\"color:maroon;\">execution_count<\/span>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<span style=\"color:blue;\">AS<\/span>\u00a0\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<span style=\"color:maroon;\">qs_execution_count<\/span><span style=\"color:silver;\">,<\/span>\u00a0\n<span style=\"color:maroon;\">qs<\/span><span style=\"color:silver;\">.<\/span><span style=\"color:maroon;\">last_execution_time<\/span>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<span style=\"color:blue;\">AS<\/span>\u00a0\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<span style=\"color:maroon;\">qs_last_exec_time<\/span><span style=\"color:silver;\">,<\/span>\u00a0\n<span style=\"color:maroon;\">qs<\/span><span style=\"color:silver;\">.<\/span><span style=\"color:maroon;\">total_logical_reads<\/span>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<span style=\"color:blue;\">AS<\/span>\u00a0\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<span style=\"color:maroon;\">qs_total_logical_reads<\/span><span style=\"color:silver;\">,<\/span>\u00a0\n<span style=\"color:maroon;\">qs<\/span><span style=\"color:silver;\">.<\/span><span style=\"color:maroon;\">total_elapsed_time<\/span>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<span style=\"color:blue;\">AS<\/span>\u00a0\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<span style=\"color:maroon;\">qs_total_elapsed_time<\/span><span style=\"color:silver;\">,<\/span>\u00a0\n<span style=\"color:maroon;\">qs<\/span><span style=\"color:silver;\">.<\/span><span style=\"color:maroon;\">total_physical_reads<\/span>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<span style=\"color:blue;\">AS<\/span>\u00a0\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<span style=\"color:maroon;\">qs_total_physical_reads<\/span><span style=\"color:silver;\">,<\/span>\u00a0\n<span style=\"color:maroon;\">qs<\/span><span style=\"color:silver;\">.<\/span><span style=\"color:maroon;\">total_worker_time<\/span>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<span style=\"color:blue;\">AS<\/span>\u00a0\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<span style=\"color:maroon;\">qs_total_worker_time<\/span><span style=\"color:silver;\">,<\/span>\u00a0\n<span style=\"color:maroon;\">StmtPlanStub<\/span><span style=\"color:silver;\">.<\/span><span style=\"color:#ff0080;\"><b>value<\/b><\/span><span style=\"color:maroon;\">(<\/span><span style=\"color:red;\">'(StmtSimple\/@StatementText)[1]'<\/span><span style=\"color:silver;\">,<\/span>\u00a0<span style=\"color:red;\">'varchar(8000)'<\/span><span style=\"color:maroon;\">)<\/span>\u00a0<span style=\"color:blue;\">AS<\/span>\u00a0\n<span style=\"color:maroon;\">statement_text<\/span>\u00a0\n<span style=\"color:blue;\">FROM<\/span>\u00a0\u00a0\u00a0<span style=\"color:maroon;\">(<\/span><span style=\"color:blue;\">SELECT<\/span>\u00a0<span style=\"color:#ff0080;\"><b>ROW_NUMBER<\/b><\/span><span style=\"color:maroon;\">(<\/span><span style=\"color:maroon;\">)<\/span>\u00a0\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<span style=\"color:blue;\">OVER<\/span>\u00a0<span style=\"color:maroon;\">(<\/span>\u00a0\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<span style=\"color:maroon;\">PARTITION<\/span>\u00a0<span style=\"color:blue;\">BY<\/span>\u00a0<span style=\"color:maroon;\">qs<\/span><span style=\"color:silver;\">.<\/span><span style=\"color:maroon;\">plan_handle<\/span>\u00a0\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<span style=\"color:blue;\">ORDER<\/span>\u00a0<span style=\"color:blue;\">BY<\/span>\u00a0<span style=\"color:maroon;\">qs<\/span><span style=\"color:silver;\">.<\/span><span style=\"color:maroon;\">statement_start_offset<\/span>\u00a0<span style=\"color:maroon;\">)<\/span>\u00a0<span style=\"color:blue;\">AS<\/span>\u00a0<span style=\"color:maroon;\">StatementID<\/span><span style=\"color:silver;\">,<\/span>\u00a0\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<span style=\"color:maroon;\">qs<\/span><span style=\"color:silver;\">.<\/span><span style=\"color:silver;\">*<\/span>\u00a0\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<span style=\"color:blue;\">FROM<\/span>\u00a0\u00a0\u00a0<span style=\"color:maroon;\">sys<\/span><span style=\"color:silver;\">.<\/span><span style=\"color:maroon;\">dm_exec_query_stats<\/span>\u00a0<span style=\"color:maroon;\">qs<\/span><span style=\"color:maroon;\">)<\/span>\u00a0<span style=\"color:blue;\">AS<\/span>\u00a0<span style=\"color:maroon;\">qs<\/span>\u00a0\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<span style=\"color:blue;\">JOIN<\/span>\u00a0<span style=\"color:maroon;\">(<\/span><span style=\"color:blue;\">SELECT<\/span>\u00a0<span style=\"color:maroon;\">x<\/span><span style=\"color:silver;\">.<\/span><span style=\"color:#ff0080;\"><b>query<\/b><\/span><span style=\"color:maroon;\">(<\/span><span style=\"color:red;\">'..\/..\/..'<\/span><span style=\"color:maroon;\">)<\/span>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<span style=\"color:blue;\">AS<\/span>\u00a0<span style=\"color:maroon;\">StmtPlanStub<\/span>\u00a0\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<span style=\"color:silver;\">,<\/span>\u00a0\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<span style=\"color:maroon;\">x<\/span><span style=\"color:silver;\">.<\/span><span style=\"color:#ff0080;\"><b>query<\/b><\/span><span style=\"color:maroon;\">(<\/span><span style=\"color:red;\">'.'<\/span><span style=\"color:maroon;\">)<\/span>\u00a0\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<span style=\"color:blue;\">AS<\/span>\u00a0<span style=\"color:maroon;\">MissingIndexNode<\/span><span style=\"color:silver;\">,<\/span>\u00a0\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<span style=\"color:maroon;\">x<\/span><span style=\"color:silver;\">.<\/span><span style=\"color:#ff0080;\"><b>value<\/b><\/span><span style=\"color:maroon;\">(<\/span><span style=\"color:red;\">'(..\/..\/..\/@StatementId)[1]'<\/span><span style=\"color:silver;\">,<\/span>\u00a0<span style=\"color:red;\">'int'<\/span><span style=\"color:maroon;\">)<\/span>\u00a0<span style=\"color:blue;\">AS<\/span>\u00a0<span style=\"color:maroon;\">StatementID<\/span><span style=\"color:silver;\">,<\/span>\u00a0\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<span style=\"color:maroon;\">cp<\/span><span style=\"color:silver;\">.<\/span><span style=\"color:silver;\">*<\/span><span style=\"color:silver;\">,<\/span>\u00a0\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<span style=\"color:maroon;\">qp<\/span><span style=\"color:silver;\">.<\/span><span style=\"color:silver;\">*<\/span>\u00a0\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<span style=\"color:blue;\">FROM<\/span>\u00a0\u00a0\u00a0<span style=\"color:maroon;\">sys<\/span><span style=\"color:silver;\">.<\/span><span style=\"color:maroon;\">dm_exec_cached_plans<\/span>\u00a0<span style=\"color:blue;\">AS<\/span>\u00a0<span style=\"color:maroon;\">cp<\/span>\u00a0\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<span style=\"color:blue;\">CROSS<\/span>\u00a0<span style=\"color:maroon;\">APPLY<\/span>\u00a0<span style=\"color:maroon;\">sys<\/span><span style=\"color:silver;\">.<\/span><span style=\"color:#ff0080;\"><b>dm_exec_query_plan<\/b><\/span>\u00a0<span style=\"color:maroon;\">(<\/span><span style=\"color:maroon;\">cp<\/span><span style=\"color:silver;\">.<\/span><span style=\"color:maroon;\">plan_handle<\/span><span style=\"color:maroon;\">)<\/span>\u00a0<span style=\"color:maroon;\">qp<\/span>\u00a0\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<span style=\"color:blue;\">CROSS<\/span>\u00a0<span style=\"color:maroon;\">APPLY<\/span>\u00a0<span style=\"color:maroon;\">qp<\/span><span style=\"color:silver;\">.<\/span><span style=\"color:maroon;\">query_plan<\/span><span style=\"color:silver;\">.<\/span><span style=\"color:#ff0080;\"><b>nodes<\/b><\/span>\u00a0<span style=\"color:maroon;\">(<\/span>\u00a0\n<span style=\"color:red;\">'\/ShowPlanXML\/BatchSequence\/\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Batch\/Statements\/StmtSimple\/\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0QueryPlan\/MissingIndexes\/\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0MissingIndexGroup'<\/span>\n<span style=\"color:maroon;\">)<\/span>\u00a0<span style=\"color:maroon;\">mi<\/span>\u00a0<span style=\"color:maroon;\">(<\/span>\u00a0<span style=\"color:maroon;\">x<\/span>\u00a0<span style=\"color:maroon;\">)<\/span><span style=\"color:maroon;\">)<\/span>\u00a0<span style=\"color:blue;\">AS<\/span>\u00a0<span style=\"color:maroon;\">sub<\/span>\u00a0\n\u00a0\u00a0<span style=\"color:blue;\">ON<\/span>\u00a0<span style=\"color:maroon;\">qs<\/span><span style=\"color:silver;\">.<\/span><span style=\"color:maroon;\">plan_handle<\/span>\u00a0<span style=\"color:silver;\">=<\/span>\u00a0<span style=\"color:maroon;\">sub<\/span><span style=\"color:silver;\">.<\/span><span style=\"color:maroon;\">plan_handle<\/span>\u00a0\n\u00a0\u00a0\u00a0\u00a0\u00a0<span style=\"color:blue;\">AND<\/span>\u00a0<span style=\"color:maroon;\">qs<\/span><span style=\"color:silver;\">.<\/span><span style=\"color:maroon;\">StatementID<\/span>\u00a0<span style=\"color:silver;\">=<\/span>\u00a0<span style=\"color:maroon;\">sub<\/span><span style=\"color:silver;\">.<\/span><span style=\"color:maroon;\">StatementID<\/span><\/pre>\n<\/blockquote>\n","protected":false},"excerpt":{"rendered":"<p>Problem Statement: Script to find the missing indexes using 3 different ways. Solution: 1) Find missing indexes\u00a0using\u00a0DMVs &#8211;Find\u00a0top\u00a0ten\u00a0missing\u00a0indexes\u00a0using\u00a0following\u00a0DMV\u00a0 SELECT\u00a0TOP\u00a010\u00a0(\u00a0user_seeks\u00a0)\u00a0*\u00a0avg_total_user_cost\u00a0*\u00a0(\u00a0avg_user_impact\u00a0*\u00a00.01\u00a0)\u00a0 \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0AS\u00a0[&#8230;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"om_disable_all_campaigns":false,"_mi_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0},"categories":[23,29],"tags":[87,114],"aioseo_notices":[],"_links":{"self":[{"href":"https:\/\/pramodsingla.com\/index.php?rest_route=\/wp\/v2\/posts\/969"}],"collection":[{"href":"https:\/\/pramodsingla.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/pramodsingla.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/pramodsingla.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/pramodsingla.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=969"}],"version-history":[{"count":0,"href":"https:\/\/pramodsingla.com\/index.php?rest_route=\/wp\/v2\/posts\/969\/revisions"}],"wp:attachment":[{"href":"https:\/\/pramodsingla.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=969"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/pramodsingla.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=969"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/pramodsingla.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=969"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}