Critical5
High12
Medium4
Low1

Hallazgos

Umbrales de un DBA: no se listan indices chicos como fragmentados, no se propone borrar un indice por una sola escritura y no se trata una espera de 0 ms como bloqueo. Este archivo no aplico ningun cambio.

SeveridadAreaHallazgoDetalle
CriticalConfiguracionAUTO_CLOSE esta ONCierra la base al quedar sin sesiones y tira planes y cache.
CriticalConfiguracionAUTO_SHRINK esta ONEncoge y vuelve a crecer el archivo. Fragmenta datos y log.
CriticalConfiguracionPAGE_VERIFY no es CHECKSUMValor: NONE. Paginas rotas pueden pasar sin detectarse.
CriticalDurabilidadDELAYED_DURABILITY = FORCEDUn apagon puede perder transacciones ya confirmadas por la aplicacion.
CriticalEstadisticasEstadisticas automaticas desactivadasauto_create=True, auto_update=False. Encenderlas no refresca las que ya estan viejas.
HighAlmacenamientoCrecimiento riesgoso en DBA_BICrecimiento 1MB, tamano 512 MB. En data, el crecimiento en porcentaje o menor a 64 MB provoca autogrow seguidos.
HighAlmacenamientoCrecimiento riesgoso en DBA_BI_logCrecimiento 1MB, tamano 343 MB. En el log, crecer de a pocos MB crea muchos VLF.
HighBackupEl ultimo full tiene mas de 7 diasUltimo full: 09/10/2026 17:31:01.
HighBackupRecovery FULL sin backup de log recienteEn FULL, sin backup de log el archivo de log crece y el RPO es el ultimo full o diferencial.
HighBloqueosHay bloqueo en vivo de 15 segundos o mas1 solicitud(es). Cabeza visible en blocking_session_id. No mate la sesion desde este informe.
HighEstadisticasEstadistica vieja lab.CdbFrag.CX_CdbFrag_Sort80000 cambios sobre 120000 filas (66.7%).
HighEstadisticasEstadistica vieja lab.CdbStale.IX_CdbStale_Code2000 cambios sobre 2000 filas (100.0%).
HighIndicesPosible indice faltante en [DBA_BI].[lab].[CdbSeek]Impacto 99.6%, seeks=300. Igualdad: [CustomerId]. Desigualdad: [OrderDate].
HighMantenimientoIndice fragmentado lab.CdbFrag.CX_CdbFrag_Sort99.1% en 26483 paginas. Accion sugerida: REBUILD.
HighOptimizadorLegacy cardinality estimator ONFuerza el estimador anterior a SQL 2014. Puede empeorar planes nuevos.
HighParalelismoCost threshold for parallelism demasiado bajoValor actual 5. Consultas pequenas se van en paralelo. Un punto de partida habitual es 50, medido con el workload.
HighSeguridadTRUSTWORTHY esta ONPermite elevar privilegios desde modulos de esta base. Dejelo OFF salvo un requisito auditado.
MediumLogVLF elevados221 VLF.
MediumMemoriamax server memory no deja margen al sistema operativomax server memory=2147483647 MB y la RAM fisica es 3960 MB.
MediumParalelismoMAXDOP de la base = 1El paralelismo esta apagado solo en esta base. Confirme que es intencional.
MediumQuery StoreQuery Store captura ALLEn una base ocupada ALL llena el almacen con consultas de un solo uso. AUTO es mas seguro.
LowPlan cacheoptimize for ad hoc workloads esta apagadoCon mucho SQL ad hoc el cache guarda planes de un solo uso. La opcion guarda solo el stub en el primer uso.

Configuracion de instancia

namevalue_in_usedescription
backup checksum default0Enable checksum of backups by default
backup compression default0Enable compression of backups by default
cost threshold for parallelism5cost threshold for parallelism
fill factor (%)0Default fill factor percentage
max degree of parallelism2maximum degree of parallelism
max server memory (MB)2147483647Maximum size of server memory (MB)
max worker threads0Maximum worker threads
min server memory (MB)16Minimum size of server memory (MB)
optimize for ad hoc workloads0When this option is set, plan cache size is further reduced for single-use adhoc OLTP workload.
remote query timeout (s)600remote query timeout

Base de datos

namestate_descrecovery_model_desccompatibility_levelcollation_namepage_verify_option_descis_auto_close_onis_auto_shrink_onis_auto_create_stats_onis_auto_update_stats_onis_auto_update_stats_async_onis_trustworthy_onis_read_committed_snapshot_onis_parameterization_forcedlog_reuse_wait_descdelayed_durability_descuser_access_desc
DBA_BIONLINEFULL160SQL_Latin1_General_CP1_CI_ASNONETrueTrueTrueFalseFalseTrueFalseFalseLOG_BACKUPFORCEDMULTI_USER

Configuracion con alcance de base

namevaluevalue_for_secondary
ACCELERATED_PLAN_FORCINGTrue
LEGACY_CARDINALITY_ESTIMATIONTrue
MAXDOP1
PARAMETER_SNIFFINGTrue
QUERY_OPTIMIZER_HOTFIXESFalse

Esperas de la instancia (desde el ultimo reinicio)

EsperaTareasEspera_recurso_msEspera_signal_msPct_recurso
SOS_WORK_DISPATCHER638441266028320218682499,98
WRITELOG6626717267438660,01
PWAIT_ALL_COMPONENTS_INITIALIZED381938100,00
PAGEIOLATCH_SH1592470439690,00
ASYNC_IO_COMPLETION735469940,00
BACKUPIO3653471680,00
LCK_M_X16633175440,00
IO_COMPLETION138228731500,00
BACKUPBUFFER40328296640,00
PREEMPTIVE_OS_QUERYREGISTRY3259312633400,00
LCK_M_S162019120,00
PREEMPTIVE_OS_WRITEFILE31541254600,00
PAGEIOLATCH_EX6479353530,00
PREEMPTIVE_OS_FLUSHFILEBUFFERS243827400,00
CXCONSUMER1256562730,00
PREEMPTIVE_OS_FILEOPS1144531900,00
PREEMPTIVE_OS_AUTHENTICATIONOPS38844525100,00
MSQL_XP4482477600,00
WRITE_COMPLETION1053816360,00
THREADPOOL61324300,00

Acumulado desde el arranque de SQL. No es una ventana de una hora como el AWR. CXCONSUMER, solo, no es un problema de paralelismo.

Query Store

actual_state_descdesired_state_descreadonly_reasoncurrent_storage_size_mbmax_storage_size_mbquery_capture_mode_descstale_query_threshold_dayssize_based_cleanup_mode_desc
READ_WRITEREAD_WRITE061000ALL30AUTO

Query Store - SQL por duracion total

query_idejecucionesduracion_total_mscpu_total_mslecturas_logicassql_text
18213,0032.337,550,4115,00(@1 tinyint)UPDATE [lab].[CdbSeek] set [Amount] = [Amount] WHERE [OrderId]=@1
18,004.803,959.540,63224.544,00SELECT SUM(CHECKSUM(Payload)) AS payload_checksum FROM lab.CdbFrag WHERE Payload LIKE '%Q%'
1816300,002.099,261.149,95347.700,00(@c int)SELECT SUM(Amount) AS total_amount FROM lab.CdbSeek WHERE CustomerId = @c AND OrderDate >= '2024-06-01'
1845300,001.701,251.141,80347.700,00(@c int,@sink decimal(18,2))SELECT @sink = SUM(Amount) FROM lab.CdbSeek WHERE CustomerId = @c AND OrderDate >= '2024-06-01'
2280,001.042,30986,95324.520,00(@c int)SELECT SUM(Amount) AS total_amount FROM lab.CdbSeek WHERE CustomerId = @c AND OrderDate >= '2024-06-01'
18401,00683,1810,37224,00SELECT TOP 1 CONVERT(decimal(18,1), avg_fragmentation_in_percent) AS frag, page_count FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID('lab.CdbFrag'), NULL, NULL, 'LIMITED') WHERE page_count >= 1000
18321,00602,9325,448.332,00SELECT TOP (25) OBJECT_SCHEMA_NAME(ips.object_id) AS esquema, OBJECT_NAME(ips.object_id) AS tabla, i.name AS indice, CONVERT(decimal(18,1), ips.avg_fragmentation_in_percent) AS frag_pct, ips.page_count AS
18271,00344,36240,2011.055,00SELECT TOP (15) q.query_id, CONVERT(decimal(18,2), SUM(rs.count_executions)) AS ejecuciones, CONVERT(decimal(18,2), SUM(rs.avg_duration * rs.count_executions) / 1000.0) AS duracion_total_ms, CONVERT(decimal(1
18051,00284,41284,3927.166,00SELECT (SELECT COUNT(*) FROM lab.CdbFrag) AS frag_rows, (SELECT COUNT(*) FROM lab.CdbSeek) AS seek_rows, (SELECT COUNT(*) FROM sys.dm_db_log_info(DB_ID())) AS vlf_count, (SELECT TOP (1) CONVERT(decima
18081,0066,500,88148,00SELECT s.name, sp.rows, sp.modification_counter FROM sys.stats s CROSS APPLY sys.dm_db_stats_properties(s.object_id, s.stats_id) sp WHERE s.object_id = OBJECT_ID('lab.CdbStale')
18171,0048,5648,560,00SELECT TOP (5) mid.statement AS objeto, migs.user_seeks AS seeks, CONVERT(decimal(18,1), migs.avg_user_impact) AS impacto FROM sys.dm_db_missing_index_groups mig JOIN sys.dm_db_missing_index_group_stats migs ON migs.grou
18041,0033,8833,8812,00UPDATE msdb.dbo.backupset SET backup_start_date = DATEADD(day, -12, GETDATE()), backup_finish_date = DATEADD(day, -12, GETDATE()) WHERE database_name = N'DBA_BI' AND type = 'D' AND backup_set_id = ( S
18311,0029,5927,98179,00SELECT TOP (25) OBJECT_SCHEMA_NAME(i.object_id) AS esquema, OBJECT_NAME(i.object_id) AS tabla, i.name AS indice, ISNULL(s.user_updates, 0) AS escrituras FROM sys.indexes i LEFT JOIN sys.dm_db_index_usage_st
18281,0026,1224,431.369,00SELECT TOP (15) qs.execution_count AS ejecuciones, CONVERT(decimal(18,2), qs.total_elapsed_time / 1000.0) AS total_ms, CONVERT(decimal(18,2), qs.total_worker_time / 1000.0) AS cpu_ms, qs.total_logical_reads A
1819130,006,355,423.386,00(@u int)UPDATE lab.CdbWriteOnly SET A = A + 1, B = B + 1, C = C + 1, D = D + 1, E = E + 1, F = F + 1 WHERE Id = ((@u % 200) + 1)

Plan cache - SQL por tiempo transcurrido

ejecucionestotal_mscpu_mslecturas_logicaslecturas_fisicassql_text
1298,56228,3911228217SELECT TOP (15) q.query_id, CONVERT(decimal(18,2), SUM(rs.count_executions)) AS ejecuciones, CONVERT(decimal(18,2), SUM(rs.avg_duration * rs.count_executions) / 1000.0) AS duracion_total_ms, CONVERT(decimal(1
11,281,28480SELECT actual_state_desc, desired_state_desc, readonly_reason, current_storage_size_mb, max_storage_size_mb, query_capture_mode_desc, stale_query_threshold_days, size_based_cleanup_mode_desc FROM sys.dat
11,221,221490select @sumCountExecutions = sum(case when (execution_type = 0 or @accountAbortedFlag = 1) then count_executions else 0 end), @sumCountAborted = sum(case when execution_type in(3, 4) th
10,690,6900SELECT wait_type, waiting_tasks_count, wait_time_ms, signal_wait_time_ms, wait_time_ms - signal_wait_time_ms AS resource_wait_ms FROM sys.dm_os_wait_stats WHERE wait_time_ms > 0 AND waiting_tasks_count > 0
10,570,571920SELECT name, value_in_use, description FROM sys.configurations WHERE name IN ( N'max server memory (MB)', N'min server memory (MB)', N'max degree of parallelism', N'cost threshold for parallelism', N'optimize for
10,480,48720SELECT name, value, value_for_secondary FROM sys.database_scoped_configurations WHERE name IN ( N'MAXDOP', N'LEGACY_CARDINALITY_ESTIMATION', N'PARAMETER_SNIFFING', N'QUERY_OPTIMIZER_HOTFIXES', N'ACCELERATED_PLAN_
10,290,29180SELECT name, state_desc, recovery_model_desc, compatibility_level, collation_name, page_verify_option_desc, is_auto_close_on, is_auto_shrink_on, is_auto_create_stats_on, is_auto_update_stats_on, is_auto_update_s
10,270,2700SELECT ISNULL(MAX(r.wait_time),0) AS wait_ms, COUNT(*) AS bloqueadas FROM sys.dm_exec_requests r WHERE r.blocking_session_id <> 0 AND DB_NAME(r.database_id) = 'DBA_BI'
10,190,19160insert into @intervals(interval_id) select runtime_stats_interval_id from sys.plan_persist_runtime_stats_
10,170,1700SELECT MAX(r.wait_time) AS wait_ms FROM sys.dm_exec_requests r WHERE r.blocking_session_id <> 0 AND DB_NAME(r.database_id) = 'DBA_BI'
10,100,10130insert into @plansToCheck(plan_id) select @planId
10,100,1000SELECT COUNT(*) AS missing FROM sys.dm_db_missing_index_details WHERE database_id = DB_ID()
10,100,1000SELECT COUNT(*) AS missing FROM sys.dm_db_missing_index_details WHERE database_id = DB_ID()

Plan cache - ad hoc de la instancia

planes_un_solo_usomb_un_solo_usoplanes_adhoc
71775753

Indices faltantes (DMV, seeks+scans >= 50 e impacto >= 40)

objetoseeksscansimpacto_pctigualdaddesigualdadinclude_cols
[DBA_BI].[lab].[CdbSeek]300099,60[CustomerId][OrderDate]

El DMV ignora indices parecidos que ya existen y no mide el costo de escritura. No cree el indice solo con esta fila.

Indices sin lecturas y con al menos 100 escrituras

esquematablaindiceescrituras
labCdbWriteOnlyIX_CdbWrite_F130
labCdbWriteOnlyIX_CdbWrite_E130
labCdbWriteOnlyIX_CdbWrite_D130
labCdbWriteOnlyIX_CdbWrite_C130
labCdbWriteOnlyIX_CdbWrite_B130
labCdbWriteOnlyIX_CdbWrite_A130

Estos contadores vuelven a cero al reiniciar SQL. Uptime actual: 1580 minutos. No borre un indice solo con esta lista.

Fragmentacion (solo indices de 1000 paginas o mas)

esquematablaindicefrag_pctpaginasaccion_sugerida
labCdbFragCX_CdbFrag_Sort99,1026483REBUILD

Por debajo de 1000 paginas el porcentaje no se informa. REORGANIZE entre 10% y 30%. REBUILD desde 30% en indices grandes. Un REBUILD toma bloqueo de esquema.

Estadisticas por encima del umbral clasico (500 + 20% de filas)

esquematablaestadisticamodificacionesfilasratio_pctlast_updated
labCdbFragCX_CdbFrag_Sort8000012000066,702026-09-22 17:29:59
labCdbStaleIX_CdbStale_Code20002000100,002026-09-22 17:30:13

En tablas de 1 millon de filas o mas no haga FULLSCAN como primer paso: SAMPLE suele alcanzar.

Fill factor bajo (informativo)

esquematablaindicefill_factor
labCdbSeekIX_CdbSeek_Status30
labCdbWriteOnlyIX_CdbWrite_A40
labCdbWriteOnlyIX_CdbWrite_B50
labCdbWriteOnlyIX_CdbWrite_C60
labCdbWriteOnlyIX_CdbWrite_D70

Un FILLFACTOR bajo suele ser intencional. No lo suba a 90 con un REBUILD si no midio page splits.

Archivos de la base

nombre_logicotipotamano_mbcrecimientois_percent_growthgrowth_pagesphysical_name
DBA_BI_logLOG3431MBFalse128C:\SQLLog\DBA_BI_log.ldf
DBA_BIROWS5121MBFalse128C:\SQLData\DBA_BI.mdf

VLF del log

vlf_count
221

Ultimo backup por tipo (D=full, I=diferencial, L=log)

tipoultimohoras
D2026-09-10 17:31:01288

Archivos de TempDB

nametype_desctamano_mbcrecimiento
tempdevROWS864MB
temp2ROWS864MB
templogLOG864MB

Solicitudes bloqueadas ahora

session_idblocking_session_idwait_typewait_msbasestatussql_text
7674LCK_M_X144370DBA_BIsuspended(@1 tinyint)UPDATE [lab].[CdbSeek] set [Amount] = [Amount] WHERE [OrderId]=@1