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.

 

http://www.microsoft.com/downloads/details.aspx?FamilyID=0CEB6317-0E52-4A25-8AF2-2702C9C21358&displayLang=en

 

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

 image image image image

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/

Note: Cross posted from Eduardo Castro.

Permalink