Download - Know your SQL Server - DMVs
![Page 1: Know your SQL Server - DMVs](https://reader033.vdocuments.us/reader033/viewer/2022060107/55496ca8b4c905525e8b4d43/html5/thumbnails/1.jpg)
Know your SQL Server - DMVsVítor Pombeiro
http://netponto.org23ª Reunião Lisboa - 24/09/2011
![Page 3: Know your SQL Server - DMVs](https://reader033.vdocuments.us/reader033/viewer/2022060107/55496ca8b4c905525e8b4d43/html5/thumbnails/3.jpg)
Patrocinadores desta reunião
![Page 4: Know your SQL Server - DMVs](https://reader033.vdocuments.us/reader033/viewer/2022060107/55496ca8b4c905525e8b4d43/html5/thumbnails/4.jpg)
Vítor PombeiroDeveloper no Banco Primus.Net 2.0 Web e Windows ApplicationsSQL Server 2000BI
![Page 5: Know your SQL Server - DMVs](https://reader033.vdocuments.us/reader033/viewer/2022060107/55496ca8b4c905525e8b4d43/html5/thumbnails/5.jpg)
Agenda
• DMVs• CPU• Memória• Índices e Estatísticas• IO
![Page 6: Know your SQL Server - DMVs](https://reader033.vdocuments.us/reader033/viewer/2022060107/55496ca8b4c905525e8b4d43/html5/thumbnails/6.jpg)
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)
![Page 7: Know your SQL Server - DMVs](https://reader033.vdocuments.us/reader033/viewer/2022060107/55496ca8b4c905525e8b4d43/html5/thumbnails/7.jpg)
CPU
![Page 8: Know your SQL Server - DMVs](https://reader033.vdocuments.us/reader033/viewer/2022060107/55496ca8b4c905525e8b4d43/html5/thumbnails/8.jpg)
CPUselect
scheduler_id, current_tasks_count, runnable_tasks_count -->Valor a verificar
from sys.dm_os_schedulerswhere scheduler_id < 1048576
![Page 9: Know your SQL Server - DMVs](https://reader033.vdocuments.us/reader033/viewer/2022060107/55496ca8b4c905525e8b4d43/html5/thumbnails/9.jpg)
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
![Page 10: Know your SQL Server - DMVs](https://reader033.vdocuments.us/reader033/viewer/2022060107/55496ca8b4c905525e8b4d43/html5/thumbnails/10.jpg)
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
![Page 11: Know your SQL Server - DMVs](https://reader033.vdocuments.us/reader033/viewer/2022060107/55496ca8b4c905525e8b4d43/html5/thumbnails/11.jpg)
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>%'
![Page 12: Know your SQL Server - DMVs](https://reader033.vdocuments.us/reader033/viewer/2022060107/55496ca8b4c905525e8b4d43/html5/thumbnails/12.jpg)
CPU
• Causas de bottleneck no CPU– Estatísticas – “WITH RECOMPILE” nas SPs • OPTION (RECOMPILE)
– Paralelismo OPTION (MAXDOP) hint
![Page 13: Know your SQL Server - DMVs](https://reader033.vdocuments.us/reader033/viewer/2022060107/55496ca8b4c905525e8b4d43/html5/thumbnails/13.jpg)
Memória
![Page 14: Know your SQL Server - DMVs](https://reader033.vdocuments.us/reader033/viewer/2022060107/55496ca8b4c905525e8b4d43/html5/thumbnails/14.jpg)
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)
![Page 15: Know your SQL Server - DMVs](https://reader033.vdocuments.us/reader033/viewer/2022060107/55496ca8b4c905525e8b4d43/html5/thumbnails/15.jpg)
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'
![Page 16: Know your SQL Server - DMVs](https://reader033.vdocuments.us/reader033/viewer/2022060107/55496ca8b4c905525e8b4d43/html5/thumbnails/16.jpg)
Memória
• Causas– Configuração do SQL Server– AWE inactivo (32 bits)– Nº sessões activas
![Page 17: Know your SQL Server - DMVs](https://reader033.vdocuments.us/reader033/viewer/2022060107/55496ca8b4c905525e8b4d43/html5/thumbnails/17.jpg)
Índices e estatísticas
ROOT
Intermediate Intermediate
Leaf Leaf Leaf Leaf
![Page 18: Know your SQL Server - DMVs](https://reader033.vdocuments.us/reader033/viewer/2022060107/55496ca8b4c905525e8b4d43/html5/thumbnails/18.jpg)
Í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
![Page 19: Know your SQL Server - DMVs](https://reader033.vdocuments.us/reader033/viewer/2022060107/55496ca8b4c905525e8b4d43/html5/thumbnails/19.jpg)
Í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;
![Page 20: Know your SQL Server - DMVs](https://reader033.vdocuments.us/reader033/viewer/2022060107/55496ca8b4c905525e8b4d43/html5/thumbnails/20.jpg)
Í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
![Page 21: Know your SQL Server - DMVs](https://reader033.vdocuments.us/reader033/viewer/2022060107/55496ca8b4c905525e8b4d43/html5/thumbnails/21.jpg)
Índices e estatísticas
• Causas– Muitos inserts, updates e deletes– Plano manutenção ineficaz– Excesso ou falta de índices– Shrink
![Page 22: Know your SQL Server - DMVs](https://reader033.vdocuments.us/reader033/viewer/2022060107/55496ca8b4c905525e8b4d43/html5/thumbnails/22.jpg)
IO
![Page 23: Know your SQL Server - DMVs](https://reader033.vdocuments.us/reader033/viewer/2022060107/55496ca8b4c905525e8b4d43/html5/thumbnails/23.jpg)
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);
![Page 24: Know your SQL Server - DMVs](https://reader033.vdocuments.us/reader033/viewer/2022060107/55496ca8b4c905525e8b4d43/html5/thumbnails/24.jpg)
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
![Page 25: Know your SQL Server - DMVs](https://reader033.vdocuments.us/reader033/viewer/2022060107/55496ca8b4c905525e8b4d43/html5/thumbnails/25.jpg)
IO
• tempDB• Índices• Recolha excessiva de dados• Base de dados com problemas no schema• Pouca memória
![Page 26: Know your SQL Server - DMVs](https://reader033.vdocuments.us/reader033/viewer/2022060107/55496ca8b4c905525e8b4d43/html5/thumbnails/26.jpg)
Questões?
![Page 27: Know your SQL Server - DMVs](https://reader033.vdocuments.us/reader033/viewer/2022060107/55496ca8b4c905525e8b4d43/html5/thumbnails/27.jpg)
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
![Page 29: Know your SQL Server - DMVs](https://reader033.vdocuments.us/reader033/viewer/2022060107/55496ca8b4c905525e8b4d43/html5/thumbnails/29.jpg)
Patrocinadores desta reunião
![Page 30: Know your SQL Server - DMVs](https://reader033.vdocuments.us/reader033/viewer/2022060107/55496ca8b4c905525e8b4d43/html5/thumbnails/30.jpg)
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! :)