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

acestream @ Kodi

To reproduce in kodi, search for the addon acestream, then you must find a source of the stream you want:

1. http:// arenavision. in/

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​

VALUES(ID,

Patern to replace/remove

\VALUES \([1-9][0-9]{0,4}|10000,

Home Setup [NAS, pi]

Two raspberrypi with raspbian and acer aspire on with lubuntu

- Raspberrypi

1. Install raspian
2. sudo raspian-config (on desktop), setup keyboard, language, locale
1. If needed sudo nano /etc/default/keyboard then XKBLAYOUT=”gb”
3. Install xrdp
4. Locally run  sudo xrdp-genkeymap /etc/xrdp/km-0409.ini (fix auth issue)

- Lubuntu

1. Repeat 3. and 4. (Raspberrypi - xrdp)
2. Install transmission
3. Config web access
1. sudo find / -iname settings.json
2. Important to edit right file: /etc/transmission-daemon/settings.json
3. sudo /etc/init.d/transmission-daemon stop !IMPORTANT
4. edit settings sudo nano /etc/transmission-daemon/settings.json
5. sudo /etc/init.d/transmission-daemon start
4. *It works* - After install lubuntu, configure remote access on transmission client, edit > preferences > Remote. Done

My Nexus 7 factory img

bootstrap templates

http://startbootstrap.com/

http://bootsnipp.com/
(form builder  / Snippets / buttons )