{"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_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":"","_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":2071,"url":"https:\/\/www.tiagoneves.net\/blog\/o-perigo-da-server-role-securityadmin\/","url_meta":{"origin":254,"position":0},"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":1},"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":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":2},"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":1422,"url":"https:\/\/www.tiagoneves.net\/blog\/replique-os-backups-de-sua-vm-sql-server-do-azure-para-amazon\/","url_meta":{"origin":254,"position":3},"title":"Replique os backups de sua VM SQL Server do Azure para Amazon","author":"tiagoneves","date":"25 de julho de 2018","format":false,"excerpt":"Ol\u00e1 pessoal, tudo certo? No post de hoje vou compartilhar com voc\u00eas um caso que atendi em um de nossos clientes. O nosso cliente tem uma VM com o SQL Server no Azure e gostaria de ter um site de DR (disaster recovery) na Amazon. At\u00e9 ai tudo bem, voc\u00ea\u2026","rel":"","context":"Em "Azure"","block_context":{"text":"Azure","link":"https:\/\/www.tiagoneves.net\/blog\/category\/azure\/"},"img":{"alt_text":"","src":"https:\/\/i0.wp.com\/www.tiagoneves.net\/blog\/wp-content\/uploads\/2018\/07\/Return_Query-300x129.png?resize=350%2C200&ssl=1","width":350,"height":200,"srcset":"https:\/\/i0.wp.com\/www.tiagoneves.net\/blog\/wp-content\/uploads\/2018\/07\/Return_Query-300x129.png?resize=350%2C200&ssl=1 1x, https:\/\/i0.wp.com\/www.tiagoneves.net\/blog\/wp-content\/uploads\/2018\/07\/Return_Query-300x129.png?resize=525%2C300&ssl=1 1.5x, https:\/\/i0.wp.com\/www.tiagoneves.net\/blog\/wp-content\/uploads\/2018\/07\/Return_Query-300x129.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":4},"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":[]},{"id":1475,"url":"https:\/\/www.tiagoneves.net\/blog\/voce-sabe-a-diferenca-entre-uma-consulta-sargable-e-non-sargable\/","url_meta":{"origin":254,"position":5},"title":"Voc\u00ea sabe a diferen\u00e7a entre uma consulta Sargable e Non-Sargable?","author":"tiagoneves","date":"24 de outubro de 2018","format":false,"excerpt":"Ol\u00e1 pessoal tudo certo? No post de hoje vou compartilhar com voc\u00eas a respeito de um conceito muito comum no SQL Server, que \u00e9 o conceito de SARG (\"S\" search argument) e Non-SARGable cuja a tradu\u00e7\u00e3o livre pode ser \"Argumento de busca\". O termo SARG nada mais \u00e9 que a\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\/2018\/10\/Consulta_3.1.png?resize=350%2C200&ssl=1","width":350,"height":200,"srcset":"https:\/\/i0.wp.com\/www.tiagoneves.net\/blog\/wp-content\/uploads\/2018\/10\/Consulta_3.1.png?resize=350%2C200&ssl=1 1x, https:\/\/i0.wp.com\/www.tiagoneves.net\/blog\/wp-content\/uploads\/2018\/10\/Consulta_3.1.png?resize=525%2C300&ssl=1 1.5x, https:\/\/i0.wp.com\/www.tiagoneves.net\/blog\/wp-content\/uploads\/2018\/10\/Consulta_3.1.png?resize=700%2C400&ssl=1 2x, https:\/\/i0.wp.com\/www.tiagoneves.net\/blog\/wp-content\/uploads\/2018\/10\/Consulta_3.1.png?resize=1050%2C600&ssl=1 3x, https:\/\/i0.wp.com\/www.tiagoneves.net\/blog\/wp-content\/uploads\/2018\/10\/Consulta_3.1.png?resize=1400%2C800&ssl=1 4x"},"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}]}}