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/