Continuamos con los videos de T-SQL avanzado con los modificadores WITH CUBE y WITH ROLLUP. Estos son modificadores de la cláusula GROUP BY. Gracias a WITH CUBE vamos a poder totalizar nuestras agrupaciones junto a los resultados de nuestras consultas y con WITH ROLLUP podremos ver solo totales agrupados por varios campos.
Al igual que comentamos en nuestro anterior video de T_SQL Avanzado el uso de este tipo de consultas en vistas que luego se consumirán desde Power BI nos va a evitar caer en el error de romper el plegado de consultas. También vamos a conseguir con una sola consulta los resultados que, de otra manera, necesitaríamos por lo menos dos consultas, con el doble de lecturas sobre los datos y, por tanto, con mayor consumo de recursos.
Espero que te haya gustado el video, si es así por favor, deja tu me gusta y suscríbete al canal que nos ayuda mucho. Si tenéis alguna duda o sugerencia, podéis dejarla en Twitter, por mail o dejarnos un mensaje en los comentarios. Y recuerda que también tenemos un grupo de Telegram al que te puede unir. En este grupo estamos creando una comunidad de usuarios y administradores de SQL Server donde cualquiera pueda preguntar sus dudas y compartir sus casos prácticos para que todos seamos mejores profesionales. ¡Hasta la próxima!
Empezamos una nueva serie en este video blog sobre como hacer consultas SQL complejas. Uno de los conceptos más importantes y menos conocido del código SQL avanzado es la opción de Pivot y Unpivot para pivotar o despivotar consultas respectivamente.
Gracias a estas opciones de Pivot y Unpivot vamos a poder delegar en el origen SQL agrupaciones avanzadas que, a veces por desconocimiento, estamos haciendo en modelos tabulares con grandes y costosas transformaciones. Como os digo, no son pocas las veces que en informes de Crystal reports o consultas con Power Query tratamos de realizar estas transformaciones no plegables. Con este detalle, haciendo el Pivot en una vista en el origen, podremos optimizar enormemente el rendimiento de nuestros informes.
Espero que te haya gustado el video, si es así por favor, deja tu me gusta y suscríbete al canal que nos ayuda mucho. Si tenéis alguna duda o sugerencia, podéis dejarla en Twitter, por mail o dejarnos un mensaje en los comentarios. Y recuerda que también tenemos un grupo de Telegram al que te puede unir. En este grupo estamos creando una comunidad de usuarios y administradores de SQL Server donde cualquiera pueda preguntar sus dudas y compartir sus casos prácticos para que todos seamos mejores profesionales. ¡Hasta la próxima!
Cuando tenemos un SQL Server con licencia standard o enterprise tenemos la opción de comprimir las copias de seguridad. Pero, ¿realmente es buena idea comprimir los backups? ¿se reduce mucho el tamaño? ¿afecta al rendimiento? Eso es justo lo que os trato de demostrar en este video con varias pruebas para que podamos comprobarlo juntos. Si quieres jugar déjame un comentario con lo que crees que va a pasar antes de reproducir el vídeo.
Como has visto, aunque los backups comprimidos van a requerir más recursos de CPU tanto para hacerse como para restaurarse terminan tardando mucho menos. El almacenamiento SIEMPRE es el hardware más lento y por tanto cualquier ahorro en ese recurso se va a notar sobre cualquier otro aspecto. Y esta prueba ha sido con la copia en local, imaginaos que se tiene que llevar por red a un NAS o a la nube dependiendo de nuestra tasa de subida de la conexión a internet, la diferencia sería mucho mayor.
A no ser que tengas problemas de consumo de CPU en tu máquina y el consumo de E/S de disco en el dispositivo de backup no sea un problema para ti mi recomendación es que comprimas los backups.
Espero que te haya gustado el video, si es así por favor, deja tu me gusta y suscríbete al canal que nos ayuda mucho. Si tenéis alguna duda o sugerencia, podéis dejarla en Twitter, por mail o dejarnos un mensaje en los comentarios. Y recuerda que también tenemos un grupo de Telegram al que te puede unir. En este grupo estamos creando una comunidad de usuarios y administradores de SQL Server donde cualquiera pueda preguntar sus dudas y compartir sus casos prácticos para que todos seamos mejores profesionales. ¡Hasta la próxima!
Controlar diariamente el tamaño de nuestras tablas y los ficheros de bases de datos es clave para poder afrontar en el futuro una posible incidencia de espacio en disco. Gracias a esta monitorización en nuestro SQL Server vamos a poder localizar que base de datos y que tabla es la que ha crecido hasta llenar el disco y provocarnos una incidencia. En ocasiones una tabla crece sin control hasta llenar el disco y gracias a esta monitorización del tamaño de las bases de datos y las tablas nos va a ser muy sencillo de localizar. No esperes a que sea tarde y no tengas las herramientas necesarias, monitoriza desde ya tus ficheros.
A continuación te dejo los scripts que he usado para monitorizar el tamaño en el video, recuerda adaptarlos a tu base de datos de monitorización y, en caso de ser necesario, cambiar la verificación de las tablas por un código compatible con tu versión de SQL (pídeme ayuda en los comentarios del video si lo necesitas).
USE [DBA]
GO
/****** Object: StoredProcedure [dbo].[MonitorTableUsage] Script Date: 27/05/2024 18:29:26 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Roberto Carrancio (www.soydba.es)
-- Create date: 04/04/2024
-- Description: Procedimiento para monitorizar a través del tiempo
-- el crecimiento de las tablas de las BBDD.
-- =============================================
CREATE PROCEDURE [dbo].[MonitorTableUsage]
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
IF NOT EXISTS (SELECT 1 FROM sys.tables WHERE name='TableUsage')
BEGIN
CREATE TABLE TableUsage (
[Fecha] DATE NOT NULL,
[BaseDatos] VARCHAR(150) NOT NULL,
[Esquema] VARCHAR(150) NOT NULL,
[Tabla] VARCHAR(150) NOT NULL,
[Filas] BIGINT,
[TamañoTotalMb] FLOAT,
[EspacioDatosMb] FLOAT,
[EspacioIndicesMb] FLOAT,
[EspacioLibreMb] FLOAT,
CONSTRAINT PK_TableUsage PRIMARY KEY CLUSTERED (Fecha, BaseDatos, Esquema, Tabla)
)
END
EXEC sp_MSforeachdb 'USE [?]
IF EXISTS (SELECT 1 FROM sys.databases WHERE is_read_only = 0 and name=''?'')
BEGIN
INSERT INTO [DBA].dbo.[TableUsage] ([Fecha], [BaseDatos], [Esquema], [Tabla], [Filas], [TamañoTotalMb], [EspacioDatosMb], [EspacioIndicesMb], [EspacioLibreMb] )
SELECT
GETDATE() Fecha
,''?'' as BaseDatos
,a3.name AS SchemaName,
a2.name AS TableName,
a1.rows as Row_Count,
(a1.reserved )* 8.0 / 1024 AS reserved_mb,
a1.data * 8.0 / 1024 AS data_mb,
(CASE WHEN (a1.used ) > a1.data THEN (a1.used ) - a1.data ELSE 0 END) * 8.0 / 1024 AS index_size_mb,
(CASE WHEN (a1.reserved ) > a1.used THEN (a1.reserved ) - a1.used ELSE 0 END) * 8.0 / 1024 AS unused_mb
FROM ( SELECT
ps.object_id,
SUM ( CASE WHEN (ps.index_id < 2) THEN row_count ELSE 0 END ) AS [rows],
SUM (ps.reserved_page_count) AS reserved,
SUM (CASE WHEN (ps.index_id < 2) THEN (ps.in_row_data_page_count + ps.lob_used_page_count + ps.row_overflow_used_page_count)
ELSE (ps.lob_used_page_count + ps.row_overflow_used_page_count) END
) AS data,
SUM (ps.used_page_count) AS used
FROM sys.dm_db_partition_stats ps
GROUP BY ps.object_id
) AS a1
INNER JOIN sys.all_objects a2 ON ( a1.object_id = a2.object_id )
INNER JOIN sys.schemas a3 ON (a2.schema_id = a3.schema_id)
WHERE a2.type <> N''S'' and a2.type <> N''IT''
option (recompile)
END'
END
GO
/****** Object: StoredProcedure [dbo].[Monitorizar_FileGroups] Script Date: 27/05/2024 18:29:37 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Roberto Carrancio (www.soydba.es)
-- Create date: 31/12/2018
-- Description: Procedimiento para monitorizar a través del tiempo
-- el crecimiento de los ficheros de las BBDD.
-- =============================================
CREATE PROCEDURE [dbo].[Monitorizar_Ficheros]
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
IF NOT EXISTS (SELECT 1 FROM sys.tables WHERE name='CRECIMIENTO_FICHEROS')
BEGIN
CREATE TABLE [dbo].[CRECIMIENTO_FICHEROS](
[dbname] [nvarchar](128) NULL,
[Type] [nvarchar](60) NULL,
[FileName] [nvarchar](128) NULL,
[FileGroup] [nvarchar](128) NULL,
[Path] [nvarchar](260) NULL,
[CurrentSizeMB] [numeric](17, 6) NULL,
[UsedSpace] [numeric](17, 6) NULL,
[Fecha] [datetime] NULL
) ON [PRIMARY]
END
EXEC sp_MSforeachdb 'USE [?]
IF EXISTS (SELECT 1 FROM sys.databases WHERE is_read_only = 0 and name=''?'')
BEGIN
INSERT INTO [DBA].dbo.[CRECIMIENTO_FICHEROS]
([dbname]
,[Type]
,[FileName]
,[FileGroup]
,[Path]
,[CurrentSizeMB]
,[UsedSpace]
,[Fecha])
select
''?''
,f.type_desc as [Type]
,f.name as [FileName]
,fg.name as [FileGroup]
,f.physical_name as [Path]
,f.size / 128.0 as [CurrentSizeMB]
,convert(int,fileproperty(f.name,''SpaceUsed'')) /
128.0 [UsedSpace]
,GETDATE() Fecha
from
sys.database_files f left outer join
sys.filegroups fg on
f.data_space_id = fg.data_space_id
option (recompile)
END'
END
GO
USE [msdb]
GO
/****** Object: Job [MP_Monitor_Crecimiento_Ficheros] Script Date: 27/05/2024 18:28:50 ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object: JobCategory [[Uncategorized (Local)]] Script Date: 27/05/2024 18:28:51 ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
END
DECLARE @jobId BINARY(16)
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'MP_Monitor_Crecimiento_Ficheros',
@enabled=1,
@notify_level_eventlog=0,
@notify_level_email=0,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N'No description available.',
@category_name=N'[Uncategorized (Local)]',
@owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [Monitor FileGroups] Script Date: 27/05/2024 18:28:51 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Monitor Ficheros',
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=3,
@on_success_step_id=0,
@on_fail_action=3,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'exec dbo.[Monitorizar_Ficheros]',
@database_name=N'DBA',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [Monitor Tablas] Script Date: 27/05/2024 18:28:51 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Monitor Tablas',
@step_id=2,
@cmdexec_success_code=0,
@on_success_action=1,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'exec dba.dbo.MonitorTableUsage',
@database_name=N'DBA',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Daily 23:30',
@enabled=1,
@freq_type=4,
@freq_interval=1,
@freq_subday_type=1,
@freq_subday_interval=0,
@freq_relative_interval=0,
@freq_recurrence_factor=0,
@active_start_date=20231017,
@active_end_date=99991231,
@active_start_time=233000,
@active_end_time=235959,
@schedule_uid=N'15367ccc-1d50-4318-95aa-5c78663dd142'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
GO
Espero que te haya gustado el video, si es así por favor, deja tu me gusta y suscríbete al canal que nos ayuda mucho. Si tenéis alguna duda o sugerencia, podéis dejarla en Twitter, por mail o dejarnos un mensaje en los comentarios. Y recuerda que también tenemos un grupo de Telegram al que te puede unir. En este grupo estamos creando una comunidad de usuarios y administradores de SQL Server donde cualquiera pueda preguntar sus dudas y compartir sus casos prácticos para que todos seamos mejores profesionales. ¡Hasta la próxima!
En ocasiones podemos pensar en un nivel de aislamiento read uncommitted o en el uso de la clausula NoLOCK como la solución a nuestros problemas de bloqueos. Si a ti también te ha pasado este video es para ti. Vamos a ver como su uso tiene muchos riegos y hay resultados inesperados que, para mi, hacen que no sea la mejor de la soluciones.
Espero que te haya gustado esta demostración rápida de los problemas a los que nos podemos enfrentar por usar NoLOCK. Ahora que ya conoces los riegos te recomiendo valorar una solución como Read Committed Snapshot para tus transacciones. Si quieres saber más de niveles de aislamiento te recomiendo nuestros post sobre niveles de aislamiento, niveles de aislaminto – casos prácticos y el uso de nolock.
Espero que te haya gustado el video, si es así por favor, deja tu me gusta y suscríbete al canal que nos ayuda mucho. Si tenéis alguna duda o sugerencia, podéis dejarla en Twitter, por mail o dejarnos un mensaje en los comentarios. Y recuerda que también tenemos un grupo de Telegram al que te puede unir. En este grupo estamos creando una comunidad de usuarios y administradores de SQL Server donde cualquiera pueda preguntar sus dudas y compartir sus casos prácticos para que todos seamos mejores profesionales. ¡Hasta la próxima!
Volvemos con otro Video Blog y hoy vamos a ver un caso de uso gracioso para nuestro servidor SQL Server. Usando DBMail vamos a automatizar un envío diario de correos electrónicos. Tendremos una tabla con los contactos y otras columnas para poder aplicar filtros y decidir cuando enviar o no los correos electrónicos.
La semana pasada me encontré en LinkedIn un usuario que compartía un script para enviar diariamente un correo electrónico a su pareja diciéndole «te quiero» gracias al uso de DBMail y de Jobs de SQL Server. A raíz de ese post, y siguiendo la broma, comenté que sería posible iterar por una tabla de contactos para automatizar este mismo envío pero personalizado para tantos contactos como tengas en tu tabla maestro.
Mi colega Rubén, lector recurrente del blog (que ya me conoce y sabe que detesto los cursores dado su mal rendimiento), me preguntó como hacer el proceso sin depender de bucles. Así que, para Rubén en especial y para todos vosotros en general, aquí lo tenéis. Mi video blog más canalla hasta la fecha. Os recomiendo verlo a pantalla completa para poder leer bien el código. Espero que os guste.
Os comparto ahora los scripts que hemos visto en el video
Cursor:
Declare @email nvarchar(128)
Declare @name nvarchar(128)
DECLARE email_cursor CURSOR FOR
SELECT Nombre, Mail
FROM contactos
WHERE activo = 1
OPEN email_cursor
FETCH NEXT FROM email_cursor
INTO @name,@email
WHILE @@FETCH_STATUS = 0
BEGIN
Declare @subject nvarchar(255)
Declare @Bodytext nvarchar(512)
Set @BodyText = 'Te quiero más que a nadie ' + @name + '. Quiero que sepas que eres la única en mi vida'
Set @Subject = 'Love You'
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'DB_Mail',
@recipients = @email,
@body = @Bodytext,
@subject = @subject;
FETCH NEXT FROM email_cursor
INTO @name,@email
END
CLOSE email_cursor
Consulta con SQL dinámico:
DECLARE @sql NVARCHAR(MAX) = N'';
SELECT @sql = @sql + N'
EXEC msdb.dbo.sp_send_dbmail
@profile_name = ''DB_Mail'',
@recipients = ''' + mail + ''',
@body = ''Te quiero más que a nadie ' + nombre + '. Quiero que sepas que eres la única en mi vida'',
@subject = ''Love You''; '
FROM contactos
WHERE activo = 1;
--PRINT @sql;
EXEC sp_executesql @sql;
Espero que te haya gustado el video, si es así por favor, deja tu me gusta y suscríbete al canal que nos ayuda mucho. Si tenéis alguna duda o sugerencia, podéis dejarla en Twitter, por mail o dejarnos un mensaje en los comentarios. Y recuerda que también tenemos un grupo de Telegram al que te puede unir. En este grupo estamos creando una comunidad de usuarios y administradores de SQL Server donde cualquiera pueda preguntar sus dudas y compartir sus casos prácticos para que todos seamos mejores profesionales. ¡Hasta la próxima!
Cuando movemos bases de datos entre entornos o, a la hora de tener un Always On moviendo bases de datos entre distintos servidores SQL Server, es común encontrarse con un problema de usuarios huérfanos ya que los sid de los logins pueden diferir entre los distintos servidores. Para evitar este problema vamos a usar el procedimiento almacenado sp_help_revlogin y así crear los logins con el mismo sid y contraseña.
Ahora ya sabes como recrear tus logins para no tener problemas de usuarios huerfanos en un grupo de alta disponibilidad Always On o cuando mueves bases de datos entre servidores. Recuerda también que tenemos un artículo explicando «como prevenir usuarios huérfanos» en el que explicamos otros métodos para evitar este problema.
Espero que te haya gustado el video, si es así por favor, deja tu me gusta y suscríbete al canal que nos ayuda mucho. Si tenéis alguna duda o sugerencia, podéis dejarla en Twitter, por mail o dejarnos un mensaje en los comentarios. Y recuerda que también tenemos un grupo de Telegram al que te puede unir. En este grupo estamos creando una comunidad de usuarios y administradores de SQL Server donde cualquiera pueda preguntar sus dudas y compartir sus casos prácticos para que todos seamos mejores profesionales. ¡Hasta la próxima!
Colabora con nosotros
SoyDBA.es es gratis para ti y siempre va a serlo. Sin embargo, a nosotros si que nos cuesta dinero además de mucho esfuerzo. Puedes colaborar con nosotros con un donativo por PayPal o usando nuestros enlaces de afiliado para colaborar sin que te cueste nada. Tenemos enlace de Amazon y de Aliexpress
Para ofrecer las mejores experiencias, utilizamos tecnologías como las cookies para almacenar y/o acceder a la información del dispositivo. El consentimiento de estas tecnologías nos permitirá procesar datos como el comportamiento de navegación o las identificaciones únicas en este sitio. No consentir o retirar el consentimiento, puede afectar negativamente a ciertas características y funciones.
Funcional
Siempre activo
El almacenamiento o acceso técnico es estrictamente necesario para el propósito legítimo de permitir el uso de un servicio específico explícitamente solicitado por el abonado o usuario, o con el único propósito de llevar a cabo la transmisión de una comunicación a través de una red de comunicaciones electrónicas.
Preferencias
El almacenamiento o acceso técnico es necesario para la finalidad legítima de almacenar preferencias no solicitadas por el abonado o usuario.
Estadísticas
El almacenamiento o acceso técnico que es utilizado exclusivamente con fines estadísticos.El almacenamiento o acceso técnico que se utiliza exclusivamente con fines estadísticos anónimos. Sin un requerimiento, el cumplimiento voluntario por parte de tu proveedor de servicios de Internet, o los registros adicionales de un tercero, la información almacenada o recuperada sólo para este propósito no se puede utilizar para identificarte.
Marketing
El almacenamiento o acceso técnico es necesario para crear perfiles de usuario para enviar publicidad, o para rastrear al usuario en una web o en varias web con fines de marketing similares.
