Tag Archives: 2005

Scripts, SQL, Virtual PASS BR

Estou fazendo index scan sim, idaí?

Leave a comment

Complementando o Post Procurando por conversão implícita sobre a apresentação do Marcos Freccia (Blog | Twitter)  “10 coisas que todo desenvolvedor deveria saber sobre SQL Server”

Um dos grandes problemas dessa conversão é o SQL utilizar index scan ao invés de index seek.

Esse código mostra consultas que estão executado Index Scan por motivos de Conversões Implícitas.

with XMLNAMESPACES
('http://schemas.microsoft.com/sqlserver/2004/07/showplan' as sql)
select
total_worker_time/execution_count AS AvgCPU
, total_elapsed_time/execution_count AS AvgDuration
, (total_logical_reads+total_physical_reads)/execution_count AS AvgReads
, execution_count
, SUBSTRING(st.TEXT, (qs.statement_start_offset/2)+1 , ((CASE
qs.statement_end_offset WHEN -1 THEN datalength(st.TEXT) ELSE
qs.statement_end_offset END - qs.statement_start_offset)/2) + 1) AS txt
, qs.max_elapsed_time
, db_name(qp.dbid) as database_name
, quotename(object_schema_name(qp.objectid, qp.dbid)) + N'.' +
quotename(object_name(qp.objectid, qp.dbid)) as obj_name
, qp.query_plan.value(
N'(/sql:ShowPlanXML/sql:BatchSequence/sql:Batch/sql:Statements/sql:StmtSimple[@StatementType = "SELECT"]/sql:QueryPlan/sql:RelOp/descendant::*/sql:ScalarOperator[contains(@ScalarString, "CONVERT_IMPLICIT")])[1]/@ScalarString', 'nvarchar(4000)' ) as scalar_string
, qp.query_plan
from sys.dm_exec_query_stats as qs
cross apply sys.dm_exec_query_plan(qs.plan_handle) as qp
cross apply sys.dm_exec_sql_text(qs.sql_handle) st
where qp.query_plan.exist(
N'/sql:ShowPlanXML/sql:BatchSequence/sql:Batch/sql:Statements/sql:StmtSimple[@StatementType = "SELECT"]/sql:QueryPlan/sql:RelOp/sql:IndexScan/descendant::*/sql:ScalarOperator[contains(@ScalarString, "CONVERT_IMPLICIT")]' ) = 1;
Scripts, SQL, Virtual PASS BR

Gerador de Senhas

Leave a comment

Esse é um código antigo que estava escondido no HD.

Bem simples e usual.

/*
Versao: 1.0
Data: 20100302
Por: Ricardo Leka
Site: https://leka.com.br
email: ricardo@leka.com.br
twitter: @bigleka
*/
/*
Gerador de Senhas
Pode gerar senhas com quantidades minima e maxima de caracteres
com numeros ou complexidade
*/
DECLARE
  @complex tinyint
  , @minlen tinyint
  , @maxlen tinyint  

SET @minlen = 4 --tamanho minimo da senha
SET @maxlen = 8 --tamanho maximo da senha
SET @complex = 4
-- 1 todas as letras minusculas
-- 2 inclui letras maiusculas
-- 3 inclui numeos
-- 4 inclui caracteres especiais

DECLARE
  @password varchar(12)
  , @len tinyint
  , @type  tinyint
  , @type2 tinyint

SET @len = 0
SET @password = ''
WHILE @len NOT BETWEEN @minlen and @maxlen
  BEGIN
    SET @len = ROUND(1 + (RAND(CHECKSUM(NEWID())) * @maxlen), 0) + 1
  END
WHILE @len > 0
  BEGIN
    DECLARE @newchar CHAR(1)
    SET @type = ROUND(1 + (RAND(CHECKSUM(NEWID())) * (@complex - 1)), 0)
    IF @type = 1
      SET @newchar = CHAR(ROUND(97 + (RAND(CHECKSUM(NEWID())) * 25), 0))
    IF @type = 2
      SET @newchar = CHAR(ROUND(65 + (RAND(CHECKSUM(NEWID())) * 25), 0))
    IF @type = 3
      SET @newchar = CHAR(ROUND(48 + (RAND(CHECKSUM(NEWID())) * 9), 0))
    IF @type = 4
      BEGIN
        SET @type2 = ROUND(1 + (RAND(CHECKSUM(NEWID())) * 3), 0)
        IF @type2 = 1
          SET @newchar = CHAR(ROUND(33 + (RAND(CHECKSUM(NEWID())) * 14), 0))
        IF @type2 = 2
          SET @newchar = CHAR(ROUND(58 + (RAND(CHECKSUM(NEWID())) * 6), 0))
        IF @type2 = 3
          SET @newchar = CHAR(ROUND(91 + (RAND(CHECKSUM(NEWID())) * 5), 0))
        IF @type2 = 4
          SET @newchar = CHAR(ROUND(123 + (RAND(CHECKSUM(NEWID())) * 3), 0))
      END
-- remove caracteres que podem ser confundidos com outros
    IF @newchar NOT IN ('b', 'l', 'o', 's', 'I', 'O', 'S', '0', '1', '!', '''', '.', ',', '/', '`', '\', '|')
      BEGIN
        SET @password = @password + @newchar
        SET @len = @len - 1
      END
  END
SELECT @password as Senha
SQL, Virtual PASS BR

[Vídeo] Instant File Initialization

Leave a comment

Já vou logo avisando:

  • Não assista esse vídeo depois das 22h,,, não me responsabilizo por pesadelos,,,
  • Não assista perto dos seus filhos,,, é mais feio que o bicho papão,,,
  • Não assista no trabalho,,, seus colegas já sofrem tendo que trabalhar com você,,,,
  • Meu primeiro vídeo,,, Gravei o vídeo as 4am,,, estava com muito sono,,,

Se, depois de tudo isso, clicar em Play não me responsabilizo por absolutamente nada,,,

[vimeo http://www.vimeo.com/27824432 w=701&h=438]

Instant file initialization from Ricardo Leka on Vimeo.

Scripts, SQL, Virtual PASS BR

1807

2 Comments

Atualmente meu notebook (também conhecido como desktop porque a bateria já era) não tem muita capacidade de suportar alguns testes,,, (Acer Aspire 5050 – AMD Turion 1 core, 1.9GB RAM, 35GB HD),,,

Então tentei fazer uma coisa interessante,,,, criar um banco e apontar os arquivos para meu storage (QNAP TS-110),,,

Tenho 2 instâncias de SQL instaladas nesse note, 1 SQL Server 2008 R2 e 1 SQL Server 2005.

Claro que no 2008 R2 funcionou e no 2005 não…

yep2008

Ai lembrei que para o SQL 2005 (e para o 2008 sem ser R2) criar uma base em local UNC eu precisava habilitar a trace flag 1807. Feito isso conseguir criar a base sem problema.

Achei interessante relembrar isso, pois vai que uma hora qualquer alguém precisa, ou para fazer um LAB ou por falta de espaço (meu caso) você sempre tem uma alternativa… e muita gente nem lembra dessa possibilidade…

Scripts, SQL, T-SQL Tuesday

T-SQL Tuesday #21 – Depois arrumo esse código,,,

1 Comment

Dessa vez o anfitrião do T-SQL Tuesday é o próprio idealizador Adam Machanic (Blog | Twitter).

E de uma forma diferente, não é na Terça-feira,,, por quê?

Porque não importa, desde que funcione pode ser zuado mesmo,,, o tópico desse mês é sobre “mostrar seu código lixo para o mundo”,,,

Todo mundo já escreveu um código uma vez ou outra,,,, todo o DBA tem aquele conjunto de scripts que o ajudam a identificar alguns problemas, fazer um tunning, arrumar alguma coisa,,, E com certeza todo mundo tem aquele código que hoje, pega para olha e começa a dar risada de como é que teve coragem de fazer uma coisa como aquela…

Meu exemplo é bem simples, O código é horrível, demora demais mas, funciona,,,

Ele troca os dados de posição de uma coluna específica.

WITH cteTableTel AS (
    SELECT
        ROW_NUMBER() OVER (ORDER BY NEWID()) AS n,
        SOBRENOME
    FROM CLIENTES
    )
UPDATE CLIENTES
   SET ENDERECO = (
       SELECT ENDERECO
       FROM cteTableTel
       WHERE cteTableTel.n = CLIENTES.ID)
Esse código funciona, não é nada bonito, não é performático, mas para a necessidade de uma base de treino com dados reais ele ajuda bastante,,,
Se você percebeu ele tem um problema,,, sabe qual é?
Como você garante que todas as linhas da tabela de clientes realmente estão em uma sequencia? Você pode ter apagado algum registro uma hora ou outra,,, Logo, haverá cliente que o endereço não vai ser atualizado,,
Já sei, você nem reparou nesse problema,,, você deve estar pensando: “por que ele esta fazendo um update na tabela de clientes se ele está usando uma CTE?”, ou também, “Legal, ele está fazendo isso em uma tabela que tem algum tipo de ID e quando tem aquelas tabelas que não da pra usar nada de referencia?”
Como eu disse, ele não é performático, e eu nem tinha me atentado nesses detalhes na época,,,  Houveram duas situações que me refizeram rever esse código: o primeiro caso dos usuários que não tinham os dados alteradores e o último… ai com muita vontade resolvi reescrever,,,
E ele ficou mais ou menos assim:
;WITH cte AS (
    SELECT
        ROW_NUMBER() OVER (ORDER BY NEWID()) AS n1,
        ROW_NUMBER() OVER (order by SOBRENOME) AS n2,
        SOBRENOME
    FROM CLIENTES
    )
UPDATE c1
   SET SOBRENOME = c2.SOBRENOME
   from cte as c1
   inner join cte as c2
   on c1.n1 = c2.n2

Dessa vez não tem problema com o usuário sem o dado alterado, performance melhorada em quase 90% e pouco importa se a tabela em algum registro para referencia,,,

Eu comparei a execução dos 2 códigos em uma tabela com um pouco mais de  3 milhões de linhas e o segundo código demorou quase 4 horas para finalizar,,, já o primeiro código eu parei a execução dele depois de 2 SEMANAS executando.

Tenho códigos piores no meu repositório? claro que sim. Esse é apenas um exemplo de um dos piores que já fiz…

Qual é o seu? tem coragem mostrar?

Scripts, SQL, Virtual PASS BR

Procurando por conversão implícita

4 Comments

Assistindo ontem a apresentação do Marcos Freccia (Blog | Twitter) sobre “10 coisas que todo desenvolvedor deveria saber sobre SQL Server” alguém perguntou como ver as conversões implícitas que estão sendo executadas no SQL,,, ou alguma coisa assim,,,

Então, segue um script rapidão que mostra as conversões,,, o chato desse script é que ele tem que ser executado por banco,,,

Vou tentar montar um outro que traga a informação de todos os bancos,,,

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED 

DECLARE @dbname SYSNAME
SET @dbname = QUOTENAME(DB_NAME()); 

WITH XMLNAMESPACES
   (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
SELECT
   stmt.value('(@StatementText)[1]', 'varchar(max)'),
   t.value('(ScalarOperator/Identifier/ColumnReference/@Schema)[1]', 'varchar(128)'),
   t.value('(ScalarOperator/Identifier/ColumnReference/@Table)[1]', 'varchar(128)'),
   t.value('(ScalarOperator/Identifier/ColumnReference/@Column)[1]', 'varchar(128)'),
   ic.DATA_TYPE AS ConvertFrom,
   ic.CHARACTER_MAXIMUM_LENGTH AS ConvertFromLength,
   t.value('(@DataType)[1]', 'varchar(128)') AS ConvertTo,
   t.value('(@Length)[1]', 'int') AS ConvertToLength,
   query_plan
FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS qp
CROSS APPLY query_plan.nodes('/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple') AS batch(stmt)
CROSS APPLY stmt.nodes('.//Convert[@Implicit="1"]') AS n(t)
JOIN INFORMATION_SCHEMA.COLUMNS AS ic
   ON QUOTENAME(ic.TABLE_SCHEMA) = t.value('(ScalarOperator/Identifier/ColumnReference/@Schema)[1]', 'varchar(128)')
   AND QUOTENAME(ic.TABLE_NAME) = t.value('(ScalarOperator/Identifier/ColumnReference/@Table)[1]', 'varchar(128)')
   AND ic.COLUMN_NAME = t.value('(ScalarOperator/Identifier/ColumnReference/@Column)[1]', 'varchar(128)')
WHERE t.exist('ScalarOperator/Identifier/ColumnReference[@Database=sql:variable("@dbname")][@Schema!="[sys]"]') = 1
SQL, T-SQL Tuesday, Virtual PASS BR

T-SQL Tuesday #20 – Melhores Práticas

2 Comments

Neste mês o tema escolhido pelo Sr. Amit Banerjee (blog | twitter) foi: Melhores Práticas,,,

A algum tempo atrás escrevi um post com “Dicas para otimizar suas funções SQL”,,, vou adicionar algumas coisas que acho interessante com este post aqui.

  • Comentários,,,,

Lembra daquela procedure que fazia uma validação de alguma coisa em algum lugar ??? Lembra por que você declarou aquele campo bit no começo ??? Não? tem certeza que sabe? acha que era para alguma coisa importante?

Uma coisa que acho muito importante em todo o código, mas são poucas as pessoas que fazem direito e menos ainda as que fazem, é comentar o que ele faz,,,

Não custa muito adicionar algumas linhas com algum tipo de descritivo do que aquilo deveria fazer, quem fez, quando fez, qual a versão, algum exemplo,,,

Lembre-se: Uma hora ou outra o código pode precisar de manutenção,,, ela pode ser feita por você ou outra pessoa,,, em todo o caso,,, é sempre bom ter alguma coisa para te ajudar a lembrar,,,

Ex:

/*
Nome: usp_mostra_nome_base
Versão: 2.0
Data de criação: 11-07-2011
Data da última modificação: 12-07-2011

Autor: Ricardo Leka Roveri
Últuma modificação executada por: Ricardo Leka Roveri

Script:
Esta procedure server para listas o nome das bases existentes no sistema.

Exemplo
exec usp_mostra_nome_base

Versão 1:
Autor: Ricardo Leka Roveri

nesta versão a proc faz XYZ

Versão 2:
Autor: Ricardo Leka Roveri

nesta versão a proc faz XYZ ordenado por data de criação

*/

Outra coisa interessante seria comentar partes do código,,, não precisa escrever um livro do motivo que você está fazendo aquele join entre 20 tabelas mas, de uma forma clara, escrever o objetivo dessa bagunça,,,

  • NOLOCK,,,

Você é um fanático por NOLOCK? Legal,,, nada contra,,, mas se vai usar isso umas 40 vezes dentro de uma procedure porque você não declara ele como:

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITED;

é legal, ajuda, mais simples pra você e para qualquer outra pessoa que veja seu código…

  • Lembrete,,,

Lembre-se:

A maior parte de vocês não desenvolve coisas pra vocês,,, vocês desenvolvem coisas pra outras pessoas usarem…

Não é porque na sua maquina tudo funciona direitinho que quando for para produção tem que funcionar,,, Você precisa testar,,, faça teste de carga… stresse sua aplicação até ela parar,,, 10,,, 100,,, 1000,,, acessos simultâneos,,,

Existem muitas ferramentas que fazem teste de carga. Ex.: JMeter, o próprio SQL Profiler, etc..

SQL, Virtual PASS BR

Corrompendo um Banco SQL

3 Comments

Qual a necessidade de criar uma base corrompida?

Pra que você precisa desse tipo de coisa?

Você precisa treinar…

Mesmo que saiba o que fazer, é importante treinar para situações complicadas…

É melhor fazer muita tentativa em erro em um ambiente controlado do que no ambiente de produção,,, certo?

Então,,, antes de mais nada… eu sei que você sabe,,, mas não custa nada relembrar,,,

NUNCA FAÇA ISSO EM PRODUÇÃO !!!

Bom,,, com isso em mente,,, vamos começar,,,

1. Vamos criar uma base:

CREATE DATABASE [corrompeu]
GO

2. Vamos alterar o modo de recovery da base:

alter database corrompeu
set recovery full
GO

3. Agora vamos colocar uma tabela:

use corrompeu
GO

create table vendas
(
vendasID int identity,
clienteID int default convert(int, 100000 * RAND()),
vendaData datetime default getdate(),
vendaTotal money
)
GO

4. Adicionamos um índice pra dar gosto…

create clustered index vendaCI on vendas (vendasID)
GO

5. Colocamos uns dados,,,

set nocount ON
GO

declare @conta INT
select @conta = 0
while (@conta < 50000)
begin
    insert into vendas (vendaTotal)
    values (100*RAND())
    select @conta = @conta +1
end
GO

7. E vamos fazer uns backups

use master
GO

backup database corrompeu
to disk = ‘d:\db01\local\corrompeu_1.bak’
with init
go

backup log corrompeu
to disk = ‘d:\db01\local\corrompeu_2.trn’
go

8. Bom,,, com os backups feitos,,, vamos ver as páginas que foram criadas,,,

dbcc ind (‘corrompeu’,‘vendas’,1)
GO

9. Escolha uma página e coloque no lugar do XXXXX

DBCC TRACEON (3604)
GO
dbcc page(‘corrompeu’,1,XXXXX,3)

Você deve ver alguma coisa do tipo:

A página que eu escolhi foi a 1:493. Meu vendasID vai do registro 24256 até 245000.

Agora começa a ficar legal….

10. Vamos colocar a base offline:

alter database corrompeu
set offline
GO

11. Agora um simples cálculo…

select 493*8192
GO

Temos o número em decimal da localização da página no arquivo .mdf

12. Com esse número vamos utilizar um editor Hexadecimal para achar a linha dentro do arquivo .mdf.

  • Dentro do editor de Hexadecimal, abra o arquivo .mdf (neste caso D:\DB01\Corrompeu.mdf).
  • Clique em “Localizar” e escolha “Ir Para”.
  • Escolha a opção “DEC”, digite ou cole o resultado do cálculo acima e depois cliente em “HEX”, ele vai converter o valor para Hexadecimal.

  • clique em “OK”
  • Altere a linha onde o cursor esta piscando para 00 (zero-zero), ela vai ficar em vermelho.

  • Salve o arquivo.

13. Agora dentro do SQL vamos voltar com a base online:

alter database corrompeu
set online
GO

14. Usando o DBCC CHECKDB, vamos ver se a base está realmente corrompida…

Legal,,, temos uma base corrompida…

15. vamos tentar um select na tabela,,, e olha lá o erro,,,

Bom,,, legal… temos uma base corrompida,,, e agora?

Agora fica legal… o objetivo é deixar a base operacional sem perder informação… imagine que essa é sua base de produção e justamente essa tabela é a folha de pagamento,,, olha que legal…

Uma dica: tentei fazer o processo de restore no SQL Server Denali CTP 1 e não consegui restaurar apenas a página, tive que remover ela e reinserir os dados através de outra base, fiz o mesmo processo de restore apenas da página no SQL Server 2008 R2 e funcionou sem problema.

Se alguém precisar de ajuda é só deixar o comentário…

ATUALIZAÇÃO:

Segue o link do SkyDrive com a base, backup e o script desse exemplo:

https://skydrive.live.com/?cid=5145b04265f2979d&sc=documents&id=5145B04265F2979D%21171#

SQL, Uncategorized, Virtual PASS BR

TOP 5 – Ferramentas grátis

4 Comments

ATUALIZAÇÃO !!! – 20/09/2012

Esse post é para falar de ferramentas gratuitas,,, é com muito pesar que estou retirando o SSMS Tools Pack do primeiro lugar, a partir da versão 2.5.0.0 ele deixou de ser de graça, logo, vai contra o intuito do post…

Estou substituindo pela ferramenta SSMSBoost…

Tem gente que gosta de fazer as coisas na marra,,, sem ajuda de nada,,, script de baixo de script,,, Isso é muito legal, tem muita coisa que só se resolve assim,,,

O importante é conhecer o que o mercado oferece quando você quer “uma ajuda” ou pra realmente facilitar o dia a dia,,,

O meu TOP 5 de ferramentas gratuitas são:

  1. Pra quem gosta de trabalhar com o SSMS, um add-on bem legal é o SSMS Tools Pack desenvolvido por Mladen Prajdić. Ele adiciona algumas funções bem legais como: histórico, snippets, gerador de código… Acho uma ferramenta pequena e legal… Uma ferramenta muito interessante para adicionar funcionalidades ao SSMS é o SSMSBoost ele adicionar recursos muito bons como snippets, localizador de objetos, alterador de barra de titulo e uma coisa bem legal que é o cadastro de conexão onde você pode colocar alerta de ambiente de produção,,, ai ele avisa, dependendo do comando que você precisa prestar atenção antes de dar um truncate table por exemplo…. Ele é de graça, mas naquelas, você precisa reinstalar ele a cada 45 dias (não é trial, é só uma coisa chata que o desenvolvedor colocou),,,
  2. Quem nunca passou raiva com o gerador de plano de execução do SSMS que drop um banco?,,, Se você usar o SQL Sentry Plan Explorer pelo menos uma vez, não vai querer deixar de usar,,, ele mostra de uma forma fácil de entender qual parte do plano estásendo mais custoso para a operação… fora outras coisas legais…
  3. Não pode faltar de jeito nenhum o Who is Active desenvolvido por Adam Machanic e por falar nele, existe um add-on da Schema Solutionsque adiciona uma interface gráfica para a execução de procedure.
  4. Na primeira vez que vi essa ferramenta não achei que seria tão útil, mas o SQL Trace Analyzeré bem interessante. Ele analisa o Profiler capturado em arquivo ou banco e gera um relatório consolidado mostrando o impacto, tempo, processamento, IO, etc.. E de brinde ele instala um monitorador de Locks/Blocks. O problema dessa ferramenta é a parafernália que ele instala, mas você pode remover o resto das coisas e ficar só com o programa principal.
  5. E não podia faltar alguma forma de monitorar o que acontece com o banco,,, para isso achei o IgniteFree, uma ferramenta muito simples de configurar e com muita informação relevante. Claro que a versão Trial/Full tem mais opções, mas mesmo na versão free é uma ótima ferramenta. Ela é leve, não ocupa muito espaço, não gera pressão na máquina que está sendo monitorada e de quebra ainda consegue monitorar uns Oracles que você tenha perdido no ambiente…
Scripts, SQL, Virtual PASS BR

Quando foi que trocaram a senha?

Leave a comment

Quando estamos no SQL Server (2005 ou superior), existem uma função que mostra algumas propriedades interessante sobre o login do SQL chamada LoginProperty.

Não conheço muitas aplicações que possuem interface que permite o usuário trocar a senha dele no SQL,,, mas achei interessante ter essa opção para saber quando alguém trocou a senha e “esqueceu” de avisar,,, ai tem aplicação que não abre,,, usuário que não loga,,, e quase sempre ninguém nunca fez nada….

Aqui vão alguns selects interessantes….

Mostra todos os logins que tiveram a senha trocada a mais de 30 dias:

 1: SELECT name, LOGINPROPERTY([name], 'PasswordLastSetTime') AS 'SenhaTrocada'
 2: FROM sys.sql_logins
 3: WHERE LOGINPROPERTY([name], 'PasswordLastSetTime') < DATEADD(dd, -60, GETDATE());

Mostra todos os logins que tiveram a senha trocada no último dia:

 1: SELECT name, LOGINPROPERTY([name], 'PasswordLastSetTime') AS 'SenhaTrocada'
 2: FROM sys.sql_logins
 3: WHERE LOGINPROPERTY([name], 'PasswordLastSetTime') > DATEADD(dd, -1, GETDATE());