Guardando o histórico de uso dos índices

Olá Pessoal,

Hoje vou colocar aqui umas das consultas que utilizo bastante nos clientes quando preciso de um histórico de utilização dos índices e a quantidade de utilização de scans, seeks e lookups já realizados.

Primeiramente precisamos de uma tabela onde vamos salvar estes históricos, então vamos criá-lá.

CREATE TABLE dbo.Historico_Uso_Indices(
Database varchar(128) NOT NULL,
table_name varchar(100) NOT NULL,
user_seeks int NULL,
user_scans int NULL,
user_lookups int NULL,
TOTAL_LEITURAS int NOT NULL,
last_user_seek datetime  NULL,
last_user_scan datetime  NULL,
last_user_lookup datetime  NULL,
NOME_INDEX varchar(100) NOT NULL,
DATA_REGISTRO datetime NOT NULL)

Temos a nossa tabela criada o proximo passo é criar a consulta que irá popular essa tabela. A DMV principal aqui será a sys.dm_db_index_usage_stats. Segundo BOL a definição desta DMV é:

Returns counts of different types of index operations and the time each type of operation was last performed.

Como viram a intenção aqui é retornar alguns contadores referentes aos índices do meu banco de dados. Juntamente com essa DMV, foram utilizadas também algumas outras DMV’s somente para deixar mais claro o que eram determinadas informações. Abaixo segue então o segundo passo do nosso script.

insert into Historico_Uso_Indices
select
DB_NAME(database_id) as DB, ob.name as TABLE_NAME,
user_seeks,user_scans,user_lookups,(user_seeks+user_scans+user_lookups) as TOTAL_LEITURAS,
last_user_seek,last_user_scan,last_user_lookup,sx.name as NOME_INDEX,GETDATE() AS DATA_REGISTRO
from sys.dm_db_index_usage_stats as st
join sys.indexes as sx
on st.object_id = sx.object_id and st.index_id = sx.index_id
join sys.objects as ob
on sx.object_id = ob.object_id
where DB_NAME(database_id) not in (‘ReportServerTempDB’,'ReportServer’)
and database_id = DB_ID() — Remova esta linha para pegar de todas as bases de dados da instancia

Como vocês podem ver, ja estou inserindo as informações recuperadas apartir dessa minha consulta. Se desejarem pegar esses dados de todas as bases da instancia então é só retirar a ultima linha do script.

No final disso tudo existe uma terçeira consulta onde ela faz algumas validações, para me retornar aquilo que eu realmente preciso, que são as quantidades de scans, seeks e lookups realizados em X dias.

select a.[Database],a.table_name,a.NOME_INDEX,
DATEDIFF(DAY,a.DATA_REGISTRO,b.DATA_REGISTRO ) as Intervalo_Dias,
SUM((b.user_scans – a.user_scans)) as Diff_Scan,
SUM((b.user_lookups – a.user_lookups)) as Diff_Lookup,
SUM((b.user_seeks – a.user_seeks)) as Diff_Seek
from Historico_Uso_Indices as a
join Historico_Uso_Indices as b
on a.[Database] = b.[Database]
and a.table_name = b.table_name
and a.NOME_INDEX = b.NOME_INDEX
and a.user_seeks < b.user_seeks
and a.user_lookups < b.user_lookups
and a.user_scans < b.user_scans
group by a.[Database],a.table_name,a.NOME_INDEX,
DATEDIFF(DAY,a.DATA_REGISTRO,b.DATA_REGISTRO);

Digamos que eu executei o segundo script ontem (28/09), e hoje eu executei novamente a terceira consulta vai me retornar a diferença de acessos do tipo scans, seeks e lookups entre esse dia. Essa informação é bem valiosa para mim, pois eu consigo identificar quais foram os índices mais acessados, e quais os métodos de pesquisa foram os mais realizados.

Se você quiser, você ainda pode realizar essa consulta:

select database,table_name, nome_index, total_leituras from Historico_Uso_Indice
order by total_leituras desc

E analisar quais foram os índices mais acessados do seu banco de dados.

Abaixo segue uma imagem do resultado a consulta do terceiro script.

 

Pessoal por hoje é isso, espero que seja esses scripts sejam úteis para vocês também.

Marcos Freccia
@SQLFreccia
MCTS SQL Server 2008

The process could not connect to Subscriber. E agora??

Olá Pessoal,

Hoje vamos falar sobre um problema de replicação. Esses dias configurando uma replicação, recebo o seguinte erro

The process could not connect to Subscriber 'XXXX\XXXX'. (Source: MSSQL_REPL, Error number: MSSQL_REPL20084).

Entao a dica é muito simples para resolver esse problema você deve ir em:

1) No publicador da replicação.

2) Dentro do publicador existe uma configuração, que seria mais ou menos um link com o cara que assina essa publicação. Você deve clicar nele e ir em propriedades.


O padrão é que a conta venha configurada como Windows Authentication (como mostra figura abaixo).

Porem nem sempre temos a conta configurada no servidor de destino, pois pode ser até mesmo uma maquina fora da rede, um acesso externo no caso. Entao devemos colocar na autenticação um usuario e senha valido.
Claro não esqueça que o servidor assinante dessa replicação deve possuir esse usuário e senha.

Após essa configuração a sua replicação já começara a realizar a publicação dos dados para o servidor de destino.
Pessoal resolvi compartilhar isso, pois esse problema me tirou do serio, então caso alguem passe por esse problema ja tera uma ajuda.
Att,
Marcos Freccia
MCTS SQL Server 2008
@SQLFreccia

Select’s que voce nunca esperava ver Post 01

Ola Pessoal,

Nesse blog inicia-se a serie: Select's que voce nunca esperava em ver.
É isso mesmo que você lê, o objetivo aqui é mostrar certas gambiarras que você pode encontrar por ai, mas que com um pouco de dedicação tudo pode se tornar mais facil. 
O primeiro caso (real) começa agora.

Quero consultar dados a partir de uma range de datas (sem o horário), 
porem nao quero utilizar a expressao >= e <=, para ficar mais facil poderemos utilizar somente > e <.

create table #temp (id int identity(1,1), mensagem varchar (200),
datainicio datetime, dataFim datetime)

insert into #temp values ('mensagem 01','20110101','20110617') insert into #temp values ('mensagem 02','20110102','20110617') insert into #temp values ('mensagem 03','20110103','20110617') insert into #temp values ('mensagem 04','20110104','20110617') insert into #temp values ('mensagem 05','20110103','20110614') insert into #temp values ('mensagem 06','20110304','20110614') insert into #temp values ('mensagem 07','20110504','20110614') insert into #temp values ('mensagem 08','20110504','20110618') insert into #temp values ('mensagem 09','20110504','20110621')
Agora o que se faz necessário é pegar todos os dados onde a data de inicio é maior e igual a 02/01/2011 e a data fim é menor e igual a 14/06/2011.

Simples basta realizarmos essa consulta:
select * from temp
where datainicio > dateadd(dd,1,'20101231')
and dataFim < DATEADD(DD,1,'20110614')
Viu? Olha que abordagem mais fácil.. Por que não usar essa consulta na parte de cima ao invés dessa:
select * from temp
where datainicio >= '20110102'
and dataFim <= '20110614'
Entao pessoal, hoje foi o primeiro post de uma serie que esta por vir, 
vou tentar postar o máximo de casos reais possiveis como esse de hoje.
Marcos Freccia
MCTS SQL Server 2008
@SQLFreccia

Dica do Dia: Lista de Servidores SQL Server

Pessoal,

Apenas uma dica do dia: Ontem precisava acessar uma instancia do SQL Server, porem eu nao tinha o management studio instalado, bom qual recurso a ser usado? Acertou quem disse sqlcmd. Como eu não sabia o nome da instancia um comando do sqlcmd me ajudou muito o sqlcmd /L ou -L. Esse comando te retorna  uma lista dos servidores de SQL Server que ele encontrar na rede, a partir dai é só correr pro abraço. Abaixo segue uma imagem de como é retornado a lista para o usuario

Obs: Com powershell também existe uma solução bem legal, porem como eu nao sou expert nao consegui fazer.

Até mais,
Marcos Freccia
MCTS SQL Server 2008
@SQLFreccia

Problema com Job de Backup

Pessoal,

Alguns dias eu recebi um e-mail de um job de backup que nao tinha disparado com sucesso, a mensagem de erro dizia o seguinte: The process could not be created for step 1 of job 0xE081DC71A38E3740BE2F0A0C0C5F3FA8 (reason: The system cannot find the file specified). Depois de muitas analises eis que com a ajuda de Felipe Ferreira (Twitter|Blog) vimos que no job que disparava esse backup o step estava configurado como mostra a figura abaixo.

Porem na caixa Command foi inserido um script normal e nao um script invocando o sqlcmd. Após passar o tipo do step para Transact-SQL Script o backup rodou normalmente.

O mais estranho aqui é que a mensagem de erro no log nao condizia com o real problema.

Pessoal hoje era isso, espero que esse post possa servir de ajuda para alguem.

Marcos Freccia
MCTS SQL Server 2008
@SQLFreccia

Transaction Log cheio? E agora?

Pessoal,

Um problema que aconteceu comigo esses dias e gostaria de compartilhar com vocês. Em uma manha recebi a seguinte mensagem:
Msg 9002, Level 17, State 2, Line 3
The transaction log for database ‘MCITP’ is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases

Como o próprio erro diz:  O Log de transações da base de dados MCITP esta cheio e não poderá ser reutilizado.
A causa do problema pode ser:

1)  O arquivo de log foi configurado com um tamanho maximo e com isso o arquivo chegou em seu limite maximo e nao conseguiu mais crescer.

2) O arquivo de log nao tem um tamanho maximo, porem nao existe mais espaço em disco.

Como podemos resolver esse problema.

1) Onde esta o arquivo do log e qual seu tamanho maximo, qual o tamanho atual e a proporção de crescimento?
R: select msf.name,physical_name, db.recovery_model_desc,(max_size * 8 /1024) as MaxSizeMB,
(size * 8 /1024) as SizeMB, growth
from sys.master_files as msf
join sys.databases as db
on msf.database_id = db.database_id
where msf.name = ‘MCITP_LOG’

Temos o caminho.

2) Vamos analisar o espaço em disco?
R: exec master..xp_fixeddrives

C	5064
D	6060

Ai ja conseguimos ver qual é o problema. No nosso caso aqui temos um problema é o arquivo de log configurado um tamanho maximo, como o fator de crescimento esta para 20%, o proximo tamanho que o arquivo de log teria seria de 258MB o que ultrapassaria o tamanho maximo de 220MB, por isso o SQL Server nao conseguiu mais crescer o arquivo de log.

Solução.

Aqui temos algumas alternativas.

1) Realizar um backup desse log, e sempre cuidar para manter o backup do log sendo realizado periodicamente.

2) Caso a base de dados nao for importante, realize primeiro um backup desse log e depois passe o modo de recovery para SIMPLE, assim periodicamente o SQL Server se encarrega de manter esse arquivo pequeno, ja que pouquissimas informações serão logadas.

3) Isso nao seria uma alternativa viavel, mas poderiamos ainda realizar um shrink no log, mas eu nao recomendo isso de maneira nenhuma. Se você chega ao ponto de truncar o log, o modo de recovery FULL com certeza não é necessario para você.

Bom pessoal por hoje é isso, espero que isso seja util para vocês.

Marcos Freccia
MCTS SQL Server 2008
@SQLFreccia

Precedência de tipo de dados

Ola Pessoal,

No ultimo post eu falei sobre como Problemas com conversão implicita pode acabar com seu índice. Ainda no final do post falei que ainda temos mais problemas com a conversão implícita, então esse é o nosso assunto de hoje.

A grande questão aqui é a  precedência do tipo de dados, a precedência também é um serio fator na conversão implícita de dados, mas como?

A precedência de dados diz que certos tipos de dados tem uma certa “vantagem” sobre outros tipos de dados em comparações, conversões, etc.. até ai tudo bem, mas como a precedência pode também ser um empecilho na conversão implícita dos dados e assim acabar com seu índice. Vamos aos testes.

Utilizaremos aqui os mesmos scripts do post anterior.

create table #temp ( id int not null, cdVendedor varchar(100))

go

alter table #temp add constraint PK primary key (id)

go

insert into #temp (id,cdVendedor)

values (1,1),(2,2),(3,3),(4,4),(5,5),(6,6)

go

create nonclustered index ix_teste on #temp(cdVendedor)

go

select cdVendedor from #temp

where cdVendedor = 1

Analisando temos o seguinte plano de execução.

Como vimos nessa consulta estamos realizando uma conversão de um tipo de dado texto (varchar) para um tipo de dado inteiro (int), se analisar o tipo de dados varchar que foi declarado na criação da tabela, o mesmo não tem precedência sobre o tipo de dados int, logo o SQL Server não consegue realizar um índex seek na consulta.

Agora vamos mudar nosso tipo de dado do campo cdVendedor.

drop table #temp

go

create table #temp ( id int not null, cdVendedor int)

go

alter table #temp add constraint PK_2 primary key (id)

go

insert into #temp (id,cdVendedor)

values (1,1),(2,2),(3,3),(4,4),(5,5),(6,6)

create nonclustered index ix_teste on #temp(cdVendedor)

select * from #temp where cdVendedor = 3

Executando a consulta a seguir temos o seguinte plano de execução.

Agora execute a seguinte consulta.

select * from #temp2

where cdVendedor = ’3′

Abaixo plano de execução.

Agora vimos que como o tipo de dados inteiro (int) tem precedência sobre o tipo de dados texto (varchar) não importa como realizamos nossa consulta, se for com aspas ‘’ ou sem aspas sempre teremos um índex seek.

Caso alguém se interessar em realizar esse mesmo teste com outros tipos de dados me avise depois, mas provável que esse comportamento só ocorra com esses dois tipos de dados no caso de inteiro para texto e vice-versa.

Então pessoal por hoje era isso, espero que seja de utilidade para alguém isso, pois é muito importante realizar uma boa modelagem de dados para não ter problemas mais tarde.

Marcos Freccia
MCTS SQL Server 2008
@SQLFreccia

Problemas com Conversão Implicita

Pessoal,

A dica de hoje é bem simples: CUIDADO COM CONVERSAO IMPLICITA há algum tempo atrás precisei criar alguns índices e tudo ocorreu normalmente, porem ao realizar testes desses índices vi que eles não realizavam um índex seek na minha tabela, mas sim um índex scan L. Isso tudo ocorreu por conta de uma conversão implícita estar acontecendo na minha consulta. Então vamos a uma demonstração de como a conversão pode deixar seu índice ineficaz na consulta.

create table #temp ( id int not null, cdVendedor varchar(100))

go

alter table #temp add constraint PK primary key (id)

go

insert into #temp (id,cdVendedor)

values (1,1),(2,2),(3,3),(4,4),(5,5),(6,6)

go

create nonclustered index ix_teste on #temp(cdVendedor)

go

select cdVendedor from #temp

where cdVendedor = 1

Executando a consulta acima temos o seguinte plano de execução.

Como viram nada adiantou eu criar meu índice, mas será que foi um erro do SQL Server ou meu? Bom eu diria que é um erro nosso ao realizar a declaração dos dados, pois como vocês podem ver o campo cdVendedor é do tipo varchar e antes de realizar a consulta o query optimizer teve que realizar a conversão desse campo para encontrar a melhor forma possível de realizar a consulta. Analisando as informações da consulta temos:

Viram o convert_implicit? Então ele foi o responsável por fazer o SQL Server percorrer toda a arvore do índice para procurar o valor que satisfizesse a consulta, porem foi erro nosso declarar o valor na clausula where de forma errada, o correto então seria declarar a consulta da seguinte maneira.

select cdVendedor from #temp

where cdVendedor = ’1′

Assim temos o seguinte plano de execução.

E como podem ver não temos mais a conversão do tipo de dado antes da consulta ser executada.

Então pessoal por hoje era isso, espero que vocês tenham aprendido alguma coisa por aqui. Ainda temos mais um empecilho com a conversão implícita de dados, mas isso é assim para isso é um assunto que deixo para um próximo post.

Marcos Freccia
MCTS SQL Server 2008
@SQLFreccia