Check All SQL Server Database or Individual File I/O stall stats

--
-- step 1: over all view across all databases

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(25,2)) 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(25,2)) 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;

--
-- step 2: check each database individually
-- temp db i/o or any other database 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]
    ,cast((fs.[size_on_disk_bytes]+0.00) / 1024 /1024 as decimal(25,2)) as    [Size(MB)]
    -- this seems to be off! data could be written by dropped later
    -- this is NOT space used
    ,cast((fs.[num_of_bytes_written]+0.00)/1024/1024 as decimal(25,2)) as    [TotalWrite(MB)]
    ,cast((fs.[io_stall_write_ms]+0.00)/fs.[num_of_writes] as decimal(25, 2)) as [AverageWriteDelay(ms)]
    -- deal with 0 write or read case
    ,cast(1024.00*1024*fs.[io_stall_write_ms]/(case when fs.[num_of_bytes_written]=0 then 1 else fs.[num_of_bytes_written] end) as decimal(25, 2)) as [AverageWrite Delay(ms/MB)]
    ,cast((fs.[num_of_bytes_read]+0.00)/1024/1024 as decimal(25, 2)) as    [TotalRead(MB)]
    ,cast((fs.[io_stall_read_ms]+0.00)/(case when fs.[num_of_reads]=0 then 1 else fs.[num_of_reads] end) as decimal(25, 2)) as [AverageReadDelay(ms)]
    ,cast(1024.00*1024*fs.[io_stall_read_ms]/(case when fs.[num_of_bytes_read]=0 then 1 else fs.[num_of_bytes_read] end) as decimal(25, 2)) as [AverageReadDelay(ms/MB)]
    ,fs.[num_of_writes]
    ,fs.[num_of_reads]
    --,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--,6

Indexes not used, Missing index and stats, table fragmentation for sql server

0)

select
object_name(I.object_id) as TableName
,I.name
,'drop index '+ I.name + ' on '+ object_name(I.object_id) as DropIndexStatement
,I.object_id
,I.index_id
--,I.*
from sys.indexes I inner join sys.objects O
on I.object_id=O.object_id
left join (select object_id, index_id from sys.dm_db_index_usage_stats where database_id=db_id()) S on
I.object_id=S.object_id and I.index_id=S.index_id
where
-- only looking for no clustered and non primary key indexes
--type_desc='NONCLUSTERED'
I.type=2
and I.is_primary_key=0

-- index not used so far
and (S.object_id is null or S.index_id is null)
-- excluding system tables
--and I.object_id>100
-- a better way to identify system tables
-- this is confirmed by the fact that the following query always return empty result set
-- select * from sys.objects where object_id<100 and is_ms_shipped<>1
-- otherwise we don't need sys.objects at all
and O.is_ms_shipped<>1
order by 1,2

1)
http://blog.sqlauthority.com/2011/01/03/sql-server-2008-missing-index-script-download/

SELECT TOP 25
    dm_migs.avg_user_impact*(dm_migs.user_seeks+dm_migs.user_scans) Avg_Estimated_Impact
    ,db_name(dm_mid.database_id) as DatabaseName
    ,OBJECT_NAME(dm_mid.OBJECT_ID,dm_mid.database_id) AS [TableName]
    ,dm_mid.equality_columns
    ,dm_mid.inequality_columns
    ,dm_mid.included_columns
    ,dm_migs.last_user_seek AS Last_User_Seek
    ,dm_mid.database_id AS DatabaseID
    ,'CREATE INDEX [IX_' + OBJECT_NAME(dm_mid.OBJECT_ID,dm_mid.database_id) + '_'
    + REPLACE(REPLACE(REPLACE(ISNULL(dm_mid.equality_columns,''),', ','_'),'[',''),']','') +
    CASE
    WHEN dm_mid.equality_columns IS NOT NULL AND dm_mid.inequality_columns IS NOT NULL THEN '_'
    ELSE ''
    END
    + REPLACE(REPLACE(REPLACE(ISNULL(dm_mid.inequality_columns,''),', ','_'),'[',''),']','')
    + ']'
    + ' ON ' + dm_mid.statement
    + ' (' + ISNULL (dm_mid.equality_columns,'')
    + CASE WHEN dm_mid.equality_columns IS NOT NULL AND dm_mid.inequality_columns IS NOT NULL THEN ',' ELSE
    '' END
    + ISNULL (dm_mid.inequality_columns, '')
    + ')'
    + ISNULL (' INCLUDE (' + dm_mid.included_columns + ')', '') AS Create_Statement
    FROM    sys.dm_db_missing_index_groups dm_mig
            INNER JOIN sys.dm_db_missing_index_group_stats dm_migs
            ON        dm_migs.group_handle = dm_mig.index_group_handle
            INNER JOIN sys.dm_db_missing_index_details dm_mid
            ON dm_mig.index_handle = dm_mid.index_handle
    WHERE    dm_mid.database_ID = DB_ID()
    ORDER BY 1 desc--Avg_Estimated_Impact DESC
go
-- Missing Index Script
-- Original Author: Pinal Dave (C) 2011
SELECT TOP 25
dm_mid.database_id AS DatabaseID,
dm_migs.avg_user_impact*(dm_migs.user_seeks+dm_migs.user_scans) Avg_Estimated_Impact,
dm_migs.last_user_seek AS Last_User_Seek,
OBJECT_NAME(dm_mid.OBJECT_ID,dm_mid.database_id) AS [TableName],
'CREATE INDEX [IX_' + OBJECT_NAME(dm_mid.OBJECT_ID,dm_mid.database_id) + '_'
+ REPLACE(REPLACE(REPLACE(ISNULL(dm_mid.equality_columns,''),', ','_'),'[',''),']','') +
CASE
WHEN dm_mid.equality_columns IS NOT NULL AND dm_mid.inequality_columns IS NOT NULL THEN '_'
ELSE ''
END
+ REPLACE(REPLACE(REPLACE(ISNULL(dm_mid.inequality_columns,''),', ','_'),'[',''),']','')
+ ']'
+ ' ON ' + dm_mid.statement
+ ' (' + ISNULL (dm_mid.equality_columns,'')
+ CASE WHEN dm_mid.equality_columns IS NOT NULL AND dm_mid.inequality_columns IS NOT NULL THEN ',' ELSE
'' END
+ ISNULL (dm_mid.inequality_columns, '')
+ ')'
+ ISNULL (' INCLUDE (' + dm_mid.included_columns + ')', '') AS Create_Statement
FROM sys.dm_db_missing_index_groups dm_mig
INNER JOIN sys.dm_db_missing_index_group_stats dm_migs
ON dm_migs.group_handle = dm_mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details dm_mid
ON dm_mig.index_handle = dm_mid.index_handle
WHERE dm_mid.database_ID = DB_ID()
ORDER BY Avg_Estimated_Impact DESC
GO

2)
SELECT

Last_Updated    = STATS_DATE(si.id, si.indid)

,TableName      = object_name(si.id)

,Name           = RTRIM(si.name)

,Size           = DATALENGTH (si.statblob)

FROM

sysindexes si WITH (nolock)

WHERE OBJECTPROPERTY(si.id, N'IsUserTable') = 1

--AND INDEXPROPERTY (si.id , si.name , 'IsAutoStatistics' ) = 0

order by last_updated, tablename

3)

if (object_id('tempdb..#IndexStat') is not null) drop table #IndexStat;
SELECT
    --top 10
    db_name(DB_ID()) as DatabaseName
    ,object_name(s.object_id, db_id()) as TableName
    ,i.name as IndexName
    ,s.object_id AS objectid
    ,i.index_id AS indexid
    ,partition_number AS PartitionNumber
    ,avg_fragmentation_in_percent AS ExternalFragmentation
    ,s.page_count
    ,s.record_count
    into  #IndexStat
    FROM    sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL , NULL, 'LIMITED')  s  
            inner join sys.indexes i
            on s.index_id=i.index_id and s.object_id=i.object_id
    WHERE    --avg_fragmentation_in_percent > 5 AND
            s.index_id > 0 and
            s.database_id=DB_ID()
    --order by avg_fragmentation_in_percent desc;

SELECT top 10 * from #IndexStat order by ExternalFragmentation desc;

http://sqlserverplanet.com/dmv-queries/find-table-fragmentation
--
-- table fragmentation
SELECT

Last_Updated    = STATS_DATE(si.id, si.indid)

,TableName      = object_name(si.id)

,Name           = RTRIM(si.name)

,Size           = DATALENGTH (si.statblob)

FROM

sysindexes si WITH (nolock)

WHERE OBJECTPROPERTY(si.id, N'IsUserTable') = 1

--AND INDEXPROPERTY (si.id , si.name , 'IsAutoStatistics' ) = 0

order by last_updated, tablename

Or you can still do it the old fashioned way. Just substitute the name of the table or index below. It should not be in quotes.
–Just the table
DBCC SHOWCONTIG (tablename)
–Table with the index
DBCC SHOWCONTIG (tablename, indexname)
–Do not block anything (run during prod hours)
DBCC SHOWCONTIG (tablename, indexname) WITH FAST –(2005)

Send text message by email

websms.rogers.page.ca/2way

From http://smartcanucks.ca/how-to-send-free-text-messages-to-rogers-fido-telus-bell-virgin-cell-phones-canada/

you can easily send emails to your friends' cell phones directly from your email. You just need to know the number and the carrier of the recipient to start emailing them to their cell phone. Here is a list of the emails of different carriers. You need to replace the part before the "@" with your friend's number.

• Rogers Wireless: [10-digit phone number]@pcs.rogers.com
• Fido: [10-digit phone number]@fido.ca
• Telus: [10-digit phone number]@msg.telus.com
• Bell Mobility: [10-digit phone number]@txt.bell.ca
• Kudo Mobile: [10-digit phone number]@msg.koodomobile.com
• MTS: [10-digit phone number]@text.mtsmobility.com
• President's Choice: [10-digit phone number]@txt.bell.ca
• Sasktel: [10-digit phone number]@sms.sasktel.com
• Solo: [10-digit phone number]@txt.bell.ca
• Virgin: [10-digit phone number]@vmobile.ca

You need to replace the [10-digit phone number] with your friend's number. For example, If your friend's number was 613-123-4567 and their carrier was Rogers, you'd send an email to 6131234567@pcs.rogers.com

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/