Scripts y Utilerías para SQL Server

--Valida el historial de Respaldos Ejecutados
select a.backup_set_id, a.backup_set_uuid, a.media_set_id, a.name, a.database_name, b.physical_device_name as 'BackupPath',
Case a.type
When 'D' Then 'Database'
When 'L' Then 'Log'
End As backup_type, 
a.recovery_model, a.description, a.database_creation_date, a.backup_start_date, a.backup_finish_date, 
FORMAT((cast(a.backup_size as float)/1024)/1024, 'N2')  as backupSize, 
FORMAT((cast (a.compressed_backup_size as float)/1024)/1024, 'N2') as Compressed_BackupSize, 
a.user_name, a.server_name, a.software_vendor_id, c.software_name as 'BackedUp_By', a.machine_name, a.first_lsn, a.last_lsn, 
Case a.is_password_protected
When 0 Then 'NO'
When 1 Then 'YES'
End As Is_Password_Protected, 
Case a.is_damaged
When 0 Then 'NO'
When 1 Then 'YES'
End As Is_Damaged, 
Case a.has_incomplete_metadata
When 0 Then 'NO'
When 1 Then 'YES'
End As Has_Incomplete_Metadata, 
a.key_algorithm, a.encryptor_type, a.compression_algorithm
from msdb.dbo.backupset as a
Inner Join msdb.dbo.backupmediafamily as b
On a.media_set_id = b.media_set_id
Inner Join msdb.dbo.backupmediaset as c
On a.media_set_id = c.media_set_id

--Backu Database
USE MyDatabase
GO
BACKUP DATABASE MyDatabase
TO DISK = 'D:\sql\BACKUPS\MyDatabase.bak'

--Monitorea el progreso de procesos de Respaldo o Restaurado
SELECT r.session_id, r.command,
       CONVERT(NUMERIC(6,2), r.percent_complete) AS [Percent Complete],
       CONVERT(VARCHAR(20), DATEADD(ms, r.estimated_completion_time, GETDATE()), 20) AS [ETA],
       CONVERT(NUMERIC(10,2), r.total_elapsed_time/1000.0/60.0) AS [Elapsed Min],
       SUBSTRING(t.text, r.statement_start_offset/2,
              CASE WHEN r.statement_end_offset = -1 THEN 1000
                   ELSE (r.statement_end_offset - r.statement_start_offset)/2 END) AS [Command]
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE r.command IN ('RESTORE DATABASE', 'BACKUP DATABASE')

/*
  SCRIPT DE MONITOREO GRATUITO GM-TECH
  Versión: 1.0
  Descripción: Recolecta métricas clave de rendimiento en SQL Server.
  Instrucciones:
    1. Ejecutar con permisos de lectura en motor (view server state).
    2. Ejecutar en la base de datos master.
    3. Revisar los resultados que aparecen en varios conjuntos de resultados.
*/

-- 1. ESTADO DE ESPERA (WAIT STATS)
SELECT
  wait_type,
  SUM(wait_time_ms)      AS TotalWaitMs,
  SUM(max_wait_time_ms)  AS MaxWaitMs,
  SUM(signal_wait_time_ms) AS SignalWaitMs,
  SUM(waiting_tasks_count) AS WaitingTasks
FROM sys.dm_os_wait_stats
WHERE wait_type NOT IN (
  'CLR_SEMAPHORE','LAZYWRITER_SLEEP','RESOURCE_QUEUE','SLEEP_TASK',
  'SLEEP_SYSTEMTASK','SQLTRACE_BUFFER_FLUSH','WAITFOR','LOGMGR_QUEUE',
  'CHECKPOINT_QUEUE','REQUEST_FOR_DEADLOCK_SEARCH','XE_TIMER_EVENT',
  'XE_DISPATCHER_WAIT_EVENT','FT_IFTS_SCHEDULER_IDLE_WAIT'
)
GROUP BY wait_type
ORDER BY TotalWaitMs DESC;


-- 2. TOP 10 CONSULTAS POR CPU
SELECT TOP 10
  qs.total_worker_time/1000 AS TotalCPUms,
  qs.total_elapsed_time/1000 AS TotalElapsedMs,
  qs.execution_count,
  qs.total_logical_reads,
  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 QueryText,
  DB_NAME(st.dbid) AS DatabaseName
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
ORDER BY qs.total_worker_time DESC;


-- 3. TOP 10 CONSULTAS POR LECTURAS LÓGICAS
SELECT TOP 10
  qs.total_logical_reads AS TotalReads,
  qs.execution_count,
  qs.total_worker_time/1000 AS TotalCPUms,
  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 QueryText,
  DB_NAME(st.dbid) AS DatabaseName
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
ORDER BY qs.total_logical_reads DESC;


-- 4. BLOQUEOS ACTUALES
SELECT
  blocking_session_id AS BlockingSPID,
  session_id           AS BlockedSPID,
  wait_type,
  wait_time,
  wait_resource,
  TEXT AS QueryText
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle)
WHERE blocking_session_id <> 0;


-- 5. USO DE ÍNDICES
SELECT
  OBJECT_NAME(s.object_id)        AS TableName,
  i.name                         AS IndexName,
  s.user_seeks,
  s.user_scans,
  s.user_lookups,
  s.user_updates
FROM sys.dm_db_index_usage_stats s
INNER JOIN sys.indexes i
  ON s.object_id = i.object_id
  AND s.index_id = i.index_id
WHERE database_id = DB_ID()
ORDER BY s.user_updates DESC;


-- 6. TAMAÑO DE BASES DE DATOS Y ARCHIVOS
SELECT
  DB_NAME(database_id) AS DatabaseName,
  SUM(size*8.0/1024)   AS SizeMB
FROM sys.master_files
GROUP BY database_id
ORDER BY SizeMB DESC;


-- 7. SUGERENCIAS DE ÍNDICES FALTANTES
SELECT
  mid.statement AS TableAndColumns,
  migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans) 
    AS ImprovementMeasure,
  'CREATE INDEX IX_' + OBJECT_NAME(mid.object_id) + '_' + REPLACE(REPLACE(mid.equality_columns, ', ', '_'), '[', '') 
    + CASE WHEN mid.inequality_columns IS NOT NULL THEN '_' + REPLACE(REPLACE(mid.inequality_columns, ', ', '_'), '[', '') ELSE '' END
    + ' ON ' + mid.statement + 
      '(' + ISNULL(mid.equality_columns,'') 
      + CASE WHEN mid.equality_columns IS NOT NULL AND mid.inequality_columns IS NOT NULL THEN ',' ELSE '' END 
      + ISNULL(mid.inequality_columns,'') + ')' AS CreateIndexStatement,
  migs.*
FROM sys.dm_db_missing_index_group_stats migs
INNER JOIN sys.dm_db_missing_index_groups mig
  ON migs.group_handle = mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details mid
  ON mig.index_handle = mid.index_handle
ORDER BY ImprovementMeasure DESC;

--Monitor de Transacciones Activas
select transaction_begin_time,name,transaction_begin_time,
case when transaction_type = 1 then 'Read_Write_TX' else '' end  +
case when transaction_type = 2 then 'Read_Only_TX' else '' end +
case when transaction_type = 1 then 'System_TX' else '' end +
case when transaction_type = 1 then 'Distributed_TX' else '' end as transaction_type,
transaction_uow,
case when transaction_state = 0 then 'The transaction has not been completely initialized yet' else '' end +
case when transaction_state = 1 then 'The transaction has been initialized but has not started' else '' end +
case when transaction_state = 2 then 'The transaction is active' else '' end +
case when transaction_state = 3 then 'The transaction has ended' else '' end +
case when transaction_state = 4 then 'The commit process has been initiated on the distributed transaction' else '' end +
case when transaction_state = 5 then 'The transaction is in a prepared state and waiting resolution' else '' end +
case when transaction_state = 6 then 'The transaction has been committed' else '' end +
case when transaction_state = 7 then 'The transaction is being rolled back' else '' end +
case when transaction_state = 8 then 'The transaction has been rolled back' else '' end as transaction_state,
case when dtc_state = 1 then 'ACTIVE' else '' end +
case when dtc_state = 2 then 'PREPARED' else '' end +
case when dtc_state = 3 then 'COMMITTED' else '' end +
case when dtc_state = 4 then 'ABORTED' else '' end +
case when dtc_state = 5 then 'RECOVERED' else '' end as dtc_state
from sys.dm_tran_active_transactions

--Job Monitor
SELECT DISTINCT substring(a.name,1,100) AS [Job Name],
 'Enabled'=case
 WHEN a.enabled = 0 THEN 'No'
 WHEN a.enabled = 1 THEN 'Yes'
 end,
 substring(b.name,1,30) AS [Name of the schedule],
 'Frequency of the schedule execution'=case
 WHEN b.freq_type = 1 THEN 'Once'
 WHEN b.freq_type = 4 THEN 'Daily'
 WHEN b.freq_type = 8 THEN 'Weekly'
 WHEN b.freq_type = 16 THEN 'Monthly'
 WHEN b.freq_type = 32 THEN 'Monthly relative'
 WHEN b.freq_type = 32 THEN 'Execute when SQL Server Agent starts'
 END,
 'Units for the freq_subday_interval'=case
 WHEN b.freq_subday_type = 1 THEN 'At the specified time'
 WHEN b.freq_subday_type = 2 THEN 'Seconds'
 WHEN b.freq_subday_type = 4 THEN 'Minutes'
 WHEN b.freq_subday_type = 8 THEN 'Hours'
 END,
 cast(cast(b.active_start_date as varchar(15)) as datetime) as active_start_date,
 cast(cast(b.active_end_date as varchar(15)) as datetime) as active_end_date,
 Stuff(Stuff(right('000000'+Cast(c.next_run_time as Varchar),6),3,0,':'),6,0,':') as Run_Time,
 convert(varchar(24),b.date_created) as Created_Date,
 
 'Status'=Case
 when e.run_status = 0 THEN 'Failed'
 when e.run_status = 1 THEN 'Succeeded'
 when e.run_status = 2 THEN 'Retry'
 when e.run_status = 3 THEN 'Canceled'
 END,
 e.run_duration, e.step_name as Last_Step_Name 


FROM msdb.dbo.sysjobs a
INNER JOIN msdb.dbo.sysJobschedules c ON a.job_id = c.job_id
INNER JOIN msdb.dbo.SysSchedules b on b.Schedule_id=c.Schedule_id
INNER JOIN msdb.dbo.sysjobhistory e On a.job_id = e.job_id
GO
SELECT [Name], [Date_Created], 'Job Enabled..?'=case
 WHEN enabled = 0 THEN 'No'
 WHEN enabled = 1 THEN 'Yes'
 end ,
 'notification Enabled..?'=case
 WHEN Notify_Level_Email = 0 THEN 'No'
 WHEN Notify_Level_Email = 1 THEN 'Yes'
 end
FROM MSDB.dbo.sysjobs
WHERE [Notify_Level_Email] = 0
AND [Enabled] = 1;
go
--Muestra los operadores habilitados en el Agente de SQL Server 
SELECT [ID], [Name], [Enabled]
FROM MSDB.dbo.sysoperators
WHERE [Enabled] = 1
ORDER BY [Name];
GO
--Obtiene información sobre las configuraciones de los procesos en el Agente de SQL Server

SELECT
 [sJOB].[job_id] AS [JobID]
 , [sJOB].[name] AS [JobName]
 , [sDBP].[name] AS [JobOwner]
 , [sCAT].[name] AS [JobCategory]
 , [sJOB].[description] AS [JobDescription]
 , CASE [sJOB].[enabled]
 WHEN 1 THEN 'Yes'
 WHEN 0 THEN 'No'
 END AS [IsEnabled]
 , [sJOB].[date_created] AS [JobCreatedOn]
 , [sJOB].[date_modified] AS [JobLastModifiedOn]
 , [sSVR].[name] AS [OriginatingServerName]
 , [sJSTP].[step_id] AS [JobStartStepNo]
 , [sJSTP].[step_name] AS [JobStartStepName]
 , CASE
 WHEN [sSCH].[schedule_uid] IS NULL THEN 'No'
 ELSE 'Yes'
 END AS [IsScheduled]
 , [sSCH].[schedule_uid] AS [JobScheduleID]
 , [sSCH].[name] AS [JobScheduleName]
 , CASE [sJOB].[delete_level]
 WHEN 0 THEN 'Never'
 WHEN 1 THEN 'On Success'
 WHEN 2 THEN 'On Failure'
 WHEN 3 THEN 'On Completion'
 END AS [JobDeletionCriterion]
FROM
 [msdb].[dbo].[sysjobs] AS [sJOB]
 LEFT JOIN [msdb].[sys].[servers] AS [sSVR]
 ON [sJOB].[originating_server_id] = [sSVR].[server_id]
 LEFT JOIN [msdb].[dbo].[syscategories] AS [sCAT]
 ON [sJOB].[category_id] = [sCAT].[category_id]
 LEFT JOIN [msdb].[dbo].[sysjobsteps] AS [sJSTP]
 ON [sJOB].[job_id] = [sJSTP].[job_id]
 AND [sJOB].[start_step_id] = [sJSTP].[step_id]
 LEFT JOIN [msdb].[sys].[database_principals] AS [sDBP]
 ON [sJOB].[owner_sid] = [sDBP].[sid]
 LEFT JOIN [msdb].[dbo].[sysjobschedules] AS [sJOBSCH]
 ON [sJOB].[job_id] = [sJOBSCH].[job_id]
 LEFT JOIN [msdb].[dbo].[sysschedules] AS [sSCH]
 ON [sJOBSCH].[schedule_id] = [sSCH].[schedule_id]
ORDER BY [JobName]
go
--Obtiene información sobre las calendarizaciones de procesos en el Agente de SQL Server

SELECT
 [schedule_uid] AS [ScheduleID]
 , [name] AS [ScheduleName]
 , CASE [enabled]
 WHEN 1 THEN 'Yes'
 WHEN 0 THEN 'No'
 END AS [IsEnabled]
 , CASE
 WHEN [freq_type] = 64 THEN 'Start automatically when SQL Server Agent starts'
 WHEN [freq_type] = 128 THEN 'Start whenever the CPUs become idle'
 WHEN [freq_type] IN (4,8,16,32) THEN 'Recurring'
 WHEN [freq_type] = 1 THEN 'One Time'
 END [ScheduleType]
 , CASE [freq_type]
 WHEN 1 THEN 'One Time'
 WHEN 4 THEN 'Daily'
 WHEN 8 THEN 'Weekly'
 WHEN 16 THEN 'Monthly'
 WHEN 32 THEN 'Monthly - Relative to Frequency Interval'
 WHEN 64 THEN 'Start automatically when SQL Server Agent starts'
 WHEN 128 THEN 'Start whenever the CPUs become idle'
 END [Occurrence]
 , CASE [freq_type]
 WHEN 4 THEN 'Occurs every ' + CAST([freq_interval] AS VARCHAR(3)) + ' day(s)'
 WHEN 8 THEN 'Occurs every ' + CAST([freq_recurrence_factor] AS VARCHAR(3))
 + ' week(s) on '
 + CASE WHEN [freq_interval] & 1 = 1 THEN 'Sunday' ELSE '' END
 + CASE WHEN [freq_interval] & 2 = 2 THEN ', Monday' ELSE '' END
 + CASE WHEN [freq_interval] & 4 = 4 THEN ', Tuesday' ELSE '' END
 + CASE WHEN [freq_interval] & 8 = 8 THEN ', Wednesday' ELSE '' END
 + CASE WHEN [freq_interval] & 16 = 16 THEN ', Thursday' ELSE '' END
 + CASE WHEN [freq_interval] & 32 = 32 THEN ', Friday' ELSE '' END
 + CASE WHEN [freq_interval] & 64 = 64 THEN ', Saturday' ELSE '' END
 WHEN 16 THEN 'Occurs on Day ' + CAST([freq_interval] AS VARCHAR(3))
 + ' of every '
 + CAST([freq_recurrence_factor] AS VARCHAR(3)) + ' month(s)'
 WHEN 32 THEN 'Occurs on '
 + CASE [freq_relative_interval]
 WHEN 1 THEN 'First'
 WHEN 2 THEN 'Second'
 WHEN 4 THEN 'Third'
 WHEN 8 THEN 'Fourth'
 WHEN 16 THEN 'Last'
 END
 + ' '
 + CASE [freq_interval]
 WHEN 1 THEN 'Sunday'
 WHEN 2 THEN 'Monday'
 WHEN 3 THEN 'Tuesday'
 WHEN 4 THEN 'Wednesday'
 WHEN 5 THEN 'Thursday'
 WHEN 6 THEN 'Friday'
 WHEN 7 THEN 'Saturday'
 WHEN 8 THEN 'Day'
 WHEN 9 THEN 'Weekday'
 WHEN 10 THEN 'Weekend day'
 END
 + ' of every ' + CAST([freq_recurrence_factor] AS VARCHAR(3))
 + ' month(s)'
 END AS [Recurrence]
 , CASE [freq_subday_type]
 WHEN 1 THEN 'Occurs once at '
 + STUFF(
 STUFF(RIGHT('000000' + CAST([active_start_time] AS VARCHAR(6)), 6)
 , 3, 0, ':')
 , 6, 0, ':')
 WHEN 2 THEN 'Occurs every '
 + CAST([freq_subday_interval] AS VARCHAR(3)) + ' Second(s) between '
 + STUFF(
 STUFF(RIGHT('000000' + CAST([active_start_time] AS VARCHAR(6)), 6)
 , 3, 0, ':')
 , 6, 0, ':')
 + ' & '
 + STUFF(
 STUFF(RIGHT('000000' + CAST([active_end_time] AS VARCHAR(6)), 6)
 , 3, 0, ':')
 , 6, 0, ':')
 WHEN 4 THEN 'Occurs every '
 + CAST([freq_subday_interval] AS VARCHAR(3)) + ' Minute(s) between '
 + STUFF(
 STUFF(RIGHT('000000' + CAST([active_start_time] AS VARCHAR(6)), 6)
 , 3, 0, ':')
 , 6, 0, ':')
 + ' & '
 + STUFF(
 STUFF(RIGHT('000000' + CAST([active_end_time] AS VARCHAR(6)), 6)
 , 3, 0, ':')
 , 6, 0, ':')
 WHEN 8 THEN 'Occurs every '
 + CAST([freq_subday_interval] AS VARCHAR(3)) + ' Hour(s) between '
 + STUFF(
 STUFF(RIGHT('000000' + CAST([active_start_time] AS VARCHAR(6)), 6)
 , 3, 0, ':')
 , 6, 0, ':')
 + ' & '
 + STUFF(
 STUFF(RIGHT('000000' + CAST([active_end_time] AS VARCHAR(6)), 6)
 , 3, 0, ':')
 , 6, 0, ':')
 END [Frequency]
 , STUFF(
 STUFF(CAST([active_start_date] AS VARCHAR(8)), 5, 0, '-')
 , 8, 0, '-') AS [ScheduleUsageStartDate]
 , STUFF(
 STUFF(CAST([active_end_date] AS VARCHAR(8)), 5, 0, '-')
 , 8, 0, '-') AS [ScheduleUsageEndDate]
 , [date_created] AS [ScheduleCreatedOn]
 , [date_modified] AS [ScheduleLastModifiedOn]
FROM [msdb].[dbo].[sysschedules]
ORDER BY [ScheduleName]

--Ver Uso
declare @dbsize as  float(1),
		@logsize as float(1),
		@dbsize2 as  float(1),
		@logsize2 as float(1),  
		@Totalsize as float(1),
		@TotalFree as float(1),	
		@reservedpages as int,
		@percentageUsed as float(1),
		@percentageFree as float(1),
		@DataGrowth as float(1),
		@LogGrowth as float(1),
		@isPercentD as float(1),
		@isPercentL as float(1),
		@Max_SizeD as int,
		@Max_SizeL as int;

	select @Max_SizeD = max_size, @DataGrowth = growth, @isPercentD = is_percent_growth from sys.database_files where file_id = 1
	select @Max_SizeL = max_size, @LogGrowth = growth, @isPercentL = is_percent_growth from sys.database_files where file_id = 2

	select @dbsize = sum(convert(bigint,case when status & 64 = 0 then size else 0 end))
			, @logsize = sum(convert(bigint,case when status & 64 <> 0 then size else 0 end))
			from dbo.sysfiles

	select @reservedpages = sum(a.total_pages) from sys.partitions p join sys.allocation_units a on p.partition_id = a.container_id
			left join sys.internal_tables it on p.object_id = it.object_id

	set @dbsize2 = @dbsize * 8192 / 1048576
	set @logsize2 = @logsize * 8192 / 1048576
	
	set @Totalsize = ((@dbsize + @logsize) * 8192 / 1048576)

	set @TotalFree =  case when @dbsize >= @reservedpages then
					((@dbsize - @reservedpages) * 8192 / 1048576) else 0 end

	set @percentageFree = ((@TotalFree*100)/@Totalsize)
	SET @percentageUsed = 100 - @percentageFree

		
	select database_Name = DB_NAME(),@dbsize2 as 'Tamaño_Datos(MBs)', @logsize2 as 'Tamaño_Log(MBs)', @Totalsize as 'Tamaño_total(MBs)', @TotalFree as 'Espacio_Libre', @percentageUsed as '%Total_Utilizado', @percentageFree as '%Total_Libre',@Max_SizeD as 'Data_Max_Size', @DataGrowth as 'Data_Growth', @isPercentD as 'Percent_Data_Growth', @Max_SizeL as 'Log_Max_Size', @LogGrowth as 'Log_Growth', @isPercentL as 'Percent_Log_Growth'
		

--DETECTAR FRAGMENTACION EN UNA TABLA ESPECIFICA DE UNA BASE DE DATOS

SELECT a.index_id, name, avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats (DB_ID(N'DB_Name'), OBJECT_ID(N'Esquema.tabla'), NULL, NULL, NULL) AS a
    JOIN sys.indexes AS b ON a.object_id = b.object_id AND a.index_id = b.index_id; 

--DETECTAR FRAGMENTACION EN TODAS LAS TABLAS DE LA BASE DE DATOS

SELECT a.index_id, c.name, b.name, avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats (DB_ID(N'DB_Name'), NULL, NULL, NULL, NULL) AS a
    JOIN sys.indexes AS b ON a.object_id = b.object_id AND a.index_id = b.index_id
	JOIN sys.all_objects c ON a.object_id = c.object_id
Where a.avg_fragmentation_in_percent > 5 and a.avg_fragmentation_in_percent <= 30 --INDICES A REORGANIZAR
--where a.avg_fragmentation_in_percent > 30 --INDICES A REGENERAR


--REORGANIZAR INDICES DE UNA TABLA

-- Reorganize all indexes on the HumanResources.Employee table.
ALTER INDEX ALL ON esquema.tabla
REORGANIZE ; 
GO

--REORGANIZA UN INDICE DE UNA TABLA

-- Reorganize the IX_Employee_OrganizationalLevel_OrganizationalNode index on the HumanResources.Employee table. 

ALTER INDEX IndexName ON esquema.Tabla
REORGANIZE ; 
GO

--REGENERAR INDICES DE UNA TABLA

ALTER INDEX ALL ON esquema.tabla
REBUILD WITH (FILLFACTOR = 80, SORT_IN_TEMPDB = ON,
              STATISTICS_NORECOMPUTE = ON);


--REGENERA UN INDICE DE UNA TABLA

GO
ALTER INDEX IndexName ON Scheme.Tabla
REBUILD;
GO

--Script para calcular operación sobre Indices
WITH INDICES (BD, INDICETIPO, FRAGMENTACION, INDICE, TABLA)
AS (
SELECT DBS.NAME BASEDEDATOS, PS.INDEX_TYPE_DESC, PS.AVG_FRAGMENTATION_IN_PERCENT, 
IND.NAME INDICE, TAB.NAME TABLA
FROM 
SYS.DM_DB_INDEX_PHYSICAL_STATS (DB_ID(), NULL, NULL,  NULL, NULL) PS
INNER JOIN SYS.DATABASES DBS
ON PS.DATABASE_ID = DBS.DATABASE_ID 
INNER JOIN SYS.INDEXES IND
ON PS.OBJECT_ID  = IND.OBJECT_ID 
INNER JOIN SYS.TABLES TAB 
ON TAB.OBJECT_ID = IND.OBJECT_ID 
WHERE IND.NAME IS  NOT NULL AND PS.INDEX_ID = IND.INDEX_ID
AND PS.AVG_FRAGMENTATION_IN_PERCENT  > 30)
SELECT DISTINCT  
      CASE
      WHEN FRAGMENTACION > 5 AND FRAGMENTACION <= 30 THEN  'ALTER INDEX ' + INDICE  + ' ON ' +  TABLA + ' REORGANIZE'     
      WHEN FRAGMENTACION > 30 THEN 'ALTER INDEX ' + INDICE + ' ON ' + TABLA + ' REBUILD'
      END QUERY, FRAGMENTACION, BD, INDICE, TABLA
FROM (SELECT FRAGMENTACION, INDICE, TABLA, BD FROM INDICES
      WHERE FRAGMENTACION > 5) A
ORDER BY FRAGMENTACION DESC 

--Utilización de índices
SELECT 
    OBJECT_NAME(i.object_id) AS table_name,
    i.name AS index_name,
    ius.user_seeks,
    ius.user_scans,
    ius.user_lookups,
    ius.user_updates
FROM sys.indexes i
JOIN sys.dm_db_index_usage_stats ius 
    ON i.object_id = ius.object_id AND i.index_id = ius.index_id
WHERE OBJECTPROPERTY(i.object_id, 'IsUserTable') = 1
ORDER BY ius.user_scans DESC;
                                

--1. Olvidar el WHERE en un DELETE o UPDATE
--ERROR: Se borran todos los empleados
DELETE FROM HumanResources.Employee;
GO
--SOLUCIÓN: Filtrar solo el empleado deseado
DELETE FROM HumanResources.Employee
WHERE BusinessEntityID = 42;
GO
--2. No usar alias al hacer JOINs
--ERROR: Confuso y difícil de leer
SELECT Person.FirstName, Sales.SalesOrderID
FROM Person.Person
JOIN Sales.SalesOrderHeader
ON Person.BusinessEntityID = Sales.CustomerID;
GO
--SOLUCIÓN: Uso claro de alias
SELECT P.FirstName, SO.SalesOrderID
FROM Person.Person AS P
JOIN Sales.SalesOrderHeader AS SO
ON P.BusinessEntityID = SO.CustomerID;
GO
--3. Confusión entre NULL y valores vacíos
--ERROR: No considera registros con valores NULL
SELECT *
FROM Person.Person
WHERE MiddleName = '';
GO
--SOLUCIÓN: Evaluar explícitamente los NULL
SELECT *
FROM Person.Person
WHERE MiddleName IS NULL;
GO
--4. Uso incorrecto de comillas y tipos de datos
--ERROR: Comparación entre texto y entero
SELECT *
FROM Production.Product
WHERE ProductID = '101'; -- '101' es cadena
GO
--SOLUCIÓN: Usar tipo de dato correcto (entero)
SELECT *
FROM Production.Product
WHERE ProductID = 101;
GO
--5. No agrupar correctamente al usar GROUP BY
--ERROR: Falla porque falta GROUP BY
SELECT SalesPersonID, COUNT(*)
FROM Sales.SalesOrderHeader;
GO
--SOLUCIÓN: Agregar GROUP BY
SELECT SalesPersonID, COUNT(*)
FROM Sales.SalesOrderHeader
GROUP BY SalesPersonID;

--Ejercicio de instrucciones básicas de SQL.sql
USE master;
GO

-- Crear base de datos
CREATE DATABASE TestData;
GO

USE TestData;
GO

-- Crear tabla
CREATE TABLE dbo.Products (
    ProductID int PRIMARY KEY NOT NULL,
    ProductName varchar(25) NOT NULL,
    Price money NULL,
    ProductDescription text NULL
);
GO

-- Insertar datos
INSERT dbo.Products VALUES (1, 'Clamp', 12.48, 'Workbench clamp');
INSERT dbo.Products VALUES (50, 'Screwdriver', 3.17, 'Flat head');
INSERT dbo.Products VALUES (75, 'Tire Bar', NULL, 'Tool for changing tires.');
GO

-- Actualizar datos
UPDATE dbo.Products SET ProductName = 'Flat Head Screwdriver' WHERE ProductID = 50;
GO

-- Consultar datos
SELECT ProductID, ProductName, Price FROM dbo.Products;

--Practicas de Consultas
--Relacionar Multiples Tablas con Join
--Uso de Multiples Joins, Concatenación de Campos, Filtro con Fechas
-- Relaciona Ventas, Clientes y Nombres de Personas
Select p.FirstName + ' ' + p.LastName As Cliente, 
	c.CustomerID,
	soh.SalesOrderID,
	soh.OrderDate,
	soh.TotalDue
From Sales.SalesOrderHeader as soh
Join Sales.Customer as c
	On soh.CustomerID = c.CustomerID
Join Person.Person as p
	On c.PersonID = p.BusinessEntityID
Where soh.OrderDate >= '2014-01-01';

--Metricas Agregadas con Group By y Having
--Funciones Agregadas, Agrupación Temporal, Filtros de Agregados (Having)
--Agrega el valor de las ventas por año
Select Year(soh.OrderDate) as Año,
	Count(*) as TotalOrdenes,
	Sum(soh.TotalDue) As TotalIngresos 
From Sales.SalesOrderHeader As soh
Group By YEAR(soh.OrderDate)
Having SUM(soh.TotalDue) > 100000;

--Orden y Jerarquía con funciones de Ventana Rank()
--Rank() para Clasificación, Orden con Order By Filtros con Between
-- Ranking por monto total de Compra
Select p.FirstName + ' ' + p.LastName as Cliente,
	soh.TotalDue, 
	RANK() Over (Order By soh.TotalDue DESC) as RangoCompra
From Sales.SalesOrderHeader as soh
Join Sales.Customer As c
	On soh.CustomerID = c.CustomerID
Join Person.Person As p
	On c.PersonID = p.BusinessEntityID
Where soh.OrderDate Between '2014-01-01' And '2014-12-31';

--Jerarquias con CTE y Niveles Organizacionales
--Uso de CTE, Función GetLevel(), Representación de Estructuras Jerárquircas
With Organigrama As (
	Select e.BusinessEntityID, e.OrganizationNode.GetLevel() as Nivel,
		p.FirstName + ' ' + p.LastName as Empleado
	From HumanResources.Employee as e
	Join Person.Person as p
		On e.BusinessEntityID = p.BusinessEntityID
)
Select * From Organigrama Where Nivel <= 3;

--Limpieza y Transformación de Datos
--Uso de ISNULL, Análisis de Atributos categóricos, Limpieza de valores nulos
Select p.EmailPromotion, COUNT(*) as TotalPersonas
From Person.Person as p
Where ISNULL(p.EmailPromotion, 0) >= 0
Group By p.EmailPromotion

--Consultas Dinámicas
Begin Try
	declare @Tabla NVARCHAR(128) = 'Person.Person';
	declare @SQL NVARCHAR(MAX);
	
	SET @SQL = N'Select * From ' + QUOTENAME(@Tabla);

	Exec sp_executesql @SQL;

End Try
Begin Catch
	select ERROR_MESSAGE() as ErrorSQL;
End Catch

--Uso de sp_readerrorlog para monitoreo
--Ideal para ver errores recientes en el servidor SQL
Exec sp_readerrorlog 0, 1, 'error';