Monitorizacion y optimizacion de SQL Server Querys (I)

El objetivo que me plantearon es optimizar una base de datos para un programa, que utilizaba en la capa de conexión con los datos la tecnología de Linq -> LinqToSql.
Esta es una tecnología impresionante, pero hace de la optimización del servicio algo complicada, porque casi todo el trabajo se esta haciendo a nivel de codigo, no se utilizan Store Procedure o funciones.
Así pues, durante un tiempo me dedique a monitorizar mediante querys las consultas, observando parámetros que el propio servicio mide durante la gestión de las consultas.
Este es un ejemplo de dichas consultas:

-- Caution!! Fechas en formato AMERICANO

Declare @fechaInicioBusqueda as SmallDatetime;

                set @fechaInicioBusqueda = '1/1/2012';

Declare @fechaFinBusqueda as SmallDatetime;

                set @fechaFinBusqueda  = '1/29/2012';

select * from (

select SUBSTRING(st.TEXT, (qs.statement_start_offset/2)+1 , ((CASE qs.statement_end_offset WHEN -1 THEN datalength(st.TEXT) ELSE qs.statement_end_offset END - qs.statement_start_offset)/2) + 1) AS txt,

                creation_time,

                total_worker_time/execution_count AS AvgCPU,

                total_elapsed_time/execution_count AS AvgDuration,

                total_worker_time,execution_count,total_logical_reads,total_physical_reads,

                max_elapsed_time,min_elapsed_time,last_elapsed_time

                from sys.dm_exec_query_stats qs

                               cross apply sys.dm_exec_sql_text(sql_handle) st) as [tablaP]

                where (txt like 'select%')

                               and (creation_time > @fechaInicioBusqueda)

                               and (creation_time < @fechaFinBusqueda)

                ORDER BY AvgCPU DESC

También analice parte de la arquitectura de la base de datos, sin entran en detalla sobre cada table y sus definiciones, sino partiendo de comprobaciones como buscar tablas que no tengan indices o claves primarias(PK)

SELECT SCHEMA_NAME(o.schema_id) AS [schema]
,object_name(i.object_id ) AS [table]
,p.rows
,user_seeks
,user_scans
,user_lookups
,user_updates
,last_user_seek
,last_user_scan
,last_user_lookup
FROM sys.indexes i
INNER JOIN sys.objects o ON i.object_id = o.object_id
INNER JOIN sys.partitions p ON i.object_id = p.object_id AND i.index_id = p.index_id
LEFT OUTER JOIN sys.dm_db_index_usage_stats ius ON i.object_id = ius.object_id AND i.index_id = ius.index_id
WHERE i.type_desc = 'HEAP'
ORDER BY rows desc

También hay comprobaciones en el lado de los indices como:

1. Estudio de índices no declarados pero necesarios
2. Encontrar índices sin uso
3. Buscar consultas en la caché que les falta un Índice
4. ...

Continuara ...

Posted in: Gestión de proyectos,Programación BD