Checkup


Como consultor, muitas vezes me deparo com ambientes SQL que não fui eu que fiz as configurações, cada ambiente tem sua peculiaridade.

Este script serve para te ajudar a fazer uma checagem estrutural do SQL, entender o que existe neste ambiente e quais os riscos existentes.

Ele não irá modificar nada existente no seu ambiente, no máximo criará tabelas temporárias para coleta de resultado.

Ele não é um script do tipo cole e execute, coloquei alguns raisererror para impedir que você execute tudo de uma vez (estou montando outro que poderemos fazer isso).

Execute o script por partes, vá coletando os resultados para uma planilha excel para analisá-los depois…

***Lembrete: este é um script que você está copiando da internet, execute-o por sua conta e risco. Lembre-se:  na dúvida, não confie em ninguém.

Novas versões serão modificadas nesta página e atualizado o link do skydrive e no meu NAS.

05/03/15 10:32 – Atualizada a parte do código que lista os jobs e os agendamentos


/*
Versao: 1.07
Data: 20160131
Por: Ricardo Leka
Site: http://leka.com.br
email: ricardo@leka.com.br
twitter:@bigleka

Este script é para SQL 2005/2008/R2/2012/2014

*/

/*
Esse script deve ser executado em pedaços,
você executar apenas a parte que interessa e vai copiando o resultado para o excel ou bloco de notas
*/
raiserror ('Esse script é para ser executado por partes, você está executando tudo de uma vez,,,',20,-1) with log
GO

/*
Vamos ver se você é importante o suficiente para rodar essas querys
o nível necessário é sysadmin
*/
if (IS_SRVROLEMEMBER('sysadmin') = 0)
raiserror ('Seu usuário não é importante o suficiente,,,',15,-1)
else if (IS_SRVROLEMEMBER('sysadmin') IS NULL)
raiserror ('A server role que seu usuário está não é valida,,,',15,-1)
else if (IS_SRVROLEMEMBER('sysadmin') = 1)
print 'Legal, podemos continuar,,,'
GO

/*
Versao do SQL
Traz informacoes interessante sobre o nome da maquina, se tem instancia, versao de produto,
SP, collation da instancia, se esta ou nao em cluster
*/

Print 'Versao de SQL'

SELECT CAST(SERVERPROPERTY('MachineName') AS VARCHAR(30)) AS MachineName ,
 CAST(SERVERPROPERTY('InstanceName') AS VARCHAR(30)) AS Instance ,
 CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR(30)) AS ProductVersion ,
 CAST(SERVERPROPERTY('ProductLevel') AS VARCHAR(30)) AS ProductLevel ,
 CAST(SERVERPROPERTY('Edition') AS VARCHAR(30)) AS Edition ,
 ( CASE SERVERPROPERTY('EngineEdition')
 WHEN 1 THEN 'Personal or Desktop'
 WHEN 2 THEN 'Standard'
 WHEN 3 THEN 'Enterprise'
 END ) AS EngineType ,
 CAST(SERVERPROPERTY('LicenseType') AS VARCHAR(30)) AS LicenseType ,
 SERVERPROPERTY('NumLicenses') AS #Licenses ,
 ( CASE SERVERPROPERTY('IsIntegratedSecurityOnly')
 WHEN 0 THEN 'Mista'
 WHEN 1 THEN 'Integrada'
 END ) AS [IsIntegratedSecurityOnly] ,
 SERVERPROPERTY('Collation') AS Collation ,
 ( CASE SERVERPROPERTY('IsClustered')
 WHEN 0 THEN 'Nao'
 WHEN 1 THEN 'Sim'
 END ) AS Cluster;

SELECT @@VERSION AS [SQL Server Details];

exec xp_msver

/*
Backup FULL

Uma das informacoes mais importantes, tem backup?
*/

Print 'Backup FULL'

SELECT d.name ,
 MAX(b.backup_finish_date) AS [ultima data do backup]
FROM master.sys.databases d
 LEFT OUTER JOIN msdb.dbo.backupset b ON d.name = b.database_name
 AND b.type = 'D'
WHERE d.database_id NOT IN ( 2, 3 )
GROUP BY d.name
ORDER BY 2 DESC

/*
Backup LOG

Outra informacao muito importante,,,,
*/

Print 'Backup Log'

SELECT d.name ,
 MAX(b.backup_finish_date) AS [ultima data do backup]
FROM master.sys.databases d
 LEFT OUTER JOIN msdb.dbo.backupset b ON d.name = b.database_name
 AND b.type = 'L'
WHERE d.database_id NOT IN ( 2, 3 )
 AND d.recovery_model <> 3
GROUP BY d.name
ORDER BY 2 DESC

/*
Localizacao dos backups

Se houve backup, pra onde ele foi?
*/

Print 'Localizacao dos Backups'

SELECT TOP 100
 a.database_name ,
 b.physical_device_name
FROM msdb.dbo.backupmediafamily b
 INNER JOIN msdb.dbo.backupset a ON b.media_set_id = a.media_set_id
where a.type = 'D'
ORDER BY a.backup_start_date desc

SELECT TOP 100
 a.database_name ,
 b.physical_device_name
FROM msdb.dbo.backupmediafamily b
 INNER JOIN msdb.dbo.backupset a ON b.media_set_id = a.media_set_id
where a.type = 'L'
ORDER BY a.backup_start_date desc

/*
A quanto tempo existe historico de backup?

No SQL 2k, havia um grande problema de performance quando o historico ficava muito grande.
*/

Print 'Backup History'

SELECT TOP 1
 backup_start_date
FROM msdb.dbo.backupset WITH ( NOLOCK )
ORDER BY backup_set_id ASC

/*
Informacoes sobre o SO
(SQL Server 2008 R2 SP1 ou superior)
para saber mais sobre Windows release (http://msdn.microsoft.com/en-us/library/ms724832(VS.85).aspx)
para saber mais sobre SKU (http://msdn.microsoft.com/en-us/library/ms724358.aspx)
*/

SELECT windows_release ,
 windows_service_pack_level ,
 windows_sku ,
 os_language_version
FROM sys.dm_os_windows_info WITH ( NOLOCK )
OPTION ( RECOMPILE );

/*
Informacoes sobre o Servico do SQL Server
o principal ponto ver o service_account
(SQL Server 2008 R2 SP1 ou superior)
*/

SELECT servicename ,
 startup_type_desc ,
 status_desc ,
 last_startup_time ,
 service_account ,
 is_clustered ,
 cluster_nodename
FROM sys.dm_server_services WITH ( NOLOCK )
OPTION ( RECOMPILE );

/*
Informacao sobre o fabricante do hardware
se o errorlog nao foi reciclado
*/

EXEC xp_readerrorlog 0, 1, "Manufacturer";

/*
Informacao sobre o processador
*/

EXEC xp_instance_regread 'HKEY_LOCAL_MACHINE',
 'HARDWARE\DESCRIPTION\System\CentralProcessor\0', 'ProcessorNameString';

/*
quantos processadores existem, quanto de memoria tem no servidor
*/
SELECT cpu_count ,
 affinity_type_desc ,
 ( physical_memory_kb ) / 1024 AS [memory in MB]
FROM sys.dm_os_sys_info

/*
quais processadores estao disponiveis para o SQL?
*/

SELECT cpu_id ,
 status ,
 is_online
FROM sys.dm_os_schedulers
WHERE status = 'VISIBLE ONLINE'

/*
Informacoes no registro sobre o SQL
*/
SELECT registry_key ,
 value_name ,
 value_data
FROM sys.dm_server_registry WITH ( NOLOCK )
OPTION ( RECOMPILE );

/*
permicoes do usuario que inicia o SQL
*/
sp_configure 'show advanced options', 1
reconfigure

sp_configure 'xp_cmdshell', 1
reconfigure

xp_cmdshell "whoami /priv"

sp_configure 'xp_cmdshell', 0
reconfigure

sp_configure 'show advanced options', 0
reconfigure

/*
Se o SQL estiver em cluster qual o nome do host atual?
*/
SELECT NodeName
FROM sys.dm_os_cluster_nodes WITH ( NOLOCK )
OPTION ( RECOMPILE );

/*
Pega algumas informacoes interessantes como
nome, owner, data de criação, dbid, Modo de Compatiblidade das bases, recovery model, versao e status

ATENCAO: Lembre-se que a ideia do script eh de apenas ver o que tem,
caso encontre alguma base com status diferente de online ele nao faz nada
e na teoria ainda nao eh a hora de voce fazer. Apenas reporte.
*/

Print 'Nome, owner, dbcreate, dbid, cmptlvl, recovery, version, status'

SELECT name AS [NAME] ,
 SUSER_SNAME(owner_sid) AS [Owner] ,
 CONVERT(NVARCHAR(11), create_date) AS [Data Criacao] ,
 database_id AS [DBID] ,
 compatibility_level ,
 DATABASEPROPERTYEX(name, 'recovery') AS [Recovery Model] ,
 page_verify_option_desc AS [Page Verify] ,
 ( CASE is_auto_create_stats_on
 WHEN 0 THEN 'Nao'
 WHEN 1 THEN 'Sim'
 END ) AS [is_auto_create_stats_on] ,
 ( CASE is_auto_update_stats_on
 WHEN 0 THEN 'Nao'
 WHEN 1 THEN 'Sim'
 END ) AS [is_auto_update_stats_on] ,
 ( CASE is_auto_update_stats_async_on
 WHEN 0 THEN 'Nao'
 WHEN 1 THEN 'Sim'
 END ) AS [is_auto_update_stats_async_on] ,
 ( CASE is_parameterization_forced
 WHEN 0 THEN 'Nao'
 WHEN 1 THEN 'Sim'
 END ) AS [is_parameterization_forced] ,
 snapshot_isolation_state_desc ,
 ( CASE is_read_committed_snapshot_on
 WHEN 0 THEN 'Nao'
 WHEN 1 THEN 'Sim'
 END ) AS [is_read_committed_snapshot_on] ,
 ( CASE is_auto_close_on
 WHEN 0 THEN 'Nao'
 WHEN 1 THEN 'Sim'
 END ) AS [is_auto_close_on] ,
 ( CASE is_auto_shrink_on
 WHEN 0 THEN 'Nao'
 WHEN 1 THEN 'Sim'
 END ) AS [is_auto_shrink_on] ,
 DATABASEPROPERTYEX(name, 'status') AS [Status]
FROM master.sys.databases

/*
Quais traces estão habilitados no SQL?
*/

Print 'Quais Traces estão habilitados'

DBCC TRACESTATUS

/*
Quando o SQL Server foi instalado?
*/

Print 'Quando o SQL Server foi instalado'

SELECT @@SERVERNAME AS [Server Name] ,
 createdate AS [Instalacao do SQL Server]
FROM sys.syslogins
WHERE [sid] = 0x010100000000000512000000;

/*
Quando foi que o DBCC CHECKDB rodou pela última vez?

Isso eh tao bom quando retorna alguma coisa,,,
*/

Print 'DBCC CHECKDB'

CREATE TABLE #temp (
 ParentObject VARCHAR(255)
 , [Object] VARCHAR(255)
 , Field VARCHAR(255)
 , [Value] VARCHAR(255)
 )

CREATE TABLE #DBCCResults (
 ServerName VARCHAR(255)
 , DBName VARCHAR(255)
 , LastCleanDBCCDate DATETIME
 )

EXEC master.dbo.sp_MSforeachdb
 @command1 = 'USE ? INSERT INTO #temp EXECUTE (''DBCC DBINFO WITH TABLERESULTS'')'
 , @command2 = 'INSERT INTO #DBCCResults SELECT @@SERVERNAME, ''?'', Value FROM #temp WHERE Field = ''dbi_dbccLastKnownGood'''
 , @command3 = 'TRUNCATE TABLE #temp'

 SELECT DISTINCT
 ServerName
 , DBName
 , CASE LastCleanDBCCDate
 WHEN '1900-01-01 00:00:00.000' THEN 'Nunca rodou DBCC CHECKDB'
 ELSE CAST(LastCleanDBCCDate AS VARCHAR) END AS LastCleanDBCCDate
 FROM #DBCCResults
 ORDER BY 3

 DROP TABLE #temp, #DBCCResults;

/*
Existem páginas corrompidas?
*/

Print 'Páginas corrompidas'

SELECT *
FROM msdb..suspect_pages

/*
Essa query é apenas para SQL Server 2008 e SQL Server 2008 R2 quando você tem Mirror configurado
ele vai analisar se houve necessidade de recuperação de página corrompida.
*/

Print 'Recuperaçãp de página usando o mirror'

SELECT *
FROM sys.dm_db_mirroring_auto_page_repair

/*
Quem faz parte do sysadmin ou securityadmin

Quantas outras pessoas podem acessar o SQL e fazer algum tipo de estrago?
*/

SELECT l.name AS [Nome] ,
 ( CASE l.denylogin
 WHEN 0 THEN 'Nao'
 WHEN 1 THEN 'Sim'
 END ) AS [Negado] ,
 ( CASE l.isntname
 WHEN 0 THEN 'Nao'
 WHEN 1 THEN 'Sim'
 END ) AS [nt nome] ,
 ( CASE l.isntgroup
 WHEN 0 THEN 'Nao'
 WHEN 1 THEN 'Sim'
 END ) AS [nt grupo] ,
 ( CASE l.isntuser
 WHEN 0 THEN 'Nao'
 WHEN 1 THEN 'Sim'
 END ) AS [nt user]
FROM master.dbo.syslogins l
WHERE l.sysadmin = 1
 OR l.securityadmin = 1
ORDER BY l.isntgroup ,
 l.isntname ,
 l.isntuser

/*
Users

Quais sao os usuarios de Windows e os de SQL
Preste atencao quais sao os usuarios, pode ter mais do que precisa,,,,
*/

Print 'Usuarios windows e SQL'

SELECT Name AS [Name] ,
 ( CASE isntname
 WHEN 0 THEN 'SQL Server Standard'
 WHEN 1 THEN 'Windows Authentication'
 END ) AS [Type]
FROM master.sys.syslogins

/*
Usuarios com senha em branco ou senha com o mesmo nome
*/
SELECT SERVERPROPERTY('machinename') AS 'Server Name' ,
 ISNULL(SERVERPROPERTY('instancename'), SERVERPROPERTY('machinename')) AS 'Instance Name' ,
 name AS 'Login com senha em branco'
FROM master.sys.sql_logins
WHERE pwdcompare('', password_hash) = 1
ORDER BY name
OPTION ( MAXDOP 1 )

SELECT SERVERPROPERTY('machinename') AS 'Server Name' ,
 ISNULL(SERVERPROPERTY('instancename'), SERVERPROPERTY('machinename')) AS 'Instance Name' ,
 name AS 'Login com senha igual ao nome'
FROM master.sys.sql_logins
WHERE pwdcompare(name, password_hash) = 1
ORDER BY name
OPTION ( MAXDOP 1 )
/*
usuários sem base associada
*/

CREATE TABLE #UsrSemBase (
LoginName nvarchar(max),
DBname nvarchar(max),
Username nvarchar(max),
AliasName nvarchar(max)
)

INSERT INTO #UsrSemBase
EXEC master..sp_msloginmappings

SELECT *
FROM #UsrSemBase
where DBname is null
ORDER BY dbname, username

DROP TABLE #UsrSemBase
/*
Usuarios x Bases

Esse aqui nao deve demorar muito,,,
apenas gera um relacionamento dos usuarios que estao nas bases
compare com a quantidade de usuarios que voce pegou na lista acima
*/

/*
Versão para SQL Server 2005/2008/2008R2/2012
*/
Print 'Usuarios por Bases'

create table #UsrDataMapping(
[database_name] [sysname] NULL,
[name] [sysname] NULL,
[schema] [sysname] NULL
)
EXEC sp_MSforeachdb 'insert into #UsrDataMapping SELECT ''?'' as DBNAME,
--u.name AS [Name],
SUSER_SNAME(sid) AS [Name],
ISNULL(u.default_schema_name,N'''') AS [DefaultSchema]
FROM
[?].sys.database_principals AS u
LEFT OUTER JOIN [?].sys.database_permissions AS dp ON dp.grantee_principal_id = u.principal_id
WHERE
(u.type in (''U'', ''S'', ''G'', ''C'', ''K''))
and dp.state = ''G''
'
select * from #UsrDataMapping order by database_name
drop table #UsrDataMapping

/*
Versão para 2000
*/

create table #UsrDataMapping(
[database_name] [sysname] NULL,
[name] [sysname] NULL
)
EXEC sp_MSforeachdb 'insert into #UsrDataMapping SELECT ''?'' as DBNAME,
--u.name AS [Name],
SUSER_SNAME(u.sid) AS [Name]
FROM
[?].dbo.sysusers AS u
LEFT OUTER JOIN [?].dbo.syspermissions AS dp ON dp.grantee = u.u_id
'
select distinct database_name, name from #UsrDataMapping
where name is not null
order by database_name
drop table #UsrDataMapping
/*
Membros de roles
*/

create table ##RolesMembers
(
[Database] sysname,
RoleName sysname,
MemberName sysname
)

exec dbo.sp_MSforeachdb 'insert into ##RolesMembers select ''[?]'', ''['' + r.name + '']'', ''['' + m.name + '']''
from [?].sys.database_role_members rm
inner join [?].sys.database_principals r on rm.role_principal_id = r.principal_id
inner join [?].sys.database_principals m on rm.member_principal_id = m.principal_id
-- where r.name = ''db_owner'' and m.name != ''dbo'' -- you may want to uncomment this line';

select * from ##RolesMembers
order by [Database], [RoleName]

drop table ##RolesMembers

/*
usuários órfãos
*/

create table ##OrphanedUsers
(
[Database] sysname,
Username sysname
)


exec dbo.sp_MSforeachdb 'insert into ##OrphanedUsers select ''[?]'', UserName = name
from [?].sys.sysusers
where issqluser = 1
and (sid is not null and sid <> 0x0)
and (len(sid) <= 16)
and suser_sname(sid) is null;'


select * from ##OrphanedUsers with (nolock)
drop table ##OrphanedUsers
/*
Objetos em bases de sistema

Coisas que nao deveriam estar neste lugar
*/

Print 'Coisas no lugar errado'

SELECT *
FROM master.sys.tables
WHERE name NOT IN ( 'spt_fallback_db', 'spt_fallback_dev',
 'spt_fallback_usg', 'spt_monitor', 'spt_values',
 'MSreplication_options' )
 AND is_ms_shipped = 0
SELECT *
FROM master.sys.procedures
WHERE name NOT IN ( 'sp_MSrepl_startup', 'sp_MScleanupmergepublisher' )
 AND is_ms_shipped = 0
SELECT *
FROM model.sys.tables
SELECT *
FROM model.sys.procedures

/*
Start up Stored procedure

Achou alguma procedure aqui? estranho,,, veja o que ela faz,,,
pode ser o mau esperando um boot,,,
*/

SELECT *
FROM master.INFORMATION_SCHEMA.ROUTINES
WHERE OBJECTPROPERTY(OBJECT_ID(ROUTINE_NAME), 'ExecIsStartup') = 1

/*
Quem mais existe em server-level
*/

SELECT *
FROM sysservers

/*
Usuarios com acesso ao linkedserver
*/

SELECT S.srvname ,
 U.rmtloginame ,
 SUSER_SNAME(U.loginsid) AS [local_login]
FROM sysservers AS S
 INNER JOIN sys.sysoledbusers AS U ON S.srvid = U.rmtsrvid

/*
Agora mais informacoes sobre as bases

classico,,, nada fora do comum,,,
*/

SELECT *
FROM sysdatabases

/*
Onde estao os arquivos

onde eles estao? quantos sao?
*/

CREATE TABLE #ArquivosDB
 (
 [Banco] [sysname] NOT NULL ,
 [file_guid] [smallint] NULL ,
 [Local] [nvarchar](260) NOT NULL,
 )
EXEC dbo.sp_MSforeachdb 'INSERT INTO #ArquivosDB SELECT ''[?]'' AS database_name, groupid, filename FROM [?].dbo.sysfiles'
SELECT Banco ,
 ( CASE file_guid
 WHEN 0 THEN 'Log'
 ELSE 'Data'
 END ) ,
 Local
FROM #ArquivosDB
ORDER BY Banco, file_guid DESC
DROP TABLE #ArquivosDB

/*
TempDB

voce deve ter visto a quantidade de datafiles para o TempDB na query acima
mas sera que eh um problema?
query para contencao de TempDB
(http://sqlcat.com/sqlcat/b/technicalnotes/archive/2011/01/25/table-valued-functions-and-tempdb-contention.aspx)
*/

SELECT r.session_id ,
 r.status ,
 r.command ,
 r.database_id ,
 r.blocking_session_id ,
 r.wait_type ,
 AVG(r.wait_time) AS [WaitTime] ,
 r.wait_resource
FROM sys.dm_exec_requests AS r
 INNER JOIN sys.dm_exec_sessions AS s ON ( r.session_id = s.session_id )
WHERE r.wait_type IS NOT NULL
 AND s.is_user_process = 1
GROUP BY GROUPING SETS(( r.session_id ,
 r.status ,
 r.command ,
 r.database_id ,
 r.blocking_session_id ,
 r.wait_type ,
 r.wait_time ,
 r.wait_resource
 ), ( ))

/*
Triggers

isso pode demorar um pouco,,,
e mais ainda pra ler,,,,
*/

Print 'Triggers'

EXEC dbo.sp_MSforeachdb 'SELECT ''[?]'' AS Banco, o.name AS Tabela, t.* FROM [?].sys.triggers t INNER JOIN [?].sys.objects o ON t.parent_id = o.object_id'

/*
Alguns Waits

Nao zere o contador, apenas veja o que tem,,,
*/

Print 'Alguns Waits'

WITH Waits AS
 (SELECT
 wait_type,
 wait_time_ms / 1000.0 AS WaitS,
 (wait_time_ms - signal_wait_time_ms) / 1000.0 AS ResourceS,
 signal_wait_time_ms / 1000.0 AS SignalS,
 waiting_tasks_count AS WaitCount,
 100.0 * wait_time_ms / SUM (wait_time_ms) OVER() AS Percentage,
 ROW_NUMBER() OVER(ORDER BY wait_time_ms DESC) AS RowNum
 FROM sys.dm_os_wait_stats
 WHERE wait_type NOT IN (
 'CLR_SEMAPHORE', 'LAZYWRITER_SLEEP', 'RESOURCE_QUEUE', 'SLEEP_TASK',
 'SLEEP_SYSTEMTASK', 'SQLTRACE_BUFFER_FLUSH', 'WAITFOR', 'LOGMGR_QUEUE',
 'CHECKPOINT_QUEUE', 'REQUEST_FOR_DEADLOCK_SEARCH', 'XE_TIMER_EVENT', 'BROKER_TO_FLUSH',
 'BROKER_TASK_STOP', 'CLR_MANUAL_EVENT', 'CLR_AUTO_EVENT', 'DISPATCHER_QUEUE_SEMAPHORE',
 'FT_IFTS_SCHEDULER_IDLE_WAIT', 'XE_DISPATCHER_WAIT', 'XE_DISPATCHER_JOIN', 'BROKER_EVENTHANDLER',
 'TRACEWRITE', 'FT_IFTSHC_MUTEX', 'SQLTRACE_INCREMENTAL_FLUSH_SLEEP',
 'BROKER_RECEIVE_WAITFOR', 'ONDEMAND_TASK_QUEUE', 'DBMIRROR_EVENTS_QUEUE',
 'DBMIRRORING_CMD', 'BROKER_TRANSMITTER', 'SQLTRACE_WAIT_ENTRIES',
 'SLEEP_BPOOL_FLUSH', 'SQLTRACE_LOCK')
 )
SELECT
 W1.wait_type AS WaitType,
 CAST (W1.WaitS AS DECIMAL(14, 2)) AS Wait_S,
 CAST (W1.ResourceS AS DECIMAL(14, 2)) AS Resource_S,
 CAST (W1.SignalS AS DECIMAL(14, 2)) AS Signal_S,
 W1.WaitCount AS WaitCount,
 CAST (W1.Percentage AS DECIMAL(4, 2)) AS Percentage,
 CAST ((W1.WaitS / W1.WaitCount) AS DECIMAL (14, 4)) AS AvgWait_S,
 CAST ((W1.ResourceS / W1.WaitCount) AS DECIMAL (14, 4)) AS AvgRes_S,
 CAST ((W1.SignalS / W1.WaitCount) AS DECIMAL (14, 4)) AS AvgSig_S
FROM Waits AS W1
 INNER JOIN Waits AS W2 ON W2.RowNum &lt;= W1.RowNum
GROUP BY W1.RowNum, W1.wait_type, W1.WaitS, W1.ResourceS, W1.SignalS, W1.WaitCount, W1.Percentage
HAVING SUM (W2.Percentage) - W1.Percentage &lt; 95;
GO

/*
Querys de alto custo
*/

SELECT TOP (1)
 MAX(query) AS sample_query,
 SUM(execution_count) AS cnt,
 SUM(total_worker_time) AS cpu,
 SUM(total_physical_reads) AS reads,
 SUM(total_logical_reads) AS logical_reads,
 SUM(total_elapsed_time) AS duration
FROM (SELECT
 QS.*,
 --sq.query_plan,
 SUBSTRING(ST.text, (QS.statement_start_offset/2) + 1,
 ((CASE statement_end_offset
 WHEN -1 THEN DATALENGTH(ST.text)
 ELSE QS.statement_end_offset END
 - QS.statement_start_offset)/2) + 1
 ) AS query
 FROM sys.dm_exec_query_stats AS QS
 CROSS APPLY sys.dm_exec_sql_text(QS.sql_handle) AS ST
 CROSS APPLY sys.dm_exec_plan_attributes(QS.plan_handle) AS PA
 --cross apply sys.dm_exec_query_plan (QS.plan_handle) sq
 WHERE PA.attribute = 'dbid'
 AND PA.value = DB_ID()) AS D --Alterar aqui para verificar alguma base específica
GROUP BY query
ORDER BY duration DESC;

/*
Frangmentacao de indices

se voce achou que o de Triggers demorou,,, imagina esse,,,,

esse tem que rodar banco a banco
*/

Print 'Fragmentacao de indices'

SELECT db.name AS databaseName ,
 ps.OBJECT_ID AS objectID ,
 ps.index_id AS indexID ,
 ps.partition_number AS partitionNumber ,
 ps.avg_fragmentation_in_percent AS fragmentation ,
 ps.page_count
FROM sys.databases db
 INNER JOIN sys.dm_db_index_physical_stats(NULL, NULL, NULL, NULL,
 N'Limited') ps ON db.database_id = ps.database_id
WHERE ps.index_id &gt; 0
 AND ps.page_count &gt; 100
 AND ps.avg_fragmentation_in_percent &gt; 30
OPTION ( MAXDOP 1 );

/*
Índices hipotéticos
*/

create table #hipotetico(
banco sysname NULL,
table_name sysname NULL,
index_name sysname NULL
)
insert into #hipotetico
EXEC dbo.sp_MSforeachdb '
select ''[?]'' AS banco, object_name(object_id) AS tabela, name from [?].sys.indexes where is_hypothetical = 1
'
select * from #hipotetico

drop table #hipotetico

/*

Índices faltantes
*/

SELECT
 migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) * (migs.user_seeks + migs.user_scans) AS improvement_measure,
 'CREATE INDEX [missing_index_' + CONVERT (varchar, mig.index_group_handle) + '_' + CONVERT (varchar, mid.index_handle)
 + '_' + LEFT (PARSENAME(mid.statement, 1), 32) + ']'
 + ' ON ' + mid.statement
 + ' (' + ISNULL (mid.equality_columns,'')
 + CASE WHEN mid.equality_columns IS NOT NULL AND mid.inequality_columns IS NOT NULL THEN ',' ELSE '' END
 + ISNULL (mid.inequality_columns, '')
 + ')'
 + ISNULL (' INCLUDE (' + mid.included_columns + ')', '') AS create_index_statement,
 migs.*, mid.database_id, mid.[object_id]
 --into MS.dbo.Missing_index_BDCORP
FROM sys.dm_db_missing_index_groups mig
INNER JOIN sys.dm_db_missing_index_group_stats migs ON migs.group_handle = mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details mid ON mig.index_handle = mid.index_handle
WHERE migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) * (migs.user_seeks + migs.user_scans) &gt; 10
ORDER BY migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans) DESC

/*
Índices sem uso
*/

SELECT
o.name
, indexname=i.name
, i.index_id
, reads=user_seeks + user_scans + user_lookups
, writes = user_updates
, rows = (SELECT SUM(p.rows) FROM sys.partitions p WHERE p.index_id = s.index_id AND s.object_id = p.object_id)
, CASE
 WHEN s.user_updates &lt; 1 THEN 100
 ELSE 1.00 * (s.user_seeks + s.user_scans + s.user_lookups) / s.user_updates
 END AS reads_per_write
, 'DROP INDEX ' + QUOTENAME(i.name)
+ ' ON ' + QUOTENAME(c.name) + '.' + QUOTENAME(OBJECT_NAME(s.object_id)) as 'drop statement'
 into MS.dbo.DROP_index_BDCORP
FROM sys.dm_db_index_usage_stats s
INNER JOIN sys.indexes i ON i.index_id = s.index_id AND s.object_id = i.object_id
INNER JOIN sys.objects o on s.object_id = o.object_id
INNER JOIN sys.schemas c on o.schema_id = c.schema_id
WHERE OBJECTPROPERTY(s.object_id,'IsUserTable') = 1
AND s.database_id = DB_ID()
AND i.type_desc = 'nonclustered'
AND i.is_primary_key = 0
AND i.is_unique_constraint = 0
AND (SELECT SUM(p.rows) FROM sys.partitions p WHERE p.index_id = s.index_id AND s.object_id = p.object_id) &gt; 10000
ORDER BY reads

/*
Essa query mostra algumas informações sobre a fragmentação dos índices e estatísticas
mas o mais interessante é a coluna lastStatsUpdate
*/

create table #estatisticas
(
 Banco sysname,
 table_schema sysname,
 table_name sysname,
 index_name sysname,
 table_id bigint,
 index_id tinyint ,
 groupid tinyint,
 modifiedRows bigint,
 rowcnt bigint,
 ModifiedPct decimal(18,8),
 lastStatsUpdate datetime,
 Processed varchar(5)
)
EXEC dbo.sp_MSforeachdb 'INSERT INTO #estatisticas
SELECT ''[?]'',
schemas.name AS table_schema ,
 tbls.name AS table_name ,
 i.name AS index_name ,
 i.id AS table_id ,
 i.indid AS index_id ,
 i.groupid ,
 i.rowmodctr AS modifiedRows ,
 ( SELECT MAX(rowcnt)
 FROM sysindexes i2
 WHERE i.id = i2.id
 AND i2.indid &lt; 2
 ) AS rowcnt ,
 CONVERT(DECIMAL(18, 8), CONVERT(DECIMAL(18, 8), i.rowmodctr)
 / CONVERT(DECIMAL(18, 8), ( SELECT MAX(rowcnt)
 FROM sysindexes i2
 WHERE i.id = i2.id
 AND i2.indid &lt; 2
 ))) AS ModifiedPct ,
 STATS_DATE(i.id, i.indid) AS lastStatsUpdate ,
 ''False'' AS Processed

FROM [?].sys.sysindexes i
 INNER JOIN [?].sys.sysobjects tbls ON i.id = tbls.id
 INNER JOIN [?].sys.sysusers schemas ON tbls.uid = schemas.uid
 INNER JOIN [?].information_schema.tables tl ON tbls.name = tl.table_name
 AND schemas.name = tl.table_schema
 AND tl.table_type = ''BASE TABLE''
WHERE 0 &lt; i.indid
 AND i.indid &lt; 255
 AND table_schema &lt;&gt; ''sys''
 AND i.rowmodctr &lt;&gt; 0
 AND ( SELECT MAX(rowcnt)
 FROM [?].sys.sysindexes i2
 WHERE i.id = i2.id
 AND i2.indid &lt; 2
 ) &gt; 0

'
SELECT *
FROM #estatisticas
order by lastStatsUpdate desc

DROP TABLE #estatisticas

/*
Configurações da instância

Isso mostra como esta configurado hoje, e nao como estava configurado...
*/

EXEC dbo.sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO
EXEC dbo.sp_configure

/*
Extended Stored Procedures

procure por qualquer coisa fora do padrao...
*/

EXEC sp_helpextendedproc;
GO

/*
Email

O SQL envia email?
*/

/*
Pule para a próxima linha,,,
esse é apenas um teste para saber se vc está executando todo o script ou está indo por partes
*/
raiserror ('Eu já escrevi que esse script é para ser executado por partes,,,',20,-1) with log
GO

Print 'Email'

EXEC msdb.dbo.sp_send_dbmail
 @recipients = 'seuemail@aqui.com.br',
 @body = @@SERVERNAME,
 @subject = 'Testando SQL Server Database Mail - veja no corpo o nome do servidor';
GO

/*
Jobs

Simples,,, quais sao, a quem pertence e se estao ativos,,,
NAO tente corrigir-los agora,,, entenda o que eles fazem ou deveriam fazer,,,
*/

Print 'Jobs'

SELECT Name AS [Name] ,
 SUSER_SNAME(owner_sid) AS [Owner] ,
 ( CASE enabled
 WHEN 0 THEN 'Nao'
 WHEN 1 THEN 'Sim'
 END ) AS [Enable] ,
 description AS [Description]
FROM msdb.dbo.sysjobs_view
ORDER BY name

/*
Informação detalhada sobre os Jobs

*/

USE msdb
GO

SELECT /*S.job_id,*/ S.job_name AS [Nome do Job]
	,S.is_job_enabled AS [Job]
	,S.is_schedule_enabled AS [Agenda]
	,SUSER_SNAME(S.job_owner) AS [Owner]
	,S.schedule_name
	,S.Description AS [Descriçao]
	,avg(((h.run_duration / 1000000) * 86400) + (((h.run_duration - ((h.run_duration / 1000000) * 1000000)) / 10000) * 3600) + (((h.run_duration - ((h.run_duration / 10000) * 10000)) / 100) * 60) + (h.run_duration - (h.run_duration / 100) * 100)) AS [MédiaDeDuraçao(s)]
--    ,number_of_runs = count(1)
FROM (
	SELECT SJ.job_id
		,sj.owner_sid AS job_owner
		,SJ.NAME AS job_name
		,SJ.enabled AS is_job_enabled
		,SS.enabled AS is_schedule_enabled
		,SS.NAME AS schedule_name
		,CASE freq_type
			WHEN 1
				THEN 'Ocorre em ' + STUFF(RIGHT(active_start_date, 4), 3, 0, '/') + '/' + LEFT(active_start_date, 4) + ' as ' + REPLACE(RIGHT(CONVERT(VARCHAR(30), CAST(convert(VARCHAR(8), STUFF(STUFF(RIGHT('000000' + CAST(active_start_time AS VARCHAR(10)), 6), 3, 0, ':'), 6, 0, ':'), 8) AS DATETIME) /* hh:mm:ss 24H */, 9), 14), ':000', ' ') /* HH:mm:ss:000AM/PM then replace the :000 with space.*/
			WHEN 4
				THEN 'Ocorre a cada ' + CAST(freq_interval AS VARCHAR(10)) + ' dia(s) ' + CASE freq_subday_type
						WHEN 1
							THEN 'a(s) ' + LTRIM(REPLACE(RIGHT(CONVERT(VARCHAR(30), CAST(convert(VARCHAR(8), STUFF(STUFF(RIGHT('000000' + CAST(active_start_time AS VARCHAR(10)), 6), 3, 0, ':'), 6, 0, ':'), 8) AS DATETIME), 9), 14), ':000', ' '))
						WHEN 2
							THEN 'a cada ' + CAST(freq_subday_interval AS VARCHAR(10)) + ' segundo(s)'
						WHEN 4
							THEN 'a cada ' + CAST(freq_subday_interval AS VARCHAR(10)) + ' minuto(s)'
						WHEN 8
							THEN 'a cada ' + CAST(freq_subday_interval AS VARCHAR(10)) + ' hora(s)'
						ELSE ''
						END + CASE
						WHEN freq_subday_type IN (
								2
								,4
								,8
								) /* repeat seconds/mins/hours */
							THEN ' entre ' + LTRIM(REPLACE(RIGHT(CONVERT(VARCHAR(30), CAST(convert(VARCHAR(8), STUFF(STUFF(RIGHT('000000' + CAST(active_start_time AS VARCHAR(10)), 6), 3, 0, ':'), 6, 0, ':'), 8) AS DATETIME), 9), 14), ':000', ' ')) + ' e ' + LTRIM(REPLACE(RIGHT(CONVERT(VARCHAR(30), CAST(convert(VARCHAR(8), STUFF(STUFF(RIGHT('000000' + CAST(active_end_time AS VARCHAR(10)), 6), 3, 0, ':'), 6, 0, ':'), 8) AS DATETIME), 9), 14), ':000', ' '))
						ELSE ''
						END
			WHEN 8
				THEN 'Ocorre a cada ' + CAST(freq_recurrence_factor AS VARCHAR(10)) + ' semana(s) em ' + REPLACE(CASE
							WHEN freq_interval &amp; 1 = 1
								THEN 'Domingo, '
							ELSE ''
							END + CASE
							WHEN freq_interval &amp; 2 = 2
								THEN 'Segunda, '
							ELSE ''
							END + CASE
							WHEN freq_interval &amp; 4 = 4
								THEN 'Terça, '
							ELSE ''
							END + CASE
							WHEN freq_interval &amp; 8 = 8
								THEN 'Quarta, '
							ELSE ''
							END + CASE
							WHEN freq_interval &amp; 16 = 16
								THEN 'Quinta, '
							ELSE ''
							END + CASE
							WHEN freq_interval &amp; 32 = 32
								THEN 'Sexta, '
							ELSE ''
							END + CASE
							WHEN freq_interval &amp; 64 = 64
								THEN 'Sabado, '
							ELSE ''
							END + '|', ', |', ' ') /* get rid of trailing comma */
					+ CASE freq_subday_type
						WHEN 1
							THEN 'a(s) ' + LTRIM(REPLACE(RIGHT(CONVERT(VARCHAR(30), CAST(convert(VARCHAR(8), STUFF(STUFF(RIGHT('000000' + CAST(active_start_time AS VARCHAR(10)), 6), 3, 0, ':'), 6, 0, ':'), 8) AS DATETIME), 9), 14), ':000', ' '))
						WHEN 2
							THEN 'a cada ' + CAST(freq_subday_interval AS VARCHAR(10)) + ' segundo(s)'
						WHEN 4
							THEN 'a cada ' + CAST(freq_subday_interval AS VARCHAR(10)) + ' minuto(s)'
						WHEN 8
							THEN 'a cada ' + CAST(freq_subday_interval AS VARCHAR(10)) + ' hora(s)'
						ELSE ''
						END + CASE
						WHEN freq_subday_type IN (
								2
								,4
								,8
								) /* repeat seconds/mins/hours */
							THEN ' entre ' + LTRIM(REPLACE(RIGHT(CONVERT(VARCHAR(30), CAST(convert(VARCHAR(8), STUFF(STUFF(RIGHT('000000' + CAST(active_start_time AS VARCHAR(10)), 6), 3, 0, ':'), 6, 0, ':'), 8) AS DATETIME), 9), 14), ':000', ' ')) + ' e ' + LTRIM(REPLACE(RIGHT(CONVERT(VARCHAR(30), CAST(convert(VARCHAR(8), STUFF(STUFF(RIGHT('000000' + CAST(active_end_time AS VARCHAR(10)), 6), 3, 0, ':'), 6, 0, ':'), 8) AS DATETIME), 9), 14), ':000', ' '))
						ELSE ''
						END
			WHEN 16
				THEN 'Ocorre a cada ' + CAST(freq_recurrence_factor AS VARCHAR(10)) + ' mes(s) on ' + 'dia ' + CAST(freq_interval AS VARCHAR(10)) + ' deste mes ' + CASE freq_subday_type
						WHEN 1
							THEN 'a(s) ' + LTRIM(REPLACE(RIGHT(CONVERT(VARCHAR(30), CAST(convert(VARCHAR(8), STUFF(STUFF(RIGHT('000000' + CAST(active_start_time AS VARCHAR(10)), 6), 3, 0, ':'), 6, 0, ':'), 8) AS DATETIME), 9), 14), ':000', ' '))
						WHEN 2
							THEN 'a cada ' + CAST(freq_subday_interval AS VARCHAR(10)) + ' segundo(s)'
						WHEN 4
							THEN 'a cada ' + CAST(freq_subday_interval AS VARCHAR(10)) + ' minutos(s)'
						WHEN 8
							THEN 'a cada ' + CAST(freq_subday_interval AS VARCHAR(10)) + ' horas(s)'
						ELSE ''
						END + CASE
						WHEN freq_subday_type IN (
								2
								,4
								,8
								) /* repeat seconds/mins/hours */
							THEN ' entre ' + LTRIM(REPLACE(RIGHT(CONVERT(VARCHAR(30), CAST(convert(VARCHAR(8), STUFF(STUFF(RIGHT('000000' + CAST(active_start_time AS VARCHAR(10)), 6), 3, 0, ':'), 6, 0, ':'), 8) AS DATETIME), 9), 14), ':000', ' ')) + ' e ' + LTRIM(REPLACE(RIGHT(CONVERT(VARCHAR(30), CAST(convert(VARCHAR(8), STUFF(STUFF(RIGHT('000000' + CAST(active_end_time AS VARCHAR(10)), 6), 3, 0, ':'), 6, 0, ':'), 8) AS DATETIME), 9), 14), ':000', ' '))
						ELSE ''
						END
			WHEN 32
				THEN 'Ocorre ' + CASE freq_relative_interval
						WHEN 1
							THEN 'toda primeira '
						WHEN 2
							THEN 'toda segunda '
						WHEN 4
							THEN 'toda terceira '
						WHEN 8
							THEN 'toda quarta '
						WHEN 16
							THEN 'na última '
						END + CASE freq_interval
						WHEN 1
							THEN 'Domingo'
						WHEN 2
							THEN 'Segunda'
						WHEN 3
							THEN 'Terça'
						WHEN 4
							THEN 'Quarta'
						WHEN 5
							THEN 'Quinta'
						WHEN 6
							THEN 'Sexta'
						WHEN 7
							THEN 'Sabado'
						WHEN 8
							THEN 'dia'
						WHEN 9
							THEN 'dia da semana'
						WHEN 10
							THEN 'final de semana'
						END + ' de cada ' + CAST(freq_recurrence_factor AS VARCHAR(10)) + ' mes(s) ' + CASE freq_subday_type
						WHEN 1
							THEN 'a(s) ' + LTRIM(REPLACE(RIGHT(CONVERT(VARCHAR(30), CAST(convert(VARCHAR(8), STUFF(STUFF(RIGHT('000000' + CAST(active_start_time AS VARCHAR(10)), 6), 3, 0, ':'), 6, 0, ':'), 8) AS DATETIME), 9), 14), ':000', ' '))
						WHEN 2
							THEN 'a cada ' + CAST(freq_subday_interval AS VARCHAR(10)) + ' segundo(s)'
						WHEN 4
							THEN 'a cada ' + CAST(freq_subday_interval AS VARCHAR(10)) + ' minuto(s)'
						WHEN 8
							THEN 'a cada ' + CAST(freq_subday_interval AS VARCHAR(10)) + ' hora(s)'
						ELSE ''
						END + CASE
						WHEN freq_subday_type IN (
								2
								,4
								,8
								) /* repeat seconds/mins/hours */
							THEN ' entre ' + LTRIM(REPLACE(RIGHT(CONVERT(VARCHAR(30), CAST(convert(VARCHAR(8), STUFF(STUFF(RIGHT('000000' + CAST(active_start_time AS VARCHAR(10)), 6), 3, 0, ':'), 6, 0, ':'), 8) AS DATETIME), 9), 14), ':000', ' ')) + ' e ' + LTRIM(REPLACE(RIGHT(CONVERT(VARCHAR(30), CAST(convert(VARCHAR(8), STUFF(STUFF(RIGHT('000000' + CAST(active_end_time AS VARCHAR(10)), 6), 3, 0, ':'), 6, 0, ':'), 8) AS DATETIME), 9), 14), ':000', ' '))
						ELSE ''
						END
			WHEN 64
				THEN 'Roda quando o serviço do SQL Server Agent iniciar'
			WHEN 128
				THEN 'Roda quando o computador estiver idle'
			END AS [Description]
	FROM msdb.dbo.sysjobs SJ
	INNER JOIN msdb.dbo.sysjobschedules SJS ON SJ.job_id = SJS.job_id
	INNER JOIN msdb.dbo.sysschedules SS ON SJS.schedule_id = SS.schedule_id
	INNER JOIN msdb.dbo.syscategories SC ON SJ.category_id = SC.category_id
		--WHERE SC.name = 'Name from query below'
	) S
INNER JOIN msdb.dbo.sysjobhistory H ON s.job_id = H.job_id
	AND H.step_id = 0
WHERE H.run_date &gt;= /* 7 days ago */ cast(datepart(yyyy, dateadd(d, - 7, getDate())) AS VARCHAR(10)) + cast(datepart(mm, dateadd(d, - 7, getDate())) AS VARCHAR(10)) + cast(datepart(dd, dateadd(d, - 7, getDate())) AS VARCHAR(10)) --format getDate once to compare against multiple run_dates
GROUP BY /*S.job_id,*/ S.job_name
	,S.is_job_enabled
	,S.is_schedule_enabled
	,S.job_owner
	,S.schedule_name
	,S.Description
ORDER BY S.job_name

Anúncios

Um pensamento sobre “Checkup

Deixe um comentário

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair / Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair / Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair / Alterar )

Foto do Google+

Você está comentando utilizando sua conta Google+. Sair / Alterar )

Conectando a %s