Fala pessoal,<\/p>\n
Algum tempo atr\u00e1s, compartilhei uma experi\u00eancia que tive no Blog do Fabr\u00edcio Catae (Blog<\/a>|Twitter<\/a>), mas tamb\u00e9m resolvi deixar registrado por aqui.<\/p>\n Muitos de voc\u00eas j\u00e1 utilizaram a dmv sys.dm_db_index_usage_stats para verificar a utiliza\u00e7\u00e3o e atualiza\u00e7\u00e3o dos \u00edndices de uma tabela. Tamb\u00e9m sabemos que essa dmv tem seus dados reiniciados quando o servi\u00e7o do SQL Server \u00e9 reiniciado. Assim, como os servidores do meu ambiente de banco de dados possuem uma atualiza\u00e7\u00e3o mensal de seguran\u00e7a, os servidores\u00a0s\u00e3o reiniciados mensalmente e os dados dessa dmv seriam perdidos. Ou seja, eu s\u00f3 teria essas valiosas informa\u00e7\u00f5es sobre os \u00edndices durante o per\u00edodo de um m\u00eas.<\/p>\n Para resolver esse problema, criei uma tabela que armazena diariamente a utiliza\u00e7\u00e3o dos \u00edndices. Com isso, posso analisar durante um per\u00edodo muito grande, a utiliza\u00e7\u00e3o dos meus \u00edndices antes de exclu\u00ed-los. Eu j\u00e1 possuo mais de 1 ano de baseline j\u00e1 que o espa\u00e7o ocupado por essas informa\u00e7\u00f5es \u00e9 pequeno.<\/p>\n Para quem quiser possuir um hist\u00f3rico dessas informa\u00e7\u00f5es, o script abaixo cria uma tabela de hist\u00f3rico e insere as informa\u00e7\u00f5es\u00a0de utiliza\u00e7\u00e3o dos \u00edndices nessa tabela.<\/p>\n CREATE TABLE [dbo].[Historico_Utilizacao_Indices]( INSERT INTO Historico_Utilizacao_Indices(Dt_Historico, Nm_Servidor, Nm_Database, Nm_Tabela, Nm_Indice,\u00a0 User_Seeks, User_Scans,User_Lookups, User_Updates, Ultimo_acesso) Para armazenar as informa\u00e7\u00f5es, criei um job que roda essa query de INSERT para cada database que eu defini guardar os hist\u00f3ricos.<\/p>\n Assim, eu utilizo essa tabela de hist\u00f3rico para analisar uma poss\u00edvel exclus\u00e3o dos \u00edndices que s\u00e3o pouco utilizados ou que n\u00e3o s\u00e3o utilizados. Agora, compartilhando a experi\u00eancia que tive, eu possuo uma tabela com muitas consultas e altera\u00e7\u00f5es\u00a0que armazena\u00a050 milh\u00f5es de registros. Nessa tabela, tenho um \u00edndice em um campo chamado Fl_Situacao que pode possuir os valores 0, 1, 2, 3 ou 4.<\/p>\n Sempre acompanhei esse \u00edndice e verifiquei que tinha\u00a0algumas utiliza\u00e7\u00f5es somente no inicio do m\u00eas. Um certo dia, resolvi excluir esse \u00edndice seguindo o racioc\u00ednio de que o \u00edndice era\u00a0pouco seletivo, a tabela \u00e9 muito grande e o \u00edndice era\u00a0pouco utilizado, n\u00e3o valendo a pena o custo de manuten\u00e7\u00e3o do mesmo. Ap\u00f3s excluir o \u00edndice, acompanhando meu trace com as querys que demoram mais de 3 segundos<\/a>, verifiquei que nenhuma query apresentou problema de lentid\u00e3o.<\/p>\n Show de bola, diminu\u00ed uma opera\u00e7\u00e3o de manuten\u00e7\u00e3o de um \u00edndice em uma tabela muito utilizada.<\/p>\n Entretanto, no inicio do m\u00eas, existia uma query com uma condi\u00e7\u00e3o “where Fl_Situacao = 2”\u00a0dentre outras restri\u00e7\u00f5es. Quando essa query rodou sem o \u00edndice que eu exclu\u00ed, a mesma fez um clustered index scan nessa tabela, me causando um grande problema de lentid\u00e3o no\u00a0banco de dados. Isso aconteceu\u00a0pois, dos 50 milh\u00f5es de registros existentes na tabela, apenas 1.000 registros possu\u00edam o campo Fl_Situacao = 2, o que tornava o \u00edndice existente nessa coluna\u00a0extremamente eficiente para essa query.<\/p>\n Resultado, como n\u00e3o dava para alterar a consulta, tive que recriar o \u00edndice na mesma noite.<\/p>\n Mais uma vez eu digo, vivendo e aprendendo!!! Meu maior aprendizado est\u00e1 no meu dia a dia de trabalho.<\/p>\n Gostou dessa dica?<\/b><\/p>\n Cadastre seu e-mail para receber novos Posts e curta minha P\u00e1gina no Facebook<\/a> para receber Dicas de Leituras e Eventos sobre SQL Server.<\/p>\n
\n[Id_Historico_Utilizacao_Indices] [int] IDENTITY(1,1) NOT NULL,
\n[Dt_Historico] [datetime] NULL,
\n[Nm_Servidor] [varchar](30) NULL,
\n[Nm_Database] [varchar](30) NULL,
\n[Nm_Tabela] [varchar](50) NULL,
\n[Nm_Indice] [varchar](50) NULL,
\n[User_Seeks] [int] NULL,
\n[User_Scans] [int] NULL,
\n[User_Lookups] [int] NULL,
\n[User_Updates] [int] NULL,
\n[Ultimo_Acesso] [datetime] NULL )<\/p>\n
\nSELECT getdate(), @@SERVERNAME, db_name(db_Id()), o.Name, i.name, s.user_seeks,s.user_scans,s.user_lookups, s.user_Updates,\u00a0\u00a0isnull(s.last_user_seek,isnull(s.last_user_scan,s.last_User_Lookup)) Ultimo_acesso
\nFROM sys.dm_db_index_usage_stats s
\njoin sys.indexes i on i.object_id = s.object_id and i.index_id = s.index_id
\njoin sys.sysobjects o on i.object_id = o.id
\nWHERE s.database_id = db_id()
\nORDER BY o.Name, i.name, s.index_id<\/p>\n