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

http://mswindowscr.org

http://comunidadwindows.org

Costa Rica

Technorati Tags: SQL Server

LiveJournal Tags: SQL Server

del.icio.us Tags: SQL Server

http://ecastrom.blogspot.com

https://ecastrom.wordpress.com

http://ecastrom.spaces.live.com

http://universosql.blogspot.com

http://todosobresql.blogspot.com

http://todosobresqlserver.wordpress.com

http://mswindowscr.org/blogs/sql/default.aspx

http://citicr.org/blogs/noticias/default.aspx

http://sqlserverpedia.blogspot.com/