Ainda no Rascunho

Levantando o nível de performance do ambiente.

 SELECT object_name, counter_name, cntr_value  FROM sys.dm_os_performance_counters

-- Visualizar ADHocs (consultas pré armazenadas, quando não são stored procedure)

use master

go

SELECT  cast(text as varchar(8000)) as Query,(cp.size_in_bytes/1024) as KB

    FROM sys.dm_exec_cached_plans AS cp

    CROSS APPLY sys.dm_exec_sql_text(plan_handle)

WHERE cp.cacheobjtype = 'Compiled Plan' AND cp.objtype = 'Adhoc' AND cp.usecounts = 1

order by KB desc

Levantando I/O percentualmente de cada Banco. Verifique o consumo de IO

WITH Agg_IO_Stats

AS

(

  SELECT

    DB_NAME(database_id) AS database_name,

    CAST(SUM(num_of_bytes_read + num_of_bytes_written) / 1048576.

         AS DECIMAL(12, 2)) AS io_in_mb

  FROM sys.dm_io_virtual_file_stats(NULL, NULL) AS DM_IO_Stats

  GROUP BY database_id

)

SELECT

  ROW_NUMBER() OVER(ORDER BY io_in_mb DESC) AS row_num,

  database_name,

  io_in_mb,

  CAST(io_in_mb / SUM(io_in_mb) OVER() * 100

       AS DECIMAL(5, 2)) AS Porcento

FROM Agg_IO_Stats

ORDER BY row_num;

Levantando qtd. de vezes que foi executada uma query

SELECT  text,plan_handle, cp.size_in_bytes,usecounts--,*           

FROM sys.dm_Exec_cached_plans AS cp        

      CROSS APPLY sys.dm_exec_sql_text(plan_handle)            

WHERE text not like '%dm_exec_sql_text%' --para não aparecer essa propria query        

    and text not like '%dm_Exec_cached_plans%' --para não aparecer essa propria query      

    and text like '%select%' -- aqui coloca o começo da sua consulta

ORDER BY usecounts DESC

Analisando as dores de cabeça  

( Rode algumas vezes e armazene os resultados em uma planilha isto servira para termos estatísticas após rodar as manutenções dos ganhos obtidos )

SELECT object_name, counter_name, cntr_value

FROM sys.dm_os_performance_counters

DESCOBRINDO TAMANHO ARQUIVOS

with fs

as

(

    select database_id, type, size * 8.0 / 1024 size

    from sys.master_files

)

select  @@servername as seridor, 

    name as NomeDoBanco,

    (select sum(size) from fs where type = 0 and fs.database_id = db.database_id) DataFileSizeMB,

    (select sum(size) from fs where type = 1 and fs.database_id = db.database_id) LogFileSizeMB

from sys.databases db where name like '%SGA00%' OR  name like '%SIG00%'

INICIANDO AS MANUTENÇÕES

Esta Query indica quais índices devemos criar, lembrando que devemos selecionar o banco para analise:

Use banco_para_ analise :

 SELECT 

dm_mid.database_id AS DatabaseID,

dm_migs.avg_user_impact*(dm_migs.user_seeks+dm_migs.user_scans) Avg_Estimated_Impact,

dm_migs.last_user_seek AS Last_User_Seek,

OBJECT_NAME(dm_mid.OBJECT_ID,dm_mid.database_id) AS [TableName],

'CREATE NONCLUSTERED INDEX [SK01_'

 + OBJECT_NAME(dm_mid.OBJECT_ID,dm_mid.database_id) +']'+ 

 ' ON ' + dm_mid.statement+ ' (' + ISNULL (dm_mid.equality_columns,'')

+ CASE WHEN dm_mid.equality_columns IS NOT NULL AND dm_mid.inequality_columns IS NOT NULL THEN ',' ELSE

'' END+ ISNULL (dm_mid.inequality_columns, '')

+ ')'+ ISNULL (' INCLUDE (' + dm_mid.included_columns + ')', '') AS Create_Statement,dm_migs.user_seeks,dm_migs.user_scans

FROM sys.dm_db_missing_index_groups dm_mig

INNER JOIN sys.dm_db_missing_index_group_stats dm_migs

ON dm_migs.group_handle = dm_mig.index_group_handle

INNER JOIN sys.dm_db_missing_index_details dm_mid

ON dm_mig.index_handle = dm_mid.index_handle

WHERE dm_mid.database_ID = DB_ID()

ORDER BY Avg_Estimated_Impact DESC

Vamos ver agora o nosso amigo tempdb

* O número de arquivos depende do número de núcleos (lógicos) no computador. O valor será o número de núcleos ou 8, o que for menor.
O valor padrão para o número de arquivos de dados baseia-se nas diretrizes gerais de KB 2154845.

USE [master]

GO

ALTER DATABASE [tempdb] ADD FILE ( NAME = N'temp_cpu1', FILENAME = N'E:\Index\temp_cpu1.mdf' , SIZE = 3072KB , FILEGROWTH = 1024KB )

GO

ALTER DATABASE [tempdb] ADD FILE ( NAME = N'temp_cpu2', FILENAME = N'E:\Index\temp_cpu2.mdf' , SIZE = 3072KB , FILEGROWTH = 1024KB )

GO

ALTER DATABASE [tempdb] ADD FILE ( NAME = N'temp_cpu3', FILENAME = N'E:\Index\temp_cpu3.mdf' , SIZE = 3072KB , FILEGROWTH = 1024KB )

GO

USE [master]

GO

ALTER DATABASE [tempdb] ADD LOG FILE ( NAME = N'temp_log_cpu1', FILENAME = N'E:\Index\temp_log_cpu1.ldf' , SIZE = 17408KB , FILEGROWTH = 10%)

GO

ALTER DATABASE [tempdb] ADD LOG FILE ( NAME = N'temp_log_cpu2', FILENAME = N'E:\Index\temp_log_cpu2.ldf' , SIZE = 17408KB , FILEGROWTH = 10%)

GO

ALTER DATABASE [tempdb] ADD LOG FILE ( NAME = N'temp_log_cpu3', FILENAME = N'E:\Index\temp_log_cpu3.ldf' , SIZE = 17408KB , FILEGROWTH = 10%)

GO

Utilize este Script para  retorna a utilização dos data files do Tempdb.

SELECT files.physical_name, files.name,

stats.num_of_writes,(1.0 * stats.io_stall_write_ms / stats.num_of_writes) AS avg_write_stall_ms,

stats.num_of_reads,(1.0 * stats.io_stall_read_ms / stats.num_of_reads) AS avg_read_stall_ms

FROM sys.dm_io_virtual_file_stats(2, NULL) as stats

INNER JOIN master.sys.master_files AS files

ON stats.database_id = files.database_id

AND stats.file_id = files.file_id

WHERE files.type_desc = 'ROWS'

https://msdn.microsoft.com/pt-br/windows/desktop/ms190768

https://lhgaretti.wordpress.com/2016/02/05/melhores-praticas-com-tempdb/

USE Tempdb
GO

ALTER DATABASE [tempdb] MODIFY FILE ( NAME = N’tempdev’, SIZE = 1GB, FILEGROWTH = 512MB )
GO
ALTER DATABASE [tempdb] MODIFY FILE ( NAME = N’templog’, SIZE = 1GB, FILEGROWTH = 512MB )
GO

IF (SELECT COUNT(1) FROM sys.database_files) = 2
BEGIN
DECLARE
@physical_name VARCHAR(200),
@cpus TINYINT

SELECT @cpus = cpu_count FROM sys.dm_os_sys_info
SELECT
@physical_name = REPLACE(physical_name, REVERSE(SUBSTRING(REVERSE(physical_name), 1, CHARINDEX(‘\’, REVERSE(physical_name)) – 1)), ”)
FROM sys.database_files
WHERE name = ‘tempdev’

— Cria os arquivos de acordo com a quantidade de CPUs
IF @cpus >= 2
BEGIN
EXEC (‘ALTER DATABASE tempdb ADD FILE (NAME = tempdev02, FILENAME = ”’ + @physical_name + ‘tempdb02.ndf”, SIZE = 1GB, FILEGROWTH = 512MB)’)
END
IF @cpus >= 4
BEGIN
EXEC (‘ALTER DATABASE tempdb ADD FILE (NAME = tempdev03, FILENAME = ”’ + @physical_name + ‘tempdb03.ndf”, SIZE = 1GB, FILEGROWTH = 512MB)’)
EXEC (‘ALTER DATABASE tempdb ADD FILE (NAME = tempdev04, FILENAME = ”’ + @physical_name + ‘tempdb04.ndf”, SIZE = 1GB, FILEGROWTH = 512MB)’)
END
IF @cpus >= 8
BEGIN
EXEC (‘ALTER DATABASE tempdb ADD FILE (NAME = tempdev05, FILENAME = ”’ + @physical_name + ‘tempdb05.ndf”, SIZE = 1GB, FILEGROWTH = 512MB)’)
EXEC (‘ALTER DATABASE tempdb ADD FILE (NAME = tempdev06, FILENAME = ”’ + @physical_name + ‘tempdb06.ndf”, SIZE = 1GB, FILEGROWTH = 512MB)’)
EXEC (‘ALTER DATABASE tempdb ADD FILE (NAME = tempdev07, FILENAME = ”’ + @physical_name + ‘tempdb07.ndf”, SIZE = 1GB, FILEGROWTH = 512MB)’)
EXEC (‘ALTER DATABASE tempdb ADD FILE (NAME = tempdev08, FILENAME = ”’ + @physical_name + ‘tempdb08.ndf”, SIZE = 1GB, FILEGROWTH = 512MB)’)
END
RAISERROR (‘Arquivos criados com sucesso’, 10, 1)
END
ELSE
RAISERROR (‘O tempdb já possui mais que 2 arquivos, analise e crie manualmente’, 10, 1)

Outro ponto dentro das boas praticas, é referente aos Trace Flags 1117 e 1118

=====================

use << SEU BANCO >>

DECLARE @dbid INT ; --what's the dbid for DBNAME?

SET @dbid = DB_ID() ;

DECLARE @objectid INT ; --what's objectid for our demo table?

SET @objectid = OBJECT_ID(N'TABLENAME') ;

--look at locking in the DB

SELECT  resource_type

       ,resource_database_id

       ,resource_associated_entity_id

       ,request_mode

       ,request_type

       ,request_session_id

FROM    sys.dm_tran_locks

WHERE   resource_database_id = @dbid ;

REBUILD INDICES

use << SEU BANCO >>

 drop table #Reindex_Tables

SELECT 

    RowNum = ROW_NUMBER() OVER(ORDER BY t.TABLE_NAME)

    ,TableName = t.TABLE_SCHEMA + '.' + t.TABLE_NAME

    ,AlterMe = 'ALTER INDEX ALL ON [' + t.TABLE_SCHEMA + '].[' + t.TABLE_NAME + '] REBUILD;'

INTO #Reindex_Tables

FROM INFORMATION_SCHEMA.TABLES t

WHERE TABLE_TYPE = 'BASE TABLE'

DECLARE @Iter INT

DECLARE @MaxIndex INT

DECLARE @ExecMe VARCHAR(MAX)

SET @Iter = 1

SET @MaxIndex = 

(

    SELECT COUNT(1)

    FROM #Reindex_Tables

)

WHILE @Iter < @MaxIndex

BEGIN 

    SET @ExecMe = 

    (

        SELECT AlterMe

        FROM #Reindex_Tables

        WHERE RowNum = @Iter

    )

  --  EXEC (@ExecMe)

    PRINT @ExecMe -- + ' /* Executed  */'

    SET @Iter = @Iter + 1

END