Mapeando uma letra no SSMS – SQL Server

Olá galera, tudo bem? Ando sumido do blog, mas são as demandas do dia a dia atribulados como sempre, no qual tenho agradecido muito de estar assim (risos!!!). Sempre lembro que é nessa agonia e correria que tenho aprendido muito com o SQL Server. Não só nos bate papos com colegas e amigos da área, mas em estudos e pesquisas em livros, fóruns e blogs em busca de solucionar problemas. Tentarei ser bem rápido neste post, pois passei apenas para comentar sobre um comando muito conhecido da galera de TI e que tem me ajudado bastante na construção de algumas rotinas administrativas dos ambientes que administro. É ele o xp_cmdshell, onde o mesmo nada mais é um comando do Windows que passa uma cadeia de caracteres para execução. Tenho utilizado para definir mapeamentos de redes na plataforma SQL Server, no qual posso apontar meus backups para serem alocados em locais distintos na rede e até mesmo restaurados desses locais. Abaixo segue um exemplo de como criar um mapeamento usando a letra P.

Inicialmente devemos habilitar o comando xp_cmdshell, onde esse vem desabilitado por default no SQL Server. Executando a consulta abaixo no SSMS estaremos habilitando a opção Show Advanced Options que nos permitirá ter acesso a xp_cmdshell.

use master
go

exec sp_configure ‘Show Advanced Options’,1
reconfigure
go

exec sp_configure ‘xp_cmdshell’,1
reconfigure
go

sp_conf

xp_cmd

Posteriormente devemos definir a letra do mapeamento e o caminho que ficará exposto no SQL Server através do comando abaixo.

exec xp_cmdshell ‘net use P: \\192.168.1.191\backup’

Após configurarmos o mapeamento é possível conferirmos nas propriedades da instancia em Configurações de Banco de Dados -> Locais Padrão de Banco de Dados como mostra a imagem abaixo.

resultado

Por fim é importante desabilitarmos as opções default do SQL Server com os comandos:

use master
go

exec sp_configure ‘xp_cmdshell’,0
reconfigure
go

exec sp_configure ‘Show Advanced Options’,0
reconfigure
go

Se eventualmente errou a letra ou quer alterar a mesma é passível também de exclusão do mapeamento seguindo os passos:

use master
go

exec sp_configure ‘Show Advanced Options’,1
reconfigure
go

exec sp_configure ‘xp_cmdshell’,1
reconfigure
go

exec xp_cmdshell ‘Net use P: /delete’

exec sp_configure ‘xp_cmdshell’,0
reconfigure
go

exec sp_configure ‘Show Advanced Options’,0
reconfigure
go

Por hoje é só. Espero que aproveitem a dica e aprimorem para compartilhar.

Bom final de semana à todos.

Continuar lendo “Mapeando uma letra no SSMS – SQL Server”

Anúncios

Encontrando as consultas mais lentas no servidor

Olá pessoal, tudo bem? Essa semana vou contribuir com um script bem útil para quem trabalha com tuning de consultas. Existem diversos aspectos que podem comprometer o desempenho num banco de dados. Já é de conhecimento de todos que grande parte desses problemas de performance que encontramos está muito relacionado às consultas mal modeladas. Bem, no primeiro instante que recebemos um reclamação de lentidão no ambiente de dados, por recomendação, é sempre bom partirmos para a análise das esperas presentes na instância, para diante dos resultados, identificarmos quais os caminhos devemos tomar para solucionar o problema. Porém, não será esse o nosso propósito neste poste. Deixarei essas questões para os próximos postes. Abaixo considero a identificação das cincos consultas mais lentas no servidor.

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

SELECT TOP 5
CAST(qs.total_elapsed_time / 1000000.0 AS DECIMAL(28, 2))
AS [Total Elapsed Duration (s)]
, qs.execution_count
, SUBSTRING (qt.text,(qs.statement_start_offset/2) + 1,
((CASE WHEN qs.statement_end_offset = -1
THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
ELSE
qs.statement_end_offset
END – qs.statement_start_offset)/2) + 1) AS [Individual Query]
, qt.text AS [Parent Query]
, DB_NAME(qt.dbid) AS DatabaseName
, qp.query_plan
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
INNER JOIN sys.dm_exec_cached_plans cp
ON qs.plan_handle=cp.plan_handle
ORDER BY total_elapsed_time DESC

Um exemplo abaixo mostra o que a consulta gera como resultado.

23

Percebam que o retorno do resultado traz informações importantes, como duração decorrida da consulta, quantidade de vezes que foi executada, a consulta em si e o plano em cache, no qual considero ser o dado mais importante neste script, pois é através dele que poderemos analisar de forma coerente as decisões tomadas pelo SQL Server para trazer os Results Sets, além de nos dar uma ideia de como é possível melhoramos as consultas.

Por hoje é só, pois preciso também de descanso. (Risos!!).

Bom feriado à todos.

Estimando o tempo final dos jobs no SQL Server

Olá pessoal, como têm indo a semana? Antes de começar a escrever quero que possam refletir sobre a seguinte mensagem: “O conhecimento não é pra ser guardado a sete chaves e sim pra ser compartilhado, dividido, pois conhecimento guardado é igual dinheiro num cofre, perde o valor e não rende nada.”

Essa senana quero falar rapidinho sobre uma dica que costumo usar as vezes nos ambientes que administro. Quem nunca se perguntou ou foi perguntado: “Em quanto tempo conclui o job que mandou rodar?”. E ai o que você faz? Com o script abaixo será possível programar e informar aos usuários quando a tarefa em execução será concluída. Ressaltando mais uma vez sobre a importância da leitura do livro DMVs SQL Server em Ação.

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

SELECT r.percent_complete
, DATEDIFF(MINUTE, start_time, GETDATE()) AS Age
, DATEADD(MINUTE, DATEDIFF(MINUTE, start_time, GETDATE()) /
percent_complete * 100, start_time) AS EstimatedEndTime
, t.Text AS ParentQuery
, SUBSTRING (t.text,(r.statement_start_offset/2) + 1,
((CASE WHEN r.statement_end_offset = -1
THEN LEN(CONVERT(NVARCHAR(MAX), t.text)) * 2
ELSE r.statement_end_offset
END – r.statement_start_offset)/2) + 1) AS IndividualQuery
, start_time
, DB_NAME(Database_Id) AS DatabaseName
, Status
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(sql_handle) t
WHERE session_id > 50
AND percent_complete > 0
ORDER BY percent_complete DESC

 

Percebam a junção da DMV e DMF, pois é através delas que encontramos informações suficientes para determinar o término dos jobs administrativos que rodamos em nosso ambiente. Vou explicitar apenas o conceito da sys.dm_exec_requests, pois a sys.dm_exec_sql_text foi adicionada no quadro do artigo anterior. Caso queira dar uma olhada Exibindo as últimas consultas SQL em execução.

DMV Descrição
sys.dm_exec_requests

Contém detalhes de pedidos em execução no SQL Server.

Um exemplo abaixo mostra o que a consulta gera como resultado.

Sem título

As colunas apresentam informações sobre o percentual decorrido do job, o tempo em minutos de quanto o trabalho foi executado, o tempo final estimado, a instrução executado no job, o horário de ínicio e a base de dados envolvida.

Bem, por hoje acho que é só. Espero que o script possa contribuir no dia a dia dos profissionais mais experientes e iniciantes.

Abraços, até a próxima!

Exibindo as últimas consultas SQL em execução

Olá pessoal,  tudo bem? Espero que tenham tido uma semana de muitos problemas no ambiente de vocês (risos), no qual sempre nos deixa super apreensivos. Afinal de contas, o problema faz parte da nossa rotina, como profissional de tecnologia, assim dizia a lei de Murphy “Se alguma coisa tem chances de sair errado certamente sairá, e da pior maneira possível!“. Por isso a necessidade de nos aperfeiçoarmos sempre para estarmos preparados para definir soluções eficazes e eficientes no ambiente que estamos responsáveis. Busquemos viver os momentos difíceis como uma grande oportunidade de aprender e crescer na profissão de escolhemos seguir. São desses momentos de caus que adquirimos experiências fantásticas e descobrirmos que o profissional experiente é aquele que encara o problema de frente sem medo dos riscos e desafios, seguro que pode dar conta do recado.

Bem, a partir de hoje irei iniciar uma série de exposição de consultas que certamente serão de grande utilidade no dia a dia de um administrador de banco de dados. Serão postagens curtas e objetivas, no qual abordaram informações que podem ser importantes na determinação do comportamento de usuários como até teste de desempenho de um ambiente.

Confesso que a leitura do livro DMVs SQL Server em ação, escrito por Ian W. Stirk tem me ajudado bastante em meu trabalho. Considero um livro de fundamental importância para quem trabalha com administração de bancos de dados SQL Server, pois através de uma gramática de fácil compreensão e de scripts escritos de forma muito simples, tem facilitado bastante a minha vida.

Hoje irei aborda uma instrução que trás informação detalhadas das últimas consultas executadas em um banco de dados. Quem nunca se deparou com o seguinte argumento do usuário: “…o único comando que executei foi…”. Nós sabemos que as vezes sempre a uma discrepância no que o usuário diz quando queremos extrair algo dele para nos ajudar a resolver determinado problema e com o que realmente foi feito por ele.

O script abaixo nos ajudará a eliminar essa diferença.

SELECT c.session_id, s.host_name, s.login_name, s.status
, st.text, s.login_time, s.program_name, *
FROM sys.dm_exec_connections c
INNER JOIN sys.dm_exec_sessions s ON c.session_id = s.session_id
CROSS APPLY sys.dm_exec_sql_text(most_recent_sql_handle) AS st
ORDER BY c.session_id

Percebam que estamos usando na consulta objetos DMVs e DMF. É a junção delas que nos trás as informações que precisamos analisar.

DMVs/DMF Descrição
sys.dm_exec_connections Contém detalhes de conexão do SQL Server.
sys.dm_exec_sessions Contém detalhes de sessões do SQL Server
sys.dm_exec_sql_text DMF que retorna ao texto SQL identificado por um lado sql_handle.

Um exemplo abaixo mostra o que a consulta gera como resultado.

1

Por hoje é só. Espero que o script possa contribuir no dia a dia de vocês.

Até a próxima!

Quantificando o impacto da atualização no sistema

Olá galera, segue um script bem interessante que extrair da leitura do livro DMVs SQL Server em ação, escrito por Ian W. Stirk. É um script útil no dia a dia de muitos administradores de banco, pois nos auxilia a quantificar o impacto que uma atualização pode causar no ambiente de dados. O uso do script é bem simples, executa  a consulta antes de qualquer atualização, seja de substituição de versão do SQL Server ou um service pack, salvar a saida num arquivo excel  e após a atualização utiliza o mesmo script para comparar os resultados e compreender se a mudança foi benéfica ou prejudicial. Para os administradores que se preocupam em acompanhar as atualizações de seus servidores fica a dica.

Abraços!

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

SELECT
total_elapsed_time, total_worker_time, total_logical_reads
, total_logical_writes, total_clr_time, execution_count
, statement_start_offset, statement_end_offset, sql_handle, plan_handle
INTO #prework
FROM sys.dm_exec_query_stats

SELECT
total_elapsed_time, total_worker_time, total_logical_reads
, total_logical_writes, total_clr_time, execution_count
, statement_start_offset, statement_end_offset, sql_handle, plan_handle
INTO #postwork
FROM sys.dm_exec_query_stats

SELECT
SUM(p2.total_elapsed_time – ISNULL(p1.total_elapsed_time, 0)) AS [TotalDuration]
, SUM(p2.total_worker_time – ISNULL(p1.total_worker_time, 0)) AS [Total Time on CPU]
, SUM((p2.total_elapsed_time – ISNULL(p1.total_elapsed_time, 0)) –
(p2.total_worker_time – ISNULL(p1.total_worker_time, 0))) AS [Total Time Waiting]
, SUM(p2.total_logical_reads – ISNULL(p1.total_logical_reads, 0)) AS [TotalReads]
, SUM(p2.total_logical_writes – ISNULL(p1.total_logical_writes, 0)) AS [TotalWrites]
, SUM(p2.total_clr_time – ISNULL(p1.total_clr_time, 0)) AS [Total CLR time]
, SUM(p2.execution_count – ISNULL(p1.execution_count, 0)) AS [Total Executions]
, DB_NAME(qt.dbid) AS DatabaseName
FROM #prework p1
RIGHT OUTER JOIN
#postwork p2 ON p2.sql_handle = ISNULL(p1.sql_handle, p2.sql_handle)
AND p2.plan_handle = ISNULL(p1.plan_handle, p2.plan_handle)
AND p2.statement_start_offset =ISNULL(p1.statement_start_offset, p2.statement_start_offset)
AND p2.statement_end_offset =ISNULL(p1.statement_end_offset, p2.statement_end_offset)
CROSS APPLY sys.dm_exec_sql_text(p2.sql_handle) as qt
WHERE p2.execution_count != ISNULL(p1.execution_count, 0)
GROUP BY DB_NAME(qt.dbid)

SELECT
SUM(p2.total_elapsed_time – ISNULL(p1.total_elapsed_time, 0)) AS [TotalDuration]
, SUM(p2.total_worker_time – ISNULL(p1.total_worker_time, 0)) AS [Total Time on CPU]
, SUM((p2.total_elapsed_time – ISNULL(p1.total_elapsed_time, 0))
– (p2.total_worker_time – ISNULL(p1.total_worker_time, 0))) AS [Total Time Waiting]
, SUM(p2.total_logical_reads – ISNULL(p1.total_logical_reads, 0)) AS [TotalReads]
, SUM(p2.total_logical_writes – ISNULL(p1.total_logical_writes, 0)) AS [TotalWrites]
, SUM(p2.total_clr_time – ISNULL(p1.total_clr_time, 0)) AS [Total CLR time]
, SUM(p2.execution_count – ISNULL(p1.execution_count, 0)) AS [Total Executions]
, DB_NAME(qt.dbid) AS DatabaseName
, qt.text AS [Parent Query]
FROM #prework p1
RIGHT OUTER JOIN
#postwork p2 ON p2.sql_handle = ISNULL(p1.sql_handle, p2.sql_handle)
AND p2.plan_handle = ISNULL(p1.plan_handle, p2.plan_handle)
AND p2.statement_start_offset =ISNULL(p1.statement_start_offset, p2.statement_start_offset)
AND p2.statement_end_offset =ISNULL(p1.statement_end_offset, p2.statement_end_offset)
CROSS APPLY sys.dm_exec_sql_text(p2.sql_handle) as qt
WHERE p2.execution_count != ISNULL(p1.execution_count, 0)
GROUP BY DB_NAME(qt.dbid), qt.text
ORDER BY [TotalDuration] DESC

SELECT
p2.total_elapsed_time – ISNULL(p1.total_elapsed_time, 0) AS [TotalDuration]
, p2.total_worker_time – ISNULL(p1.total_worker_time, 0) AS [Total Time on CPU]
, (p2.total_elapsed_time – ISNULL(p1.total_elapsed_time, 0))
– (p2.total_worker_time – ISNULL(p1.total_worker_time, 0)) AS [Total Time Waiting]
, p2.total_logical_reads – ISNULL(p1.total_logical_reads, 0) AS [TotalReads]
, p2.total_logical_writes – ISNULL(p1.total_logical_writes, 0) AS [TotalWrites]
, p2.total_clr_time – ISNULL(p1.total_clr_time, 0) AS [Total CLR time]
, p2.execution_count – ISNULL(p1.execution_count, 0) AS [Total Executions]
, SUBSTRING (qt.text,p2.statement_start_offset/2 + 1,
((CASE WHEN p2.statement_end_offset = -1
THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
ELSE p2.statement_end_offset
END – p2.statement_start_offset)/2) + 1) AS [Individual Query]
, qt.text AS [Parent Query]
, DB_NAME(qt.dbid) AS DatabaseName
FROM #prework p1
RIGHT OUTER JOIN
#postwork p2 ON p2.sql_handle = ISNULL(p1.sql_handle, p2.sql_handle)
AND p2.plan_handle = ISNULL(p1.plan_handle, p2.plan_handle)
AND p2.statement_start_offset = ISNULL(p1.statement_start_offset, p2.statement_start_offset)
AND p2.statement_end_offset = ISNULL(p1.statement_end_offset, p2.statement_end_offset)
CROSS APPLY sys.dm_exec_sql_text(p2.sql_handle) as qt
WHERE p2.execution_count != ISNULL(p1.execution_count, 0)
ORDER BY [TotalDuration] DESC

DROP TABLE #prework
DROP TABLE #postwork

Habilitando notificação de mensagem 1205

Olá pessoal, férias acabando heim!! Espero que tenham descansado. Quero compartilhar com vocês uma solução interessante e simples sobre o não envio de notificação do evento de deadlock pelo database_email. Pra quem trabalha com gestão de ambiente de dados sabe que a ocorrência de deadlock gera a mensagem de erro 1205 no SQL Server. Recentemente recebi de um colega um email procurando saber porque o database_email não estava enviando as notificações de alerta de deadlocks para seu email. Inicialmente achei que o problema poderia ser na configuração database_email, no operador e em último caso, até no provedor do email dele. Verificada a configuração, e através de testes constatados que estava tudo bem configurado, iniciei uma pesquisa e encontrei um artigo bem legal de Michael K. Campbell que esclarece bem o fato e ensina como resolvermos o problema usando o comando abaixo :

EXEC master..sp_altermessage 1205, ‘WITH_LOG’, TRUE;

Mas nos perguntamos, pra que ser esse comando?  De acordo com a biblioteca da Microsoft “Altera o estado de mensagens definidas pelo usuário ou do sistema em uma instância do Mecanismo de Banco de Dados do SQL Server.” Porque devemos executá-lo para nossas notificações de mensagem 1205 possam chegar em nosso email? De acordo com a biblioteca da Microsoft “Se uma mensagem foi alterada para ser WITH_LOG, ela sempre será gravada no log de aplicativos do Windows, independentemente de como um usuário invocar o erro.”  Sendo gravada a mensagem no log do SQL Server é possível recebermos os alertas em nosso email.

Veja na figura abaixo a seleção da tabela de sistema sys.messages que mostra através da coluna is_event_logged a mensagem 1205 ativa.

Sem título

Só mais uma dica, é importante também verificar se o perfil de email está habilitado no sistema de alerta do SQL Agent, pois se essa opção não estiver habilitada as notifições de alerta não chegarão com êxito.

Bem pessoal, por hoje é só. Caso queiram conhecer um pouco mais sobre deadlock e os comandos usados é possível encontrar muitas informações na biblioteca da Microsoft e no de Michael K. Campbell nos links abaixo:

https://msdn.microsoft.com/pt-br/library/ms175094(v=sql.120).aspx https://technet.microsoft.com/pt-br/library/ms177433(v=sql.105).aspx http://sqlmag.com/blog/enabling-email-alerts-sql-server-deadlocks.

Até a próxima!