22,206
社区成员
发帖
与我相关
我的任务
分享
DECLARE userobj_cursor CURSOR FOR
select
sys.schemas.name + '.' + sys.objects.name
from sys.objects, sys.schemas
where object_id > 100 and
type_desc = 'USER_TABLE'and
sys.objects.schema_id = sys.schemas.schema_id
go
open userobj_cursor
go
declare @name varchar(256)
fetch userobj_cursor into @name
while (@@FETCH_STATUS = 0)
begin
exec sp_spaceused @objname = @name
fetch userobj_cursor into @name
end
close userobj_cursor
--internal objects:
SELECT
t1.session_id,
(t1.internal_objects_alloc_page_count + task_alloc) as allocated,
(t1.internal_objects_dealloc_page_count + task_dealloc) as
deallocated
from sys.dm_db_session_space_usage as t1,
(select session_id,
sum(internal_objects_alloc_page_count)
as task_alloc,
sum (internal_objects_dealloc_page_count) as
task_dealloc
from sys.dm_db_task_space_usage group by session_id) as t2
where t1.session_id = t2.session_id and t1.session_id >50
order by allocated DESC
SELECT @@VERSION
SELECT * ,
cntr_value * 1.0 / 1024 / 1024 AS Memory_GB
FROM sys.dm_os_performance_counters
WHERE ( [OBJECT_NAME] LIKE '%Memory Manager%' )
AND counter_name IN ( 'Target Server Memory (KB)',
'Total Server Memory (KB)' )
SELECT SUM(single_pages_kb) * 1.0 / 1024 AS total_single_pages_MB ,
SUM(multi_pages_kb) * 1.0 / 1024 AS total_multi_pages_MB ,
SUM(virtual_memory_reserved_kb) * 1.0 / 1024 AS total_virtual_memory_reserved_MB ,
SUM(virtual_memory_committed_kb) * 1.0 / 1024 AS total_virtual_memory_committed_MB ,
SUM(awe_allocated_kb) * 1.0 / 1024 AS total_awe_allocated_MB ,
SUM(shared_memory_reserved_kb) * 1.0 / 1024 AS total_shared_memory_reserved_MB ,
SUM(shared_memory_committed_kb) * 1.0 / 1024 AS total_shared_memory_committed_MB
FROM sys.dm_os_memory_clerks
SELECT a.name DBName ,
SUM(CAST(b.size * 8.0 / 1024 / 1024 AS NUMERIC(20, 3))) [FileSize(GB)]
FROM sys.databases a
INNER JOIN sys.master_files b ON a.database_id = b.database_id
GROUP BY a.name
ORDER BY [FileSize(GB)] DESC
SELECT CASE database_id
WHEN 32767 THEN 'ResourceDb'
ELSE DB_NAME(database_id) END AS Database_name ,
COUNT(*) AS cached_pages_count ,
COUNT(*) * 8.0 / 1024 / 1024 AS cached_pages_count_inSize_GB
FROM sys.dm_os_buffer_descriptors
GROUP BY DB_NAME(database_id),
database_id
ORDER BY cached_pages_count DESC;
SELECT TOP 50
[Wait type] = wait_type,
[Total_Wait time (s)] = wait_time_ms / 1000,
[Max_Wait_time (s)] = max_wait_time_ms /1000,
[% waiting] = CONVERT(DECIMAL(12,2), wait_time_ms * 100.0
/ SUM(wait_time_ms) OVER())
FROM sys.dm_os_wait_stats
WHERE wait_type NOT LIKE '%SLEEP%'
and wait_type NOT IN
('KSOURCE_WAKEUP', 'SLEEP_BPOOL_FLUSH', 'BROKER_TASK_STOP',
'XE_TIMER_EVENT', 'XE_DISPATCHER_WAIT', 'FT_IFTS_SCHEDULER_IDLE_WAIT',
'SQLTRACE_BUFFER_FLUSH', 'CLR_AUTO_EVENT', 'BROKER_EVENTHANDLER',
'LAZYWRITER_SLEEP', 'BAD_PAGE_PROCESS', 'BROKER_TRANSMITTER',
'CHECKPOINT_QUEUE', 'DBMIRROR_EVENTS_QUEUE', 'LAZYWRITER_SLEEP',
'ONDEMAND_TASK_QUEUE', 'REQUEST_FOR_DEADLOCK_SEARCH', 'LOGMGR_QUEUE',
'SLEEP_TASK', 'SQLTRACE_BUFFER_FLUSH', 'CLR_MANUAL_EVENT',
'BROKER_RECEIVE_WAITFOR', 'PREEMPTIVE_OS_GETPROCADDRESS',
'PREEMPTIVE_OS_AUTHENTICATIONOPS', 'BROKER_TO_FLUSH')
ORDER BY wait_time_ms DESC;