sql server dmv i/o preformane checking

-- updated based on the reference below
-- Calculates average stalls per read, per write,
-- and per total input/output for each database file.
SELECT    DB_NAME(fs.database_id) AS [Database Name]
        ,ms.name as LogicName
        ,ms.type_desc
        ,io_stall/1000./3600. as [io_stall_hr]
        ,CAST((io_stall_read_ms + io_stall_write_ms)/(num_of_reads + num_of_writes) AS NUMERIC(10,1)) AS [avg_io_stall_ms]  
        ,num_of_reads + num_of_writes AS [total_io]
        ,num_of_reads
        ,num_of_writes
        ,CAST(io_stall_read_ms/(case when num_of_reads=0 then 1 else 0.0 end + num_of_reads) AS NUMERIC(10,1)) AS [avg_read_stall_ms]
        ,CAST(io_stall_write_ms/(case when num_of_writes=0 then 1.0 else 0.0 end +num_of_writes) AS NUMERIC(10,1)) AS [avg_write_stall_ms]
        ,io_stall_read_ms
        ,io_stall_write_ms
        ,io_stall_read_ms + io_stall_write_ms AS [io_stalls]
        ,fs.file_id
FROM    sys.dm_io_virtual_file_stats(null,null) fs inner join sys.master_files ms
        on ms.database_id=fs.database_id and ms.file_id=fs.file_id
--WHERE    (num_of_reads + num_of_writes)>0
WHERE    (num_of_reads + num_of_writes)>10
--ORDER BY (io_stall) desc;
ORDER BY avg_io_stall_ms DESC;

From:

http://sqlserverperformance.wordpress.com/2010/03/08/easy-ways-to-detect-io-pressure-in-sql-server-2008/

-- Some I/O Specific DMV Queries
-- Glenn Berry
-- March 2010
-- http://glennberrysqlperformance.spaces.live.com/
-- Twitter: GlennAlanBerry

-- Always look at Avg Disk Sec/Read and Avg Disk Sec/Write 
-- in PerfMon for each Physical Disk 

-- Isolate top waits for server instance since last restart or statistics clear
-- Look for I/O specific waits at the top of the list
WITH Waits AS
(SELECT wait_type, wait_time_ms / 1000. AS wait_time_s,
100. * wait_time_ms / SUM(wait_time_ms) OVER() AS pct,
ROW_NUMBER() OVER(ORDER BY wait_time_ms DESC) AS rn
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'))
SELECT W1.wait_type, 
CAST(W1.wait_time_s AS DECIMAL(12, 2)) AS wait_time_s,
CAST(W1.pct AS DECIMAL(12, 2)) AS pct,
CAST(SUM(W2.pct) AS DECIMAL(12, 2)) AS running_pct
FROM Waits AS W1
INNER JOIN Waits AS W2
ON W2.rn <= W1.rn
GROUP BY W1.rn, W1.wait_type, W1.wait_time_s, W1.pct
HAVING SUM(W2.pct) - W1.pct < 95; -- percentage threshold

-- *** I/O Related Waits ***
-- ASYNC_IO_COMPLETION  Occurs when a task is waiting for I/Os to finish
-- IO_COMPLETION        Occurs while waiting for I/O operations to complete. 
--                      This wait type generally represents non-data page I/Os. 
--                      Data page I/O completion waits appear 
--                      as PAGEIOLATCH_* waits
-- PAGEIOLATCH_SH       Occurs when a task is waiting on a latch for a buffer that 
--                      is in an I/O request. The latch request is in Shared mode. 
--                      Long waits may indicate problems with the disk subsystem.
-- PAGEIOLATCH_EX       Occurs when a task is waiting on a latch for a buffer that 
--                      is in an I/O request. The latch request is in Exclusive mode. 
--                      Long waits may indicate problems with the disk subsystem.
-- WRITELOG             Occurs while waiting for a log flush to complete. 
--                      Common operations that cause log flushes 
--                      are checkpoints and transaction commits.
-- PAGELATCH_EX         Occurs when a task is waiting on a latch for a buffer that 
--                      is not in an I/O request. The latch request is in Exclusive mode.
-- BACKUPIO             Occurs when a backup task is waiting for data, or is waiting for a 
--                      buffer in which to store data


-- Check for IO Bottlenecks (run multiple times, look for values above zero)
SELECT cpu_id, pending_disk_io_count 
FROM sys.dm_os_schedulers
WHERE [status] = 'VISIBLE ONLINE'
ORDER BY cpu_id;

-- Look at average for all schedulers (run multiple times, look for values above zero)
SELECT AVG(pending_disk_io_count) AS [AvgPendingDiskIOCount]
FROM sys.dm_os_schedulers 
WHERE [status] = 'VISIBLE ONLINE';

-- High Latch waits (SH and EX) indicates the I/O subsystem is too busy 
SELECT wait_type, waiting_tasks_count, wait_time_ms, signal_wait_time_ms,
       wait_time_ms - signal_wait_time_ms AS [io_wait_time_ms]
FROM sys.dm_os_wait_stats
WHERE wait_type IN('PAGEIOLATCH_EX', 'PAGEIOLATCH_SH', 'PAGEIOLATCH_UP')
ORDER BY wait_type;


-- File Names and Paths for TempDB and all user databases in instance 
SELECT DB_NAME([database_id])AS [Database Name], [file_id], 
       name, physical_name, type_desc
FROM sys.master_files
WHERE [database_id] > 4 AND [database_id] <> 32767
OR [database_id] = 2;

-- Things to look at:
-- Are data files and log files on different drives?
-- Is everything on C: drive?
-- Is TempDB on dedicated drives?
-- Are there multiple data files?


-- Which queries are causing the most IO operations (can take a few seconds)
SELECT TOP (20) total_logical_reads/execution_count AS [avg_logical_reads],
    total_logical_writes/execution_count AS [avg_logical_writes],
    total_worker_time/execution_count AS [avg_cpu_cost], execution_count,
    total_worker_time, total_logical_reads, total_logical_writes, 
    (SELECT DB_NAME(dbid) + ISNULL('..' + OBJECT_NAME(objectid), '') 
     FROM sys.dm_exec_sql_text([sql_handle])) AS query_database,
    (SELECT SUBSTRING(est.[text], statement_start_offset/2 + 1,
        (CASE WHEN statement_end_offset = -1
            THEN LEN(CONVERT(nvarchar(max), est.[text])) * 2
            ELSE statement_end_offset
            END - statement_start_offset
        ) / 2)
        FROM sys.dm_exec_sql_text(sql_handle) AS est) AS query_text,
    last_logical_reads, min_logical_reads, max_logical_reads,
    last_logical_writes, min_logical_writes, max_logical_writes,
    total_physical_reads, last_physical_reads, min_physical_reads, max_physical_reads,
    (total_logical_reads + (total_logical_writes * 5))/execution_count AS io_weighting,
    plan_generation_num, qp.query_plan
FROM sys.dm_exec_query_stats
OUTER APPLY sys.dm_exec_query_plan([plan_handle]) AS qp
WHERE [dbid] >= 5 AND (total_worker_time/execution_count) > 100
ORDER BY io_weighting DESC;


-- Calculates average stalls per read, per write, 
-- and per total input/output for each database file. 
SELECT DB_NAME(database_id) AS [Database Name], file_id ,io_stall_read_ms, num_of_reads,
CAST(io_stall_read_ms/(1.0 + num_of_reads) AS NUMERIC(10,1)) 
AS [avg_read_stall_ms],io_stall_write_ms, 
num_of_writes,CAST(io_stall_write_ms/(1.0+num_of_writes) AS NUMERIC(10,1)) 
AS [avg_write_stall_ms],io_stall_read_ms + io_stall_write_ms AS [io_stalls], 
num_of_reads + num_of_writes AS [total_io], CAST((io_stall_read_ms + 
io_stall_write_ms)/(1.0 + num_of_reads + num_of_writes) AS NUMERIC(10,1)) 
AS [avg_io_stall_ms]
FROM sys.dm_io_virtual_file_stats(null,null)
ORDER BY avg_io_stall_ms DESC;

-- Helps determine which database files on the entire instance have the most I/O bottlenecks


-- Analyze Database I/O, ranked by IO Stall%
WITH DBIO AS
(SELECT DB_NAME(IVFS.database_id) AS db,
 CASE WHEN MF.type = 1 THEN 'log' ELSE 'data' END AS file_type,
 SUM(IVFS.num_of_bytes_read + IVFS.num_of_bytes_written) AS io,
 SUM(IVFS.io_stall) AS io_stall
 FROM sys.dm_io_virtual_file_stats(NULL, NULL) AS IVFS
 INNER JOIN sys.master_files AS MF
 ON IVFS.database_id = MF.database_id
 AND IVFS.file_id = MF.file_id
 GROUP BY DB_NAME(IVFS.database_id), MF.[type])
SELECT db, file_type, 
  CAST(1. * io / (1024 * 1024) AS DECIMAL(12, 2)) AS io_mb,
  CAST(io_stall / 1000. AS DECIMAL(12, 2)) AS io_stall_s,
  CAST(100. * io_stall / SUM(io_stall) OVER()
       AS DECIMAL(10, 2)) AS io_stall_pct,
  ROW_NUMBER() OVER(ORDER BY io_stall DESC) AS rn
FROM DBIO
ORDER BY io_stall DESC;


-- The queries below are database specific
USE yourdatabasename;
GO

-- Top Cached SPs By Total Physical Reads (SQL 2008). 
-- Physical reads relate to disk I/O pressure
SELECT TOP(25) p.name AS [SP Name],qs.total_physical_reads AS [TotalPhysicalReads], 
qs.total_physical_reads/qs.execution_count AS [AvgPhysicalReads], qs.execution_count, 
ISNULL(qs.execution_count/DATEDIFF(Second, qs.cached_time, GETDATE()), 0) AS [Calls/Second],
qs.total_elapsed_time, qs.total_elapsed_time/qs.execution_count 
AS [avg_elapsed_time], qs.cached_time 
FROM sys.procedures AS p
INNER JOIN sys.dm_exec_procedure_stats AS qs
ON p.[object_id] = qs.[object_id]
WHERE qs.database_id = DB_ID()
ORDER BY qs.total_physical_reads DESC;
       
-- Top Cached SPs By Total Logical Writes (SQL 2008). 
-- Logical writes relate to both memory and disk I/O pressure 
SELECT TOP(25) p.name AS [SP Name], qs.total_logical_writes AS [TotalLogicalWrites], 
qs.total_logical_writes/qs.execution_count AS [AvgLogicalWrites], qs.execution_count,
ISNULL(qs.execution_count/DATEDIFF(Second, qs.cached_time, GETDATE()), 0) AS [Calls/Second],
qs.total_elapsed_time, qs.total_elapsed_time/qs.execution_count AS [avg_elapsed_time], 
qs.cached_time
FROM sys.procedures AS p
INNER JOIN sys.dm_exec_procedure_stats AS qs
ON p.[object_id] = qs.[object_id]
WHERE qs.database_id = DB_ID()
ORDER BY qs.total_logical_writes DESC;


-- Lists the top statements by average input/output usage for the current database
SELECT TOP(50) OBJECT_NAME(qt.objectid) AS [SP Name],
(qs.total_logical_reads + qs.total_logical_writes) /qs.execution_count AS [Avg IO],
SUBSTRING(qt.[text],qs.statement_start_offset/2, 
        (CASE 
            WHEN qs.statement_end_offset = -1 
         THEN LEN(CONVERT(nvarchar(max), qt.[text])) * 2 
            ELSE qs.statement_end_offset 
         END - qs.statement_start_offset)/2) AS [Query Text]    
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
WHERE qt.[dbid] = DB_ID()
ORDER BY [Avg IO] DESC;

-- Helps you find the most expensive statements for I/O by SP

calling remote function with specific isolaton level, storing data into table varialbe

declare @TT table (
    [Field1] [decimal](9, 0) NOT NULL,
    [Field2] [int] NOT NULL,
    [Feild3] [smalldatetime] NOT NULL,
    [Field4] [smalldatetime] NOT NULL,
    [Field5] [tinyint] NULL
    )

insert into @TT
exec ('set transaction isolation level read uncommitted;select * from DatabaseName.[dbo].[fn_FuncitonName](p1, p2)') at LinkedServerName
select * from @TT

SQL server partion query using dmv

1)
 select
    distinct
    object_name(p.object_id) as ObjectName,
    index_name = i.name,
    index_type_desc = i.type_desc,
    partition_scheme = ps.name,
    function_name = pf.name,
    p.object_id,
    data_space_id = ps.data_space_id,
    function_id = ps.function_id
    from    sys.partitions p
            inner join    sys.indexes i on p.object_id = i.object_id and p.index_id = i.index_id
            inner join    sys.data_spaces ds on i.data_space_id = ds.data_space_id
            inner join    sys.partition_schemes ps on ds.data_space_id = ps.data_space_id
            inner join    sys.partition_functions pf on ps.function_id = pf.function_id
    order by 1,2,3,4

2)

select
    object_name(p.object_id) as ObjectName,
    index_name = i.name,
    prv.value,
    p.partition_number,
    p.rows,
    ps.name as partition_scheme,
    pf.name as function_name,
    p.object_id,
    p.index_id,
    index_type_desc = i.type_desc,
    i.data_space_id,
    pf.function_id,
    pf.type_desc,
    pf.boundary_value_on_right,
    destination_data_space_id = dds.destination_id,
    prv.parameter_id
    from    sys.partitions p inner join
            sys.indexes i on p.object_id = i.object_id and p.index_id = i.index_id
            inner join    sys.data_spaces ds on i.data_space_id = ds.data_space_id
            inner join    sys.partition_schemes ps on ds.data_space_id = ps.data_space_id
            inner join    sys.partition_functions pf on ps.function_id = pf.function_id
            inner join    sys.destination_data_spaces dds on dds.partition_scheme_id = ds.data_space_id and p.partition_number = dds.destination_id
            left outer join    sys.partition_range_values prv on prv.function_id = ps.function_id and p.partition_number = prv.boundary_id
   where p.rows>0
   order by 1,2,3,4,5

Some performance related squery for SQL

use tempdb
--DBCC SQLPERF (LOGSPACE);
--DBCC SQLPERF (WAITSTATS)
--DBCC SQLPERF ('sys.dm_os_latch_stats', CLEAR);
--DBCC SQLPERF ('sys.dm_os_wait_stats', CLEAR);
--CHECKPOINT
--DBCC FREEPROCCACHE
--DBCC DROPCLEANBUFFERS
--sp_whoisactive
--sp_who
--sp_who2
--dbcc opentran
--
go
--
-- real time ddl bottleneck
select 
    session_id
    ,wait_type
    ,wait_duration_ms
    ,resource_description
    from    sys.dm_os_waiting_tasks
    where    wait_type like 'PAGE%LATCH_%'
            and resource_description like '2:%'
    --sp_whoisactive
go
sp_spaceused
go
--
-- check dirty pages
SELECT   
    db_name(database_id) AS 'DatabaseName'
    ,count(page_id) AS 'DirtyPages'
    ,count(page_id)*8/1024 AS 'TotalSize(MB)'
    FROM    sys.dm_os_buffer_descriptors
    WHERE    is_modified =1
    GROUP BY db_name(database_id)
    ORDER BY count(page_id) DESC
go
--
-- temp db i/o wait stat
-- switch to any database and run stat for that db
SELECT
    DB_NAME(fs.database_id)    as    [Database]
    ,f.[Name]                  as    [LogicName]
    ,f.[filename]              as    [FileName]
    ,fs.[size_on_disk_bytes] / 1024 /1024  as    [Size(MB)]
    -- this seems to be off! data could be written by dropped later
    -- this is NOT space used
    ,fs.[num_of_bytes_written]/1024/1024  as    [TotalWrite(MB)]
    ,case when fs.[num_of_writes]=0 then 0.00 else fs.[io_stall_write_ms]/(fs.[num_of_writes]) end as [AverageWriteDelay(ms)]
    ,case when fs.[num_of_writes]=0 then 0.00 else 1024.00*1024*fs.[io_stall_write_ms]/(fs.[num_of_bytes_written]) end as [AverageWrite Delay(ms/MB)]
    ,fs.[num_of_bytes_read]/1024/1024  as    [TotalRead(MB)]
    ,case when fs.[num_of_reads]=0 then 0.00 else fs.[io_stall_read_ms]/fs.[num_of_reads] end as [AverageReadDelay(ms)]
    ,case when fs.[num_of_reads]=0 then 0.00 else 1024.00*1024*fs.[io_stall_read_ms]/(fs.[num_of_bytes_read]) end as [AverageReadDelay(ms/MB)]
    --,fs.*
    FROM    -- 2(tempdb database id), NULL(all the files related to tempdb)
            --sys.dm_io_virtual_file_stats(2, NULL) fs inner join sys.sysfiles f
            -- current database
            sys.dm_io_virtual_file_stats(db_id(), NULL) fs inner join sys.sysfiles f
            on fs.file_id = f.fileid
    WHERE [num_of_writes]>0 or [num_of_reads]>0
    order by 3,6go
-- reset all counters to 0
--DBCC SQLPERF ('sys.dm_os_wait_stats', CLEAR);
GO
--
-- i/o related wait stat
select
    ([signal_wait_time_ms]+0.0)/[waiting_tasks_count] as AverageSignalWait
    ,([wait_time_ms]-[signal_wait_time_ms]+0.0)/[waiting_tasks_count] as AverageOprationWait
    ,*
    from sys.dm_os_wait_stats
    where    (wait_type like 'PAGEIO%'
            or wait_type like '%IO_COMPLETION'
            or wait_type like 'DISKIO%'
            or wait_type like 'BACKUPIO%'
            or wait_type like 'WRITE%'
            ) and
            waiting_tasks_count>0
    order by wait_time_ms desc
go
--
-- temp table list
select
    crdate
    ,name
    ,refdate
    ,*
    from    tempdb.dbo.sysobjects
    where    type='U'
            and name like '#%'
    order by 1 asc
go
SELECT
    [create_date]
    ,[modify_date]
    ,[name]
    ,[object_id]
    ,[principal_id]
    ,[schema_id]
    ,[parent_object_id]
    ,[lock_escalation]
    ,[lock_escalation_desc]
    ,[is_tracked_by_cdc]
    /*
    ,[type]
    ,[type_desc]
    ,[is_published]
    ,[is_schema_published]
    ,[lob_data_space_id]
    ,[filestream_data_space_id]
    ,[max_column_id_used]
    ,[lock_on_bulk_load]
    ,[uses_ansi_nulls]
    ,[is_replicated]
    ,[has_replication_filter]
    ,[is_merge_published]
    ,[is_sync_tran_subscribed]
    ,[has_unchecked_assembly_data]
    ,[text_in_row_limit]
    ,[large_value_types_out_of_row]
    */
    FROM    [tempdb].[sys].[tables]
    WHERE type='U' and is_ms_shipped=0
    order by 1,2
go
--
-- get session for temp table

DECLARE @FileName VARCHAR(MAX)

SELECT @FileName = SUBSTRING(path, 0, LEN(path)-CHARINDEX('\', REVERSE(path))+1) + '\Log.trc'
FROM sys.traces
WHERE is_default = 1;

SELECT
    o.name,
    o.OBJECT_ID,
    o.create_date,
    gt.NTUserName,
    gt.HostName,
    gt.SPID,
    gt.DatabaseName,
    gt.TEXTData
    FROM    sys.fn_trace_gettable( @FileName, DEFAULT ) AS gt
            JOIN tempdb.sys.objects AS o
            ON gt.ObjectID = o.OBJECT_ID
    WHERE    gt.DatabaseID = 2
            AND gt.EventClass = 46 -- (Object:Created Event from sys.trace_events)
            AND o.create_date >= DATEADD(ms, -100, gt.StartTime)
            AND o.create_date <= DATEADD(ms, 100, gt.StartTime)
            and o.name='#023D5A04'
            --#023D5A04
    ORDER BY o.create_date
go

--
-- what are we waiting for recently?
select * into #BufferWaitStats from sys.dm_os_wait_stats

-- wait ten seconds or so
select ws.wait_type,
      ws.waiting_tasks_count - bws.waiting_tasks_count as waiting_tasks_count,
      ws.wait_time_ms - bws.wait_time_ms as wait_time_ms,
      ws.max_wait_time_ms,
      ws.signal_wait_time_ms - bws.signal_wait_time_ms as signal_wait_time_ms
from sys.dm_os_wait_stats ws
join #BufferWaitStats bws
      on ws.wait_type = bws.wait_type
order by wait_time_ms desc

--clean up
drop table #BufferWaitStats
go

--
-- monitor real time i/o stall
select
    database_id,
    db_name(database_id) as databaseName,
    file_id,
    io_type,
    io_stall,
    io_pending_ms_ticks,
    scheduler_address
from    sys.dm_io_virtual_file_stats(NULL, NULL) fs inner join
        sys.dm_io_pending_io_requests as ir on
        fs.file_handle = ir.io_handle
where    io_pending_ms_ticks>0
order by io_pending_ms_ticks descgo


-------------- reference and other detail------------------------
1) check dirty page size:

SELECT     db_name(database_id) AS 'DatabaseName'     ,count(page_id) AS 'DirtyPages'     , count(page_id)*8/1024 AS 'TotalSize(MB)'     FROM sys.dm_os_buffer_descriptors     WHERE is_modified =1     GROUP BY db_name(database_id)     ORDER BY count(page_id) DESC

http://www.sqlservergeeks.com/blogs/AmitBansal/sql-server-bi/60/sql-server-observing-dirty-pages-in-memory

2) wait statistics
http://www.eraofdata.com/sql-server-wait-stats/
DBCC SQLPERF (WAITSTATS)

select * from sys.dm_os_wait_stats
where
wait_type like 'PAGEIO%' or
wait_type like '%IO_COMPLETION' or
wait_type like 'DISKIO%' or
wait_type like 'BACKUPIO%'or
wait_type like 'WRITE%'
order by wait_time_ms

3)

SELECT *
FROM fn_virtualfilestats(DB_ID(N'AdventureWorks2012'), 2);
GO

The following example displays statistical information for all files in all databases in the instance of SQL Server.

Transact-SQL

SELECT *
FROM fn_virtualfilestats(NULL,NULL);
GO

SELECT * FROM sys.dm_io_virtual_file_stats(DB_ID(N'AdventureWorks2012'), 2);
GO 

4)

USE tempdb
GO
EXEC sp_spaceused

5) ddl bottleneck

http://blogs.msdn.com/b/sqlserverstorageengine/archive/2009/01/12/tempdb-monitoring-and-troubleshooting-ddl-bottleneck.aspx

select 
    session_id
    ,wait_type
    ,wait_duration_ms
    ,resource_description
    from    sys.dm_os_waiting_tasks
    where    wait_type like 'PAGE%LATCH_%'
            and resource_description like '2:%'

6) monitoring tempdb.

http://www.sqllion.com/2009/05/monitoring-tempdb-in-sql-server-2005/ 

7) 

use tempdb
go
sp_spaceused
go
SELECT
 DB_NAME(fs.database_id)    as [Database]
 ,f.[Name]                  as [LogicName]
 ,f.[filename]              as [FileName]
 ,fs.[size_on_disk_bytes] / 1024 /1024  as [Size(MB)]
 -- this seems to be off! data could be written by dropped later
 -- this is NOT space used
 ,fs.[num_of_bytes_written]/1024/1024  as    [TotalWrite(MB)]
 ,fs.[io_stall_write_ms]/fs.[num_of_writes] as [AverageWriteDelay(ms)]
 ,1024.00*1024*fs.[io_stall_write_ms]/fs.[num_of_bytes_written] as [AverageWrite Delay(ms/MB)]
 ,fs.[num_of_bytes_read]/1024/1024  as    [TotalRead(MB)]
 ,fs.[io_stall_read_ms]/fs.[num_of_reads] as [AverageReadDelay(ms)]
 ,1024.00*1024*fs.[io_stall_read_ms]/fs.[num_of_bytes_read] as [AverageReadDelay(ms/MB)]
 --,fs.*
 FROM -- 2(tempdb database id), NULL(all the files related to tempdb)
   sys.dm_io_virtual_file_stats(2, NULL) fs inner join sys.sysfiles f
   on fs.file_id = f.fileid 

8) a sp example to delete temp tables which are older than some date.

http://potomac9499.wordpress.com/2007/12/31/cleaning-up-tempdb/ 

9)

http://www.mssqltips.com/sqlservertip/2329/how-to-identify-io-bottlenecks-in-ms-sql-server/