SELECT a.session_id, a.wait_type, a.wait_duration_ms, a.blocking_session_id, a.resource_description, CASE WHEN CAST(RIGHT(a.resource_description, LEN(a.resource_description) - CHARINDEX(':', a.resource_description, 3)) AS INT) - 1 % 8088 = 0 THEN 'Is PFS Page' WHEN CAST(RIGHT(a.resource_description, LEN(a.resource_description) - CHARINDEX(':', a.resource_description, 3)) AS INT) - 2 % 511232 = 0 THEN 'Is GAM Page' WHEN CAST(RIGHT(a.resource_description, LEN(a.resource_description) - CHARINDEX(':', a.resource_description, 3)) AS INT) - 3 % 511232 = 0 THEN 'Is SGAM Page' ELSE 'Is Not PFS, GAM, or SGAM page' END resourcetype, c.text AS SQLText FROM sys.dm_os_waiting_tasks a INNER JOIN sys.sysprocesses b ON a.session_id = b.spid OUTER APPLY sys.dm_exec_sql_text(b.sql_handle) c WHERE a.wait_type LIKE 'PAGE%LATCH_%' AND a.resource_description LIKE '2:%';
SELECT IndexName = i.Name, ColName = c.Name FROM sys.indexes i INNERJOIN sys.index_columns ic ON ic.object_id = i.object_id AND ic.index_id = i.index_id INNERJOIN sys.columns c ON c.object_id = ic.object_id AND c.column_id = ic.column_id WHERE ic.is_included_column =1ORDERBY i.Name
SELECT owner, table_name, TRUNC(sum(bytes) / 1024 / 1024) Meg FROM (SELECT segment_name table_name, owner, bytes FROM dba_segments WHERE segment_type = 'TABLE' UNION ALL SELECT i.table_name, i.owner, s.bytes FROM dba_indexes i, dba_segments s WHERE s.segment_name = i.index_name AND s.owner = i.owner AND s.segment_type = 'INDEX' UNION ALL SELECT l.table_name, l.owner, s.bytes FROM dba_lobs l, dba_segments s WHERE s.segment_name = l.segment_name AND s.owner = l.owner AND s.segment_type = 'LOBSEGMENT' UNION ALL SELECT l.table_name, l.owner, s.bytes FROM dba_lobs l, dba_segments s WHERE s.segment_name = l.index_name AND s.owner = l.owner AND s.segment_type = 'LOBINDEX') WHERE owner in UPPER('&owner') GROUP BY table_name, owner HAVING SUM(bytes) / 1024 / 1024 > 10 /* Ignore really small tables */ ORDER BY SUM(bytes) desc;