Los sistemas OLTP se caracterizan por tener un alto número de transacciones pequeñas que incluye inserciones, actualizaciones y borrados de información. A diferencia de los sistemas que generan reportes como los Dataware House donde los CPU's trabajan en paralelo, dividiendo el query en pequeñas piezas, los sistemas OLTP no requieren de paralelismo
A continuación pongo algunos tips que pueden ayudarte a optimizar tu sistema dependiendo del diseño que tengas.
Los sistemas que requieren de un alto número de pequeñas transacciones por segundo pueden mejorarse con lo siguiente:
1. El diseño de la base de datos debe contener un mínimo de índices, debido a que cada insert, update o delete requiere de mantenimiento a los índices.
2. El consumo del CPU puede reducirse reutilizando los planes de ejecución y reduciendo en número de joins en los queries.
3. El IO puede reducirse con un buen diseño de índices, reducir el número de joins en los queries y manteniendo por más tiempo las páginas de datos en la memoria (Page life expectancy)
4. Los problemas de memoria pueden detectarse con el contador Buffer Manager\Page Life expectency. En caso de ser menor a 300 segundos probablemente se tengan problemas de memoria, si es mayor el agregar memoria no va a ayudar a mejorar el desempeño.
5. Los ordenamientos pueden ser eliminados con el uso de índices.
6. Los bloqueos pueden reducirse con un buen diseño de índices y transacciones pequeñas.
7. El uso de paralelismo debe ser evitado, aunque el dividir un query entre varios cpu hace que la transaccion sea más rápida, sacrifica recursos de CPU que pueden ser aprovechados por otros procesos.
Los sistemas que no son OLTP, se caracterizan por utilizar un pequeño número de transacciones, y por lo general realizan consultas muy grandes que requieren muchos recursos.
1. Este tipo de diseños, pueden contener muchos más índices, debido a que el costo generado por los índices solo se paga durante los procesos batch, que insertan información.
2. El reutilizar los planes de ejecución debe ser evitado, pues el no utilizar el plan adecuado es más costoso cuando hablamos de volúmenes de información muy grandes, que el generar su propio plan de ejecución.
3. Los ordenamientos de datos deben ser minimizados con el uso de índices.
4. La fragmentación debe ser monitoreada de cerca ya que puede impactar los planes de ejecución y el IO.
5. Los bloqueos no son muy comunes ya que generalmente los queries son de consultas.
6. El paralelismo es deseado en este tipo de bases de datos.
A continuación anexo algunos scripts que pueden ser utilizados para monitorear una base de datos en SQL Server 2005.
Monitorear lo 50 procesos en paralelo que han consumido más CPU.
SELECT TOP 50 qs.total_worker_time,
qs.total_elapsed_time,
SUBSTRING(qt.text,qs.statement_start_offset/2,
(case when qs.statement_end_offset = -1
then len(convert(nvarchar(max), qt.text)) * 2
else qs.statement_end_offset end -qs.statement_start_offset)/2)
as query_text,
qt.dbid, dbname=db_name(qt.dbid),
qt.objectid,
qs.sql_handle,
qs.plan_handle
FROM sys.dm_exec_query_stats qs
cross apply sys.dm_exec_sql_text(qs.sql_handle) as qt
where qs.total_worker_time > qs.total_elapsed_time
ORDER BY
qs.total_worker_time DESC
Planes de ejecución más utilizados
SELECT TOP 100
qs.sql_handle
,qs.plan_handle
,cp.cacheobjtype
,cp.usecounts
,cp.size_in_bytes
,qs.statement_start_offset
,qs.statement_end_offset
,qt.dbid
,qt.objectid
,qt.text
,SUBSTRING(qt.text,qs.statement_start_offset/2,
(case when qs.statement_end_offset = -1
then len(convert(nvarchar(max), qt.text)) * 2
else qs.statement_end_offset end -qs.statement_start_offset)/2)
as statement
FROM sys.dm_exec_query_stats qs
cross apply sys.dm_exec_sql_text(qs.sql_handle) as qt
inner join sys.dm_exec_cached_plans as cp on qs.plan_handle=cp.plan_handle
where cp.plan_handle=qs.plan_handle
--and qt.dbid = db_id()
ORDER BY [dbid],[Usecounts] DESC
Planes de ejecución menos utilizados
SELECT TOP 50
cp.cacheobjtype
,cp.usecounts
,size=cp.size_in_bytes
,stmt_start=qs.statement_start_offset
,stmt_end=qs.statement_end_offset
,qt.dbid
,qt.objectid
,qt.text
,SUBSTRING(qt.text,qs.statement_start_offset/2,
(case when qs.statement_end_offset = -1
then len(convert(nvarchar(max), qt.text)) * 2
else qs.statement_end_offset end -qs.statement_start_offset)/2)
as statement
,qs.sql_handle
,qs.plan_handle
FROM sys.dm_exec_query_stats qs
cross apply sys.dm_exec_sql_text(qs.sql_handle) as qt
inner join sys.dm_exec_cached_plans as cp on qs.plan_handle=cp.plan_handle
where cp.plan_handle=qs.plan_handle
and qt.dbid is NULL
ORDER BY [usecounts],[statement] asc
Sentencias recompiladas
select top 25
--sql_text.text,
sql_handle,
plan_generation_num,
substring(text,qs.statement_start_offset/2,
(case when qs.statement_end_offset = -1
then len(convert(nvarchar(max), text)) * 2
else qs.statement_end_offset end - qs.statement_start_offset)/2)
as stmt_executing,
execution_count,
dbid,
objectid
from sys.dm_exec_query_stats as qs
Cross apply sys.dm_exec_sql_text(sql_handle) sql_text
where plan_generation_num >1
order by sql_handle, plan_generation_num
Indices con el mayor número de bloqueos
declare @dbid int
select @dbid = db_id()
Select dbid=database_id, objectname=object_name(s.object_id)
, indexname=i.name, i.index_id --, partition_number
, row_lock_count, row_lock_wait_count
, [block %]=cast (100.0 * row_lock_wait_count / (1 + row_lock_count) as numeric(15,2))
, row_lock_wait_in_ms
, [avg row lock waits in ms]=cast (1.0 * row_lock_wait_in_ms / (1 + row_lock_wait_count) as numeric(15,2))
from sys.dm_db_index_operational_stats (@dbid, NULL, NULL, NULL) s
,sys.indexes i
where objectproperty(s.object_id,'IsUserTable') = 1
and i.object_id = s.object_id
and i.index_id = s.index_id
order by row_lock_wait_count desc
Indices que no han sido utilizados desde que levanto la base de datos de SQL Server
USE <database>
go
declare @db varchar(25)
set @db = '<database>'
select object_name(i.object_id), i.name
from sys.indexes i, sys.objects o
where i.index_id NOT IN (select s.index_id
from sys.dm_db_index_usage_stats s
where s.object_id=i.object_id and
i.index_id=s.index_id and
database_id = db_id(@db) )
and o.type = 'U'
and o.object_id = i.object_id
order by object_name(i.object_id) asc
go
Comparar entre planes de ejecución utilizados y re-utilizados
declare @single int, @reused int, @total int
select @single=
sum(case(usecounts)
when 1 then 1
else 0
end),
@reused=
sum(case(usecounts)
when 1 then 0
else 1
end),
@total=count(usecounts)
from sys.dm_exec_cached_plans
select
'Single use plans (usecounts=1)'= @single,
'Re-used plans (usecounts>1)'= @reused,
're-use %'=cast(100.0*@reused / @total as dec(5,2)),
'total usecounts'=@total
select 'single use plan size'=sum(cast(size_in_bytes as bigint))
from sys.dm_exec_cached_plans
where usecounts = 1