Ajuste (tuning) del rendimiento de SQL Server 2008

- February 23, 2010

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

View more presentations from Eduardo Castro.

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'