Optimización de consultas en SQL Azure
Si desea optimizar consultas en SQL Azure, el siguiente artículo provee guías generales de cómo utilizar los Dynamic Management Views que están disponibles en SQL Azure, y cómo éstas pueden ser utilizadas para optimizar las consultas o para detectar las consultas con bajo rendimiento.
Las siguientes son una serie de consultas sobre rendimiento en SQL Azure:
-- IDentificar las recompilaciones excesivas
select top 25
sql_text.text,
sql_handle,
plan_generation_num,
execution_count,
dbid,
objectid
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
-- Planes de ejecución ineficientes:
select
highest_cpu_queries.plan_handle,
highest_cpu_queries.total_worker_time,
q.dbid,
q.objectid,
q.number,
q.encrypted,
q.[text]
from
(select top 50
qs.plan_handle,
qs.total_worker_time
from
sys.dm_exec_query_stats qs
order by qs.total_worker_time desc) as highest_cpu_queries
cross apply sys.dm_exec_sql_text(plan_handle) as q
order by highest_cpu_queries.total_worker_time desc
-- Identificar cuellos de botella de I/O
select top 25
(total_logical_reads/execution_count) as avg_logical_reads,
(total_logical_writes/execution_count) as avg_logical_writes,
(total_physical_reads/execution_count) as avg_phys_reads,
Execution_count,
statement_start_offset as stmt_start_offset,
sql_handle,
plan_handle
from sys.dm_exec_query_stats
order by
(total_logical_reads + total_logical_writes) Desc
Regards,
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
http://citicr.org/blogs/noticias/default.aspx
http://sqlserverpedia.blogspot.com/
Note: Cross posted from Eduardo Castro.Permalink
Comments