Friday, October 2, 2015

Check tempdb Performance

 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'


--Are writes being evenly distributed between data files?
--Are writes finishing in 20ms or less

--IF NO then we have a problem
--8 database files are good ..16 data files ? may be, but try with 8 data files and then if still bad performance try adding 16


declare @milli as int
set @milli = 30251

select @milli/3600000 as hours,
(@milli - ((@milli/3600000)*3600000))/60000 as minutes,
(@milli - (((@milli)/60000)*60000)) /1000 as seconds,
@milli-(((@milli)/1000)*1000) as milliseconds

No comments:

Post a Comment

https://blog.sqlauthority.com/2009/06/27/sql-server-fix-error-17892-logon-failed-for-login-due-to-trigger-execution-changed-database-context...