Tuesday 21 October 2008

IO-Lock and Latch Wait Times

A script I run every week or so to determine index lock and latch wait times

set arithabort off
set ansi_warnings off
SET ARITHIGNORE ON
select distinct
object_name(ius.object_id) + '.' + i.name, row_lock_wait_in_ms/row_lock_wait_count as 'Average Row Lock Wait', page_lock_wait_in_ms/page_lock_wait_count as 'Average Page Lock Wait', page_io_latch_wait_in_ms/page_io_latch_wait_count as 'Average Page IO Latch Wait', allow_page_locks
from sys.dm_db_index_usage_stats ius
join
sys.dm_db_index_operational_stats(db_id(), NULL, NULL, NULL) ios
on
ius.object_id = ios.object_id
and
ius.index_id = ios.index_id
join sys.indexes i
on
ius.object_id = i.object_id
and
ius.index_id = i.index_id
join sys.sysindexes si
on
ius.object_id = si.id
and
ius.index_id = si.indid
and
ios.object_id = si.id
and
ios.index_id = si.indid
join
sys.data_spaces sds
on
partition_number = sds.data_space_id
where
ius.object_id > 97
and i.name not like 'sys%'
order by 'Average Page IO Latch Wait' desc

IO - Filespace report

Pulled straight out of SSMS :), reports on file space utilization, without having to use the UI, useful if you want to reclaim space, or for checking whether you are close to hitting a manual limit on a file if you prevent automated file growth

set xact_abort off
set ansi_warnings off
set arithignore on

create table #tmpspc (Fileid int, FileGroup int, TotalExtents int, UsedExtents int, Name sysname, FileName nchar(520))
insert #tmpspc EXEC ('dbcc showfilestats')

SELECT
s.file_id AS [ID],
s.name AS [Name],
s.physical_name AS [FileName],
CAST((s.size * CONVERT(float,8)/1024) as decimal(20,0)) AS [Size MB],
CAST(CAST(tspc.UsedExtents*convert(float,64) AS float)/1024 as decimal(20,0)) AS [UsedSpace],
cast(((tspc.UsedExtents*convert(float,64)) /(s.size * CONVERT(float,8)))*100 as decimal(10,0)) as [Percentage Used]
FROM
sys.filegroups AS g
INNER JOIN sys.master_files AS s ON (s.type = 0 and s.database_id = db_id() and (s.drop_lsn IS NULL)) AND (s.data_space_id=g.data_space_id)
INNER JOIN #tmpspc tspc ON tspc.Fileid = s.file_id
ORDER BY
[ID] ASC
--[UsedSpace] DESC
drop table #tmpspc

IO - Most Heavily Updated files and Indexes Snapshot

This was a query written in anger :)
Pulls IO Stats for a snapshot period, combines file IO statistics with index usage statistics, I have used it successfully to look for unused indexes or indexes that are being written to more than read from. changing the following line

while @counter < 1 begin waitfor delay '00:10:00'

you can configure the snapshot interval.

set xact_abort off
SET ANSI_WARNINGS OFF
set arithabort off
set arithignore ON
set nocount on

if exists (select * from tempdb..sysobjects where name like '%dbio') drop table ##dbio

declare @counter int
set @counter = 0
select db_name(Database_id) as DatabaseName, file_name(file_id) as FileName, num_of_reads, num_of_bytes_read, io_stall_read_ms, num_of_writes, io_stall_write_ms
into ##dbio
from sys.dm_io_virtual_file_stats (null, null)
where Database_id = db_id()
while @counter < 1 begin waitfor delay '00:10:00'
insert ##dbio
select db_name(Database_id) as DatabaseName, file_name(file_id) as FileName, num_of_reads, num_of_bytes_read, io_stall_read_ms, num_of_writes, io_stall_write_ms from sys.dm_io_virtual_file_stats (null, null) where Database_id = db_id() set @counter = @counter + 1 end

select
##dbio.DatabaseName,
##dbio.FileName,
max(num_of_reads) - min(num_of_reads) 'Number of Read IOs on the File',
max(io_stall_read_ms) - min(io_stall_read_ms) 'Cumulative Time m/s Waiting for reads',
(max(io_stall_read_ms) - min(io_stall_read_ms))/(max(num_of_reads) - min(num_of_reads)) 'Avg m/s Time Waiting for Reads',
max(num_of_writes) - min(num_of_writes) 'Number of Write IOs on the File',
max(io_stall_write_ms) - min(io_stall_write_ms) 'Cumulative Time m/s Waiting for Writes',
(max(io_stall_write_ms) - min(io_stall_write_ms))/(max(num_of_writes) - min(num_of_writes)) 'Avg m/s Time Waiting for Writes',
f.name 'FileGroup Name',
o.name 'Table Name',
si.name 'Index Name', CASE si.indid WHEN 0 THEN 'HEAP' WHEN 1 THEN 'CLUSTERED' ELSE 'NON-CLUSTERED' END 'Index Type',
si.rowcnt 'No.Rows',
si.rowmodctr 'Number of Changed Rows since Last Stats Update',
cast(cast(si.rowmodctr as decimal)/cast(si.rowcnt as decimal)*100 as decimal (2,1))'% Rows Changes since Last Stats Update',
cast (ios.row_lock_wait_in_ms/ios.row_lock_wait_count as decimal (5,5)) 'AVG Wait m/s for Row Lock',
cast(ios.page_lock_wait_in_ms/ios.page_lock_wait_count as decimal (5,5)) 'AVG Wait m/s for Page Lock',
ios.index_lock_promotion_attempt_count 'No. Lock Escalation Attempts',
ios.index_lock_promotion_count 'No. Successful Lock Escalations',
cast(ios.page_io_latch_wait_in_ms/ios.page_io_latch_wait_count as decimal (2,0)) 'Avg Latch I/O Wait Times',
max(ius.user_seeks) 'Number of User Seeks',
max(ius.user_scans) 'Number of User Scans',
max(ius.user_lookups) 'Number of Bookmark Lookups',
max(user_updates) 'Number of Updates',
cast (stats_date(i.object_id, i.index_id) as smalldatetime) 'Last Time Statistics Updated'
from ##dbio
join sys.sysfiles f on file_idex(##dbio.FileName) = f.fileid
join sys.sysfilegroups fg on f.groupid = fg.groupid
join sys.sysindexes si on fg.groupid = si.groupid
join sys.objects o on si.id = o.object_id
join sys.indexes i on o.object_id = i.object_id and i.object_id = si.id and si.indid = i.index_id
join sys.dm_db_index_operational_stats (db_id(), null, null,null) as ios on o.object_id = ios.object_id and i.index_id = ios.index_id
join sys.dm_db_index_usage_stats ius on ius.object_id = o.object_id and ius.index_id = i.index_id
where i.is_hypothetical = 0 and o.type = 'U'
--and si.name not like 'PK_%'
--and ius.user_seeks = 0
--and ius.user_scans = 0
--and ius.user_lookups = 0
group by DatabaseName, f.name, ##dbio.FileName, o.name , si.indid, si.name, si.rowcnt, si.rowmodctr, i.object_id, i.index_id, ios.row_lock_count,ios.row_lock_wait_in_ms,ios.row_lock_wait_count,ios.page_lock_count,page_lock_wait_in_ms,ios.page_lock_wait_count,index_lock_promotion_attempt_count,ios.index_lock_promotion_count,ios.page_io_latch_wait_count,ios.page_io_latch_wait_in_ms,ios.page_io_latch_wait_count
order by max(user_updates) desc

drop table ##dbio

Print 'The values between File Name and File Group Name are relevant to the capture period'
Print 'The values between to the Right of %Rows changed since last update are valid from when the server was started'

IO - SQL Statements with the heaviest impact

Query I have found really useful in tracking down problem sprocs,could be worth running this at regular intervals and storing the results in a database for further analysis.

Select case when sch.name is null then '' else '['+sch.name+'].' end + s3.name as ObjectName
, (select top 1 substring(text,(s1.statement_start_offset+2)/2, (case when s1.statement_end_offset = -1 then len(convert(nvarchar(max),text))*2 else s1.statement_end_offset end - s1.statement_start_offset) /2 ) from sys.dm_exec_sql_text(s1.sql_handle)) as [SQL Statement]
, execution_count
, plan_generation_num
, last_execution_time
, last_worker_time
, total_logical_reads
, last_logical_reads
, last_physical_reads
, total_logical_writes
, last_logical_writes
, execution_count*last_logical_reads as quotient
from sys.dm_exec_query_stats s1
cross apply sys.dm_exec_sql_text(s1.sql_handle) as s2
inner join sys.objects s3 on ( s2.objectid = s3.object_id )
left outer join sys.schemas sch on(s3.schema_id = sch.schema_id)
where s2.dbid = db_id()
and (select top 1 substring(text,(s1.statement_start_offset+2)/2, (case when s1.statement_end_offset = -1 then len(convert(nvarchar(max),text))*2 else s1.statement_end_offset end - s1.statement_start_offset) /2 ) from sys.dm_exec_sql_text(s1.sql_handle)) like '%TransactionParties%'
and last_execution_time > dateadd(minute,-5, current_timestamp)
order by execution_count*last_logical_reads desc

IO - Whats Pending Now

This script was developed as we were having IO issues on our Transactional Replication subscribers. This provides an easy snapshot into immediate IO requestsm just a little thing I'd keep hitting refresh on. Contains the SQL statement, so assists in tracking down problem queries.

select er.session_id,
object_name(s1.objectid) [Table],
s1.text,
er.logical_reads,
er.reads,
er.writes,
io_type,
er.wait_type,
w.pending_io_count,
oss.pending_disk_io_count,
w.pending_io_byte_count,
su.name
from sys.dm_io_pending_io_requests pir
join sys.dm_os_schedulers oss
on pir.scheduler_address = oss.scheduler_address
join sys.dm_os_workers w on w.worker_address = oss.active_worker_address
join sys.dm_os_tasks t on w.task_address = t.task_address
join sys.dm_exec_requests er on t.session_id = er.session_id
join sys.sysusers su on er.user_id = su.uid
cross apply sys.dm_exec_sql_text(sql_handle) as s1

Largest Index Query

Reports the Index Size (in Rows) and utilization since server startup.

select object_name(ius.object_id) + '.' + i.name, sds.name as 'FileGroup Name', si.rowcnt as 'Number of Rows',
(leaf_insert_count + leaf_delete_count + leaf_update_count) as Leaf_Change_Count,
(nonleaf_insert_count + nonleaf_delete_count + nonleaf_update_count) as Nonleaf_Change_Count,
user_seeks,
user_scans,
range_scan_count,
singleton_lookup_count,
user_lookups,
user_updates
from sys.dm_db_index_usage_stats ius
join
sys.dm_db_index_operational_stats(db_id(), NULL, NULL, NULL) ios
on
ius.object_id = ios.object_id
and
ius.index_id = ios.index_id
join sys.indexes i
on
ius.object_id = i.object_id
and
ius.index_id = i.index_id
join sys.sysindexes si
on
ius.object_id = si.id
and
ius.index_id = si.indid
join
sys.data_spaces sds
on
partition_number = sds.data_space_id
where
ius.object_id > 97
and i.name not like 'sys%'
order by si.rowcnt desc

Percent Complete - sys.sm_exec_requests

You can use this to monitor

* backup / restore
* dbcc checkdb / checktable / etc.
* dbcc shrinkdatabase / shrinkfile
* dbcc indexdefrag
* alter index reorganize
* rollback operations

SELECT percent_complete, (estimated_completion_time/60000) 'Estimated Completion Time in Minutes') FROM SYS.DM_EXEC_REQUESTS
WHERE percent_complete <> 0

Saturday 18 October 2008

Welcome to my world....

Im going to use this place as a place to post up some generic DMV queries that I have developed.
Im a production DBA by day (and often nights), so all this code has been tested and used in high throughput production SQL Server 2005 environments.
Please feel free to point out improvements, certainly please tell me if they are wrong. Many of them were written urgently, on the spur of the moment and in anger.
If you can improve my SQL please go ahead and mail me any improvements.
Otherwise they are there, if you fancy using them or testing them, feel free, its a sharing exercise.