SQL Performance Dashboard Reports

Install a set of reports which will help us troubloeshoot and find performance improvement opportunitites: https://www.microsoft.com/en-us/download/details.aspx?id=29063

check sql server health

----------------------------------------------
-- Latência Geral
----------------------------------------------
select io_stall/(num_of_reads + num_of_writes) IO_Stall,
case
    when io_stall/(num_of_reads + num_of_writes)<5 then 'Excelente'
    when io_stall/(num_of_reads + num_of_writes)<20 then 'Bom'
    when io_stall/(num_of_reads + num_of_writes)<60 then 'Aceitável'
    when io_stall/(num_of_reads + num_of_writes)<100 then 'Ruim'
    else 'Péssimo'
end IO_Status,
db_name(mf.database_id) DatabaseName,type_desc FileType,mf.name LogigalName,mf.physical_name PhysicalName
from sys.dm_io_virtual_file_stats(default,default) vfs
inner join sys.master_files mf on vfs.database_id = mf.database_id and vfs.file_id = mf.file_id
order by 1 desc
----------------------------------------------
-- Latência Separada em Reads e Writes
----------------------------------------------
SELECT @@ServerName as instanceName, [Drive], volume_mount_point,
 (io_stall_read_ms/(1+num_of_reads)) AS [Read Latency],
 (io_stall_write_ms/(1+num_of_writes)) AS [Write Latency],
 (io_stall/(1+num_of_reads + num_of_writes)) AS [Overall Latency],
 (num_of_bytes_read/(1+num_of_reads)) AS [Avg Bytes/Read],
 (num_of_bytes_written/(1+num_of_writes)) AS [Avg Bytes/Write],
 ((num_of_bytes_read + num_of_bytes_written)/(1+num_of_reads + num_of_writes)) AS [Avg Bytes/Transfer]
FROM (SELECT LEFT(UPPER(mf.physical_name), 2) AS Drive, UPPER (volume_mount_point) as volume_mount_point
  ,SUM(num_of_reads) AS num_of_reads,
         SUM(io_stall_read_ms) AS io_stall_read_ms, SUM(num_of_writes) AS num_of_writes,
         SUM(io_stall_write_ms) AS io_stall_write_ms, SUM(num_of_bytes_read) AS num_of_bytes_read,
         SUM(num_of_bytes_written) AS num_of_bytes_written, SUM(io_stall) AS io_stall
      FROM sys.dm_io_virtual_file_stats(NULL, NULL) AS vfs
      INNER JOIN sys.master_files AS mf WITH (NOLOCK)
      ON vfs.database_id = mf.database_id AND vfs.file_id = mf.file_id
  CROSS APPLY sys.dm_os_volume_stats(mf.database_id, mf.FILE_ID)
      GROUP BY LEFT(UPPER(mf.physical_name), 2),UPPER (volume_mount_point)) AS tab
ORDER BY [Overall Latency] DESC OPTION (RECOMPILE);
----------------------------------------------
-- tamanho tempdb
----------------------------------------------
select @@servername,name,size from sys.sysaltfiles where dbid = 2
----------------------------------------------
-- Latência Separada em Reads e Writes por ficheiro
----------------------------------------------
SELECT mf.physical_name AS Filename
  ,SUM(num_of_reads) AS num_of_reads,
         SUM(io_stall_read_ms) AS io_stall_read_ms, SUM(num_of_writes) AS num_of_writes,
         SUM(io_stall_write_ms) AS io_stall_write_ms, SUM(num_of_bytes_read) AS num_of_bytes_read,
         SUM(num_of_bytes_written) AS num_of_bytes_written, SUM(io_stall) AS io_stall
      FROM sys.dm_io_virtual_file_stats(NULL, NULL) AS vfs
      INNER JOIN sys.master_files AS mf WITH (NOLOCK)
      ON vfs.database_id = mf.database_id AND vfs.file_id = mf.file_id
  CROSS APPLY sys.dm_os_volume_stats(mf.database_id, mf.FILE_ID)
      GROUP BY mf.physical_name
----------------------------------------------
-- I/O and Size about all files
----------------------------------------------
select
db_name(mf.database_id) DatabaseName,type_desc FileType,mf.name LogigalName,mf.physical_name PhysicalName, mf.size/128 size_in_mb,
    (num_of_bytes_read + num_of_bytes_written)/(vfs.sample_ms/1000/60) io_bytes_per_minute
from sys.dm_io_virtual_file_stats(default,default) vfs
inner join sys.master_files mf on vfs.database_id = mf.database_id and vfs.file_id = mf.file_id
order by 1 desc
----

Last executed SQL Queries

SELECT deqs.last_execution_time AS [Time], dest.TEXT AS [Query] FROM sys.dm_exec_query_stats AS deqs CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest ORDER BY deqs.last_execution_time DESC

Mass delete

DECLARE @Deleted_Rows AS INT
DECLARE @TopRowsToDelete AS INT

SET @Deleted_Rows = 1
SET @TopRowsToDelete  = 5000

/* 1. CRIA TMP COM INDICES*/
CREATE TABLE #xcci_log_tmp
  (
    ID int,
    LOGICENTITY nvarchar(64)
  )
CREATE NONCLUSTERED INDEX #IDX_CC_xcci_log_tmp ON #xcci_log_tmp (ID)

/* 2. PREENCHE TMP COM CHAVE*/
SELECT  [ID]
  ,[LOGICENTITY]
INTO #xcci_log_tmp
FROM [SIRC]..[OSUSR_YAA_S_LOG] WITH (NOLOCK) WHERE [LOGICENTITY] = 'XCCI' AND [DETAIL] = 'Actualização de cliente em XCCI'

/* 3. CICLO DE DELETE DE 5000 DE CADA VEZ*/
WHILE (@Deleted_Rows > 0)

BEGIN
  BEGIN TRANSACTION
     
  DELETE TOP(@TopRowsToDelete) log FROM [SIRC]..[OSUSR_YAA_S_LOG] log INNER JOIN #xcci_log_tmp tmp ON log.ID=tmp.ID
  SET @Deleted_Rows = @@ROWCOUNT

  COMMIT TRANSACTION
END

/* 4. DESTROI TMP */
DROP TABLE #xcci_log_tmp

Count Rows and Size SQL Tables

CREATE TABLE #RowCountsAndSizes (TableName NVARCHAR(128),rows CHAR(11),      

       reserved VARCHAR(18),data VARCHAR(18),index_size VARCHAR(18), 

       unused VARCHAR(18))

EXEC       sp_MSForEachTable 'INSERT INTO #RowCountsAndSizes EXEC sp_spaceused ''?'' '

SELECT     TableName,CONVERT(bigint,rows) AS NumberOfRows,

           (CONVERT(bigint,left(data,len(data)-3))) / 1024 AS 'Size (MB)'

FROM       #RowCountsAndSizes 

ORDER BY  TableName

DROP TABLE #RowCountsAndSizes​

Advanced SQL query With BOOLEAN struct

Obter trimestres por uma data

SELECT DATEPART(q, '2007-06-01') AS "Quarterly";

via Daniel Maia

UNION ALL

SELECT   
        {GENERIC_CONTENT}.[Title] AS OPINION,     

     COUNT({TRACKING}.[ContentId]) AS NOPINION,
   'GENERIC_CONTENT'
FROM         
     {TRACKING} 
     LEFT OUTER JOIN
                      {GENERIC_CONTENT} 
    ON {TRACKING}.[ContentId]= {GENERIC_CONTENT}.[Id]
WHERE 
         {TRACKING}.[ContentId] = {GENERIC_CONTENT}.[Id]
AND {GENERIC_CONTENT}.[ContentType] = 6

GROUP BY 
         {GENERIC_CONTENT}.[Title]

UNION ALL

SELECT
         {GENERIC_CONTENT}.[Title] AS SUCCESS_STORY, 
         COUNT({TRACKING}.[ContentId]) AS NStories,
         'GENERIC_CONTENT'
FROM  
        {TRACKING} 
                    LEFT OUTER JOIN
                      {GENERIC_CONTENT}
          ON {TRACKING}.[ContentId] = {GENERIC_CONTENT}.[Id]

WHERE 
        {TRACKING}.[ContentId] = {GENERIC_CONTENT}.[Id]
AND {GENERIC_CONTENT}.[ContentType] = 7
GROUP BY 
    {GENERIC_CONTENT}.[Title]


UNION ALL

SELECT
       {GENERIC_CONTENT}.[Title] AS EDITORIAL, 
        COUNT({TRACKING}.[ContentId]) AS NEditorial,
        'GENERIC_CONTENT' 

FROM
        {TRACKING} 
            LEFT OUTER JOIN
                 {GENERIC_CONTENT}
          ON {TRACKING}.[ContentId] = {GENERIC_CONTENT}.[Id]
WHERE 
        {TRACKING}.[ContentId] = {GENERIC_CONTENT}.[Id]
AND {GENERIC_CONTENT}.[ContentType] = 3
GROUP BY 
        {GENERIC_CONTENT}.[Title]

Change Password of SA user @ MS SQL

SP_Password @new = 'password' , @loginame='sa'

List Tables used by one eSpace - Query by eSpace Name

select ENESPACE.NAME, ENENTITY.NAME, ENENTITY.PHYSICAL_TABLE_NAME
from dbo.ossys_Espace ENESPACE inner join dbo.ossys_Entity ENENTITY
    on ENENTITY.ESPACE_ID = ENESPACE.ID
WHERE ENESPACE.NAME = 'espacenameHERE'