know your sql server - dmvs
TRANSCRIPT
Know your SQL Server - DMVsVítor Pombeiro
http://netponto.org23ª Reunião Lisboa - 24/09/2011
Patrocinadores desta reunião
Vítor PombeiroDeveloper no Banco Primus.Net 2.0 Web e Windows ApplicationsSQL Server 2000BI
Agenda
• DMVs• CPU• Memória• Índices e Estatísticas• IO
DMVs
• “Dynamic management views and functions return server state information that can be used to monitor the health of a server instance, diagnose problems, and tune performance.” (Books Online)
CPU
CPUselect
scheduler_id, current_tasks_count, runnable_tasks_count -->Valor a verificar
from sys.dm_os_schedulerswhere scheduler_id < 1048576
CPUSELECT TOP 10 (total_worker_time * 1.0) / 1000000 AS Total_Worker_Time_in_Seconds, execution_count, ((total_worker_time * 1.0) / 1000000) / execution_count AS Avg_Worker_Time_in_Seconds, (min_worker_time * 1.0) / 1000000 AS Min_Worker_Time_in_Seconds, (max_worker_time * 1.0) / 1000000 AS Max_Worker_Time_in_Seconds, text FROM sys.dm_exec_query_statsCROSS APPLY sys.dm_exec_sql_text(sql_handle)order by total_worker_time desc
CPUselect top 25
sql_text.text, plan_generation_num, execution_count
from sys.dm_exec_query_stats a cross apply sys.dm_exec_sql_text(sql_handle) as sql_text where plan_generation_num >1 order by plan_generation_num desc
CPUselect
timestamp, convert(xml, record) as info
from sys.dm_os_ring_bufferswhere ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR'and record like '%<SystemHealth>%'
CPU
• Causas de bottleneck no CPU– Estatísticas – “WITH RECOMPILE” nas SPs • OPTION (RECOMPILE)
– Paralelismo OPTION (MAXDOP) hint
Memória
Memóriaselect
text,query_plan,requested_memory_kb,granted_memory_kb,used_memory_kb
from sys.dm_exec_query_memory_grants MGCROSS APPLY sys.dm_exec_sql_text(sql_handle) CROSS APPLY sys.dm_exec_query_plan(MG.plan_handle)
MemóriaSELECT
Getdate() report_time,[cntr_value] as value_sec,cast(@@SERVERNAME as varchar) as ServerName
FROM sys.dm_os_performance_countersWHERE [object_name] LIKE '%Manager%'
AND [counter_name] = 'Page life expectancy'
Memória
• Causas– Configuração do SQL Server– AWE inactivo (32 bits)– Nº sessões activas
Índices e estatísticas
ROOT
Intermediate Intermediate
Leaf Leaf Leaf Leaf
Índices e estatísticasselect
OBJECT_NAME(ps.object_id) as TableName,si.Name ,ps.Avg_Fragmentation_in_percent,STATS_DATE(ss.object_id,ss.stats_id) as LastUpdatedStatistics
from sys.dm_db_index_physical_stats(DB_ID(DB_NAME()),null,null,null,null) psjoin sysindexes si on ps.object_id = si.id
and ps.index_id = si.indidleft outer join sys.stats ss on ss.object_id = ps.object_id
and ss.name = si.namewhere ps.avg_fragmentation_in_percent > 10order by ps.avg_fragmentation_in_percent desc
Índices e estatísticasSELECT TOP 10 [Total Cost] = ROUND(avg_total_user_cost * avg_user_impact * (user_seeks + user_scans),0) , avg_user_impact , statement as TableName , equality_columns , inequality_columns , included_columns FROM sys.dm_db_missing_index_groups g INNER JOIN sys.dm_db_missing_index_group_stats s ON s.group_handle = g.index_group_handle INNER JOIN sys.dm_db_missing_index_details d ON d.index_handle = g.index_handleORDER BY [Total Cost] DESC;
Índices e estatísticasSELECT TOP 10
DB_NAME() as DatabaseName , OBJECT_NAME(s.[object_id]) as TableName ,IndexName = i.name,user_updates, system_updates
FROM sys.dm_db_index_usage_stats s INNER JOIN sys.indexes i ON s.[object_id] = i.[object_id] AND s.index_id = i.index_id WHERE s.database_id = DB_ID() AND OBJECTPROPERTY(s.[object_id], 'IsMsShipped') = 0 AND user_seeks = 0 AND user_scans = 0 AND user_lookups = 0 AND i.name IS NOT NULL -- Ignore HEAP indexes.ORDER BY user_updates DESC
Índices e estatísticas
• Causas– Muitos inserts, updates e deletes– Plano manutenção ineficaz– Excesso ou falta de índices– Shrink
IO
IOSELECT DB_NAME(fs.database_id) AS [Database Name], mf.physical_name, io_stall_read_ms, num_of_reads, CAST(io_stall_read_ms/(1.0 + num_of_reads) AS NUMERIC(10,1)) AS [avg_read_stall_ms],io_stall_write_ms, num_of_writes,CAST(io_stall_write_ms/(1.0+num_of_writes) AS NUMERIC(10,1)) AS [avg_write_stall_ms], io_stall_read_ms + io_stall_write_ms AS [io_stalls], num_of_reads + num_of_writes AS [total_io], CAST((io_stall_read_ms + io_stall_write_ms)/(1.0 + num_of_reads + num_of_writes) AS NUMERIC(10,1)) AS [avg_io_stall_ms]FROM sys.dm_io_virtual_file_stats(null,null) AS fsINNER JOIN sys.master_files AS mf ON fs.database_id = mf.database_id AND fs.[file_id] = mf.[file_id]ORDER BY avg_io_stall_ms DESC OPTION (RECOMPILE);
IOSelect wait_type, waiting_tasks_count, wait_time_ms / 1000 as wait_time_in_secfrom sys.dm_os_wait_stats where wait_type like 'PAGEIOLATCH%' order by wait_type
IO
• tempDB• Índices• Recolha excessiva de dados• Base de dados com problemas no schema• Pouca memória
Questões?
ReferênciasBooks OnLine
– http://msdn.microsoft.com/en-us/library/ms188754.aspx
SQL Server DMV Starter Pack– http://www.red-gate.com/products/dba/sql-monitor/entrypage/dmv
Glenn Berry– http://
www.sqlservercentral.com/blogs/glennberry/archive/tags/DMV/default.aspx– http://sqlserverperformance.wordpress.com
Patrocinadores desta reunião
Próximas reuniões presenciais
• 24/09/2011 – Setembro (2 anos!)• 29/10/2011 – Outubro• 19/11/2011 – Outubro (Coimbra)• 26/11/2011 – Novembro
Reserva estes dias na agenda! :)
