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/

  

update with variable and stop at some predefined target

When using update with variables, it seem the variable is always evaluated first before any other filed.
Also it will not respect the condition using variable with the where clause. Put another way, no, you ca not use variable to dynamically control the data being updated. One workaround is to set condition inside set section.
For example. Try this:


USE [Test]
GO

/****** Object:  Table [dbo].[UpdateTest]    Script Date: 8/22/2012 10:02:10 AM ******/
if (exists (select 1 from sys.Tables where name='UpdateTest'))
    DROP TABLE [dbo].[UpdateTest]
GO

/****** Object:  Table [dbo].[UpdateTest]    Script Date: 8/22/2012 10:02:10 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[UpdateTest](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [count] [int] NOT NULL,
    [value] [int] NOT NULL,
 CONSTRAINT [PK_UpdateTest] PRIMARY KEY CLUSTERED
(
    [id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO


SET IDENTITY_INSERT [dbo].[UpdateTest] ON

GO
INSERT [dbo].[UpdateTest] ([id], [count], [value]) VALUES (1, 5, 0)
GO
INSERT [dbo].[UpdateTest] ([id], [count], [value]) VALUES (2, 12, 0)
GO
INSERT [dbo].[UpdateTest] ([id], [count], [value]) VALUES (3, 20, 0)
GO
INSERT [dbo].[UpdateTest] ([id], [count], [value]) VALUES (4, 1, 0)
GO
INSERT [dbo].[UpdateTest] ([id], [count], [value]) VALUES (5, 5, 0)
GO
INSERT [dbo].[UpdateTest] ([id], [count], [value]) VALUES (6, 98, 0)
GO
INSERT [dbo].[UpdateTest] ([id], [count], [value]) VALUES (7, 98, 0)
GO
SET IDENTITY_INSERT [dbo].[UpdateTest] OFF
GO


declare @sum int, @TagetHitTimes int
set @sum=0
set @TagetHitTimes=0

--update [UpdateTest] set val1=0

--select * from [UpdateTest]

update [UpdateTest]
set  
    -- variable seems to be computed first
    -- only add sum when target is not achived
    -- stop adding when target is achived
    @sum=case when @sum>24 then @sum else @sum+[count] end
    -- used to track the first time target is achived
    ,@TagetHitTimes=@TagetHitTimes+(case when @sum>24 then 1 else 0 end)
    ,[value]=case when  @TagetHitTimes<=1 then [value]+@sum else 0 end

select * from [UpdateTest]

temp table vs table varialbe

(http://databases.aspfaq.com/database/should-i-use-a-temp-table-or-a-table-variable.html)
table varialbe:

• has some limitation compared to temp table
• has less recompilation
• not good choice for large data (row number is always 1 and no stats is available)
• can be used with DMV tables
• etc

Missing Index From SQL Server MDV

(From
http://blogs.msdn.com/b/bartd/archive/2007/07/19/are-you-using-sql-s-missing-index-dmvs.aspx
)



SELECT

    migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) * (migs.user_seeks + migs.user_scans) AS improvement_measure

    ,db_name(mid.database_id) as DatabaseName

    ,object_name( mid.[object_id], mid.database_id) as TableName

    ,'CREATE INDEX [missing_index_' + CONVERT (varchar, mig.index_group_handle) + '_' + CONVERT (varchar, mid.index_handle)

    + '_' + LEFT (PARSENAME(mid.statement, 1), 32) + ']'

    + ' ON ' + mid.statement

    + ' (' + ISNULL (mid.equality_columns,'')

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

    + ISNULL (mid.inequality_columns, '')

    + ')'

    + ISNULL (' INCLUDE (' + mid.included_columns + ')', '') AS create_index_statement

    ,migs.*

    , mid.[object_id]

    FROM    sys.dm_db_missing_index_groups mig

            INNER JOIN sys.dm_db_missing_index_group_stats migs ON migs.group_handle = mig.index_group_handle

            INNER JOIN sys.dm_db_missing_index_details mid ON mig.index_handle = mid.index_handle

    WHERE    migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) * (migs.user_seeks + migs.user_scans) > 10

    ORDER BY migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans) DESC