tag:blogger.com,1999:blog-74720997612045933102023-06-20T13:30:51.365+01:00SQLMonkey - Dynamic Management View Queries for the massesA place for useful generic Dynamic Management View - DMV queries I use on a day to day basisSQLMonkeyhttp://www.blogger.com/profile/15734985091847555104noreply@blogger.comBlogger8125tag:blogger.com,1999:blog-7472099761204593310.post-62530816331689833882008-10-21T14:34:00.001+01:002008-10-21T14:37:18.313+01:00IO-Lock and Latch Wait TimesA script I run every week or so to determine index lock and latch wait times<br /><br />set arithabort off<br />set ansi_warnings off <br />SET ARITHIGNORE ON <br />select distinct<br />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<br />from sys.dm_db_index_usage_stats ius<br />join <br />sys.dm_db_index_operational_stats(db_id(), NULL, NULL, NULL) ios <br />on <br />ius.object_id = ios.object_id <br />and<br />ius.index_id = ios.index_id<br />join sys.indexes i <br />on <br />ius.object_id = i.object_id <br />and<br />ius.index_id = i.index_id<br />join sys.sysindexes si <br />on <br />ius.object_id = si.id <br />and<br />ius.index_id = si.indid<br />and<br />ios.object_id = si.id <br />and<br />ios.index_id = si.indid<br />join <br />sys.data_spaces sds<br />on<br />partition_number = sds.data_space_id<br />where <br />ius.object_id > 97<br />and i.name not like 'sys%'<br />order by 'Average Page IO Latch Wait' descSQLMonkeyhttp://www.blogger.com/profile/15734985091847555104noreply@blogger.com0tag:blogger.com,1999:blog-7472099761204593310.post-48106922901616509292008-10-21T14:20:00.002+01:002008-10-21T14:23:35.618+01:00IO - Filespace reportPulled 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 <br /><br />set xact_abort off<br />set ansi_warnings off<br />set arithignore on <br /><br />create table #tmpspc (Fileid int, FileGroup int, TotalExtents int, UsedExtents int, Name sysname, FileName nchar(520))<br />insert #tmpspc EXEC ('dbcc showfilestats')<br /><br />SELECT<br />s.file_id AS [ID],<br />s.name AS [Name],<br />s.physical_name AS [FileName],<br />CAST((s.size * CONVERT(float,8)/1024) as decimal(20,0)) AS [Size MB],<br />CAST(CAST(tspc.UsedExtents*convert(float,64) AS float)/1024 as decimal(20,0)) AS [UsedSpace],<br />cast(((tspc.UsedExtents*convert(float,64)) /(s.size * CONVERT(float,8)))*100 as decimal(10,0)) as [Percentage Used]<br />FROM<br />sys.filegroups AS g<br />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)<br />INNER JOIN #tmpspc tspc ON tspc.Fileid = s.file_id<br />ORDER BY<br />[ID] ASC<br />--[UsedSpace] DESC<br />drop table #tmpspcSQLMonkeyhttp://www.blogger.com/profile/15734985091847555104noreply@blogger.com0tag:blogger.com,1999:blog-7472099761204593310.post-58820931076398870172008-10-21T14:09:00.003+01:002008-10-21T14:38:04.106+01:00IO - Most Heavily Updated files and Indexes SnapshotThis was a query written in anger :)<br />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<br /><br />while @counter < 1 begin waitfor delay <span style="font-weight:bold;">'00:10:00'</span><br /><br />you can configure the snapshot interval. <br /><br />set xact_abort off<br />SET ANSI_WARNINGS OFF<br />set arithabort off<br />set arithignore ON<br />set nocount on <br /><br />if exists (select * from tempdb..sysobjects where name like '%dbio') drop table ##dbio <br /><br />declare @counter int<br />set @counter = 0<br />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 <br />into ##dbio <br />from sys.dm_io_virtual_file_stats (null, null) <br />where Database_id = db_id() <br />while @counter < 1 begin waitfor delay '00:10:00'<br />insert ##dbio<br />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<br /><br />select<br />##dbio.DatabaseName,<br />##dbio.FileName,<br />max(num_of_reads) - min(num_of_reads) 'Number of Read IOs on the File',<br />max(io_stall_read_ms) - min(io_stall_read_ms) 'Cumulative Time m/s Waiting for reads',<br />(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', <br />max(num_of_writes) - min(num_of_writes) 'Number of Write IOs on the File',<br />max(io_stall_write_ms) - min(io_stall_write_ms) 'Cumulative Time m/s Waiting for Writes',<br />(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',<br />f.name 'FileGroup Name', <br />o.name 'Table Name', <br />si.name 'Index Name', CASE si.indid WHEN 0 THEN 'HEAP' WHEN 1 THEN 'CLUSTERED' ELSE 'NON-CLUSTERED' END 'Index Type', <br />si.rowcnt 'No.Rows', <br />si.rowmodctr 'Number of Changed Rows since Last Stats Update', <br />cast(cast(si.rowmodctr as decimal)/cast(si.rowcnt as decimal)*100 as decimal (2,1))'% Rows Changes since Last Stats Update', <br />cast (ios.row_lock_wait_in_ms/ios.row_lock_wait_count as decimal (5,5)) 'AVG Wait m/s for Row Lock', <br />cast(ios.page_lock_wait_in_ms/ios.page_lock_wait_count as decimal (5,5)) 'AVG Wait m/s for Page Lock', <br />ios.index_lock_promotion_attempt_count 'No. Lock Escalation Attempts', <br />ios.index_lock_promotion_count 'No. Successful Lock Escalations', <br />cast(ios.page_io_latch_wait_in_ms/ios.page_io_latch_wait_count as decimal (2,0)) 'Avg Latch I/O Wait Times',<br />max(ius.user_seeks) 'Number of User Seeks',<br />max(ius.user_scans) 'Number of User Scans',<br />max(ius.user_lookups) 'Number of Bookmark Lookups',<br />max(user_updates) 'Number of Updates',<br />cast (stats_date(i.object_id, i.index_id) as smalldatetime) 'Last Time Statistics Updated'<br />from ##dbio<br />join sys.sysfiles f on file_idex(##dbio.FileName) = f.fileid <br />join sys.sysfilegroups fg on f.groupid = fg.groupid <br />join sys.sysindexes si on fg.groupid = si.groupid <br />join sys.objects o on si.id = o.object_id <br />join sys.indexes i on o.object_id = i.object_id and i.object_id = si.id and si.indid = i.index_id <br />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 <br />join sys.dm_db_index_usage_stats ius on ius.object_id = o.object_id and ius.index_id = i.index_id <br />where i.is_hypothetical = 0 and o.type = 'U'<br />--and si.name not like 'PK_%'<br />--and ius.user_seeks = 0<br />--and ius.user_scans = 0 <br />--and ius.user_lookups = 0<br />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<br />order by max(user_updates) desc<br /><br />drop table ##dbio<br /><br />Print 'The values between File Name and File Group Name are relevant to the capture period'<br />Print 'The values between to the Right of %Rows changed since last update are valid from when the server was started'SQLMonkeyhttp://www.blogger.com/profile/15734985091847555104noreply@blogger.com0tag:blogger.com,1999:blog-7472099761204593310.post-23007812524828489422008-10-21T14:02:00.002+01:002008-10-21T14:06:40.628+01:00IO - SQL Statements with the heaviest impactQuery 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. <br /><br />Select case when sch.name is null then '' else '['+sch.name+'].' end + s3.name as ObjectName<br />, (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]<br />, execution_count<br />, plan_generation_num<br />, last_execution_time<br />, last_worker_time<br />, total_logical_reads<br />, last_logical_reads<br />, last_physical_reads<br />, total_logical_writes<br />, last_logical_writes<br />, execution_count*last_logical_reads as quotient<br />from sys.dm_exec_query_stats s1 <br />cross apply sys.dm_exec_sql_text(s1.sql_handle) as s2 <br />inner join sys.objects s3 on ( s2.objectid = s3.object_id ) <br />left outer join sys.schemas sch on(s3.schema_id = sch.schema_id) <br />where s2.dbid = db_id() <br />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%'<br />and last_execution_time > dateadd(minute,-5, current_timestamp)<br />order by execution_count*last_logical_reads descSQLMonkeyhttp://www.blogger.com/profile/15734985091847555104noreply@blogger.com0tag:blogger.com,1999:blog-7472099761204593310.post-34951492013605694462008-10-21T13:56:00.002+01:002008-10-21T14:02:34.487+01:00IO - Whats Pending NowThis 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. <br /><br />select er.session_id, <br />object_name(s1.objectid) [Table], <br />s1.text, <br />er.logical_reads, <br />er.reads,<br />er.writes,<br />io_type,<br />er.wait_type, <br />w.pending_io_count, <br />oss.pending_disk_io_count, <br />w.pending_io_byte_count, <br />su.name<br />from sys.dm_io_pending_io_requests pir <br />join sys.dm_os_schedulers oss <br />on pir.scheduler_address = oss.scheduler_address <br />join sys.dm_os_workers w on w.worker_address = oss.active_worker_address <br />join sys.dm_os_tasks t on w.task_address = t.task_address <br />join sys.dm_exec_requests er on t.session_id = er.session_id <br />join sys.sysusers su on er.user_id = su.uid<br />cross apply sys.dm_exec_sql_text(sql_handle) as s1SQLMonkeyhttp://www.blogger.com/profile/15734985091847555104noreply@blogger.com0tag:blogger.com,1999:blog-7472099761204593310.post-22199987365516867142008-10-21T12:43:00.005+01:002008-10-21T14:18:20.975+01:00Largest Index QueryReports the Index Size (in Rows) and utilization since server startup. <br /><br />select object_name(ius.object_id) + '.' + i.name, sds.name as 'FileGroup Name', si.rowcnt as 'Number of Rows', <br />(leaf_insert_count + leaf_delete_count + leaf_update_count) as Leaf_Change_Count, <br />(nonleaf_insert_count + nonleaf_delete_count + nonleaf_update_count) as Nonleaf_Change_Count, <br />user_seeks, <br />user_scans, <br />range_scan_count, <br />singleton_lookup_count, <br />user_lookups, <br />user_updates<br />from sys.dm_db_index_usage_stats ius<br />join <br />sys.dm_db_index_operational_stats(db_id(), NULL, NULL, NULL) ios <br />on <br />ius.object_id = ios.object_id <br />and<br />ius.index_id = ios.index_id<br />join sys.indexes i <br />on <br />ius.object_id = i.object_id <br />and<br />ius.index_id = i.index_id<br />join sys.sysindexes si <br />on <br />ius.object_id = si.id <br />and<br />ius.index_id = si.indid<br />join <br />sys.data_spaces sds<br />on<br />partition_number = sds.data_space_id<br />where <br />ius.object_id > 97<br />and i.name not like 'sys%'<br />order by si.rowcnt descSQLMonkeyhttp://www.blogger.com/profile/15734985091847555104noreply@blogger.com0tag:blogger.com,1999:blog-7472099761204593310.post-60210105648307442342008-10-21T12:43:00.002+01:002008-10-21T13:55:18.367+01:00Percent Complete - sys.sm_exec_requestsYou can use this to monitor <br /><br /> * backup / restore<br /> * dbcc checkdb / checktable / etc.<br /> * dbcc shrinkdatabase / shrinkfile<br /> * dbcc indexdefrag<br /> * alter index reorganize<br /> * rollback operations<br /><br />SELECT percent_complete, (estimated_completion_time/60000) 'Estimated Completion Time in Minutes') FROM SYS.DM_EXEC_REQUESTS <br />WHERE percent_complete <> 0SQLMonkeyhttp://www.blogger.com/profile/15734985091847555104noreply@blogger.com0tag:blogger.com,1999:blog-7472099761204593310.post-275191314574673732008-10-18T12:33:00.000+01:002008-10-18T12:44:41.639+01:00Welcome to my world....Im going to use this place as a place to post up some generic DMV queries that I have developed.<br />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.<br />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.<br />If you can improve my SQL please go ahead and mail me any improvements.<br />Otherwise they are there, if you fancy using them or testing them, feel free, its a sharing exercise.SQLMonkeyhttp://www.blogger.com/profile/15734985091847555104noreply@blogger.com0