{"id":254,"date":"2015-08-05T14:59:29","date_gmt":"2015-08-05T14:59:29","guid":{"rendered":"http:\/\/www.tiagoneves.net\/blog\/?p=254"},"modified":"2015-08-05T14:59:29","modified_gmt":"2015-08-05T14:59:29","slug":"encontrando-indices-nao-utilizados","status":"publish","type":"post","link":"https:\/\/www.tiagoneves.net\/blog\/encontrando-indices-nao-utilizados\/","title":{"rendered":"Encontrando \u00edndices n\u00e3o utilizados"},"content":{"rendered":"

Ol\u00e1 pessoal,<\/p>\n

Neste post irei compartilhar um script que nos auxilia a melhorar a performance das consultas que s\u00e3o executadas em nossa inst\u00e2ncia, mostrando \u00edndices que poderiam ser criados para melhorar a performance.<\/p>\n

Demonstra\u00e7\u00e3o:<\/p>\n

CREATE TABLE t1 (\r\n    c1 INT IDENTITY,\r\n    c2 AS c1 * 2,\r\n    c3 AS c1 + c1,\r\n    c4 CHAR (3000) DEFAULT 'a');\r\nGO\r\nCREATE UNIQUE CLUSTERED INDEX t1_clus ON t1 (c1);\r\nGO\r\n\r\n--Insert 100000 linhas\r\n\r\nSET NOCOUNT ON;\r\nGO\r\nINSERT INTO t1 DEFAULT VALUES;\r\nGO 100000\r\n\r\n--Select com a op\u00e7\u00e3o (include Actual Execution Plan Ctrl + M)\r\n\r\nSELECT * FROM t1\r\n    WHERE c2 BETWEEN 10 AND 1000\r\n    AND c3 > 1000;\r\n<\/pre>\n
Ao executar a query acima teremos o seguinte plano de execu\u00e7\u00e3o.<\/p>\n
\"missing_index\"<\/a><\/p>\n
No plano de execu\u00e7\u00e3o o SQLServer nos sugere a cria\u00e7\u00e3o de um \u00edndice na tabela T1, usando as colunas C2 e C3, incluindo as colunas C1 e C4.<\/p>\n
Depois que voc\u00ea executa a query, voc\u00ea consegue recuperar essas sugest\u00f5es de \u00edndice utilizando algumas DVM (dynamic management views).<\/p>\n
No script abaixo teremos informa\u00e7\u00f5es sobre qual impacto do \u00edndice, quais campos a ser adicionado no include, contagem de UserSeek e o script para cria\u00e7\u00e3o do \u00edndice.<\/p>\n
No script vamos usar as DMVs:<\/p>\n
sys.dm_db_missing_index_details<\/em><\/a> \u2013 Retorna informa\u00e7\u00f5es detalhadas sobre os \u00edndices ausentes, incluindo tabelas e colunas, a DMV exclui sugest\u00e3o de \u00edndices espaciais.<\/p>\n
sys.dm_db_missing_index_group_stats<\/em><\/a> \u2013 Retorna informa\u00e7\u00f5es sobre um grupo de \u00edndices ausentes, que com as atualiza\u00e7\u00f5es a cada consulta.<\/p>\n
sys.dm_db_missing_index_groups<\/em><\/a> \u2013 Retorna informa\u00e7\u00f5es de um \u00edndices contido em um grupo de \u00edndices.<\/p>\n
SELECT CAST(SERVERPROPERTY('ServerName') AS [nvarchar](256)) AS [SQLServer]\r\n    ,db.[database_id] AS [DatabaseID]\r\n    ,db.[name] AS [DatabaseName]\r\n    ,id.[object_id] AS [ObjectID]\r\n    ,id.[statement] AS [FullyQualifiedObjectName]\r\n    ,id.[equality_columns] AS [EqualityColumns]\r\n    ,id.[inequality_columns] AS [InEqualityColumns]\r\n    ,id.[included_columns] AS [IncludedColumns]\r\n    ,gs.[unique_compiles] AS [UniqueCompiles]\r\n    ,gs.[user_seeks] AS [UserSeeks]\r\n    ,gs.[user_scans] AS [UserScans]\r\n    ,gs.[last_user_seek] AS [LastUserSeekTime]\r\n    ,gs.[last_user_scan] AS [LastUserScanTime]\r\n    ,gs.[avg_total_user_cost] AS [AvgTotalUserCost]\r\n    ,gs.[avg_user_impact] AS [AvgUserImpact]\r\n    ,gs.[system_seeks] AS [SystemSeeks]\r\n    ,gs.[system_scans] AS [SystemScans]\r\n    ,gs.[last_system_seek] AS [LastSystemSeekTime]\r\n    ,gs.[last_system_scan] AS [LastSystemScanTime]\r\n    ,gs.[avg_total_system_cost] AS [AvgTotalSystemCost]\r\n    ,gs.[avg_system_impact] AS [AvgSystemImpact]\r\n    ,gs.[user_seeks] * gs.[avg_total_user_cost] * (gs.[avg_user_impact] * 0.01) AS [IndexAdvantage]\r\n    ,'CREATE INDEX [IX_' + OBJECT_NAME(id.[object_id], db.[database_id]) + '_' + REPLACE(REPLACE(REPLACE(ISNULL(id.[equality_columns], ''), ', ', '_'), '[', ''), ']', '') + CASE\r\n        WHEN id.[equality_columns] IS NOT NULL\r\n            AND id.[inequality_columns] IS NOT NULL\r\n            THEN '_'\r\n        ELSE ''\r\n        END + REPLACE(REPLACE(REPLACE(ISNULL(id.[inequality_columns], ''), ', ', '_'), '[', ''), ']', '') + '_' + LEFT(CAST(NEWID() AS [nvarchar](64)), 5) + ']' + ' ON ' + id.[statement] + ' (' + ISNULL(id.[equality_columns], '') + CASE\r\n        WHEN id.[equality_columns] IS NOT NULL\r\n            AND id.[inequality_columns] IS NOT NULL\r\n            THEN ','\r\n        ELSE ''\r\n        END + ISNULL(id.[inequality_columns], '') + ')' + ISNULL(' INCLUDE (' + id.[included_columns] + ')', '') AS [ProposedIndex]\r\n    ,CAST(CURRENT_TIMESTAMP AS [smalldatetime]) AS [CollectionDate]\r\nFROM [sys].[dm_db_missing_index_group_stats] gs WITH (NOLOCK)\r\nINNER JOIN [sys].[dm_db_missing_index_groups] ig WITH (NOLOCK)\r\n    ON gs.[group_handle] = ig.[index_group_handle]\r\nINNER JOIN [sys].[dm_db_missing_index_details] id WITH (NOLOCK)\r\n    ON ig.[index_handle] = id.[index_handle]\r\nINNER JOIN [sys].[databases] db WITH (NOLOCK)\r\n    ON db.[database_id] = id.[database_id]\r\nWHERE id.[database_id] > 4\r\n--and gs.[last_user_seek] >= '20150803 13:00'\r\n--and OBJECT_NAME(id.OBJECT_ID,id.database_id) = 't1'\r\nORDER BY [IndexAdvantage] DESC\r\nOPTION (RECOMPILE);<\/pre>\n
\"missing_index3\"<\/a> \"missing_index3_1\"<\/a> \"missing_index3_2\"<\/a><\/p>\n
Para visualizar as informa\u00e7\u00f5es das DMVs, voc\u00ea dever\u00e1 ser Sysadmin ou ter permiss\u00e3o VIEW SERVER STATE.<\/p>\n
Esses \u00edndices ausentes s\u00e3o os que o otimizador de consulta do SQL Server identifica durante as execu\u00e7\u00f5es de consultas e ficam armazenados nos planos de execu\u00e7\u00e3o, lembrando que o cache \u00e9 limpo sempre que o SQL Server for reiniciado, antes de criar os \u00edndices \u00e9 recomendado verificar se realmente o \u00edndice \u00e9 necess\u00e1rio..<\/p>\n
At\u00e9 a pr\u00f3xima.<\/p>\n","protected":false},"excerpt":{"rendered":"
Ol\u00e1 pessoal, Neste post irei compartilhar um script que nos auxilia a melhorar a performance das consultas que s\u00e3o executadas em nossa inst\u00e2ncia, mostrando \u00edndices que poderiam ser criados para melhorar a performance. Demonstra\u00e7\u00e3o: CREATE TABLE t1 ( c1 INT IDENTITY, c2 AS c1 * 2, c3 AS c1 + c1, c4 CHAR (3000) DEFAULT…<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"rop_custom_images_group":[],"rop_custom_messages_group":[],"rop_publish_now":"initial","rop_publish_now_accounts":{"twitter_91251433_91251433":""},"rop_publish_now_history":[],"rop_publish_now_status":"pending","_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_feature_clip_id":0,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":true,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"font":"","enabled":false},"version":2},"jetpack_post_was_ever_published":false,"_wpscppro_dont_share_socialmedia":false,"_wpscppro_custom_social_share_image":0,"_facebook_share_type":"","_twitter_share_type":"","_linkedin_share_type":"","_pinterest_share_type":"","_linkedin_share_type_page":"","_instagram_share_type":"","_medium_share_type":"","_threads_share_type":"","_google_business_share_type":"","_bluesky_share_type":"","_selected_social_profile":[],"_wpsp_enable_custom_social_template":false,"_wpsp_social_scheduling":{"enabled":false,"datetime":null,"platforms":[],"status":"template_only","dateOption":"today","timeOption":"now","customDays":"","customHours":"","customDate":"","customTime":"","schedulingType":"absolute"},"_wpsp_active_default_template":true},"categories":[4,20],"tags":[56,57,58,55,54],"class_list":["post-254","post","type-post","status-publish","format-standard","hentry","category-scripts","category-sqlserver-geral","tag-dm_db_missing_index_details","tag-dm_db_missing_index_groups","tag-dm_db_missing_index_group_stats","tag-indices","tag-performance"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p6eIyh-46","jetpack-related-posts":[{"id":2441,"url":"https:\/\/www.tiagoneves.net\/blog\/conversao-implicita-no-sql-server-o-vilao-invisivel-da-performance\/","url_meta":{"origin":254,"position":0},"title":"Convers\u00e3o Impl\u00edcita no SQL Server: O Vil\u00e3o Invis\u00edvel da Performance","author":"tiagoneves","date":"2 de julho de 2026","format":false,"excerpt":"Quando falamos em performance no SQL Server, normalmente pensamos em \u00edndices ausentes, estat\u00edsticas desatualizadas ou consultas mal escritas. Mas existe um problema extremamente comum e muitas vezes ignorado capaz de transformar uma consulta r\u00e1pida em um gargalo: as convers\u00f5es impl\u00edcitas (implicit conversions). Uma simples diferen\u00e7a entre os tipos de dados\u2026","rel":"","context":"Em "SQLServer Geral"","block_context":{"text":"SQLServer Geral","link":"https:\/\/www.tiagoneves.net\/blog\/category\/sqlserver-geral\/"},"img":{"alt_text":"","src":"https:\/\/i0.wp.com\/www.tiagoneves.net\/blog\/wp-content\/uploads\/2026\/06\/image.png?resize=350%2C200&ssl=1","width":350,"height":200,"srcset":"https:\/\/i0.wp.com\/www.tiagoneves.net\/blog\/wp-content\/uploads\/2026\/06\/image.png?resize=350%2C200&ssl=1 1x, https:\/\/i0.wp.com\/www.tiagoneves.net\/blog\/wp-content\/uploads\/2026\/06\/image.png?resize=525%2C300&ssl=1 1.5x, https:\/\/i0.wp.com\/www.tiagoneves.net\/blog\/wp-content\/uploads\/2026\/06\/image.png?resize=700%2C400&ssl=1 2x, https:\/\/i0.wp.com\/www.tiagoneves.net\/blog\/wp-content\/uploads\/2026\/06\/image.png?resize=1050%2C600&ssl=1 3x"},"classes":[]},{"id":2071,"url":"https:\/\/www.tiagoneves.net\/blog\/o-perigo-da-server-role-securityadmin\/","url_meta":{"origin":254,"position":1},"title":"O perigo da Server Role Securityadmin","author":"tiagoneves","date":"22 de agosto de 2019","format":false,"excerpt":"Ol\u00e1 pessoal tudo certo? No \u00faltimo post eu falei sobre as \u201cserver roles\u201d e \u201cdatabase roles\u201d e a import\u00e2ncia de conhecer os n\u00edveis de permiss\u00e3o e acesso de cada uma delas. https:\/\/www.tiagoneves.net\/blog\/conhecendo-as-server-roles-e-database-roles-do-sql-server\/ No post, quando eu falei sobre a server role securityadmin, eu falei que devemos ter muito cuidado ao\u2026","rel":"","context":"Em "Seguran\u00e7a"","block_context":{"text":"Seguran\u00e7a","link":"https:\/\/www.tiagoneves.net\/blog\/category\/seguranca\/"},"img":{"alt_text":"","src":"https:\/\/i0.wp.com\/www.tiagoneves.net\/blog\/wp-content\/uploads\/2019\/08\/image-28.png?resize=350%2C200&ssl=1","width":350,"height":200,"srcset":"https:\/\/i0.wp.com\/www.tiagoneves.net\/blog\/wp-content\/uploads\/2019\/08\/image-28.png?resize=350%2C200&ssl=1 1x, https:\/\/i0.wp.com\/www.tiagoneves.net\/blog\/wp-content\/uploads\/2019\/08\/image-28.png?resize=525%2C300&ssl=1 1.5x"},"classes":[]},{"id":836,"url":"https:\/\/www.tiagoneves.net\/blog\/sql-server-on-linux-parte-3\/","url_meta":{"origin":254,"position":2},"title":"SQL Server on Linux \u2013 Parte 3","author":"tiagoneves","date":"12 de janeiro de 2017","format":false,"excerpt":"Ol\u00e1 pessoal, tudo certo? Depois de uma pequena pausa para as festas de final de ano, estamos de volta para concluir a s\u00e9rie de posts do SQL Server on Linux. No post de hoje vamos fazer alguns testes no SQL Server vNext no Linux, como foi dito no primeiro post,\u2026","rel":"","context":"Em "Microsoft"","block_context":{"text":"Microsoft","link":"https:\/\/www.tiagoneves.net\/blog\/category\/microsoft\/"},"img":{"alt_text":"","src":"https:\/\/i0.wp.com\/www.tiagoneves.net\/blog\/wp-content\/uploads\/2016\/12\/LinuxLove.png?resize=350%2C200","width":350,"height":200},"classes":[]},{"id":2463,"url":"https:\/\/www.tiagoneves.net\/blog\/conversao-implicita-no-postgresql-por-que-o-comportamento-e-diferente-do-sql-server-e-onde-a-performance-realmente-morre\/","url_meta":{"origin":254,"position":3},"title":"Convers\u00e3o impl\u00edcita no PostgreSQL: por que o comportamento \u00e9 diferente do SQL Server (e onde a performance realmente morre)","author":"tiagoneves","date":"14 de julho de 2026","format":false,"excerpt":"O resumo (excerpt) que sugeri no kit SEO: O blog agora tamb\u00e9m fala PostgreSQL! No primeiro post da s\u00e9rie SQL Server \u00d7 PostgreSQL, desmontamos um mito sobre casts, mostramos onde os \u00edndices morrem de verdade (com EXPLAIN ANALYZE em 100 mil linhas) e apresentamos a arma secreta do PG: o\u2026","rel":"","context":"Em "PostgreSQL"","block_context":{"text":"PostgreSQL","link":"https:\/\/www.tiagoneves.net\/blog\/category\/postgresql\/"},"img":{"alt_text":"Convers\u00e3o impl\u00edcita: duas filosofias, dois caminhos \u2014 SQL Server \u00d7 PostgreSQL","src":"https:\/\/i0.wp.com\/www.tiagoneves.net\/blog\/wp-content\/uploads\/2026\/07\/og-conversao-implicita-postgresql-1200x630-1.png?fit=1200%2C630&ssl=1&resize=350%2C200","width":350,"height":200,"srcset":"https:\/\/i0.wp.com\/www.tiagoneves.net\/blog\/wp-content\/uploads\/2026\/07\/og-conversao-implicita-postgresql-1200x630-1.png?fit=1200%2C630&ssl=1&resize=350%2C200 1x, https:\/\/i0.wp.com\/www.tiagoneves.net\/blog\/wp-content\/uploads\/2026\/07\/og-conversao-implicita-postgresql-1200x630-1.png?fit=1200%2C630&ssl=1&resize=525%2C300 1.5x, https:\/\/i0.wp.com\/www.tiagoneves.net\/blog\/wp-content\/uploads\/2026\/07\/og-conversao-implicita-postgresql-1200x630-1.png?fit=1200%2C630&ssl=1&resize=700%2C400 2x, https:\/\/i0.wp.com\/www.tiagoneves.net\/blog\/wp-content\/uploads\/2026\/07\/og-conversao-implicita-postgresql-1200x630-1.png?fit=1200%2C630&ssl=1&resize=1050%2C600 3x"},"classes":[]},{"id":2234,"url":"https:\/\/www.tiagoneves.net\/blog\/dicas-do-dia-a-dia-melhorando-a-performance-reduzindo-os-waits-do-t-log\/","url_meta":{"origin":254,"position":4},"title":"Dicas do dia a dia – Melhorando a performance reduzindo os waits do T-Log","author":"tiagoneves","date":"8 de outubro de 2019","format":false,"excerpt":"Ol\u00e1 pessoal tudo certo? No post de hoje quero compartilhar com voc\u00eas uma situa\u00e7\u00e3o que \u00e9 bastante comum no meu dia a dia de Consultor de Banco de Dados SQL Server. Estava realizando uma atividade de tuning em um cliente e percebi que quando executava algo que escrevia no log\u2026","rel":"","context":"Em "Casos do dia-a-dia"","block_context":{"text":"Casos do dia-a-dia","link":"https:\/\/www.tiagoneves.net\/blog\/category\/casos-do-dia-a-dia\/"},"img":{"alt_text":"","src":"https:\/\/i0.wp.com\/www.tiagoneves.net\/blog\/wp-content\/uploads\/2019\/10\/T-Log-take-care-014.png?resize=350%2C200&ssl=1","width":350,"height":200,"srcset":"https:\/\/i0.wp.com\/www.tiagoneves.net\/blog\/wp-content\/uploads\/2019\/10\/T-Log-take-care-014.png?resize=350%2C200&ssl=1 1x, https:\/\/i0.wp.com\/www.tiagoneves.net\/blog\/wp-content\/uploads\/2019\/10\/T-Log-take-care-014.png?resize=525%2C300&ssl=1 1.5x, https:\/\/i0.wp.com\/www.tiagoneves.net\/blog\/wp-content\/uploads\/2019\/10\/T-Log-take-care-014.png?resize=700%2C400&ssl=1 2x"},"classes":[]},{"id":1495,"url":"https:\/\/www.tiagoneves.net\/blog\/dicas-de-como-realizar-um-tuning-no-sql-server\/","url_meta":{"origin":254,"position":5},"title":"Dicas de como realizar um tuning no SQL Server","author":"tiagoneves","date":"9 de maio de 2019","format":false,"excerpt":"Ol\u00e1 pessoal tudo certo? No post de hoje, eu quero compartilhar com voc\u00eas algumas dicas de como iniciar um tuning em alguma rotina, seja stored procedure, function ou query adhoc. Quando vamos iniciar um trabalho de tuning, uma das primeiras informa\u00e7\u00f5es que precisamos \u00e9 visualizar o plano de execu\u00e7\u00e3o da\u2026","rel":"","context":"Em "Casos do dia-a-dia"","block_context":{"text":"Casos do dia-a-dia","link":"https:\/\/www.tiagoneves.net\/blog\/category\/casos-do-dia-a-dia\/"},"img":{"alt_text":"","src":"https:\/\/i0.wp.com\/www.tiagoneves.net\/blog\/wp-content\/uploads\/2019\/05\/word-image-3-1.png?fit=352%2C278&ssl=1&resize=350%2C200","width":350,"height":200},"classes":[]}],"jetpack_likes_enabled":true,"_links":{"self":[{"href":"https:\/\/www.tiagoneves.net\/blog\/wp-json\/wp\/v2\/posts\/254","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.tiagoneves.net\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.tiagoneves.net\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.tiagoneves.net\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.tiagoneves.net\/blog\/wp-json\/wp\/v2\/comments?post=254"}],"version-history":[{"count":8,"href":"https:\/\/www.tiagoneves.net\/blog\/wp-json\/wp\/v2\/posts\/254\/revisions"}],"predecessor-version":[{"id":267,"href":"https:\/\/www.tiagoneves.net\/blog\/wp-json\/wp\/v2\/posts\/254\/revisions\/267"}],"wp:attachment":[{"href":"https:\/\/www.tiagoneves.net\/blog\/wp-json\/wp\/v2\/media?parent=254"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.tiagoneves.net\/blog\/wp-json\/wp\/v2\/categories?post=254"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.tiagoneves.net\/blog\/wp-json\/wp\/v2\/tags?post=254"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}