SQL GO!!!

select 'Blog focado em SQL Server' from blog where nome ='SQLGO.wordpress.com'

Arquivos da Categoria: Dicas SQL

Microsoft Product Samples

 

Pessoal,

Nesse site você encontra uma grande variedade de exemplos de projetos, veja alguns:

Microsoft Product Samples

Project Project Site Download
All Microsoft Product Samples in a Box This page Download

 

Sample Databases

Project Project Site Download
Microsoft Sample Databases Project Site Download
VSTS DB Edition Sample DB Projects Project Site Download
Northwind Community Edition Project Site No Releases Yet
SQL Server 2000 Sample DBs Project Site Download

 

Microsoft SQL Server Analysis Services

Project Project Site Download
Microsoft Samples (OLAP, Data Mining, Administration) Project Site Download
Community Analysis Services Samples Gallery Project Site Download
Analysis Services Stored Procedures Project Site Download
MDX Script Performance Analyzer Project Site Download
powerSSAS: A PowerShell provider for SQL Server Analysis Services Project Site Download

 

Microsoft SQL Server Database Engine

Project Project Site Download
Microsoft Database Engine Samples (CLR, Full Text, SMO) Project Site Download
Microsoft Data Programmability Samples (ADO.NET, OLEDB, ODBC) Project Site Download
Microsoft Replication Samples Project Site Download
Microsoft Service Broker Samples Project Site Download
Community Service Broker Samples Project Site Download
Microsoft Transact-SQL Sample Scripts Project Site Download
Microsoft XML Samples Project Site Download
Microsoft TechNet SQL Server Scripting Center Project Site  
SQL Server 2D Matrix Builder Project Site Download
SQL 2008 Extended Events Manager Project Site Download
Allocation SQL Server Management Studio Add-in Project Site Download
Open DBDiff Project Site Download
ADO.NET Entity Framework Extensions Project Site Download
Perseus: Entity Framework EntityBag Project Site Download
SQL CLR Measure Time Project Site Download
ADO.NET Entity Framework & LINQ to Relational Data Project Site  
XML Toolkits & Extensions Project Site  

 

Microsoft SQL Server Integration Services

Project Project Site Download
Microsoft Integration Services Samples Project Site Download
Community Integration Services Samples Gallery Project Site Download
Metadata Driven ETL Management Studio (MDDE) Project Site Download
ssisUnit Project Site Download
MapPoint Batch Geocoder for SSIS Project Site Download
Vulcan Project Site No Releases Yet
Integration Services Performance Strategies Whitepaper  
SCD Component for Kimball Model DWs Project Site Download
ssisTwitterSuite via REST Project Site Download
SSIS Configuration Manager Project Site Download
DTLoggedExec Project Site Download

 

Microsoft SQL Server Reporting Services

Project Project Site Download
Microsoft Reporting Services Samples Project Site Download
ReportServer Explorer Project Site Download
SQL Server 2005 Report Packs   Download

 

Microsoft SQL Server Spatial Tools

Project Project Site Download
SQL Server Spatial Tools Project Site Download
Spatial Data Support in SQL Server 2008 Project Site Download


Anúncios

Nomenclatura de stored procedure

Como anunciamos por aqui, eu e Andressa realizamos um webcast no fim de fevereiro. No fim, tivemos tempo para responder algumas perguntas ao vivo. E uma delas foi se há perda de performance no uso da nomenclatura SP no início da stored procedure. Eu respondi a pergunta, porém,  algumas pessoas nos procuraram para dizer que desconheciam esta recomendação.  E isso é uma verdade, pois vejo muitas empresas  utilizando essa nomenclatura em seus bancos de dados. Portanto, eu resolvi escrever mais sobre assunto.

Como eu respondi no webcast, a perda de performance pode ser irrisória, dependendo da aplicação.  Entretanto temos outro problema a ser observado, a segurança.  Pois ao executar uma stored procedure iniciada por SP, o SQL Server busca no banco master, o que pode deixar brechas para atos maliciosos.

E o que seria um ato malicioso? Imaginem uma instituição bancária, onde existe uma stored procedure chamada ‘SP_TransferenciaContaCorrente’ no banco de dados MovimentacaoBancaria,  que é executada quando há a transferência entre contas correntes. Até aqui  nenhum problema, não é? Agora imaginem a mesma procedure sendo criada no banco master, entretanto transferindo um centavo para conta de algum espertinho ao executar cada transferência. O que vai acontecer aqui? Quem vai ser executada primeira? Como eu disse no nosso webcast, ao criarmos uma stored procedure iniciando com SP, o SQL Server entende que é uma procedure de sistema e procura primeiro no banco master. Ao encontrá-la, ele executará e obviamente não passará pelo banco MovimentacaoBancaria.  Então o espertinho receberá um centavo a cada transferência efetuada.

Portanto, baseado na explicação acima, a melhor prática não recomenda o uso da nomenclatura ‘SP’ no ínicio da stored procedure por deixar furos de segurança na sua instância do SQL Server e de desempenho dependendo da aplicação. Recomenda-se utilizar proc_nomedaprocedure, USP_nomedaprocedure ou qualquer nome desejado. Além disso, a partir do SQL Server 2008, é possível utilizar a ferramenta Policy Management para definir padrões de nomenclatura (assunto para outro post!), evitando nomes indesejados como o explicado nesse post.

CheckList diário utilizando o Reporting Services

Há algum tempo atrás eu li um artigo do Fabricio Lima (Criando um CheckList Automático do Banco de Dados ), adaptei os scripts dele as minhas necessidades e montei um relatório no Reporting Services, segue passo a passo o relatório que recebo diariamente por e-mail.

Para desenvolver um relatório é necessário ter instalado o Business Intelligence Development Studio, caso já tenha crie um projeto do tipo “Report Server Project”

clip_image002

Agora precisamos das fontes de dados compartilhadas, na aba Solution explorer clique com o botão direito em cima da pasta “Shared Data Sources” e selecione a opção “Add New Data Source”.

clip_image003

Defina as propriedades da conexão com o banco de dados.

clip_image005

Agora é necessário criar os DataSet de acordo com a sua necessidades, segue abaixo alguns

exemplos:

Para obtermos o tamanho das bases de dados da instância utilizamos a procedure abaixo.

— ============================================= 
— Author:  <Andressa> 
— Create date: <18/02/2011> 
— Description: <retorna dados do espaço utilizado pelas bases> 
— ============================================= 
CREATE PROCEDURE [dbo].[usp_Relatório_DBCheckList] 
  
AS 
BEGIN  
DECLARE @tabela TABLE (Nome  NVARCHAR(128),Tamanho float,NaoAlocado float) 
DECLARE AllDatabases CURSOR FOR  
  SELECT name
  FROM sys.databases
  WHERE STATE = 0 
   
OPEN AllDatabases 
 
   DECLARE @DBNome NVARCHAR(128),@SQL NVARCHAR(MAX) 
 
    FETCH NEXT FROM AllDatabases INTO @DBNome 
     WHILE (@@FETCH_STATUS = 0) 
      BEGIN 
        SET @SQL = N’USE ‘ + @DBNome +’ 
         declare @objname nvarchar(776), @id int   ,@type character(2)  ,@pages bigint    , 
         @dbname sysname    ,@dbsize bigint    ,@logsize bigint    ,@reservedpages  bigint    , 
         @usedpages  bigint    ,@rowCount bigint   
          
         SELECT @id = object_id, @type = type FROM sys.objects WHERE object_id = object_id(@objname)   
         if @id is null   
          begin   
            select @dbsize = sum(convert(bigint,case when status & 64 = 0 then size else 0 end))   
             ,@logsize = sum(convert(bigint,case when status & 64 <> 0 then size else 0 end))   
            from dbo.sysfiles   
          end 
          select   @reservedpages = sum(a.total_pages),   
             @usedpages = sum(a.used_pages),   
             @pages = sum(   
            CASE   
             When it.internal_type IN (202,204) Then 0   
             When a.type <> 1 Then a.used_pages   
             When p.index_id < 2 Then a.data_pages   
             Else 0   
            END   
            )   
         from sys.partitions p join sys.allocation_units a on p.partition_id = a.container_id   
           left join sys.internal_tables it on p.object_id = it.object_id   
           select database_name = db_name(),   
           database_size = ltrim(str((convert (dec (15,2),@dbsize) + convert (dec (15,2),@logsize))    
           * 8192 / 1048576,15,2)),   
           ”unallocated space” = ltrim(str((case when @dbsize >= @reservedpages then   
           (convert (dec (15,2),@dbsize) – convert (dec (15,2),@reservedpages))    
           * 8192 / 1048576 else 0 end),15,2) ) ‘  
        INSERT INTO @tabela 
        EXEC sp_executesql @SQL 
 
         FETCH NEXT FROM AllDatabases INTO @DBNome 
      END 
    SELECT Nome, Tamanho, NaoAlocado from @tabela 
  CLOSE AllDatabases 
  DEALLOCATE AllDatabases 
END

Agora referencie a procedure no dataset.

clip_image007

Tamanho das unidades de Disco do servidor (DataSet_Disco)

 
XP_FIXEDDRIVES
 

 

Tamanho dos arquivos de log de toda a instância (DataSet_LogSpace)

DBCC SQLPERF(LOGSPACE)
 

 

 

Listagem de Jobs que estão em execução (DataSet_JobsExecucao)

SELECT Name AS Nome

,run_Requested_Date AS DataExecucao

,DATEDIFF(mi,run_Requested_Date

,GETDATE()) AS Duracao_Minutos

FROM msdb..sysjobactivity A INNER JOIN

msdb..sysjobs B ON A.job_id = B.job_id

WHERE start_Execution_Date IS NOT NULL AND stop_execution_date IS NULL

Listagem dos Jobs que não foram executados com sucesso (DataSet_JobFailed)

DECLARE @Ontem INT

DECLARE @OntemHorario INT

SET @OntemHorario= CAST((RIGHT(‘0’+CONVERT(VARCHAR(2), (DATEPART(HOUR,DATEADD(HOUR,-1, GETDATE())))), 2) +RIGHT(‘0’+CONVERT(VARCHAR(2), (DATEPART(MINUTE,GETDATE()))), 2)+RIGHT(‘0’+CONVERT(VARCHAR(2), (DATEPART(SECOND,GETDATE()))), 2)) AS INT)

SET @Ontem = CAST(CONVERT (VARCHAR(8),(DATEADD (DAY, -1, GETDATE())),112) AS INT)

CREATE TABLE #HistoricoJobs( Cod INT identity(1,1)

,IDInstancia INT

,IDJob VARCHAR(255)

,NomeJob VARCHAR(255)

,IDStep INT

,NomeStep VARCHAR(255)

,IDSqlMensagem INT

,SqlSeverity INT

,SqlMensangem VARCHAR(3990)

,IDStatusExecucao INT

,DataExecucao VARCHAR(20)

,HorarioExecucao VARCHAR(20)

,DuracaoExecucao INT

,EmailOperador VARCHAR(100)

,NetSenderOperador VARCHAR(100)

,PagerOperador VARCHAR(100)

,TentativasRealizadAS INT

,NomeServidor VARCHAR(100))

INSERT INTO #HistoricoJobs

EXEC Msdb.dbo.SP_HELP_JOBHISTORY NULL,NULL,NULL,NULL,NULL,@Ontem,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,’FULL’

SELECT NomeJob

,CASE

WHEN IDStatusExecucao = 0 THEN ‘Failed’

WHEN IDStatusExecucao = 1 THEN ‘Succeeded’

WHEN IDStatusExecucao = 2 THEN ‘Retry (step only)’

WHEN IDStatusExecucao = 3 THEN ‘Canceled’

WHEN IDStatusExecucao = 4 THEN ‘In-progress message’

WHEN IDStatusExecucao = 5 THEN ‘Unknown’ END Status

,CAST(DataExecucao + ‘ ‘ + RIGHT(’00’ + SUBSTRING(HorarioExecucao,(LEN(HorarioExecucao)-5),2) ,2)+ ‘:’ +

RIGHT(’00’ + SUBSTRING(HorarioExecucao,(LEN(HorarioExecucao)-3),2) ,2)+ ‘:’ +

RIGHT(’00’ + SUBSTRING(HorarioExecucao,(LEN(HorarioExecucao)-1),2) ,2) AS VARCHAR) Dt_Execucao

,RIGHT(’00’ + SUBSTRING(cast(DuracaoExecucao AS VARCHAR),(LEN(DuracaoExecucao)-5),2) ,2)+ ‘:’ +

RIGHT(’00’ + SUBSTRING(cast(DuracaoExecucao AS VARCHAR),(LEN(DuracaoExecucao)-3),2) ,2)+ ‘:’ +

RIGHT(’00’ + SUBSTRING(cast(DuracaoExecucao AS VARCHAR),(LEN(DuracaoExecucao)-1),2) ,2) DuracaoExecucao

,SqlMensangem

FROM #HistoricoJobs

WHERE ((DataExecucao >=@Ontem AND HorarioExecucao >= @OntemHorario) OR DataExecucao >@Ontem)

AND IDStep = 0

AND IDStatusExecucao <> 1

ORDER BY Dt_Execucao

DROP TABLE #HistoricoJobs

Listagem dos últimos backups (DataSet_Backup)

SELECT database_name as NomeDataBase

, name as Nome

,Backup_start_date as DataInicio

, datediff(mi,Backup_start_date,Backup_finish_date) Duracao_Min

, server_name as NomeServidor

,recovery_model as RecoveryModel

, cast(backup_size/1024/1024 as numeric(15,2)) As Tamanho

,type as Tipo

FROM msdb.dbo.backupset B

INNER JOIN msdb.dbo.backupmediafamily BF ON

B.media_set_id = BF.media_set_id

WHERE Backup_start_date >= DATEADD(HH, 18 ,@Dt_Referencia – 1 ) –backups realizados a partir das 18h de ontem

and Backup_start_date < DATEADD (DAY, 1, @Dt_Referencia)

order by database_name, Backup_start_date

/

Após criar os DataSets você terá uma lista semelhante a imagem abaixo:

clip_image018

Para criar um relatório clique com o botão direito em cima da pasta report e “New Item”, selecione “report”, nomeie o arquivo e clique em “Add”. Feito isso você precisa  criar os DataSources/DataSets utilizando o DataSource/DataSet reference correspondente.

image

Com a parte de acesso a dados já finalizada, precisamos criar a parte gráfica do relatório, montei umas tabelas simples e sem formatação apenas para o melhor entendimento.

Para adicionar um campo à uma tabela é simples, somente arrastar o campo do dataset para a tabela desejada.

clip_image020

Abaixo eu apliquei uma formatação simples apenas para diferenciar os dados do nosso relatório (job, bkp, log…)

Utilizei um recurso opcional de alterar a cor da célula de acordo com o valor da linha, assim quando um banco de dados estiver com pouco espaço alocado eu posso visualizar de maneira mais rápida facilitando a tomada de decisão, segue o comando que eu montei.

image

=IIF(
((Fields!Tamanho.Value-Fields!NaoAlocado.Value)/Fields!Tamanho.Value)>0.9,
    "#f28989",
    IIF((((Fields!Tamanho.Value-Fields!NaoAlocado.Value)/Fields!Tamanho.Value)>0.8
            and ((Fields!Tamanho.Value-Fields!NaoAlocado.Value)/Fields!Tamanho.Value) < 0.9),
    "#ffff80",
    "#c0ffc0"))

Você pode adaptar a expressão acima para toda as tabelas.

Interpretação  do código:

Quando o espaço alocado do meu banco de dados for…

  • maior que 90% a linha fica vermelha.
  • maior que 80% e menor que 90% a linha fica amarela.
  • menor que 80% a linha fica verde.

Vejam como ficou o relatório depois de renderizado.

image



 
O projeto de relatórios está disponível pra download.
 
Espero que tenham gostado da dica.
 
Andressa

Identificando a aplicação no Profiler

Algumas aplicações usam o mesmo usuário para se conectar no banco de dados e isso dificulta a identificação no profiler, quando o Application name não está definido visualizamos a seguinte linha no profiler:

image

Para ajudar o DBA a identificar qual a aplicação está realizando a chamada no banco é só definir o parâmetro Application Name no seu data Source seja ele na aplicação .NET, ReportingServices, Integration Services ou demais aplicações.

Data Source=isis\sqlexpress;Initial Catalog=Relatorios; Application Name="Servidor de Relatórios"

image

Isso também pode ser adicionado no data source do Servidor de relatórios:

image

Agora vamos executar o relatório para conferir o Application name:

image

E o nosso resultado:

image

Espero que utilizem essa dica sempre. =)

Profiler para o SQL Express

Para monitorar o banco de dados nós utilizamos o Sql Profiler, porém nas versões do SQL Express e SQL Express with Adv Services essa ferramenta não está disponível, para “driblar” nós podemos utilizar uma ferramenta free muito semelhante ao Sql Profiler.

Segue o link:

http://code.google.com/p/sqlexpressprofiler/downloads/list

Enjoy