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)]
,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
-- 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 AND [num_of_reads]>0
order by 3,6
go
-- 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 with (nolock)
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='#53FF5DCD'
--#023D5A04
ORDER BY o.create_date
go
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 waiting_tasks_count>0
order by [wait_time_ms]
desc
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
select
scheduler_id,
current_tasks_count,
runnable_tasks_count
from
sys.dm_os_schedulers
where
scheduler_id < 255
select top 50
sum(qs.total_worker_time) as total_cpu_time,
sum(qs.execution_count) as total_execution_count,
count(*) as number_of_statements,
qs.plan_handle
from
sys.dm_exec_query_stats qs
group by qs.plan_handle
order by sum(qs.total_worker_time) desc
sp_whoisactive
select top 50
sum(qs.total_worker_time) as total_cpu_time,
sum(qs.execution_count) as total_execution_count,
count(*) as number_of_statements,
qs.plan_handle
from
sys.dm_exec_query_stats qs inner join dm_exec_text_query_plan qp on
qs.plan_handle=qp.plan_handle
group by qs.plan_handle
order by sum(qs.total_worker_time) desc
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 desc
go
sp_helpfile
sp_whoisactive @get_plans=1
select * from sys.dm_io_pending_io_requests
No comments:
Post a Comment