En el siguiente webcast Ajuste (tuning) del rendimiento de SQL Server 2008 http://msevents.microsoft.com/CUI/EventDetail.aspx?EventID=1032438450&Culture=es-AR analizamos las herramientas de desempeño de SQL Server 2008 y cómo utilizarlas.
La presentación pueden descargarla de la siguiente dirección http://cid-bf30da8b3fc2e667.skydrive.live.com/self.aspx/Comunidad%20Windows%20Costa%20Rica/WebCast/SQL2008Performance.pdf
Los scripts utilizados en la presentación son los siguientes:
-- memory
SELECT type, SUM(single_pages_kb)
FROM sys.dm_os_memory_clerks
GROUP BY type
ORDER BY SUM(single_pages_kb) DESC
SELECT type, SUM(multi_pages_kb)
FROM sys.dm_os_memory_clerks
WHERE multi_pages_kb != 0
GROUP BY type
ORDER BY SUM(multi_pages_kb) DESC
--querys
select text, query_plan, requested_memory_kb, granted_memory_kb, used_memory_kb from sys.dm_exec_query_memory_grants MG
CROSS APPLY sys.dm_exec_sql_text(sql_handle) t
CROSS APPLY sys.dm_exec_query_plan
(MG.plan_handle)
--buffer pool usage by database
select db_name(database_id) as dbName,
count(*)*8/1024 as BufferPoolMB
from sys.dm_os_buffer_descriptors
group by db_name(database_id)
order by 2 desc
--IO
select session_id, wait_duration_ms,
resource_description
from sys.dm_os_waiting_tasks
where wait_type like 'PAGE%LATCH_%'
and resource_description like '2:%'
--PAGE I/O* Waits
(select * from sys.dm_os_wait_stats
where wait_type like '%PAGEIO%')
--Virtual File Stats
(select * from
sys.dm_io_virtual_file_stats(-1, -1))
select * from sys.dm_exec_sessions
select * from sys. dm_io_pending_io_requests
select * from sys.dm_os_ring_buffers
select * from sys.dm_os_sys_info
select * from sys.dm_os_wait_stats
--CPU
select * from sys.dm_os_ring_buffers
where
ring_buffer_type='RING_BUFFER_RESOURCE_MONITOR'
select * from sys.dm_os_ring_buffers
where
ring_buffer_type='RING_BUFFER_SINGLE_PAGE_ALLOCATOR'
Saludos,
Ing. Eduardo Castro Martínez, PhD – Microsoft SQL Server MVP
Costa Rica
Technorati Tags: SQL Server
LiveJournal Tags: SQL Server
del.icio.us Tags: SQL Server
http://ecastrom.spaces.live.com
http://universosql.blogspot.com
http://todosobresql.blogspot.com
http://todosobresqlserver.wordpress.com
http://mswindowscr.org/blogs/sql/default.aspx
