My "First Responder" set of Scripts

1.       Script 1 – General Info

--Informações Gerais Banco de dados

SELECT

       CONVERT(VARCHAR(25), DB.name) AS [Nome do Banco de dados],

       CONVERT(VARCHAR(10), DATABASEPROPERTYEX(name, 'status')) AS [Status],

       state_desc as [Status Descrição],

       (SELECT COUNT(1) FROM sys.master_files WHERE DB_NAME(database_id) = DB.name AND type_desc = 'rows') AS [Número de Arquivos de dados],

       (SELECT SUM((size*8)/1024) FROM sys.master_files WHERE DB_NAME(database_id) = DB.name AND type_desc = 'rows') AS [Tamanho MB],

       (SELECT COUNT(1) FROM sys.master_files WHERE DB_NAME(database_id) = DB.name AND type_desc = 'log') AS [Número de Arquivos de Log],

       (SELECT SUM((size*8)/1024) FROM sys.master_files WHERE DB_NAME(database_id) = DB.name AND type_desc = 'log') AS [Tamanho Arquivos de Log MB],

       recovery_model_desc AS [Modelo de Recovery (Bancos de produção, desejável FULL],

       CASE compatibility_level

             WHEN 60 THEN '60 (SQL Server 6.0)'

             WHEN 65 THEN '65 (SQL Server 6.5)'

             WHEN 70 THEN '70 (SQL Server 7.0)'

             WHEN 80 THEN '80 (SQL Server 2000)'

             WHEN 90 THEN '90 (SQL Server 2005)'

             WHEN 100 THEN '100 (SQL Server 2008)'

       END AS [Nível de Compatibilidade],

       CONVERT(VARCHAR(20), create_date, 103) + ' ' + CONVERT(VARCHAR(20), create_date, 108) AS [Data de Criação],

       -- last backup

       ISNULL((SELECT TOP 1

       CASE TYPE WHEN 'D' THEN 'Full' WHEN 'I' THEN 'Differential' WHEN 'L' THEN 'Transaction log' END + ' – ' +

       LTRIM(ISNULL(STR(ABS(DATEDIFF(DAY, GETDATE(),Backup_finish_date))) + ' dias atrás', 'NUNCA')) + ' – ' +

       CONVERT(VARCHAR(20), backup_start_date, 103) + ' ' + CONVERT(VARCHAR(20), backup_start_date, 108) + ' – ' +

       CONVERT(VARCHAR(20), backup_finish_date, 103) + ' ' + CONVERT(VARCHAR(20), backup_finish_date, 108) +

       ' (' + CAST(DATEDIFF(second, BK.backup_start_date,

       BK.backup_finish_date) AS VARCHAR(4)) + ' '

       + 'seconds)'

       FROM msdb..backupset BK WHERE BK.database_name = DB.name ORDER BY backup_set_id DESC),'-') AS [Último Backup],

       CASE WHEN is_fulltext_enabled = 1 THEN 'Fulltext habilitado' ELSE '' END AS [fulltext],

       CASE WHEN is_auto_close_on = 1 THEN 'autoclose habilitado (não recomendado)' ELSE '' END AS [autoclose],

       page_verify_option_desc AS [page verify option],

       CASE WHEN is_read_only = 1 THEN 'read only' ELSE '' END AS [read only],

       CASE WHEN is_auto_shrink_on = 1 THEN 'autoshrink habilitado (não recomendado)' ELSE '' END AS [autoshrink],

       CASE WHEN is_auto_create_stats_on = 1 THEN 'auto create statistics (ok)' ELSE '' END AS [auto create statistics],

       CASE WHEN is_auto_update_stats_on = 1 THEN 'auto update statistics (ok)' ELSE '' END AS [auto update statistics],

       CASE WHEN is_in_standby = 1 THEN 'standby' ELSE '' END AS [standby],

       CASE WHEN is_cleanly_shutdown = 1 THEN 'cleanly shutdown' ELSE '' END AS [cleanly shutdown]

FROM

       master.sys.databases DB

ORDER BY

       1

2.       Script 2 –  SQL SERVER Memory

-- Uso de memória no SQL Server

SELECT

       [total_physical_memory_kb] / 1024 AS [Memória Física Total (MB)]

    ,[available_page_file_kb] / 1024 AS [Memória Física Disponível (MB)]

    ,[total_page_file_kb] / 1024 AS [Page File Total  (MB)]

    ,[available_page_file_kb] / 1024 AS [Page File Disponível (MB)]

    ,[kernel_paged_pool_kb] / 1024 AS [Pool do Kernel Paginado (MB)]

    ,[kernel_nonpaged_pool_kb] / 1024 AS [Pool do Kernel Não Paginado (MB)]

    ,[system_memory_state_desc] AS [Descrição do Status de Memória]

FROM

       [master].[sys].[dm_os_sys_memory]

3.       Script 3 – SQL SERVER Disk Usage

--Uso de disco do SQL SERVER

SELECT

       DB_NAME(mf.database_id) AS [Database Name]

       ,mf.physical_name as [Arquivo Físico]

       ,num_of_reads as [Número de Leituras]

       ,num_of_bytes_read as [Número de Bytes lidos]

       ,io_stall_read_ms as [Tempo de Espera para Leitura (Contensão)]

       ,num_of_writes as [Número de Escritas]

       ,num_of_bytes_written as [Número de Bytes Escritos]

       ,io_stall_write_ms as [Tempo de Espera para Escrita (Contensão)]

       ,io_stall as [Tempo de Espera I/O (Contensão)]

       ,size_on_disk_bytes as [Tamanho em Bytes]

FROM

       master.sys.dm_io_virtual_file_stats(NULL, NULL) AS divfs

JOIN master.sys.master_files AS mf

       ON mf.database_id = divfs.database_id AND mf.file_id = divfs.file_id

ORDER BY

       1 DESC

      Script 4 – Top 10 worst querys

--top 10 piores querys por tempo de processamento

SELECT TOP 10

       DB_NAME(qt.dbid) AS [Banco de dados]

       ,DATEDIFF(MI,creation_time,GETDATE()) AS [Idade do Plano de Execução(Minutos)]

       ,last_execution_time AS [Última Execução]

       ,qs.execution_count AS [Número de Execuções]

       ,CAST((qs.total_elapsed_time) / 1000000.0 AS DECIMAL(28,2)) [Tempo Total de Execução(s)]

       ,CAST((qs.total_elapsed_time ) / 1000000.0/ qs.execution_count AS DECIMAL(28, 2)) AS [Tempo Médio de Execução(s)]

       ,CAST((qs.total_worker_time) / 1000000.0 AS DECIMAL(28,2)) AS [Tempo Total de CPU(s)]

       ,CAST(qs.total_worker_time * 100.0 / qs.total_elapsed_time AS DECIMAL(28,2)) AS [% CPU]

       ,CAST((qs.total_elapsed_time - qs.total_worker_time)* 100.0 /qs.total_elapsed_time AS DECIMAL(28, 2)) AS [% Espera]

       ,CAST((qs.total_worker_time) / 1000000.0/ qs.execution_count AS DECIMAL(28, 2)) AS [Tempo Médio de CPU(s)]

       ,CAST((qs.total_physical_reads) / qs.execution_count AS DECIMAL(28, 2)) AS [Média de Leituras Físicas]

       ,CAST((qs.total_logical_reads) / qs.execution_count AS DECIMAL(28, 2))  AS [Média de Leituras Lógicas]

       ,CAST((qs.total_logical_writes) / qs.execution_count AS DECIMAL(28, 2)) AS [Média de Escritas Lógicas]

       ,max_physical_reads

       ,max_logical_reads

       ,max_logical_writes

       , 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 [Batch Statement]

       , qp.query_plan

FROM master.SYS.DM_EXEC_QUERY_STATS qs

       CROSS APPLY master.SYS.DM_EXEC_SQL_TEXT(qs.sql_handle) AS qt

       CROSS APPLY master.SYS.DM_EXEC_QUERY_PLAN(qs.plan_handle) qp

WHERE

       qs.total_elapsed_time > 0

ORDER BY

       [Tempo Total de CPU(s)]

DESC

5.       Script 5 – Database Growth

--Crescimento de Banco de dados

DECLARE

       @endDate datetime,

       @months smallint;

SELECT

       @endDate = GetDate()      

,@months = 6;          

;WITH HIST AS

   (SELECT BS.database_name AS DatabaseName

          ,YEAR(BS.backup_start_date) * 100

           + MONTH(BS.backup_start_date) AS YearMonth

          ,CONVERT(numeric(10, 1), MIN(BF.file_size / 1048576.0)) AS MinSizeMB

          ,CONVERT(numeric(10, 1), MAX(BF.file_size / 1048576.0)) AS MaxSizeMB

          ,CONVERT(numeric(10, 1), AVG(BF.file_size / 1048576.0)) AS AvgSizeMB

    FROM msdb.dbo.backupset as BS

         INNER JOIN

         msdb.dbo.backupfile AS BF

             ON BS.backup_set_id = BF.backup_set_id

    WHERE BF.file_type = 'D'

          AND BS.backup_start_date BETWEEN DATEADD(mm, - @months, @endDate) AND @endDate

    GROUP BY BS.database_name

            ,YEAR(BS.backup_start_date)

            ,MONTH(BS.backup_start_date))

SELECT MAIN.DatabaseName as [Nome do Banco]

      ,MAIN.YearMonth as [Ano - Mês]

      ,MAIN.MinSizeMB as [Menor Tamanho no Período]

      ,MAIN.MaxSizeMB as [Maior Tamanho no Período]

      ,MAIN.AvgSizeMB as [Tamanho Médio no Período]

      ,MAIN.AvgSizeMB 

       - (SELECT TOP 1 SUB.AvgSizeMB

          FROM HIST AS SUB

          WHERE SUB.DatabaseName = MAIN.DatabaseName

                AND SUB.YearMonth < MAIN.YearMonth

          ORDER BY SUB.YearMonth DESC) AS [Crescimento no Período],

       (SELECT SUM(SIZE * 8.0 / 1024) FROM SYS.MASTER_FILES MF WHERE (DB_NAME(MF.database_id) = MAIN.DatabaseName)) AS [Tamanho Atual]

FROM HIST AS MAIN

ORDER BY MAIN.DatabaseName

        ,MAIN.YearMonth

6.       Script 6 – Index Fragmentation

--Fragmentação de índices (Executar com cuidado, pode afetar a produção)

--Deve ser executado no banco que está sendo analisado

SELECT

       DB_NAME(DB_ID()) AS [Nome do Banco de dados],

       dbschemas.[name] +'.'+dbtables.[name] as [Tabela],

       dbindexes.[name] as [Índice],

       indexstats.avg_fragmentation_in_percent as [Percentual de Fragmentação],

       indexstats.page_count as [Page Count],

       'ALTER INDEX '+dbindexes.[name]+' ON '+dbschemas.[name] +'.'+dbtables.[name]+' REBUILD;' as [Script Para Rebuild do Índice]

FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS indexstats

       INNER JOIN sys.tables dbtables on dbtables.[object_id] = indexstats.[object_id]

       INNER JOIN sys.schemas dbschemas on dbtables.[schema_id] = dbschemas.[schema_id]

       INNER JOIN sys.indexes AS dbindexes ON dbindexes.[object_id] = indexstats.[object_id] AND indexstats.index_id = dbindexes.index_id

ORDER BY

       indexstats.avg_fragmentation_in_percent desc

Check out my new book about R Language http://www.amazon.com/dp/B00SX6WA06